We recently virtualized a client’s server environment. We’ll write more about virtualization, general concepts, at a later date.
As part of that, we had to migrate 3 SQL Server instances to a new server. This was made significantly simpler with a Powershell Script.
DBATools – Powershell Scripts to Migrate SQL Instances
Download and install the DBA Tools here:
I installed and ran the PS scripts on the destination SQL Server (see below)
The documentation for DBATools is not really up to date. In fact, as of this writing (11/15/2017), their “getting started” page said:
We’ve outgrown this Getting Started Page!
This Getting Started page blows
That made me chuckle. I read a few other articles on the tool in question and was able to make it work successfully.
The specific tool/script is covered here:
The purpose of this blog is to more explicitly provide my specific usage and fill-in some blanks that may not be apparent.
Two SQL Servers
Names have been changed to protect the innocent. But it will make sense.
Migrating/upgrading from SQL 2008 to SQL 2014
Our source server is named, SourceSQL. Our new/destination SQL Server is named, DestSQL – clever right? The SourceSQL was a couple different versions of SQL Express 2008. After some research, I installed SQL Express 2014 on DestSQL. All the vendor documentation indicated this should work fine.
It had three instances to move:
- Instance01 or SourceSQL\Instance01
- Instance02 or SourceSQL\Instance02
- Instance03 or SourceSQL\Instance03
I created three like-named instances on DestSQL. One vendor wanted me to append the new instance name with “_2014,” so I did.
So, now on DestSQL, I had the following empty instances:
- Instance01_2014 or DestSQL\Instance01_2014
- Instance02 or DestSQL\Instance02
- Instance03 or DestSQL\\Instance03
Create a migration share
On the destination server, DestSQL, I created a hidden share. I named it:
- SQLMig$ – DestSQL\SQLMig$
The $ hides the share. No particular reason except I try to keep administrative function shares hidden to avoid clutter.
Running the script
The script has a LOT of command line options. I wanted to bring everything over – including the SQL users and security configuration – meaning, the “sa” credentials.
-BackupRestore versus -DetachReattach
Initially I used the -DetachReattach option – but it failed due to permissions. Rather than troubleshoot that, I opted for the -BackupRestore option. Creating the above referenced share was faster than researching -DetachReattach. It worked perfectly.
Here is powershell command – moving instance01
Start-SqlMigration -Source SourceSQL\Instance01 -Destination DestSQL\Instance01_2014 -BackupRestore -NetworkShare \\DestSQL\SQLMig$
It worked perfectly, first time out. It doesn’t really provide any status updates but I could see the backup files being created on the share, so I knew it was working.
I was able to stop the old SQL server and repoint the client’s at the new server. Some of the vendors had specific software to install on the server in addition to just the database. But that was fairly straight-forward and specific to each software vendor.
This was a move from a physical server (SourceSQL) to a virtual server running on Hyper-V (DestSQL). After the migration was successful, we repurposed the SourceSQL to be a bare install running Hyper-V and copied the DestSQL virtual machine from it’s temporary (staging) server, back on to the superior hardware (cpu, drives, and memory) on SourceSQL (now renamed).
We’ll discuss the virtualization process on a later blog.
If you found this helpful, let me know. If you have questions, ask and I will try to get to them.