Enable CDC on hundreds of databases

Question

Enable CDC on hundreds of databases

tomes12

SSChasing Mays

Points: 600
More actions
September 30, 2014 at 12:25 pm

#311228

Hi, we host somewhere in the range of 500 - 1000 databases on a SQL 2008 and we're thinking of implementing CDC to replace our current "manual/triggering audit approach".
The problem I see with this so far is that we would have to do this for each of our hundreds of databases, basically meaning hundreds X 2 agent jobs. Now I would assume that the performance hit won't be as big as all those triggers, so we should be fine there, but my main question is, what about those hundreds of agent jobs?
Any way we could consolidate those into only a couple? Or anything else that would make the administration of it easier?
Thx

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kevaburg SSCoach Points: 18131 More actions · Answer 1

I would recommend creating a Central Management Server and registering each of the databases that will have CDC enabled. You can then run a single statement against all of the servers in a single execution.

This will work not only for the enabling of CDC but also any other statement that will need to be run against the group.

tomes12 SSChasing Mays Points: 600 More actions · Answer 2

kevaburg, thanks for your suggestion. I don't see however how I could add particular databases to a server group in CMS. It's correct that I need to manage CDC for hundreds of databases, but it's not ALL the databases on the servers. Am I missing something, or I can really add particular databases in CMS ?

Also, I was thinking I could somehow create an agent job that would be able to act like a "wrapper" or "caller" of all the other hundreds of cdc agent jobs. The cdc agent jobs are nothing else than executing a stored procedure with parameters stored in msdb. The problem is that call to the stored procedure is "ongoing", that is once started it always runs without returning anything, so I can't really loop through and call that stored procedure hundreds of times in one step. Or can I?

Hope I'm making sense.

Thx

kevaburg SSCoach Points: 18131 More actions · Answer 3

Hi,

you could create CMS groups and add the servers that you need to administer CDC against. You don't add a database per se in CMS but rather the server/instance but using the USE command you can specify which database the command should apply to.