Hello everyone,
We have several SQL Server 2014 instances with databases ranging from 3 to 8 terabytes. We are upgrading our datacenter and we will be moving these databases to new servers with SQL Server 2022. What would you recommend for the best transferring method to minimize or even eliminating downtime? Should we set up a mirror or replication? or just bare the few days to copy the database files to the new location?
Thanks in advance for your help.
I would recommend log shipping:
Once the initial full database backups are restored on the SQL 2022 instances, you can continue log shipping as long as you like, until you're ready to finally switch over. At that time, you'd stop all connections to the SQL 2014 instance, take final transaction log backups there, and restore them on the SQL 2022 instance using the WITH RECOVERY keyword.
Depending on your transaction log backup frequency, and size of the backup files, this last restore and switchover can be done in under 1 minute. I've used log shipping for version upgrades many times, all of them had less than 5 minutes of unavailability.
I prefer not to go through the backup and restore procedure. Each backup takes approximately 4 to 5 days. Is there any way to do this without backup/restore? Like mirroring the DB and switching over?
Your backups should not be taking days - I back up over 10TB's in about 1.5 hours and that is just one database on that one server.
You cannot mirror or set up an AG across versions - so those are out.
What I would do is:
Total downtime will just be the amount of time between the tail-log backup and the restore with recovery.
Now - if it is taking days to copy the backup files to the new data center - that is a different issue. But the same process can be used - you just need to switch over from daily full backups to weekly full backups so you have time to copy the backup file and perform the restores.
If that timing doesn't work out - then you need to figure out how to get the backup files over to the new data center faster. I have even seen processes where the backups are copied to 'tape' - overnighted to the new data center - and restored from 'tape'.
I agree with Jeff's general approach. That's roughly how we do our migrations. (Although, since we are allowed some down time, we stop all apps/activity to the dbs, take a final diff backup, then shut down the original SQL instance. This allows us to avoid having to take and apply log backups.)
Note, too, that you can apply multiple diff backups with norecovery (as long as you are always going forward, of course).
If you are not already taking advantage of compression of your backups, particulalry if the original table data is not page compressed, be sure to look into that as well.
If the version upgrades are supported:
Then yes, AGs are possible:
Assuming the OS version differences are compatible. I've done it migrating from SQL 2012 to 2016.
Just to be clear - unless you already have an AG already created you cannot setup an AG with different versions. Your approach would be to create an AG and configure that across data centers - which is not a trivial exercise.
Then - perform an in-place or rolling upgrade from 2014 to 2022.
Since the OP is actually moving to a new location - it is unlikely that time and effort will be spent on setting up the network to allow cross-site communication as well as replicating AD across sites, setting up the firewall rules, routing - etc.
So yeah - an AG or mirroring are not actually possible here without doing a lot of pre-work to build out an environment where that can be done.
Personally - that is a lot of extra work that will all be torn down after the migration.