October 29, 2019 at 9:34 am
Dear Everyone
I have a scenario my management has asked about. We have 2 databases; DB1 running SQL Server 2016 and DB2 is an exact copy of DB1.
If we restore DB2 to another server and keep it there for now. Then we run update scripts on the first server with the original database; the question is can we restore only the DELTA from the first database to the copy database DB2?
Please let me know what people have done or if any tools were utilized.
Thank you in advance
KY
October 29, 2019 at 11:45 am
As you've defined it, can you just capture the delta? No.
Instead, you have to look at additional processes or methods to arrive at this. For modern servers, I recommend looking at Availability Groups. Set up one of those, then any changes made to DB1 will automatically get to DB2. You can make it near real time and synchronous, or asynchronous. Once set up, this is all automatic. DB2 can be a read-only database from that point forward.
Another option would be to get a differential backup of DB1. You can then restore DB2 (and yes, you'll have to run the restore again) from the original full backup and run the differential restore to DB2 to only capture the changes. However, if new full backups of DB1 have been taken, you'll need to use those for the restore process on DB2.
Another approach would be to use point in time recovery to move the logs of DB1 over to DB2. That does again require that DB2 restore get rerun. Then apply all the logs since that backup to move the delta.
An old school approach would be to set up log shipping. Again, you'll have to reset DB2 before this will work. It is however automated after you set it up.
One other option, which I don't recommend, is to set up replication. Once more, DB2 has to be reset. However, you do get a copy of it and all changes made to DB1 will find their way to DB2... except when they change the data structures and then you have to figure out mechanisms for scripting that and/or changing the replication set up.
If the deployment to DB1 was well-packaged and well defined, just apply it to DB2. If you're working out of source control with automated deployment methods (the right way to get this done), it should be easy. If, on the other hand, you need to capture new data added to DB1 as well as structural and code changes, then you're back to one of the other suggestions.
Unfortunately, unless you plan for it and use one of these methods, there is no way to, after the fact, grab incremental data changes for partial restores.
"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
October 29, 2019 at 12:00 pm
Hi Grant
Thank you for the reply.
What we are planning to do is take our existing SharePoint database (5TBs in size) and migrate it to the latest version so management wants to know without any downtime; if we can just take a copy of the production database DB1 and place it on a different server (DB2). Then upgrade the original database and then just capture all changes / deltas to the new database server DB2.
We don't want to use AGs or restore DB1 again just move the changes / delta to DB2 since the SP database with the RBs folder is well over 5 TBs in size so the only options I can utilize from your list are:
log shipping - What do you mean by reset DB2?
transactional replication
The differential restore is a good option but I don't need to restore DB2 again right? Just restore the differentials on DB2 or am I missing something here.
thank you
Kal
October 29, 2019 at 12:10 pm
On the reset, I mean re-restore. To do the log shipping, you can't have recovered the database. It has to be left in recovery. If you already recovered the database, you have to run the full restore again before doing the log restores.
And yes, for an upgrade like this, the way to get it done with minimal (I would never say ZERO) downtime, is log shipping. You can keep backing up logs on DB1 and restoring them to DB2 until you're ready to make the connection switch. Take the last tail log backup of DB1, turn it off at the connection, restore it to DB2, turn it on. Downtime in these scenarios is usually, at most, seconds, but, depending on transaction volume, could be minutes. You have to allow for one database stopping before you switch to the other or you can't ever stop the log restores.
"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
October 29, 2019 at 12:11 pm
Same issue on the differentials. If you run a restore and allow recovery, you can't apply a differential.
"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
October 29, 2019 at 12:36 pm
I checked with the systems team and they mentioned that since they use networker for backups they use incremental backups so we cant use differential 🙁
So the only option is using log shipping or 3rd party migration / sync tools
Thank you Grant
November 4, 2019 at 10:48 am
Grant
does log shipping and PITR work across different DB versions?
my current production is 2012 (windows 2008) and my upgraded will be sql server 2016 (windows 2012).
November 4, 2019 at 4:35 pm
Here's the way I've done such a thing many times now...
Don't get me wrong... this isn't easy either. It DOES take proper planning and pretesting but it's been a whole lot less obnoxious than other methods for us. Done correctly, the total downtime can be measured in minutes even for 5TB and there's no need for update scripts, log shipping or anything. That's IF you can afford the short downtime (say 15-20 minutes).
The other cool part of this method is that if your smoke testing of the new databases proves that something is wrong, you can easily revert back to the new box almost in seconds by having a "non-restore" restore WITH RECOVERY at the ready on the old box.
This may not be right for you but it has worked a treat several times in the past for us. You DO need to do your homework no matter which method you use. For example, do you want to repoint all of your software connections or do you simply want to realias the machine and the SQL Server instance(s)?
"MUST LOOK EYE!" 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2019 at 4:47 pm
Hi Jeff
So you can do this across different sql versions because we are migrating / upgrading from sql server 2012 (production) to sql server 2016 (new server)?
you can apply the log files from 2012 to 2016?
what about the RBS data? How is that affected here?
thank you
KY
November 4, 2019 at 7:05 pm
Hi Jeff
So you can do this across different sql versions because we are migrating / upgrading from sql server 2012 (production) to sql server 2016 (new server)?
you can apply the log files from 2012 to 2016?
what about the RBS data? How is that affected here?
thank you
KY
Yes on your first question... I've used the technique to migrate from 2005 to 2012 and then 2012 to 2016 a couple of years later. You're not actually restoring 2012 log files to a 2016 database because you don't actually have a database until you've done the WITH RECOVERY restores. Neither is it a 2016 database until it completes that recovery of the log files. If you have some large partitioned tables (we had one that had 110 partitions and had more than 9 years of call recordings stored as WAV files in the actual database), it will take a little bit to bring all of those partitions online but it only took a couple of extra minutes.
As for RBS data, I've never had to work with it before but my understanding is that SQL Server only stores the metadata. I don't know if the restores would include the actual RBS data or not but my guess would be that the restores only include the RBS metadata and that you'd have to move the RBS Stores separately (if they need to be moved). I'd definitively setup a small test database on that even if someone comes back with a step-by-step post on how to do it.
The same holds true the method I'm recommending... You absolutely should test it a couple of times before you start setting up for real... which is what I did in all of my migrations using the restore methods.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply