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

Question

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

he katty 1

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.

1 answer

Your answer

Answer 1

Cris Zhan-MSFT 6,661

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

he katty 1 Reputation point

2021-04-19T07:35:34.583+00:00

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?
Cris Zhan-MSFT 6,661 Reputation points

2021-04-19T08:18:23.18+00:00

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.
he katty 1 Reputation point

2021-04-22T06:57:51.763+00:00

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.
Cris Zhan-MSFT 6,661 Reputation points

2021-04-22T08:14:53.24+00:00

>the LSN in the new primary node can be smaller than the old primary node?

No. The LSN from the primary replica in the Always on availability group will be no less than the secondary replica.
he katty 1 Reputation point

2021-04-22T08:35:57.8+00:00

so in this situation, the LSN will become bigger no mater what failover happened?
Cris Zhan-MSFT 6,661 Reputation points

2021-04-22T08:44:27.47+00:00

Do you mean the LSN captured by CDC? I think CDC will work normally and not be affected by failover.

The LSN from the primary replica in the Always on availability group will always be greater than or equal to the LSN on the secondary replica.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-04-22T21:02:24.547+00:00

To add what Cris says: the LSN on the secondary cannot be behind the primary if it is a synchronous replica. The secondary is maintained by applying log records, and a transaction does not commit until it has been written to the transaction log of the secondary That is, the two will always be at the same LSN.

I you have set up the secondary to be asynchronous, the secondary can behind in case of a failover. But you will have to do that failover manually. And by doing so, you are also accepting data loss. That is, the last transactions in the primary will be lost. There is a possibility, I guess, that CDC could have picked up some of these lost changes.

But if you are looking for HA, you would not have an asynchronous replica.
he katty 1 Reputation point

2021-05-10T02:42:53.687+00:00

and if i have an application to read changed data in sqlserver with Always On availability groups and replication mode, now i connect primary node which enabled CDC ,but after failover, the CDC job will run on new primary node, if i still connect old primary node, cloud i get the changed data? should i change to connect new primary node?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-10T21:08:51.257+00:00

You should connect to the listener which will route you to the current primary.

Share via

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

1 answer

Your answer