question

hekatty-5113 avatar image
0 Votes"
hekatty-5113 asked ErlandSommarskog commented

sqlserver CDC is always running? when node is failove on always on cluster

recently, i enable CDC on sqlserver with always on cluster, but when the primary is down, then there can be a change, then the secondary can be primary and primary will be secondary when it is up, but i see the CDC disappeared , and if i cannot capture the data change with the original primary node connection.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered ErlandSommarskog commented

Hi,

According to your description, I think what you said "always on cluster" refers to the Always On availability groups. Using CDC with an availability group, you need to create CDC jobs on the primary replica or possible primary replica (failover), and only enable it on the primary replica.

Please check the document- Replication, change tracking, & change data capture - Always On availability groups.

You should create the jobs at the new primary replica after failover. The CDC jobs running at the old primary database should be disabled when the local database becomes a secondary database. Post this if the replica becomes primary again, you need to reenable the CDC jobs on the replica. To disable and enable jobs, use the @enabled option of sp_update_job (Transact-SQL). For more information about creating CDC jobs, see sys.sp_cdc_add_job (Transact-SQL).

In addition here is a post about how CDC jobs are always enabled only on the current primary replica.
https://dba.stackexchange.com/questions/139231/cdc-with-alwayson-availability-groups

· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thany you,but if both CDC and replication are enabled, after failover, the CDC can still work? do i still run job to monitor whether CDC is running on primary node?

0 Votes 0 ·

Hi,
As described in the above document, if both CDC and replication are enabled for the databases in the availability group, the log reader will replace the CDC capture job to handle the population of the CDC change tables. After failover, the CDC will still work, there is no need to create capture jobs on other replica.

0 Votes 0 ·

thank you at all, and when the CDC still works after failover, what happened with the LSN number? the LSN in the new primary node can be smaller than the old primary node (now becomes slave node)? Because i am using cdc to get the latsest data by comparing LSN number , if the LSN in the new PRIMARY node can be smaller than old one, then i will not get the latest data.

0 Votes 0 ·
Show more comments