Migration of SQL Server with PowerShell dbatools #PSBlogWeek

This article is about server management with PowerShell and is part of the #PSBlogWeek series (http://psblogweek.com) , created by Adam Bertram.


  1. Introduction to dbatools
  2. Migration Prerequisites
  3. Best Practices
  4. Migration
  5. References

It is also part of my blog series about migrating our physical SQL Server to a VMware Environment. For now, all of these articles are in German only – sorry. The first three articles describe the basic server configuration, installation, and VM guest configuration of the VMware Environment. This article describes the migration itself.
I’ll write a recap of the whole series in English later on. 🙂

  1. Introduction to dbatools

I got in contact with PowerShell some years ago, but I wasn’t satisfied with what needed to be done to maintain SQL Server.

However, Microsoft has made a lot of improvements since then, and with contributions from several PowerShell Experts and MVPs – such as Chrissy LeMaire, Claudio Silva, Rob Sewell, Constantine Kokkinos and many more, there is now a module that helps to maintain SQL Server 2005+. It’s called dbatools, and you can find it here https://dbatools.io. The project is hosted on GitHub and the module is available totally free of charge!

The dbatools community has grown to over 50 contributors with more than 300 SQL Server best practice, administration and migration commands. An overview of the commands can be found here: https://dbatools.io/functions/.

2. Migration Prerequisites

Now, let’s turn our attention to the prerequisites for the migration of a physical SQL Server 2008 to a VMware-based SQL Server 2016 on Windows Server 2016. The positive thing here was that there was no need to reinstall everything  on the same physical hardware over the weekend. Instead, we bought a totally new VMware Environment with three Dell servers, two network switches, and new storage. There was enough time to test the new SQL Server, the SAN, and build a good configuration for the virtual machines. Most of the VM configuration is based on the blog series „Stairway to Server Virtualization“ by David Klee, which can be found on SQL Server Central.

For migration purposes, we installed an additional Windows Server 2016 with PowerShell 5, with SQL Server 2016 as an admin workstation. On the SQL Server, we installed the dbatools by using the easy install-module command:

# allow execution of scripts that are signed from a trusted author, if not already set
Set-ExecutionPolicy RemoteSigned 

# install 
install-module dbatools

During installation, you may get a confirmation dialog prompting you to accept installation of the NuGet Package Manager. You should accept; otherwise, you’ll need another installation option. These options are described on the dbatools website: https://dbatools.io/download.

The dbatools module is in permanent development – meanwhile, they are near the first major release 1.0 – so you should check for the latest version and update often. Updating is as easy as the installation:

# Get version number
Get-Module -Name dbatools
# Get all available versions that are installed
Get-Module dbatools -ListAvailable

dbatools available versions



On the screenshot we see five versions of the tools installed, so we have to activate the latest version with the comand import-module.

#Import specific version - here 0.9.39
"c:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.39\dbatools.psd1"

# List all available commands of the loaded module version
Get-Command –module dbatools

With the last command above you get a quick overview of all the dbatools commands.

After installation of the base SQL Server VM we need to check some basic configuration options first. dbatools can help us with this as well. 🙂

All commands are created by experts with references to the corresponding articles where the code comes from.

3. Best Practices

  • Max Memory
    • Test-DbaMaxMemory
    • This tests the actual max memory setting against the recommended setting.
    • Test-DbaMaxMemory -SqlServer sqlserver01
      # command to set the max memory to the recommended
      Set-DbaMaxMemory -SqlServer sqlserver01
      # or to a fixed value of 2 GB
      Set-DbaMaxMemory -SqlInstance sqlserver01 -MaxMb 2048
  •  TempDB
    • Test-DbaTempDbConfiguration
    • With SQL Server 2016, you get the option to configure the tempdb configuration during installation, but not with older versions. With this command, you can control and later adjust it.
    • Evaluates tempdb against a set of rules to match best practices. The rules are:
      TF 1118 Enabled: Is Trace Flag 1118 enabled? (See KB328551)
      File Count: Does the count of data files in tempdb match the number of logical cores, up to 8?
      File Growth: Are any files set to have percentage growth? Best practice is that all files have an explicit growth value.
      File Location: Is tempdb located on the C:\ drive? Best practice says to locate it elsewhere.
      File MaxSize Set (optional): Do any files have a max size value? Max size could cause tempdb problems if it isn’t allowed to grow.
    • Test-DbaTempDbConfiguration
    • The right configuration can be set by using the corresponding Set- command
      A service restart is necessary after reconfiguration, see following screenshot:
    • Set-DbaTempDbConfiguration
  • Disk
    • Test-DbaDiskAlignment
      • This command verifies that your non-dynamic disks are aligned according to physical requirements.
      • Test-DbaDiskAlignment -ComputerName sqlserver01| Format-Table
      • Test-DbaDiskAlignment
    • Test-DbaDiskAllocation
      • Checks all disks on a computer to see if they are formatted to 64k block size, the best practice for SQL Server disks.
      • Test-DbaDiskAllocation -ComputerName sqlserver01 | Format-Table
      • Test-DbaDiskAllocation
  • PowerPlan
    • Test-DbaPowerPlan
      • The Power Plan should be set to High Performance on every SQL Server.
      • Test-DbaPowerPlan -ComputerName sqlserver01
      • Test-DbaPowerPlan


  • SPN
    • We use DNS CNAMEs for referring to our SQL Server (See the article „Using Friendly Names for SQL Servers via DNS“ below). We need to adjust the SPN settings manually. That is easy with these commands:
      Get-DbaSpn and Set-DbaSPN
  • SQL Server Name
    • We created a Single VM template where all SQL Server are created from. With CPU, Memory and Disk Layout as described in the Stairway I mentioned above (1).
    • After creating a new VM out of the template the server name changes but the internal SQL Server name does not. Help comes again with dbatools command Repair-DbaServerName
      Works fine for me!

4. Migration

  • Now for the best part – the migration itself. You normally only need a single command to migrate everything from one SQL Server to another. As described in the Help documentation, this is a „one-button click“.
    Start-DbaMigration -Source sql2014 -Destination sql2016 -BackupRestore -NetworkShare \nas\sql\migration
  • This migrates the follwing parts as listed below. Every part can be skipped with a -no*** parameter as described in the Help documentation – for example, use -NoLogins if you don’t want to transfer the logins.
    • SQL Server configuration
    • Custom errors (user-defined messages)
    • SQL credentials
    • Database mail
    • User objects in system databases
    • Central Management Server
    • Backup devices
    • Linked server
    • System triggers
    • Databases
    • Logins
    • Data collector collection sets
    • Audits
    • Server audit specifications
    • Endpoints
    • Policy management
    • Resource Governor
    • Extended Events
    • JobServer = SQL Server Agent
  • If any error comes up, use the functions, that are called out of the Start-DbaMigration commands step by step.
  • Keep in mind that the server configuration is also part of the migration, so min and max memory and all other parameter in sp_configure are transferred. If you want to keep this settings as set by the best practices commands, you should skip the configuration during transfer. Use -NoSpConfigure!
  • So what is missing in the moment?
    • Most of the special parts of the additional services:
      • SSIS
      • SSAS
      • SSRS
  • You can test the whole migration with the -WhatIf parameter, which shows what’s working and what isn’t. Sometimes the connection to the target computer isn’t working because PowerShell remoting is not enabled (see above).
    There is a command to test the connection to the server, and you can find that here:
    There is no need for updating the new server to the latest version of PowerShell, Version 3.0 is enough.
  • The whole command looks like this for me:
    • Start-SqlMigration -Verbose -Source desbsql1 -Destination desbaw2 -BackupRestore -NetworkShare \\DESBAW2\Transfer -DisableJobsOnDestination -Force
  • The parameter DisableJobsOnDestination is extremly helpful when you go to the next step and test the migration itself. When you do this more than once, you also need the parameter –Force, which overwrites the target objects (logins, databases and so on) if they exist from a previous test.
  • The parameter -Verbose is useful when an error comes up and you need to dig deeper into the problem.
  • Before we wrap up, her’s a link to a YouTube video that shows how fast the migration works. Of course it’s all going to depend on the size of your databases:

5. References:

  1. Stairway to SQL Server Virtualization by David Klee
  2. Using Friendly Names for SQL Servers via DNS

Thanks for reading,
Volker Bachmann

5 Gedanken zu „Migration of SQL Server with PowerShell dbatools #PSBlogWeek

  1. Pingback: Project SQL on VMware – Migration from Physical to Virtual | Volker Bachmann and SQL Server

  2. Pingback: #PSBlogWeek – free PDF with my „SQL Server Migration with PowerShell dbatools“ now available | Volker Bachmann and SQL Server

  3. Pingback: #PSBlogWeek 2017 Recap

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.