July 4, 2017 at 4:41 am
The Production Publication Database is very large and is running on SQL Server 2008 R2. The Subscriber Database is in our Staging Area and is running SQL SERVER 2014. The Subscription is out of sync with the Publisher do to someone deleting rows from the Subscription Database. My main concern is that I don't want to put our production environment under stress(i.e. CPU, I/O) to create a new Snapshot to reinitialize the subscription. I'm thinking since the subscriber is just a staging area the best approach to sync up the Publisher and Subscriber is to reinitializing the subscription with a backup. I realize that any SQL Objects in the Subscriber database that have not been move to production yest will need to be saved off and re-applied by the developers. But I'm thinking that this is the path of least effect on our production environment:
My steps:
1) Script off the Publications
2) drop the publication
3) remove the subscription
4) copy the production database backup to the subscriber server
5) restore the production database
6) run the script to rebuild the publication(remember to check the property to reinitialize with backup)
7 rebuild the subscription.
You opinions matter greatly!
July 4, 2017 at 11:13 am
Sue
July 4, 2017 at 11:35 am
Thank you!
July 4, 2017 at 11:42 am
Sue_H - Tuesday, July 4, 2017 11:13 AMWhen you initialize from a backup, you need to create the publication first and then do a backup. Otherwise any replicated commands after the backup won't be captured - they won't be picked up until you create the publication so the LSNs won't match up.
You would also want to consider disabling the distribution clean up jobs so that no commands get deleted before it's all setup. After the subscriber has all the commands and is in sync, enable the jobs again.
In addition to setting the Publication to Allow initialization from backup, when creating the subscription you also need to specify that sync type is initialize with backup.
And then you should test it somewhere first.Sue
Given that this is a production database replicating to a staging database, would you go this route or would you consider reinitializing the subscription? I haven't executed the validation yet to see how out of sync the subscriber database is. I discovered this problem yesterday and was thinking if I could discover who deleted the rows from the subscriber database, I could possibly insert the missing rows again and avoid all of this.
July 4, 2017 at 6:30 pm
ericwenger1 - Tuesday, July 4, 2017 11:42 AMGiven that this is a production database replicating to a staging database, would you go this route or would you consider reinitializing the subscription? I haven't executed the validation yet to see how out of sync the subscriber database is. I discovered this problem yesterday and was thinking if I could discover who deleted the rows from the subscriber database, I could possibly insert the missing rows again and avoid all of this.
You are reinitializing in a sense - it's just being done from a backup. Doing it from a backup does relieve some load off the production server. When you generate a snapshot, there are points where it will have locks on the published tables. Initializing from backups is often necessarily when the database is large and/or there isn't an adequate maintenance window to generate the snapshot. I would look at the size of the database, the activity on it and what kind of maintenance windows I have, how long it has taken before to reinitialize, will any locking of any tables during the process cause problems, etc. Those are the things I would look at but you need to be the judge of what to do based on those things, the company, sometimes the politics, etc.
If I needed something safe in all of those areas, I would use the backup. I don't think I'd go down the road of inserting missing rows...you can try that but those types of things can also backfire pretty easily. People tend to minimize (or deny) whatever changes they made to break things. I've fixed things before doing that and I've also made things worse so I generally stay away from doing it. I hope you have an environment where you can test it first.
Sue
July 4, 2017 at 6:41 pm
I guess the good news is, this subscription is not really a production subscription. It's staging. I've only been at this job for about a month and I'm not sure I would have recommended replication to a staging database and development database. Refreshing these environments, yes, but, not replicating to them.
Thanks for your help.
July 4, 2017 at 7:03 pm
ericwenger1 - Tuesday, July 4, 2017 6:41 PMI guess the good news is, this subscription is not really a production subscription. It's staging. I've only been at this job for about a month and I'm not sure I would have recommended replication to a staging database and development database. Refreshing these environments, yes, but, not replicating to them.Thanks for your help.
I totally agree. It is just way too much overhead for staging. Setting up a nightly refresh job using a restore is so much easier. Addressing issues with that is pretty simple and nothing like fixing replication issues. And it's a chance to test out the production backups as well. Just makes more sense. One of those things to talk them into after you've been there a bit longer.
Sue
July 4, 2017 at 7:28 pm
Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply