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.
3 Answers
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.
-
-
AFAIK, TDE-encrypted databases are supported as well. The certificates will be migrated as well.– alrocJan 30, 2020 at 19:48
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.
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
.
-
The SQL Server Upgrade wizard only applies for in-place upgrades, which the OP Is not going to be doing (and I recommend against).– alrocJan 30, 2020 at 17:42