We help IT Professionals succeed at work.

CDC and AOG on MS SQL 2012

109 Views
Last Modified: 2018-03-21
hi,

  I am assessing a new SQL server 2012 with SP3 and the next security patches, it has AOG of just 2 x servers with CDC, any configuration best  practice should we follow?

 As CDC need to read the log, it can impact operation and I am not sure any concern when CDC work with 2012 AOG ?
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The transaction log is replicated to the secondary replicas so this should be transparent.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

you mean CDC is stable and robust enough to work in AOG servers and all replica in the group ?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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!
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
actually if I need to execute ola's index rebuild solution, by this sense, only on primary replica, make sense ?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 !
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.