2

I was reading this article about SQL Server log shipping and the statement was made that log shipping supports limited read-only access to secondary databases (during the interval between restore jobs).

The article doesn't go on to mention any other limitations, if any, which apply to the secondary database. Specifically, I would like to know if 1) CDC could be enabled on the secondary database; 2) Could triggers be applied to some tables on the secondary database.

Essentially I'm trying to determine, via T-SQL, for any given table, what rows were inserted, updated and/or deleted during the last restore job.

SQL Server 2014 is being used. Also note that CDC is not enabled on the primary and the company is adamant that it not be enabled for the primary. However, they would have no issues enabling it for the secondary, if it's possible.

Improve this question
2
  • This could be a duplicate? I know you can't put the triggers on the read only secondary, but I believe you can do the CDC. Check out this link: dba.stackexchange.com/a/22652/112253
    – Shaulinator
    Jul 10, 2018 at 19:21
  • @Shaulinator - I not convinced this is a duplicate. I cannot tell from that question if CDC on the secondary is permitted in this case, and if it's permitted when it's not enabled on the primary.
    – Randy Minder
    Jul 10, 2018 at 19:41

1 Answer 1

Reset to default
2

For log shipping, yes you can put it in read only between restores with a standby file.

However you won’t be able alter the database regardless of whether you are thinking of CDC or triggers. Also if you think about how CDC works it was created based off of replication technology. It has a log reader that reads out of the transaction log, that’s different to simply restoring a log backup which is what log shipping is doing.

You could potentially use transactional replication instead then have CDC fire on the subscriber - I have seen this a couple of times. The caveat with this approach is that if you have to re-sync your subscriber from the publisher you will either end up with duplicate data in the CDC tables or missing data - depending if you initialise from a snapshot or backup. So worth exploring your requirements and setting up some tests to go through the different scenarios.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.