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:
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.
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
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.
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.
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.
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.
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"
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.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.