Automate migration to SQL Managed Instance using Azure PowerShell and DbaTools

In Microsoft Azure, you can easily migrate your databases from SQL Server on-premises or Azure VMs to the fully-managed PaaS database service Azure SQL Database Managed Instance. In this post you will see how to create PowerShell scripts that automate migration to Managed Instance using Azure PowerShell and DbaTools.

The approach used in this article is OFFLINE migration. You should stop the activities on your source SQL Server instance while the databases and server-level objects are copied to the target Managed Instance. If you need ONLINE migration that will enable you to use your source databases while they are moving to Managed Instance, take a look at the Database Migration Service.

Typically, the steps involved in an offline migration process are:

• Create an Azure Blob Storage account that will be used to temporary hold the database backups that will be moved from SQL Server to Managed Instance.
• Back up the databases to Azure Blob Storage and restore them from Azure Blob Storage to Managed Instance.
• Migrate server-level objects such as logins, agent jobs from the source to destination instance.

In this article, I will use Azure PowerShell to create and manage necessary Azure resources, and DBATools PowerShell library to initiate migration.

NOTE: This procedure is based on the assumption that the machine where you are running this script on has access both to the source SQL Server and the target Managed Instance.

Prerequisites

Before you begin, you should make sure that you have installed all necessary PowerShell libraries. The following PowerShell modules are used:

Install-Module Az.Resources
Install-Module Az.Storage
Install-Module dbatools

NOTE: Make sure that you have the latest 150.18147.0 or higher version of Microsoft.SqlServer.SqlManagementObjects installed or these commands will fail. If you don't install anything, DbaTools will include the latest version of SMO. However, if you have the older version and include it in the PowerShell session, the script will fail.

Parameter definition

As a first step, populate the parameters that define the names of source/target instances and temporary Azure Blob Storage account used in this sample:

# temporary resources needed for backups
$location = "westus"
$resourceGroup = "temp-migration-demo-rg"
$blobStorageAccount = "temp-migrationdemostorage"
$containerName = "backups"
 
# source and target instances
$sourceInstance = "SOURCESQLSERVER"
$sourceDatabase = "WideWorldImporters"
 
$targetInstance = "targetmi.public.920d05d7463d.database.windows.net,3342"
$targetDatabase = "WideWorldImporters"

Change these parameters depending on your needs. In this sample, I am using a public endpoint to connect to Managed Instance. If you don’t have a public endpoint, you can set up a Point-to-site connection or run the script from the virtual machine that is placed in the same subnet as your Managed Instance.

NOTE: Make sure that you use the same region where your Managed Instance is placed because this will speed-up the database restore process.

Storage setup

In order to move your databases, you need to create a temporary Azure Blob Storage account. If you already have one, you can skip this step.

The following PowerShell script creates an Azure Blob Storage account that will be used in this sample:

New-AzResourceGroup -Name $resourceGroup -Location $location
 
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroup `
-Name $blobStorageAccount `
-Location $location `
-SkuName Standard_LRS `
-Kind StorageV2
 
$ctx = $storageAccount.Context
New-AzStorageContainer -Name $containerName -Context $ctx -Permission Container

Once you execute this script, you will have Azure Blob Storage account ready to be used in the migration.

Source instance setup

Once you create an Azure Blob Storage account, you need to generate a SAS key that will enable your SQL Server instance to access the Azure Blob Storage account and put the database backups there. This SAS key will be represented as a Credential object in SQL Server instance:

$sas = (New-AzStorageAccountSASToken -Service Blob -ResourceType Object -Permission "rw" -Context $ctx).TrimStart('?')
# Note: it must have r permission!
 
$sourceCred = New-DbaCredential -SqlInstance $sourceInstance `
                           -Name "https://$blobStorageAccount.blob.core.windows.net/$containerName" `
                           -Identity "SHARED ACCESS SIGNATURE" `
                           -SecurePassword (ConvertTo-SecureString $sas -AsPlainText -Force)

Now you are ready to back up your SQL Server databases to Azure Blob Storage.

Database migration

With the prepared Azure Blob Storage account and credentials, you can take the backups of you SQL Server databases and put them to Azure Blob Storage. I will assume that you have SQL Server 2012 (11.x) SP1 CU2 or higher version that supports BACKUP TO URL. If not, you would need to modify the script slightly, by taking a backup of the database to a local folder and then uploading the .bak file from the local folder to Azure Blob Storage container.

The DbaTools command that backup your database to the temporary Azure Blob Storage account is shown below:

Backup-DbaDatabase -SqlInstance $sourceInstance -Database $sourceDatabase `
                   -AzureBaseUrl "https://$blobStorageAccount.blob.core.windows.net/$containerName" `
                   -BackupFileName "WideWorldImporters.bak" `
               `   -Type Full -Checksum -CopyOnly

You can put a comma-separated list of databases that you want to migrate in the parameter -Database. Once you complete this step, you will have the backups of all databases that are ready to be restored on Managed Instance.

If you are taking a backup of a large database you might want to create a striped backup by using multiple URLs as target, and set of the following options: COMPRESSION, MAXTRANSFERSIZE = 4194304, and BLOCKSIZE = 65536. Please refer to Backing up a VLDB to Azure Blob Storage for more information on how to use these options.

Migrating the databases to the target instance

Now you need to setup the target Managed Instance where you need to create SAS token that will enable the Managed Instance to read .bak file from Azure Blob Storage, create a credential with this SAS token, and restore the database:

## Generate new SAS token that will read .bak file
$sas = (New-AzStorageAccountSASToken -Service Blob -ResourceType Object -Permission "r" -Context $ctx).TrimStart('?') # -ResourceType Container,Object
 
$targetLogin = Get-Credential -Message "Login to target Managed Instance as:"
$target = Connect-DbaInstance -SqlInstance $targetInstance -SqlCredential $targetLogin

$targetCred = New-DbaCredential -SqlInstance $target `
                           -Name "https://$blobStorageAccount.blob.core.windows.net/$containerName" `
                           -Identity "SHARED ACCESS SIGNATURE" `
                           -SecurePassword (ConvertTo-SecureString $sas -AsPlainText -Force) `
                           -Force
Restore-DbaDatabase -SqlInstance $target -Database $targetDatabase `
                   -Path "https://$blobStorageAccount.blob.core.windows.net/$containerName/WideWorldImporters.bak"

This script will prompt for the SQL login details that should be used to access Managed Instance.

As a result, you will get your database restored on the target Managed Instance and your database migration will be completed.

Server-level objects migration

Once you migrate your database objects, you might also need to migrate server-level objects such as Agent jobs/operators, Database Mail configurations, server-level logins, etc.

DbaTools provides a set of useful scripts that you can apply to migrate these objects using Copy-Dba commands:

Copy-DbaSysDbUserObject -Source $sourceInstance -Destination $targetInstance -DestinationSqlCredential $targetLogin
Copy-DbaDbMail -Source $sourceInstance -Destination $targetInstance -DestinationSqlCredential $targetLogin
Copy-DbaAgentOperator -Source $sourceInstance -Destination $targetInstance -DestinationSqlCredential $targetLogin
Copy-DbaAgentJobCategory -Source $sourceInstance -Destination $targetInstance -DestinationSqlCredential $targetLogin
Copy-DbaAgentJob -Source $sourceInstance -Destination $targetInstance -DestinationSqlCredential $targetLogin
Copy-DbaAgentSchedule -Source $sourceInstance -Destination $targetInstance -DestinationSqlCredential $targetLogin
Copy-DbaLogin -Source $sourceInstance -Destination $targetInstance -DestinationSqlCredential $targetLogin -ExcludeSystemLogins

In this sample selected object types from the source SQL Server are copied to target Managed Instance. You can change this part and select the objects that should be migrated.

Cleanup

If your Azure Blob Storage account is temporary storage that you need to use only while you are moving databases, you can remove these resources using the following PowerShell command:

Remove-AzResourceGroup -Name $resourceGroup -Force

Conclusion

Azure PowerShell and DBATools PowerShell libraries enable you to easily script and automate and customize all parts of the migration process.

This type of migration is an offline migration where you probably need to stop any changes that might happen on the source instance until you finish the migration. As an alternative, you can use Database Migration Service and perform an online migration.