Microsoft SQL Server, RDS for SQL Server, Azure SQL Database, and Azure Managed Instance sources

Source preparation

• •For your SQL Server source type, verify that you use a SQL Server edition and version that Database Ingestion and Replication supports. See the KB article FAQ: What are the supported sources and targets for Cloud Data Ingestion and Replication service.
• •Grant the permissions required to enable SQL Server CDC and replicate data. See SQL Server permissions.
• •For database ingestion and replication incremental load and combined load jobs that have SQL Server sources and use Log-based or CDC Tables capture method, you must enable SQL Server Change Data Capture (CDC) on the source database.
• - For Amazon Relational Database Service (RDS) for SQL Server sources, log in as the master user and run the msdb.dbo.rds_cdc_enable_db 'database_name' stored procedure.
• - For all other SQL Server sources, run the sys.sp_cdc_enable_db stored procedure in the database context. You must have the sysadmin role.
After SQL Server CDC is enabled on the database and source tables, SQL Server writes additional information to the transaction log and CDC tables, which Database Ingestion and Replication uses during incremental CDC processing.
Alternatively, when you create a database ingestion and replication task, you have the option of generating a script that enables CDC on the database and on all columns in the selected source tables. To execute the CDC script, you need the appropriate permissions.
Restriction:
Database Ingestion and Replication cannot enable CDC for tables that contain more than 1019 columns.
• •For incremental load and combined initial and incremental load jobs that use Query-based capture method, the source table must contain the CDC query column that is used to indicate the changed rows. You must add the query column to the source tables before creating the database ingestion and replication task. The CDC query column type must be equivalent to timestamp, without a time zone. The supported SQL Server data types for the query column are DATETIME and DATETIME2.

Usage considerations

• •Database ingestion and replication jobs support all load types for on-premises SQL Server, Amazon RDS for SQL Server, Azure SQL Managed Instance, and Azure SQL Database sources.
• •Database Ingestion and Replication provides the following alternative capture methods for SQL Server sources in incremental load or combined initial and increment load jobs:
• - CDC Tables. Capture change data from the SQL Server CDC tables only. Users must have at least SELECT permission on the source and CDC tables. SQL Server CDC must be enabled on the source tables.
• - Log-based. Captures change data from the SQL Server transaction log and CDC tables. Database Ingestion and Replication reads data changes from the SQL Server transaction log and the enabled SQL Server CDC tables. This method requires users to have extended privileges. SQL Server CDC must be enabled on the source tables.
Note:
If you select this capture method for jobs that have Azure SQL Database sources, change data is read from CDC tables only, not from the transaction log.
• - Query-based. Capture Inserts and Updates by using a SQL WHERE clause that points to a CDC query column to identify the rows with insert or update changes. Configuration of the source database is limited to adding the CDC query column to each source table. Users must have at least read only access to the source tables.
The CDC Tables option provides the best replication performance and highest reliability of results.
If you use the Log-based method for multiple jobs that run against the same database simultaneously, performance can be significantly degraded.
For more information about each capture method, see Change capture mechanisms for SQL Server sources.
• •When you enable CDC on the SQL Server database for a task that uses Log-based CDC, SQL Server automatically creates a capture job and a cleanup job that will be executed by the SQL Server Agent. The capture job is responsible for populating the SQL Server CDC tables. The cleanup job is responsible for cleaning up records from the CDC tables. The default value for data retention in the CDC table is 72 hours, or 3 days. You can check the current retention period by running the sys.sp_cdc_help_jobs stored procedure and checking the retention value in the results. If you expect a downtime greater than 3 days, you can adjust the retention in the sys.sp_cdc_change_job stored procedure or in the SQL Server Agent cleanup job. You can also suspend the cleanup job.
• •Database Ingestion and Replication supports SQL Server page compression and row compression of source data.
• •Because Database Ingestion and Replication expects each source table row to be unique, Informatica recommends that each source table have a primary key. Database Ingestion and Replication does not honor unique indexes in place of a primary key. If no primary key is specified, Database Ingestion and Replication treats all columns as if they are part of the primary key. Exception: For SQL Server Query-based CDC, a primary key is required in each source table.
• •For Log-based CDC with the transaction log, Database Ingestion and Replication requires read-write access on the source database. If you use SQL Server Always On availability groups, this requirement means that Database Ingestion and Replication can capture change data from the read-write primary replica but not from the read-only secondary replica.
• •If a Microsoft SQL Server source uses the Always Encrypted method to encrypt column data, the CDC script that is generated from the CDC Script field on the Source page in the database ingestion and replication task fails to run. This problem is caused by a SQL Server limitation. This problem does not occur with Transparent Data Encryption (TDE).
• •Database Ingestion and Replication supports schema drift options for Microsoft SQL Server sources in database ingestion and repoliclationincremental load jobs. The following limitations apply:
• - Microsoft SQL Server does not support renaming tables and columns for which Change Data Capture (CDC) is enabled.
• - Microsoft SQL Server does not support changing primary keys for CDC tables.
• - When Database Ingestion and Replication reads change data directly from the CDC tables, the CDC tables do not change once they are created. DDL changes that occur on the source tables are replicated as nulls in the CDC tables. To replicate the DDL changes to the CDC tables, set the pwx.custom.sslr_cdc_manage_instances custom property to 1 on the Source page of the task wizard. This custom property enables the modification of the CDC tables to reflect DDL changes on the source tables and enhance DML capture. You must have the db_owner role to enable the active management of the CDC tables.
• •If source table partition changes cause rowset IDs to change, Database Ingestion and Replication can process the changes to enable database ingestion and replication jobs to continue capturing DML changes from the tables.
• •For incremental load and initial and incremental load jobs that use the Query-based CDC method, the following limitations apply:
• - A primary key is required in each selected source table. If a primary key is not present in a source table, change data capture ignores the table and continues processing with the rest of the selected source tables. If none of the source tables have a primary key, the job will fail.
• - Query-based CDC does not capture Delete operations.
• - All Insert and Update operations are treated as Upserts and displayed in the monitoring interface and logs as Updates.
• - Data replication from large-object (LOB) columns is not supported. If the source table contains LOB columns, Database Ingestion and Replication propagates nulls for these columns.
• - If a Daylight Savings Time change or a time zone change is detected at the start of a particular cycle or when the job resumes from a failed or stopped state, Database Ingestion and Replication will resume and process the changes that occurred in that cycle.
• - If you use a serverless runtime environment, you can use Query-based CDC only in jobs that have a cloud data warehouse or database target, such as Amazon Redshift, Databricks, Google BigQuery, Microsoft Azure Synapse Analytics, Snowflake, Oracle, PostgreSQL, or SQL Server. If you use a non-serverless environment, you can use Query-based CDC with Kafka or with any supported cloud data warehouse, data lake, or database target type.
• •Database ingestion and replication jobs can replicate data from Microsoft SQL Server large-object (LOB) columns if you select Include LOBs under Advanced on the Source page of the task wizard.
The supported target types depend on the load type:
• - For initial load jobs: Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, and SQL Server.
• - For incremental load load jobs: Azure Event Hubs, Databricks, Microsoft Fabric OneLake, PostgreSQL, Snowflake, and SQL Server.
• - For combined initial and incremental load jobs: Databricks, Microsoft Fabric OneLake, PostgreSQL, Snowflake, and SQL Server.
LOB data types are GEOGRAPHY, GEOMETRY, IMAGE, VARBINARY(MAX), VARCHAR(MAX), NVARCHAR(MAX), TEXT, NTEXT, and XML. LOB data might be truncated before being written to the target. The truncation point, depends on the data type, target type, and load type. For more information, see the "Include LOBs" description in Configure a Microsoft SQL Server source.
• •Database Ingestion and Replication does not replicate data from SQL Server non-persisted computed columns. For initial load jobs and for incremental load and combined initial and incremental load jobs that use the Log-based or Query-based CDC method, persisted computed columns are replicated to the target. For incremental load and combined initial and incremental load jobs that capture changes from CDC Tables only, persisted computed columns are replicated as NULLs or as an empty value, depending on column nullability.
• •Database ingestion and replication initial load jobs that have a SQL Server source and a SQL Server target and include a sql_variant source column convert the sql_variant data to hexadecimal format on the target. To convert data from hexadecimal format to varbinary format, run the following query:
SELECT <column_name>, CONVERT(varbinary,<column_name>) from <table_name>;
Replace <column_name> and <table_name> with the actual target column and table names.
• •The SQL Server Hierarchyid data type is not supported in database ingestion and replication incremental load and combined initial and incremental load jobs that have a Snowflake target. Database Ingestion and Replication will propagate nulls for columns that have this data type. For more information, see Default data-type mappings.
• •If you use a Secure Agent group with multiple agents and the active agent goes down unexpectedly, database ingestion and replication jobs can automatically switch over to another available agent in the Secure Agent group. The automatic switchover occurs after the 15 minute heartbeat interval elapses. For database ingestion and replication jobs that have a SQL Server source, automatic switchovers are subject to following limitations:
• - Jobs cannot have persistent storage enabled.
• - Jobs that have Kafka targets must store checkpoint information in the Kafka header. For any jobs that existed before the July 2025 release, automatic switchovers can't occur because checkpoint information is stored in the checkpoint file in the Secure Agent.
• - Jobs that use the Query-based CDC method must have an Amazon Redshift, Databricks, Google BigQuery, Microsoft Azure Synapse Analytics, Oracle, PostgreSQL, Snowflake, or SQL Server target.
• •If a SQL Server source is in an Always On availability group, database ingestion and replication incremental load and combined load jobs can capture change data from transaction logs or CDC tables on the primary node or a secondary node. Also, if a node becomes unavailable, database ingestion and replication jobs can fail over to a primary or secondary database in an availability replica to continue processing, provided that you've configured the SQL Server connection to point to an availability group listener. An availability group listener is a virtual network name (VNN) that Database Ingestion and Replication can use to access a database in an availability replica of an availability group, without having to know the SQL Server physical instance name.
• •After a database ingestion and replication job with an SQL Server source has run, if you add or select more source columns for replication and redeploy the task, the job does not immediately re-create the target table with the additional columns or replicate data for them. If you set the schema drift Add Column option to Replicate, the job can add new columns to the target and replicate data to them when it processes the next new DML change record. An initial load job adds the new columns to the target and replicates data to them the next time the job runs.
If you use the CDC Tables method, after you add a column to a source table, DML changes are not captured for the newly added column until the existing cdc.<capture instance>_CT table is updated to include the new column. To work around this limitation, you can perform the following steps:
1. 1Stop the job.
2. 2Run the DDL that adds the column. to the source table.
3. 3Ceate a new cdc.<capture instance>_CT table for the source table.
4. 4Populate the new cdc.<capture instance>_CT table with entries from old one.
5. 5Delete the old cdc.<capture instance>_CT table instance.
6. 6Restart the job
Alternatively, you can ask the DBA to delay performing DML updates until the new cdc.<capture instance>_CT table has been re-created with the new column.
• •SQL Server memory-optimized tables are supported as sources in database ingestion and replication incremental load and combined initial and incremental load jobs that use the Query-based CDC method. However, memory-optimized tables are not supported as sources in jobs that use the CDC Tables or Log-based CDC method.
• •If your SQL Server sources include persisted computed columns, database ingestion and replication incremental load and combined load jobs can replicate the computed column expression values, regardless of which CDC method you use.

SQL Server permissions

Prerequisite for SQL Server CDC enablement

• •For RDS for SQL Server, login as the master user and run the following command:
USE <DB>;
EXEC msdb.dbo.rds_cdc_enable_db '<DB>';
• •For other supported SQL Server types, ensure you have the sysadmin role and run the following command:
USE <DB>;
EXEC sys.sp_cdc_enable_db;

Permissions required to replicate data

• •If you use the default CDC Tables method, grant the following permissions on SQL Server CDC tables:
USE <DB>;
GRANT SELECT ON cdc.<capture_instance> TO <user>;
GRANT SELECT ON cdc.ddl_history TO <user>;
To be able to manage CDC tables, run the following command:
USE <DB>;
EXEC sp_addrolemember 'db_owner', '<user>';
• •If you use the Log-based method, grant the following permissions:
USE master;
GRANT SELECT ON master.sys.fn_dblog TO <user>;
GRANT VIEW SERVER STATE TO <login_name>;
USE <DB>;
EXEC sp_addrolemember 'db_owner', '<user>';
• •If you use the Query-based method, grant only the same minimum permissions shown above for initial load jobs.

Change capture mechanisms for SQL Server sources

Change capture with CDC tables only

• •The Informatica Intelligent Cloud Services Secure Agent, which can run on a Linux or Windows box that is local to or remote from the SQL Server instance.
• •The SQL Server CDC tables: cdc.lsn_time_mapping, cdc.ddl_history, and cdc.<capture instance>_CT. SQL Server creates these tables in the CDC schema when the source database is enabled for CDC. One or two cdc.<capture instance>_CT tables are created for each CDC-enabled source table to store DML changes captured by the native log reader.
• •The CDC Capture-Instance Table Reader, which reads change records from the cdc. <capture_instance>_CT tables.
• •The CDC Writer, which writes the changes to the target.

1. 1The CDC Capture-Instance Table Reader reads changes from a cdc. <capture instance>_CT table.
2. 2The CDC Capture-Instance Table Reader sends change records to the CDC Writer.
3. 3The CDC Writer formats the change records and applies them to the target.

Log-based change data capture with transaction log and CDC tables

• •The Informatica Intelligent Cloud Services Secure Agent, which can run on a Linux or Windows box that is local to or remote from the SQL Server instance.
• •The dbo.$InfaXact table, which is associated with an open transaction and temporarily stores some information about the currently running capture job. If the table does not exist, the change capture process creates it.
• •The Log Reader and its subcomponents, which are required to parse and read DML and DDL change records from the transaction log.
• •The SQL Server fn_dblog() function, to which the Log Reader loops calls to retrieve log records from the active part of the transaction log file for the source database.
• •The SQL Server CDC tables: cdc.lsn_time_mapping, cdc.ddl_history, and cdc.<capture instance>_CT. SQL Server creates these tables in the CDC schema when the source database is enabled for CDC. One or two cdc.<capture instance>_CT tables are created for each CDC-enabled source table to store DML changes captured by the native log reader.
• •The CDC Capture-Instance Table Reader, which reads change records from the cdc. <capture_instance>_CT tables.
• •The CDC Writer, which writes the changes to the target.

1. 1The Log Reader process reads log records to capture DML and DDL changes in committed transactions.
• - The Transaction Manager subcomponent interacts with the dbo.$InfaXact table associated with the open transaction.
• - The Log Parser subcomponent loops calls to the fn_dblog() function to read log records from the active transaction log for the selected source tables that are enabled for CDC.
Capture processing continues until the committed transactions are complete, the capture process is stopped or interrupted by a fatal error, or a switch to reading change records from a cdc.<capture_instance>_CT table is triggered.
2. 2In certain situations, Database Ingestion and Replication automatically switches to reading changes from a cdc. <capture instance>_CT table by using the CDC Capture-Instance Table Reader.
Processing switches to the cdc.<capture instance>_CT table under the following conditions:
• - During initialization and startup of the capture process if the start LSN is earlier than the LSN of the active transaction log and exists within the cdc.<capture instance>_CT table.
• - Transaction log truncation occurs, causing data loss. Normally, SQL Server prevents log truncation when an open transaction is associated with dbo.$InfaXact . However, a lost network connection might end a transaction, causing log truncation.
Note:
Routine log backups can also truncate the transaction log. To prevent data loss, transactions that use the dbo.$InfaXact table lock the active transaction log.
• - Off-row LOBs or primary row records are truncated. In this situation, the Log Reader selectively patches the log records by reading column information from the cdc.<capture instance>_CT tables.
Note:
Records read from fn_dblog() function are truncated to 8000 bytes.
3. 3The Log Reader and CDC Capture-Instance Table Reader send change records to the CDC Writer.
4. 4The CDC Writer formats the change records and applies them to the target.

Query-based change capture

• •CDC Method. Select Query-based to enable this capture method.
• •CDC Query Column Name. The case-sensitive name of the CDC query column in the source table. The supported SQL Server data types for the query column are DATETIME and DATETIME2. The column must be present in the source table.
• •CDC Interval. The frequency of a query-based change data capture cycle. Default is 5 minutes.
• •Initial Start Point for Incremental Load. The point from which you want to start the change capture cycle. Default is Latest Available.

1. 1After the CDC interval elapses, Database Ingestion and Replication executes a SQL query in the source database that uses the CDC query column to extract the change data.
2. 2Change records are sent to the CDC Writer.
3. 3The CDC Writer formats the change records and applies them to the target.