replication--replication with CDC and mirroring

Both replication and CDC use LogReader to read data changes from the log, and then write to the distribution library (copy) or the change table.

1> Create a replication publication individually

In this case, the log Read Agent job is created at the Distributor (Servername_dbname_logreaderid)

2> uses the CDC alone

In that case, two jobs ' CDC will be created on the agent of the current instance. Dbname_capture ' (call Sys.sp_mscdc_capture_job to read log) and ' CDC. Demodb3_cleanup ' (call Sys.sp_mscdc_cleanup_job to clean up expired change history)

3> Create a replication publication before using the CDC

A log Read Agent job is created at the Distributor when replication is created, and the job ' CDC is created on the agent of the current instance only when using the CDC. Demodb3_cleanup '

4> First Use CDC, then create replication

When using CDC, two jobs ' CDC is created on the agent of the current instance. Dbname_capture ' and ' CDC. Demodb3_cleanup ', and then deletes the job ' CDC ' When creating a replication publication. Dbname_capture ', and then create a log Read Agent job at the Distributor.

When the last replication publication on the database is deleted, if the database is still using CDC, the replicated log Read Agent job is deleted and a CDC job is created. Dbname_capture '.

CDC jobs are not deleted when the CDC is disabled on the last table on the database, and CDC-related two jobs are deleted if the CDC is disabled at the database level.

MSDN says:

When the Log Reader Agent is used to change both data capture and transactional replication, the replicated changes are first written to the distribution database. The captured changes are then written to the change table. The two operations are submitted together. If there is any lag time when writing to the distribution database, there will be a corresponding lag time before the changes appear in the Change table.

Summarize:

1. When replicated and CDC are used together, the CDC uses the replicated log Read Agent job, which, when read to the log, prioritizes the data to the distribution library and then writes the data to the CDC's History change table.

2. No matter how many tables the CDC is enabled or how many publications are created, only one job on a database can read the log.

3. By default, logs need to wait for replication and the CDC process to complete before they are synchronized to the mirror end.

JD DBR (Andy Xiao Cut) adds:

CDC and replication use together there are some puzzling bugs, and these bugs can not be fully reproduced, do not know why, so do not recommend the use of both.

===============================================================================================

Andy Xiao mentioned: in the replication and CDC environment, the direct deletion of the last release will cause LogReader to be deleted, even if the CDC recreated capture, but also the error, seemingly LSN lag errors, resulting in CDC failure;

Test 1:

Step: Build transactional replication and CDC, transactional replication uses table TB2,CDC to use table TB1 to delete replication publications while inserting data into table TB1.

Results: The replication publication was successfully removed, the new capture job was created, and the data in the history tracking table was not lost (all operations were written to the history table during the operation)

PS: The scene of the Shaoda problem is similar to that of Test 1.

Test 2:

Step: Enable CDC on table TB1, then create snapshot replication on table TB1, then delete snapshot replication (UI action)

Result: Delete failed

WORKAROUND: After disabling the CDC on tables and databases, you can delete snapshot replication normally

Test 3:

Step: Enable the CDC on the table TB1, then create a transactional publication on the table TB1, and create a subscription that ensures that the subscription runs successfully and deletes the replication publication while the table TB1 is inserted into the data.

Results: The replication publication was successfully removed, the new capture job was created, and the data in the history tracking table was not lost (all operations were written to the history table during the operation)

Test Summary:

Failed to Shaoda the problem in the test, due to the limitations of the test, Shaoda said the problem still need to consider

Solution conjecture:

If you press Shaoda's LSN problem, can you disable replication before deleting replication, and then delete the publication, which may reduce the likelihood of problems appearing?

=================================================================================================

There is a replication problem on one server, log read problems, restart log Read agent still error, the following errors are prompted:

Error message: The
process cannot perform "sp_replcmds" on "Win-sl4un7fj2uj\sql_master". (Source: MSSQL_REPL, Error Number: mssql_repl20011)
Get help: http://help/MSSQL_REPL20011
failed to insert rows in the change data capture change table. See previous errors in the current session to determine the cause and correct any associated problems. (Source: MSSQLSERVER, error Number: 22863)
Get help: The http://help/22863
process cannot perform "sp_replcmds" on "Win-sl4un7fj2uj\sql_master". (Source: MSSQL_REPL, Error Number: mssql_repl22037)
Getting Help: http://help/MSSQL_REPL22037

After investigation, some of the tables in the release opened the CDC function.
Solution:

1. Disabling the CDC on tables and databases

2. Reinitialize Subscriptions

3. Open the database and the CDC on the table

=================================================================================================

Reference Links:

1> Disable table on CDC http://msdn.microsoft.com/zh-cn/library/bb510702 (v=sql.105)

2> disable data on the CDC http://msdn.microsoft.com/zh-cn/library/bb522508 (v=sql.105). aspx

Source Text connection: http://www.cnblogs.com/TeyGao/p/3543656.html