CDC and AOG on MS SQL 2012

The transaction log is replicated to the secondary replicas so this should be transparent.

hi,

you mean CDC is stable and robust enough to work in AOG servers and all replica in the group ?

I don't use CDC so I can't answer that but if you're concerning about the transaction log, then I can say no worries about that since the transaction log is replicated to all replicas.
Did you try to test the CDC with AG?

"then I can say no worries about that since the transaction log is replicated to all replicas."

you mean the uncommitted one also replicated to all replicas ? as CDC has to lock the transaction log to read information to audit sth.

"Did you try to test the CDC with AG?"

This already been setup in my client's site. I am sure replication and CDC together create a lot of trouble, which make user database from time to time getting full, and this is by design can't do anything on it . that's why i ask that.

Here I want to turn off the CDC as they are using IDera compliance manager, much less workload.

you know I really want to suggest MS to implement the tempdb log proportional fill modelling to tempdb log, use database log, the problem right now is , log access in sequence. not in round robin manner. then this kind of log access/locking problem can be gone!

actually if I need to execute ola's index rebuild solution, by this sense, only on primary replica, make sense ?

Correct. You can't execute reindex task in a Secondary Replica but executing it in the Primary Replica it will be replicated to others replicas so no worries about it.

"I still not get what you want to mean with "round robin". You keep saying that in few questions but I can't understand why your big focus on that."

forget about that.

tks.


ok, should I say actually the user database setup for AOG needs to be in full recovery mode? not just secondary replica,right ?

so when replicating to secondary replica, but before it replicate to secondary replica the transaction log of primary user database will be lock for replicate to secondary repilca.

ok, should I say actually the user database setup for AOG needs to be in full recovery mode? not just secondary replica,right ?

All databases in an AG group need to be in FULL Recovery mode. If they aren't then they can't be added to an AG, meaning that if they already belong to an AG they're already in FULL Recovery mode.

so when replicating to secondary replica, but before it replicate to secondary replica the transaction log of primary user database will be lock for replicate to secondary repilca.

These are details that you shouldn't worry about. AlwaysOn will take care of everything so it will be very transparent for the user. You shouldn't notice these kind of locks.

"You shouldn't notice these kind of locks."

what I am worrying about is, some case, replication, cdc still on the same box of one of the AOG member, the primary one e.g. then still log problem.

I can't assure about CDC because I never used the feature but in general you shouldn't worry.
Can't you perform some tests with CDC in an AG before using it in a Production environment?

it is  a general overview concern on AOG, it is hard to try it here.  just for information only !

but what I can say which is true is , CDC + replication , is a nightmare to the user database has CDC turned on.

no one on earth can solve that and that's why I prefer SQL server change the way it handle the tempdb and user database log, both in proportional fill model.

then in case one log file locked for CDC and replication at the same time, other log file serve.

I am so surprised that only you reply me and I think I will disable CDC and use Idera CM. I might need to post one more on what is not good about CDC !