Automate migration to SQL Managed Instance using Azure PowerShell and DbaTools

In Azure cloud, you can easily migrate your databases from on-premises or Azure VMs to fully-managed PaaS database service – Azure SQL Managed Instance. In this post you will see how to create PowerShell 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 instance while the databases and server-level objects are copied to 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 offline migration process looks like:

  • You need to create Azure that will be used to temporary hold database backups that will be moved from to Managed Instance.
  • You need to back up the databases to Azure and restore them from Azure to Managed Instance.
  • You need to 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: Assumption is that machine where you are running this script has access both to source SQL Server and target Managed Instance.

Prerequisites

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

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

NOTE: Make sure that you have the latest 150.18147.0 or higher version of Microsoft.SqlServer.SqlManagementObjects 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 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'm using public endpoint to connect to Managed Instance. If you don't have public endpoint, you can setup 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 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 for migration.

Source instance setup

Once you create Azure Blob Storage, you need to generate SAS key that will enable your SQL Server instance to access Azure Blob Storage account and put the database backups there. This SAS key should be stored in the credential object on the source 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 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 version that supports BACKUP TO URL – if not you would need to slightly modify script, backup to local folder and then upload .bak file from local folder to Azure Blob Storage.

DbaTools command that backup database to 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 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 databases you might also want to create backups on multiple URL (striped backups) and set some of the following parameters: COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536. See more info about taking the backups of large databases in Dimitri Furman article.

Migrating databases to target instance

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

## 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, DbMail configurations, logins, etc.

DbaTools provides a set of useful 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 are copied selected object types from the source SQL Server 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 offline migration where you probably need to stop any changes that might happen on the source instance until you finish migration. As an alternative, you can use Database Migration Service and perform online migration.

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.