September 13, 2013 at 9:16 am
Hi all,
I have a Primary Replica A and Secondary B part of the AG Group. CDC is enabled at the Primary and the CDC jobs are created by default when you enable(CDC) on the Primary.
According to the Microsoft article The best practice would be:
You should create the jobs at all of the possible failover targets before failover, and mark them as disabled until the availability replica at a host becomes the new primary replica. The CDC jobs running at the old primary database should be also disabled when the local database becomes a secondary database. 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).
However, When I try to specifically run the sp_cdc_add_job for the Secondary it says the "Could not update the metadata for database 'DBName' to indicate that a Change Data Capture job has been added"
Two questions:
1) Should I script the Job out from ServerA and run it on B?
2) Proposed Solution : (After I sucessfully recreate Jobs on both the replica's)
At every Job execution step add this :
If sys.fn_hadr_backup_is_preferred_replica( @dbname ) = 1 (Checks whether server is primary)
BEGIN
--RUN CDC JOB
END
ELSE
EXIT
Does this make sense? Feedback/suggestions would be appreciated.
September 14, 2013 at 6:00 am
hi,
for adding the jobs i guess that this special procedure updates the user databases too. As on a secondary the databases of the availability group are write protected i guess this is the reason for your error.
After creating the jobs on the primary, do a failover and then try to add the CDC jobs again.
In general I think it's a good idea to enable sql jobs on the secondary replica too and check if it is the primary or not, as you do. This way in case of a failover there is no need of manually updating jobs.
If this works in the special case of the cdc jobs i don't know. I could immagine if you use the snychronous mirroring mode it should not be a problem.
P.S.: did you know that CDC does log the changes only if there is at least 1 secondary replica online? So if you only have 1 secondary and you primary crashes the cdc jobs won't do their job anyway.
September 16, 2013 at 7:06 am
WolfgangE (9/14/2013)
hi,for adding the jobs i guess that this special procedure updates the user databases too. As on a secondary the databases of the availability group are write protected i guess this is the reason for your error.
P.S.: did you know that CDC does log the changes only if there is at least 1 secondary replica online? So if you only have 1 secondary and you primary crashes the cdc jobs won't do their job anyway.
When you Config the primary to be "CDC" enabled all the cdc.schema tables are available and readable in secondary. However the Jobs do not make an appearance in the Secondary and I was wondering about the Best Practices for doing this.
I did not understand your P.S Note stating "So if you only have 1 secondary and you primary crashes the cdc jobs won't do their job anyway"
Why not? Situation : Primary Crashes ---> Secondary up
Job are available on both; Primary and Secondary.
Secondary jobs scan the log files now and update the necessary cdc schema tables. As soon as Primary(which is now secondary) comes up, it re-sync's the tables and maintains the same data as the Primary (Which is the actually the failed over secondary).
September 16, 2013 at 7:29 am
For the job's I cannot say anything else, did not do it myself 😉
As for the PS, have a look at the section Log Reader Agent Modifications HERE
The LogReaderAgent is used for CDC to gather the changes on the database (as it does for replication).
Just try it: take down your secondary and try if you can query the changes on any cdc-tables.
That's the reason we decided to find an alternative way to move on data changes. It's just :w00t:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply