April 2, 2015 at 3:03 pm
I saw another post touching on this, and it sort of sounded like people just stayed away from the "mess" entirely 🙂
The MSDN doc makes it sound like after a failover of the primary, the CDC data won't "keep working" on the secondary unless you "To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP <group_name> REMOVE REPLICA. Then add a new secondary replica to the availability group."
SQL AlwaysOn Availability Group:
https://msdn.microsoft.com/en-us/library/hh403414.aspx#Changes
That sounds like a giant pain?
Has anyone else dealt with this?
We have a few CDC tracked tables that we use and the general idea of AlwaysOn I thought was to minimize all the overhead and let things "just work" so your apps just connect and the listener re-routes everything where it needs to go.
It looks like to get this working properly an automated job /trigger would have to wait for a failover event and then kick off tasks to remove and re-add the replica and perhaps start up the CDC job on the secondary?
April 3, 2015 at 2:50 pm
I haven't had to do any of this, but based on what I've ready,
In order to resume the harvesting of changes after failover, the stored procedure sp_cdc_add_job must be run at the new primary to create the local capture job.
The following example creates the capture job.
EXEC sys.sp_cdc_add_job @job_type = 'capture';
[/Quote]
https://msdn.microsoft.com/en-us/library/hh403414.aspx
It wounds like you don't have to do anything except run that stored proc on failover which you should be able to script.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply