Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Replication 9.8.0 HotFix 2
  • All Products

Table of Contents

Search

  1. Preface
  2. Data Replication Overview
  3. Understanding Data Replication
  4. Sources - Preparation and Replication Considerations
  5. Targets - Preparation and Replication Considerations
  6. Starting the Server Manager
  7. Getting Started with the Data Replication Console
  8. Defining and Managing Server Manager Main Servers and Subservers
  9. Creating and Managing User Accounts
  10. Creating and Managing Connections
  11. Creating Replication Configurations
  12. Materializing Targets with InitialSync
  13. Scheduling and Running Replication Tasks
  14. Implementing Advanced Replication Topologies
  15. Monitoring Data Replication
  16. Managing Replication Configurations
  17. Handling Replication Environment Changes and Failures
  18. Troubleshooting
  19. Data Replication Files and Subdirectories
  20. Data Replication Runtime Parameters
  21. Command Line Parameters for Data Replication Components
  22. Updating Configurations in the Replication Configuration CLI
  23. DDL Statements for Manually Creating Recovery Tables
  24. Sample Scripts for Enabling or Disabling SQL Server Change Data Capture
  25. Glossary

User Guide

User Guide

Back Next

Coordination with Microsoft SQL Server Change Data Capture

Coordination with Microsoft SQL Server Change Data Capture

To capture change data from Microsoft SQL Server transaction logs, you must enable SQL Server Change Data Capture for the SQL Server source databases and tables. Native Microsoft SQL Server replication also uses Change Data Capture.
When SQL Server Change Data Capture is enabled, Data Replication leverages the Microsoft SQL Server replication stored procedures for change data replication. These procedures perform the following functions:
  • Force Microsoft SQL Server to log Updates as a pair of Delete and Insert operations. This type of logging makes the before and after images of Update operations available to the Extractor. The Extractor can then accurately replicate the Updates.
  • Force Microsoft SQL Server to log additional information that the Extractor uses to determine the points in the log at which LOB data starts and ends. The Extractor can then correctly replicate LOB columns.
  • Mark change records in the online log for replication. The Extractor reads only the records that are marked for replication from the online log.
    When Microsoft SQL Server produces backup logs and truncates the online log, it retains the records that are marked for replication in the online log. To mark these records as replicated, execute the SQL Server sp_repldone procedure. Thereafter, Microsoft SQL Server can delete the replicated records from the online log during the next backup operation.
    If you use the SQL Server Backup task to back up SQL Server logs, Data Replication by default executes the sp_repldone procedure to prevent unlimited growth of the log.
When Change Data Capture is enabled, Microsoft SQL Server generates CDC capture tables. Data Replication does not use these SQL Server CDC tables for replication because it reads the transaction logs directly. Unless you use native Microsoft SQL Server replication, you can manually drop the SQL Server CDC capture tables.
Review the following information about enabling and disabling Change Data Capture for Microsoft SQL Server sources:
  • To manage Change Data Capture for source databases and tables from the Data Replication Console, run the Server Manager on the Microsoft SQL Server system or on a remote system.
  • If you have Microsoft SQL Server Enterprise Edition, the Server Manager enables Change Data Capture for the mapped source tables when you save the configuration from the Data Replication Console. Typically, you do not need to enable Change Data Capture manually from the Data Replication Console. However, you can view and edit CDC settings for mapped source tables in the configuration on the
    Map Tables
     tab. For more information, see Managing Microsoft SQL Server Change Data Capture Settings. You can also enable or disable SQL Server Change Data Capture by using SQL scripts. For more information, see Sample SQL Scripts for Enabling and Disabling Replicate Mode.
  • If you have Microsoft SQL Server Standard Edition, you must enable Change Data Capture manually from the Data Replication Console. Enable Change Data Capture on the
    Map Tables
     tab for the mapped source tables before you create the replication configuration. Save the SQL script that contains the Change Data Capture statements and run the scripts manually on the server. For more information, see Managing Microsoft SQL Server Change Data Capture Settings.
    A Microsoft SQL Server Standard Edition instance must be restarted for Change Data Capture to be enabled. When enabling Change Data Capture, select the
    Restart instance
     option to have the Server Manager restart the instance. For the Server Manager to restart the instance, you must be connected to the Server Manager Main server as the idradmin user.
    Before you enable Change Data Capture for a Microsoft SQL Server Standard Edition instance, ensure that no dependent services, such as the Microsoft SQL Server Agent, are running on the source system. Active dependent services prevent the Server Manager from restarting the SQL Server instance and enabling Change Data Capture.
  • If you have Microsoft SQL Server Enterprise Edition and select an SQL Server database in the
    Source Schema
     field on the
    Map Tables
     tab that includes tables for which Change Data Capture is not yet enabled, the Data Replication Console creates an auxiliary table in this database and enables Change Data Capture for the auxiliary table. When you save the configuration later, the Data Replication Console drops the auxiliary table and enables Change Data Capture for the mapped source tables. This behavior ensures that the Extractor can extract DDL and DML records starting from the LSN value that corresponds to the point at which Data Replication retrieved metadata from the source database. The metadata retrieval point corresponds to when you selected the source database on the
    Map Tables
     tab. The default name of the auxiliary table is IDR_DUMMY_1. To use another name for the auxiliary table, add the
    mssql_auxiliary_table_name_1
     parameter in the
    DataReplication_installation
    /uiconf/default.cfg file. If the default.cfg file does not exist, use a text editor to create the file in the uiconf directory and then add the parameter in the file. For more information, see Default.cfg File.
    The Extractor does not capture change data from the transactions that were open when the Data Replication Console enabled Change Data Capture for the auxiliary table.
  • The Server Manager stores Change Data Capture settings for the mapped source tables in the Server Manager SQLite database, SM.db3. The Server Manager uses the database ID and table IDs to map source tables and their Change Data Capture settings. The Server Manager updates Change Data Capture settings for mapped source tables when you click the
    Manage Database Supplemental Logging
     icon button on the
    Map Tables
     tab. This opens the
    Manage SQL Server Change Data Capture Settings
     dialog box. From this dialog box, you can manage Change Data Capture settings for Microsoft SQL Server instances. Click
    Refresh
     to update the Change Data Capture settings for the selected schema. For more information, see Managing Microsoft SQL Server Change Data Capture Settings.
    When you create a Microsoft SQL Server database, SQL Server might assign a database ID to it that was previously used for a database that has been deleted. If you previously enabled Change Data Capture for the tables that were in the deleted database, you must update the Change Data Capture settings in the Server Manager SQLite database before creating a replication configuration that maps the tables in the new database. If you do not update this information, the Server Manager might not enable Change Data Capture for the mapped source tables in the new database when you save the configuration in the Data Replication Console.
  • For Microsoft SQL Server Enterprise Edition, SQL Server generates a CDC table for each table for which you enable SQL Server Change Data Capture. SQL Server uses the following pattern to name these CDC tables:
    [cdc].
    owner
    _
    table_name
    _CT_
    object_ID
    . SQL Server also generates two jobs named
    [cdc].
    database_name
    _capture
     and
    [cdc].
    database_name
    _cleanup
     for each database for which Change Data Capture is enabled. Data Replication does not use these SQL Server CDC tables and jobs for replication because it reads the transaction logs directly.
    By default, after the Server Manager enables Change Data Capture for source tables, it does not disable the SQL Server CDC jobs. If you run Data Replication and SQL Server Change Data Capture against the same database, you must disable Data Replication management of the secondary truncation checkpoint on the
    Runtime Settings
     tab >
    General
     view. For more information, see Considerations for Managing the Secondary Truncation Checkpoint. Also, ensure that the Extractor reads both the transaction logs and backup logs to prevent incomplete data capture.
    If SQL Server Change Data Capture or native replication is not active on the source database, ensure that Data Replication management of the secondary truncation checkpoint is enabled on the
    Runtime Settings
     tab >
    General
     view.
    If you want to override the default Server Manager behavior of handling CDC jobs, you can set the Server Manager
    CdcJobsDisableMode
     advanced property to 1 or 2 or select
    Disable only if CDC is not enabled
     or
    Always disable
     from the
    SQL Server CDC Jobs
     list in the
    Microsoft SQL Server Instances
     dialog box.
  • If you drop a Microsoft SQL Server Enterprise Edition source table for which Change Data Capture was previously enabled, Microsoft SQL Server does not drop the corresponding CDC table. If you drop multiple source tables, drop the corresponding CDC tables manually to avoid unnecessary overhead on the source system.
  • To enable Change Data Capture for source tables in a SQL Server Failover Cluster environment, ensure that the SQL Server Network Name matches the local definition of the name of the server on which the SQL Server source database runs. In the Failover Cluster Manager, you can determine the local definition of the server instance by issuing the following query:
    SELECT @@SERVERNAME
    . If the resulting local definition of the server instance does not match the SQL Server Network Name, change the name of the local server by using the sp_dropserver and sp_addserver stored procedures.
0 COMMENTS
We’d like to hear from you!