How to migrate SQL Server instances with permissions to a new server

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:
https://dbatools.io/

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:
https://gallery.technet.microsoft.com/scriptcenter/Use-PowerShell-to-Migrate-86c841df

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.

Conclusion

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.

Virtualization info

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.

Posted in SQL Server, Tips and Tools and tagged , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *