September 10, 2019 at 1:33 pm
Hello!
What is the efficient method to migration 3.5TB SQL database from 2012 to 2016?
Backup and Restore is not our choice as it would take application down for long time.
Will setting up transaction replication work? Or have server team copy the Data and log files from current server to new server would be better option?
Thanks in advance.
September 10, 2019 at 2:44 pm
I would suggest log shipping.
The key to minimizing downtime is the frequency of your log backups. If they are set to, as an example, every 15 minutes, your downtime can be minimal.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 10, 2019 at 5:51 pm
Backup/Restore is actually a good option - but you need to do a couple of things.
Modify any/all connection strings to direct to new system...total outage can be reduced to a few minutes depending on how many different systems are connecting and need to be changed and how you schedule the changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 10, 2019 at 8:20 pm
You have a few options.
Before considering them, I highly advise restoring a recent backup to the target 2016 system WITH NORECOVERY, then execute RESTORE WITH RECOVERY so you can see how long the upgrade steps take. There have been significant improvements in the upgrade process rolled out in post-RTM Service Packs, so I highly advise patching the 2016 target as far as you can before starting the upgrade.
Log Shipping is simple and 100% effective if done correctly.
1. As suggested above, start by increasing the frequency of your log backups.
2. Write some simple Powershell to copy each log backup file to the new system and restore it with norecovery, then add that as a step in your backup jobs.
3. At cutover time
3a. Disable the log-backup job on the 2012 server
3b. Kick everyone out of the system
3c. Take the final log backup WITH NORECOVERY, which will ensure that no more transactions will commit on the 2012 server after you've taken the final log backup.
3d. Copy the final log backup to the 2016 system and, restore it WITH RECOVERY
3e. When the upgrade is complete, start your post-upgrade steps (resampling statistics, etc.)
LUN Swapping is another useful technique if you are using shared storage, such as from a SAN. This uses detach/attach:
1. (at upgrade time) Detach the to-be-upgraded database from it's SQL2012 instance.
2. Unmap the disks from the 2012 server/cluster
2a. (optional) snapshot the disks for an easy rollback target in case the upgrade fails
3. Map the disks to the 2016 server/cluster
4. Attach the database to the 2016 instance, which begins the upgrade
5. When the upgrade is complete, start your post-upgrade steps (resampling statistics, etc.)
My team has used both Log Shipping and LUN Swapping to upgrade thousands of databases, including several 100TB+ monsters, from SQL2008 to SQL2016. Everything can be scripted.
I would avoid Replication with all of my energy. It's overkill, and introduces more problems than it solves in this case.
-Eddie
Eddie Wuerch
MCM: SQL
September 11, 2019 at 11:52 am
Are you striping your backups? I had a greater than 1TB database and changed it to striped backups. It knocked both the backup and restore time in half. That might give you the time you need to restore the database and subsequent transactions with NORECOVERY, as has been suggested, until the final one. I have migrated databases this way before.
September 28, 2019 at 1:14 pm
you can use a mirroring, if you don't use alwayson. Mirroring is possible with two different version.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply