The change tables CDC method lets users retain logs for a shorter time, and thus
save storage space, but supports lower throughput compared to the transaction
logs method. The method has fewer limitations than transaction logs. For example,
it eliminates the risk of log truncation leading to streams failing permanently,
and supports replication of encrypted tables. For more information, see
Known limitations.
When using this CDC method, changes to the source are tracked using dedicated
change tables. Transaction logs are still used, but to a limited extent, and you
don't need to retain them for longer periods. As DML events are applied to the
source tables, the changes are replicated to the corresponding change tables.
The change tables have the same structure as the source tables, but with extra
columns to include the metadata for the changes. Only committed transactions are
added to the change tables, along with the log sequence number (LSN) of the
commit operation.
Transaction logs
When using this CDC method, Datastream reads changes in the source
directly from transaction logs. This method requires fewer resources and allows
for faster data retrieval, but has more limitations.
To prevent data loss, it's important that the logs aren't truncated before
Datastream reads them. On the other hand, if you keep the log files for
too long, they take up storage space, which might eventually cause the database
instance to enter read-only mode.
To make sure that the CDC reader has enough time to read the logs while allowing
log truncation to free up storage space, you need to apply additional configuration
steps, such as changing polling intervals and setting up a truncation safeguard.
These steps provide an additional layer of protection to ensure that Datastream
can read the data even if there's downtime on the Datastream side or a
connectivity issue between the source database and Datastream.
Datastream supports the following versions and editions of SQL Server
databases:
Self-managed (on-premise or cloud-hosted) using the following versions:
Enterprise: 2008 and later
Standard: 2016 SP1 and later
Developer: 2008 and later
Amazon RDS for SQL Server
Azure SQL Database (tier S3 and above).
Cloud SQL for SQL Server
Datastream doesn't support the following versions of SQL Server databases:
SQL Server Standard edition from version 2008 to 2014
SQL Server Express
SQL Server Web
Known limitations
Known limitations for using SQL Server database as a source include:
Streams are limited to 10,000 tables.
A table that has more than 500 million rows can't be backfilled unless the
following conditions are met:
The table has a unique index.
None of the index columns are nullable.
All columns of the index are included in the stream.
Databases with delayed durability or accelerated database recovery (ADR) enabled
aren't supported.
Streaming changes to system tables isn't supported.
Windows Active Directory (AD) authentication isn't supported.
The following data types aren't supported, and aren't replicated to the destination:
SQL_VARIANT
HIERARCHYID
GEOMETRY
GEOGRAPHY
Datastream replicates user-defined data types, however it's the base
data type from which you derive your user-defined type that is stored in the
destination. For example, if you define a USERNAME data type based on the
VARCHAR(50) data type, the data is stored in the destination as VARCHAR(50).
Datastream doesn't support CDC for large object columns (TEXT,
NTEXT, XML, IMAGE) and maximum variable length columns
(VARCHAR(MAX), VARBINARY(MAX), NVARCHAR(MAX)) in tables without a unique
index.
If large object columns aren't included in the stream, CDC is supported.
Datastream doesn't support replicating source schema changes when
using the change tables CDC method. The following schema changes might cause data
corruption or failure to process the events:
Adding columns: the new columns aren't replicated to the destination.
Dropping columns: data in these columns is replaced with NULL values.
Modifying data types: Datastream tries to insert the data to the
destination and raises an error if the data is rejected.
Renaming columns: not supported for SQL Server when CDC is enabled.
Datastream doesn't support data masking. Data is replicated without
masking.
Datastream doesn't support replicating changes applied to the database
using the Data Tier Application Package (DACPAC) package.
Datastream doesn't replicate changes made using the WRITETEXT or
UPDATETEXT statements.
Datastream doesn't support replicating computed columns unless the column is marked PERSISTED.
Datastream doesn't support PAGE, COLUMNSTORE or COLUMNSTORE ARCHIVE compression types.
Additional limitations when using the transaction logs method
If you use the transaction logs CDC method, the following additional limitations
apply:
Transparent Data Encryption (TDE) isn't supported.
Column Level Encryption isn't supported. Data in these columns is replaced with
NULL values.
Datastream doesn't support the ROLLBACK TO SAVEPOINT statement.
Such rollback events are ignored and aren't replicated to the destination.
Datastream doesn't support CDC for rows greater than 8 KB in the
following types of tables:
Tables without a unique index
Tables containing only a unique, non-clustered index with one or more
variable length columns (VARCHAR, VARBINARY, NVARCHAR)
Datastream doesn't support CDC for large object columns (TEXT,
NTEXT, XML, IMAGE) in the following types of tables:
Tables without a unique index
Tables containing only a unique, non-clustered index with one or more
variable length columns (VARCHAR, VARBINARY, NVARCHAR)
If large object columns aren't included in the stream, CDC is only supported
for such tables if they have valid indexes.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-25 UTC."],[[["\u003cp\u003eDatastream captures data changes from SQL Server databases using either change tables or transaction logs, with change tables offering lower storage needs but slower throughput, and transaction logs providing faster retrieval but with more limitations.\u003c/p\u003e\n"],["\u003cp\u003eSupported SQL Server versions include Enterprise (2008 and later), Standard (2016 SP1 and later), and Developer (2008 and later) for self-managed instances, as well as Amazon RDS, Azure SQL Database (S3 tier and above), and Cloud SQL for SQL Server.\u003c/p\u003e\n"],["\u003cp\u003eThere are limitations when using SQL Server as a source, such as a maximum of 10,000 tables per stream, the inability to backfill tables with more than 500 million rows without specific indexing conditions, and certain data types and features that are not supported.\u003c/p\u003e\n"],["\u003cp\u003eWhen using the transaction logs CDC method, additional limitations apply, including the lack of support for Transparent Data Encryption (TDE), Column Level Encryption, and the \u003ccode\u003eROLLBACK TO SAVEPOINT\u003c/code\u003e statement.\u003c/p\u003e\n"],["\u003cp\u003eSchema changes when using the change tables CDC method such as adding, dropping, renaming, or modifying data types in columns are not supported and can cause data corruption or processing failure.\u003c/p\u003e\n"]]],[],null,["# Source SQL Server database\n\nThis page contains information about:\n\n- The behavior of how Datastream handles data that's being pulled from a source SQL Server database.\n- The versions of SQL Server databases that Datastream supports.\n- Known limitations for using SQL Server database as a source.\n\nBehavior\n--------\n\nDatastream tracks data manipulation language (DML) changes using one\nof the following CDC methods:\n\n- [Change tables](#change-tables)\n- [Transaction logs](#transaction-logs)\n\n### Change tables\n\nThe change tables CDC method lets users retain logs for a shorter time, and thus\nsave storage space, but supports lower throughput compared to the transaction\nlogs method. The method has fewer limitations than transaction logs. For example,\nit eliminates the risk of log truncation leading to streams failing permanently,\nand supports replication of encrypted tables. For more information, see\n[Known limitations](#sqlserverknownlimitations).\n\nWhen using this CDC method, changes to the source are tracked using dedicated\nchange tables. Transaction logs are still used, but to a limited extent, and you\ndon't need to retain them for longer periods. As DML events are applied to the\nsource tables, the changes are replicated to the corresponding change tables.\nThe change tables have the same structure as the source tables, but with extra\ncolumns to include the metadata for the changes. Only committed transactions are\nadded to the change tables, along with the log sequence number (LSN) of the\ncommit operation.\n| **Note:** Datastream only replicates changes from the default capture instance whose name is [`\u003cschema_name\u003e_\u003ctable_name\u003e`](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-capture-instance-ct-transact-sql?view=sql-server-ver16).\n\n### Transaction logs\n\nWhen using this CDC method, Datastream reads changes in the source\ndirectly from transaction logs. This method requires fewer resources and allows\nfor faster data retrieval, but has [more limitations](#tl-limitations).\n\nTo prevent data loss, it's important that the logs aren't truncated before\nDatastream reads them. On the other hand, if you keep the log files for\ntoo long, they take up storage space, which might eventually cause the database\ninstance to enter read-only mode.\n\nTo make sure that the CDC reader has enough time to read the logs while allowing\nlog truncation to free up storage space, you need to apply additional configuration\nsteps, such as changing polling intervals and setting up a truncation safeguard.\nThese steps provide an additional layer of protection to ensure that Datastream\ncan read the data even if there's downtime on the Datastream side or a\nconnectivity issue between the source database and Datastream.\n\nFor detailed instructions on how to apply these additional measures, see the\n[Configure a source SQL Server database](/datastream/docs/configure-your-source-sql-server-database)\npage and select your database type.\n\nVersions\n--------\n\nDatastream supports the following versions and editions of SQL Server\ndatabases:\n\n- Self-managed (on-premise or cloud-hosted) using the following versions:\n - Enterprise: 2008 and later\n - Standard: 2016 SP1 and later\n - Developer: 2008 and later\n- Amazon RDS for SQL Server\n- Azure SQL Database (tier S3 and above).\n\n | **Note:** Only supported when using the change tables CDC method.\n- Cloud SQL for SQL Server\n\nDatastream doesn't support the following versions of SQL Server databases:\n\n- SQL Server Standard edition from version 2008 to 2014\n- SQL Server Express\n- SQL Server Web\n\nKnown limitations\n-----------------\n\nKnown limitations for using SQL Server database as a source include:\n\n- Streams are limited to 10,000 tables.\n- A table that has more than 500 million rows can't be backfilled unless the following conditions are met:\n 1. The table has a unique index.\n 2. None of the index columns are nullable.\n 3. All columns of the index are included in the stream.\n- Databases with delayed durability or accelerated database recovery (ADR) enabled aren't supported.\n- Streaming changes to system tables isn't supported.\n- Windows Active Directory (AD) authentication isn't supported.\n- The following data types aren't supported, and aren't replicated to the destination:\n - `SQL_VARIANT`\n - `HIERARCHYID`\n - `GEOMETRY`\n - `GEOGRAPHY`\n- Datastream replicates user-defined data types, however it's the base data type from which you derive your user-defined type that is stored in the destination. For example, if you define a `USERNAME` data type based on the `VARCHAR(50)` data type, the data is stored in the destination as `VARCHAR(50)`.\n- Datastream doesn't support CDC for large object columns (`TEXT`,\n `NTEXT`, `XML`, `IMAGE`) and maximum variable length columns\n (`VARCHAR(MAX)`, `VARBINARY(MAX)`, `NVARCHAR(MAX)`) in tables without a unique\n index.\n\n If large object columns aren't included in the stream, CDC is supported.\n- Datastream doesn't support replicating source schema changes when\n using the change tables CDC method. The following schema changes might cause data\n corruption or failure to process the events:\n\n - Adding columns: the new columns aren't replicated to the destination.\n - Dropping columns: data in these columns is replaced with `NULL` values.\n - Modifying data types: Datastream tries to insert the data to the destination and raises an error if the data is rejected.\n - Renaming columns: not supported for SQL Server when CDC is enabled.\n- Datastream doesn't support data masking. Data is replicated without\n masking.\n\n- Datastream doesn't support replicating changes applied to the database\n using the Data Tier Application Package (DACPAC) package.\n\n- Datastream doesn't replicate changes made using the `WRITETEXT` or\n `UPDATETEXT` statements.\n\n- Datastream doesn't support replicating [computed columns](https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table) unless the column is marked `PERSISTED`.\n\n- Datastream doesn't support `PAGE`, `COLUMNSTORE` or `COLUMNSTORE ARCHIVE` compression types.\n\n### Additional limitations when using the transaction logs method\n\nIf you use the transaction logs CDC method, the following additional limitations\napply:\n\n- Transparent Data Encryption (TDE) isn't supported.\n- Column Level Encryption isn't supported. Data in these columns is replaced with `NULL` values.\n- Datastream doesn't support the `ROLLBACK TO SAVEPOINT` statement. Such rollback events are ignored and aren't replicated to the destination.\n- Datastream doesn't support CDC for rows greater than 8 KB in the following types of tables:\n - Tables without a unique index\n - Tables containing only a unique, non-clustered index with one or more variable length columns (`VARCHAR`, `VARBINARY`, `NVARCHAR`)\n- Datastream doesn't support CDC for large object columns (`TEXT`,\n `NTEXT`, `XML`, `IMAGE`) in the following types of tables:\n\n - Tables without a unique index\n - Tables containing only a unique, non-clustered index with one or more variable length columns (`VARCHAR`, `VARBINARY`, `NVARCHAR`)\n\n If large object columns aren't included in the stream, CDC is only supported\n for such tables if they have valid indexes.\n\nWhat's next\n-----------\n\n- Learn how to [configure a SQL Server source](/datastream/docs/configure-your-source-sql-server-database) for use with Datastream."]]
