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.
Best Answer
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.