Migrating SQL Server 2019 to a new server

We recently undertook the task of migrating SQL Server 2019 from Windows Server 2016 to a new Windows Server (Both the OS and the hardware were getting dated). During this process, we developed several scripts and thought it would be helpful to share a detailed guide on the steps involved. This blog outlines a structured approach to ensure the safe and efficient transfer of data and configurations.

Pre-Migration Checklist

• Assess and Plan:
  Ensure the new server meets all hardware and software requirements for SQL Server 2019.
  Verify network settings, storage configurations, and security policies.
  Plan downtime (if necessary) and inform stakeholders and gain signoff..
• Document SQL Server Configuration:
  Note down instance-specific configurations such as linked servers, job schedules, security settings, and any custom SQL configuration settings.
  Ensure Compatibility:
  Check compatibility levels of databases to ensure they will operate correctly on the new server.
  Review any deprecated features that might affect the functionality on the new server.
• Backup Server:
  Perform a full backup of the SQL server Windows server for archive purposes..

Migration Process

For our migration, we opted for the "cold" migration process. This method involves copying data files directly from one server to another and can be faster for a large number of databases compared to the traditional backup and restore method. However, it requires careful handling to ensure data integrity and system compatibility.

Steps for "Cold" Migration of SQL Server Databases:

Install SQL Server 2019 on the new Server:

• Install SQL Server 2019 on the new Server, ensuring it matches the configuration of your old SQL server as closely as possible.
• Apply the same SQL Server patches or service packs that were on the old SQL server to avoid version discrepancies.

Reconfigure SQL Server:

• Reconfigure the SQL Server settings (sp_configure) to match the documented settings from your old SQL server.
• Recreate any necessary security settings, linked servers, SQL Server Agent jobs, and any other custom settings.

Transferring the SQL Server databases

Stop the various consumers (websites/portals) of the source SQL Server 2019 to ensure that database files are not being written to during the copying process, minimizing the risk of data loss.

Install the SQL server Powershell module if it is not already installed, this allows PowerShell scripts to interact with the SQL server.

 Install-Module -Name SqlServer -AllowClobber

Backing Up The Database Files:

A batch file "Backup_SQL_Databases.ps1" was created, please modify the serverName, username and passwords variables to match your source SQL servers settings.

Import-Module SqlServer

$serverName = "SERVERNAME"  # Update with your actual server name
$backupDirectory = "C:\SQLBackups"  # Ensure this directory exists and is writable
$username = "sa"  # Update with your SQL Server username
$password = "password"  # Update with your SQL Server password

# Create a ServerConnection object
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($serverName, $username, $password)

# Create a Server object with the specified ServerConnection
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)

try {
    foreach ($db in $server.Databases) {
        if (!$db.IsSystemObject) {
            $dbName = $db.Name
            $timestamp = Get-Date -Format "yyyyMMddHHmmss"
            $backupFile = Join-Path -Path $backupDirectory -ChildPath "$dbName-$timestamp.bak"

            $backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
            $backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
            $backup.Database = $dbName
            $backup.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
            $backup.SqlBackup($server)

            Write-Output "Backup completed for $dbName to $backupFile"
        }
    }
}
catch {
    Write-Error "An error occurred: $_"
}
finally {
    if ($server.ConnectionContext.IsOpen) {
        $server.ConnectionContext.Disconnect()
    }
}

NOTE: To use Windows authentication change the ServerConnection instantiation to use only the server name. And set LoginSecure to $true to enable Windows Authentication.

# Create a ServerConnection object with Windows Authentication
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($NewServerName)
$serverConnection.LoginSecure = $true  # This enables Windows Authentication
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)

An instance of Windows PowerShell was launched with administrator privileges, and the following commands were executed:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Set-ExecutionPolicy RemoteSigned
cd c:\support
./Backup_SQL_Databases.ps1

When the Powershell script was executed the databases were saved to the C:\SQLBackups directory:-

Subsequently, the backup files located in the C:\SQLBackups directory, were saved to a compressed zip archive. This archive was then transferred to the new SQL server that was set up earlier, ensuring the files were available for database restoration.

Restoring The Database Files On The New Server:

A batch file "Restore_SQL_Databases.ps1" was created, please modify the serverName, username and passwords variables to match your destination SQL servers settings.

Please note that each version of SQL Server has a different MSSQLxx identifier in the registry path. Here’s a breakdown of the identifiers:

SQL Server 2012: MSSQL11
SQL Server 2014: MSSQL12
SQL Server 2016: MSSQL13
SQL Server 2017: MSSQL14
SQL Server 2019: MSSQL15
SQL Server 2022: MSSQL16

Import-Module SqlServer

$NewServerName = "SERVERNAME,1433" # Update with your server name to restore to and port
$backupDirectory = "C:\SQLBackups"  # Ensure this directory exists and is writable
$username = "sa"  # Update with your SQL Server username
$password = "password"  # Update with your SQL Server password
$dataDirectory = "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA"  # New data directory
$logDirectory = "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA"   # New log directory

# Create a ServerConnection object with SQL Server Authentication
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($newServerName, $username, $password)
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)

# Function to restore each database
function Restore-Database {
    param(
        [string]$backupFile,
        [string]$dataDirectory,
        [string]$logDirectory
    )

    $dbName = [System.IO.Path]::GetFileNameWithoutExtension($backupFile) -replace '-\d{14}$', ''

    # Setup the restore
    $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
    $restore.Database = $dbName
    $restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
    $restore.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $restore.ReplaceDatabase = $true
    $restore.NoRecovery = $false

    # Retrieve file list from backup
    $fileList = $restore.ReadFileList($server)

    # Relocate each file to the new directory
    $restoreFileOptions = @()
    foreach ($file in $fileList) {
        $logicalFileName = $file.LogicalName
        $fileName = $file.PhysicalName
        $type = $file.Type
        
        if ($type -eq 'D') {
            $newPath = [System.IO.Path]::Combine($dataDirectory, "$dbName.mdf")
        } else {
            $newPath = [System.IO.Path]::Combine($logDirectory, "$dbName.ldf")
        }
        
        $restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logicalFileName, $newPath)))
    }

    try {
        $restore.SqlRestore($server)
        Write-Output "Restore completed for $dbName from $backupFile"
    }
    catch {
        Write-Error "Failed to restore ${dbName}: $_"
    }
}

# Restore each backup file
Get-ChildItem -Path $backupDirectory -Filter *.bak | ForEach-Object {
    Restore-Database -backupFile $_.FullName -dataDirectory $dataDirectory -logDirectory $logDirectory
}

An instance of Windows PowerShell was launched with administrator privileges, and the following commands were executed:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Set-ExecutionPolicy RemoteSigned
cd c:\support
./Restore_SQL_Databases.ps1

Backing up and Restoring the Master Databases

When then used SQL Server Management Studio (SSMS) to back up the master database and then restore it on another server involves several steps. Here’s how we did it.

Step 1: Backup the Master Database

Open SQL Server Management Studio (SSMS) and connect to your source SQL Server instance.

Expand the Databases node, then expand the System Databases node.

Right-click on the master database and select Tasks > Back Up....

In the Back Up Database dialog:

• Backup type: Ensure "Full" is selected.
• Backup component: Ensure "Database" is selected.
• Backup destination: Add or verify the backup file location. By default, a backup to disk is selected. You can add a file path by clicking on the Add... button and specifying the path where you want to store the backup file (e.g., C:\SQLBackups\master_backup.bak).

Options (Optional): Go to the Options page in the dialog:

• Choose Overwrite Media if you want to overwrite any existing backup file.
• Set Backup options like compression if needed.

Click OK to start the backup process. A progress dialog will show the status of the backup. Once complete, a success message will be displayed.

Step 2: Restore the Master Database on Another Server

Restoring the master database is a critical operation and requires the destination SQL Server to be in single-user mode. Follow these steps:

Copy the Backup File: Copy the backup file (master_backup.bak) to a location accessible by the target SQL Server.

Start SQL Server in Single-User Mode:

• Stop the SQL Server service:
• Open SQL Server Configuration Manager.
• Locate your SQL Server instance in the list, right-click on it, and choose Stop.
• Start SQL Server in single-user mode:
• Open a command prompt as an administrator.
• Run the following command (replace MSSQLSERVER with your instance name if it’s not the default instance):

net start MSSQLSERVER /m

Alternatively, you can modify the SQL Server service startup parameters:

• In SQL Server Configuration Manager, right-click your instance, select Properties.
• Go to the Startup Parameters tab, add -m and click OK.
• Start the SQL Server service.

Open SSMS in Single-User Mode:

• Open SSMS and connect to the target SQL Server instance. You might need to use the sqlcmd mode in SSMS.

Restore the Master Database:

In SSMS, open a new query window and execute the following T-SQL commands to restore the master database:

USE master;
GO
RESTORE DATABASE master FROM DISK = 'C:\Path\To\master_backup.bak' WITH REPLACE;
GO

Make sure to replace 'C:\Path\To\master_backup.bak' with the actual path to your backup file.

Restart SQL Server in Normal Mode:

• Stop the SQL Server service again:
• Open SQL Server Configuration Manager.
• Locate your SQL Server instance in the list, right-click on it, and choose Stop.
• Remove the -m startup parameter if you added it earlier.
• Start the SQL Server service normally:

net start MSSQLSERVER

Retrieving the SQL Server Product Key

A batch file "SQLProductId.ps1" was created to retrieve the SQL product Key on the source SQL server, this might not work depending on your SQL configuration. This will only work if your SQL server is a transferable version and not locked to the server hardware.

Please note each version of SQL Server has a different MSSQLxx identifier in the registry path. Here’s a breakdown of the identifiers:

SQL Server 2012: MSSQL11
SQL Server 2014: MSSQL12
SQL Server 2016: MSSQL13
SQL Server 2017: MSSQL14
SQL Server 2019: MSSQL15
SQL Server 2022: MSSQL16

function GetSqlServerProductKey {
    $localmachine = [Microsoft.Win32.RegistryHive]::LocalMachine
    $defaultview = [Microsoft.Win32.RegistryView]::Default
    $reg = [Microsoft.Win32.RegistryKey]::OpenBaseKey($localmachine, $defaultview)
    $keyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Setup"  # Updated registry path

    $subKey = $reg.OpenSubKey($keyPath)
    if ($subKey -eq $null) {
        Write-Output "Registry path not found: $keyPath"
        $reg.Close()
        return
    }

    $encodedData = $subKey.GetValue("DigitalProductID")
    if ($encodedData -eq $null) {
        Write-Output "No DigitalProductID found at the registry path."
        $subKey.Close()
        $reg.Close()
        return
    }

    $subKey.Close()
    $reg.Close()

    try {
        $binArray = [array]($encodedData)[0..66]
        $productKey = $null
        $charsArray = @("B", "C", "D", "F", "G", "H", "J", "K", "M", "P", "Q", "R", "T", "V", "W", "X", "Y", "2", "3", "4", "6", "7", "8", "9")

        $isNKey = ([math]::truncate($binArray[14] / 0x6) -band 0x1) -ne 0
        if ($isNKey) {
            $binArray[14] = $binArray[14] -band 0xF7
        }

        $last = 0
        for ($i = 24; $i -ge 0; $i--) {
            $k = 0
            for ($j = 14; $j -ge 0; $j--) {
                $k = $k * 256 -bxor $binArray[$j]
                $binArray[$j] = [math]::truncate($k / 24)
                $k = $k % 24
            }
            $productKey = $charsArray[$k] + $productKey
            $last = $k
        }

        if ($isNKey) {
            $part1 = $productKey.Substring(1, $last)
            $part2 = $productKey.Substring(1, $productKey.Length-1)
            if ($last -eq 0) {
                $productKey = "N" + $part2
            } else {
                $productKey = $part2.Insert($part2.IndexOf($part1) + $part1.Length, "N")
            }
        }

        $productKey = $productKey.Insert(20, "-").Insert(15, "-").Insert(10, "-").Insert(5, "-")
        Write-Output $productKey
    } 
    catch {
        Write-Output "Error processing the product key: $_"
    }
}

# Call the function to display the product key
GetSqlServerProductKey

When the script was executed in the Powershell, the product id was displayed (redacted).

Post-Migration Steps and Testing

Test the Migration:

• Conduct thorough testing to ensure that all components are functioning as expected. This should include:
• Testing connectivity and performance.
• Running existing applications and queries to validate outputs.Checking integration with other services.

Update Applications:

• Update connection strings and configurations in your applications to point to the new server.

Monitor Performance:

• Monitor the new server closely for any performance issues or unexpected behavior. Use tools like SQL Server Profiler or Performance Monitor to track performance.

Security Audits:

• Perform security audits and updates as necessary to ensure the new system is secure.

Decommission Old Server:

• Once you are confident that the new server is running smoothly and all data has been accurately migrated, plan for the decommissioning of the old server.

Backup New Server:

• Start regular backups on the new server to ensure all new transactions are secured.