Side by Side upgrade and migrate SQL

Question

Here is the story: We want to do side by side SQL upgrade from 2016 to 2019 with a new windows of 2016. My SA wants to robocopy all drives except drive C from the old server to the new server. Do I get any issues with live or offline databases (data and log files) after transfer? will they get corrupted? Please provide me your ideas.

Answer 1

You can only do this "robocopy everything" approach if you stop all services on the source server, and then attach the databases on the destination. But even that won't work 100%, because unless you copy the msdb and master databases as well, which I do not recommend, you won't bring over logins, Agent jobs, and other things like that.

I'm soapboxing a little here but as a DBA, I would not allow a systems administrator who does not have good SQL Server experience to perform the installation and migration of SQL Server, in part because of what I mention above.

Instead, I suggest doing a full migration using the dbatools PowerShell module, specificially the Start-DbaMigration function. The "easy" way of using it is to install your new 2019 instance, make sure that the accounts that both the source and destination services run under (preferably gMSA domain accounts) have access to a common file share, and then run that function with a backup & restore (via that share) to copy the databases.

Start-DbaMigration will pull over the entire instance - databases, security, XE sessions, Resource Governor, Agent jobs, dbmail settings, linked servers - literally everything on the instance will be brought over. You can exclude individual components if you like as well.

Manual migrations leave too much room for error, they're slow, and so 2012. Automation rules in 2020.

Here's a 50-second demo of it.

Answer 2

If the SA is building a new server then you best option is to do a migration not an upgrade (upgrades also do not have a good rollback plan and therefore is not recommended to do in production).

Here is a decent article with migration steps to start with but there may be adjustments for your environment. https://www.mssqltips.com/sqlservertip/1936/sql-server-database-migration-checklist/

Migrations should not be scary but they need everyone's involvement to make sure that if you do run into snags that they get fixed asap and hopefully documented for future reference.

Answer 3

You can Attach/Detach or Backup/Restore user databases but and general data (e.g. jobs) & configuration (e.g. Logins) stored in System Databases, And you can't use the same approach as user databases.

SQL Server migration will completely be done if you do it right with SQL Server Upgrade Wizard but mix it with RoboCopy or any external tools could damage your System Databases.