January 9, 2020 at 8:31 pm
Guys, I have a bit of an odd scenario, I have a customer who requires a primary database to be replicated to a secondary remote site for the purpose of comparing data against the replica of primary if the primary has been corrupted in some way. They currently use log shipping to secondary site with a 2 week delay to t-log restore... so that they have up to two weeks to notice the issue to the primary. They also don’t want any data loss (which is why I am concerned about the 2 week restore delay). I don’t think this is ideal and would suggest real time updates to the replica (to avoid data loss) and point in time backups of the replica. Obviously this cannot be a achieved with taking backups of the replica as it will be in restoring mode due to log shipping?
I thought about transact replication but not all tables have primary keys, which they are not up to changing.
oh they are using 2017 std
Any thoughts appreciated.
Adam
January 10, 2020 at 2:01 pm
Availability Groups will be an easier solution I'd think. Replication is just a big ball of trouble, without going into the fact that they have tables without primary keys (<sigh>). Otherwise, this all sounds like a bit of a mess of a way to protect against corruption. I agree with your approach to have them do the log shipping more frequently, because, yeah, two weeks?
In general, simply a backup and a restore to a secondary server is going to be easier to implement and will absolutely tell you as much about the state of the database as anything else. Further, you can offload the integrity checks to the restored database.
If the goal is a hot stand by, either faster restores as you suggest, or Availability Groups. If the goal is corruption detection, I'd go with the backup approach. Also, I'd separate these two goals so that you can better, and more easily, achieve both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply