Need alerts when the CDC instance goes down
We are using CDC tool in our dev environment. we have created few instances to capture the modifications done at source databases. All are working fine.
Now, we have one requirement that, when the CDC instance goes STOP, LOGGER, ABORTED etc. we should get alter message to know the status of the CDC instance.
Can you please help, is there any way to get these alerts.
Very easy. The status of CDC instances is recorded in their respective CDC DBs. Just write a stored procedure to query the status table and throw an error when status <> 'RUNNNG'. Then schedule the procedure in SQL Server Agent and set it to send an email on error.