By: John Martin | Comments (3) | Related: > Azure Backup and Restore
Problem
You have identified the databases and systems that can be moved to Azure SQL Database Managed Instances, verified the feature compatibility. But how do we make use of Native Backups to get our databases to the cloud easily and efficiently?
Solution
One of the key features of Azure SQL Database Managed Instances is that it is possible to take a native backup from SQL Server and restore this directly to a Managed Instance. In this tip, we will look at the process which will allow us to easily migrate one, some, or many databases using a method that we are familiar with.
Migration Process
The migration process for getting databases to Managed Instance via Backup and Restore is very similar to how we currently move databases between SQL Servers. At the most basic level it is a backup to a shared storage location, then a restore from there to the target system. However, there is one key stipulation, that storage location is Azure Blob Storage.
Stage 1 is to complete a SQL Server backup to Azure Blob Storage, if the source is SQL Server 2012 CU2 or higher then you can leverage the Backup to URL capability within SQL Server. However, if you are migrating from a version of SQL Server that is prior to SQL Server 2012 CU2 then you will need to backup locally and then copy the files to Azure Blob Storage.
Stage 2 is then simply a case of restoring the database from the backup that is in Azure Blob Storage. This restore process uses the native T-SQL, however, only a limited subset of WITH commands are supported. This backup process differs from SQL Server in that it is sent to the Managed Instance and executed there. If the connection from the T-SQL client to the Managed Instance fails then the restore will not continue to completion.
Prerequisites
Prior to completing the backup to Azure Blob Storage, the databases need to be set to full recovery model and have Service Broker enabled. Full recovery is required because Managed Instance will provide a point in time recovery for databases hosted on the service. As with normal SQL Server, this necessitates full recovery for Log Backups to be taken to facilitate Point in Time recovery. In the case of Service Broker, this is because it is on by default for databases hosted on Managed Instance.
It is possible to restore databases that are TDE enabled or make use of backup encryption that was introduced with SQL Server 2014. In these cases, the certificates and keys need to be installed onto the target Managed Instance. However, that is out of the scope here, but I will cover that in a future tip.
How Do You Migrate Databases to Azure SQL Database Managed Instance?
The following outlines the process:
- Configuring Azure Blog Storage
- Different ways the backups can be created and moved to the Blog Storage
- Lastly perform the database restore
Configuring Azure Blob Storage
Prior to taking the database backups, it is important to set up the Azure Blob Storage so that we can store the backups there. This step involves creating the Storage Account, creating a container, and setting permissions so that we can access the storage location. These tasks can be completed several different ways including, but not limited to, PowerShell, Azure CLI, or the Portal.
PowerShell – Create Storage Account and Container
# Define properties that will be used for creating the Azure Resources.
$resourceGroupName = "mssqltips_rg"
$storageAccountName = "mssqltipssa"
$storageAccountType = "Standard_LRS"
$azureRegion = "EastUS"
$storageContainerName = "databasebackups"
$policyName = "mssqltips_Policy"
<#
Assumptions.
- Resource Group already exists
#>
# Create Storage Account
$storageAccount = New-AzureRmStorageAccount -Name $storageAccountName -ResourceGroupName $resourceGroupName -Type $storageAccountType -Location $azureRegion
# Get Storage Account keys and define a new storage context, this facilitates container creation.
$accountKeys = Get-AzureRmStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccount.StorageAccountName
$storageContext = New-AzureStorageContext -StorageAccountName $storageAccount.StorageAccountName -StorageAccountKey $accountKeys[0].value
# Create blob container that will be the location for SQL Server backups.
$container = New-AzureStorageContainer -Context $storageContext -Name $storageContainerName
$cbc = $container.CloudBlobContainer
PowerShell – Define Access Policy
# Define an access policy # Setting start time to prior to now so that it can be used immediately. $policy = New-AzureStorageContainerStoredAccessPolicy -Container $container.Name -Policy $policyName -Context $storageContext -ExpiryTime $(Get-Date).ToUniversalTime().AddYears(10) -Permission "rwld" -StartTime $(Get-Date).ToUniversalTime().AddHours((-1)) $sas = New-AzureStorageContainerSASToken -Policy $policyName -Context $storageContext -Container $container.Name # Get details for shares access policy that has been created. $policy = new-object 'Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy' $sas = $cbc.GetSharedAccessSignature($policy, "$policyName")
PowerShell – Generate Credential Statements
# Create T-SQL for SQL Server credentials.
# $tsql2012 applies to SQL Server 2012 and 2014
# $tsql2016 applies to SQL Server 2016 and above, as well as Managed Instance.
$tSql2016 = "CREATE CREDENTIAL [{0}] WITH IDENTITY='Shared Access Signature', SECRET='{1}'" -f $cbc.Uri,$sas.Substring(1)
$tsql2012 = "CREATE CREDENTIAL [BackupToURL] WITH IDENTITY='{0}', SECRET='{1}'" -f $storageAccount.StorageAccountName,$accountKeys[0].value
# Print credential scripts to the window.
Write-Host $tSql2016
Write-Host $tsql2012
Backup to URL: Pre-SQL Server 2012 CU2
Prior to SQL Server 2012, there is no native facility to backup directly to Azure Blob Storage via T-SQL. To achieve this, we first need to backup to a disk or file share location, then we need to copy the backup files to Azure Blob Storage. The easiest way to complete this task is to make use of PowerShell and the DBATools module. By doing this we can complete the SQL Server backup, and push to blob storage all with one easy script.
PowerShell – Backup and move to Azure
# Define script parameters $localBackupDirectory= "\\winsql-0\Scratch" $databases = "Adventureworks2014" $sqlServer = "winSQL-0" $containerName = 'databasebackups' # Define storage account details $storageAccount = Get-AzureRmStorageAccount -ResourceGroupName $resourceGroupName -Name $storageAccountName $context = $storageAccount.Context # Backup database to local storage Backup-DbaDatabase -SqlInstance $sqlServer -BackupDirectory $localBackupDirectory -Database $databases -Type Full -CompressBackup:$true # Copy backup files to Azure Blob Storage $backupFiles = Get-ChildItem -Path $localBackupDirectory foreach($f in $backupFiles) { Set-AzureStorageBlobContent -File $f.FullName -Container $containerName -Blob $f.Name -Context $context }
The script above first defines the local storage location $localBackupDirectory that we will use. Then we complete the initial backup, followed by copying the files that were created in the location to Azure blob storage. Finally, the script generates the restore commands that we will need later when we restore the databases to our Managed Instance.
Backup to URL: SQL Server 2012 CU2 and 2014
SQL Server 2012 introduces the capability to perform a native backup directly to URL with a T-SQL Statement. In order to facilitate this, we first need to create a Credential object that we will specify as part of the backup command. In order to create the SQL Server Credential, we will need the name of the Azure Storage Account that we created earlier, as well as the key. This will then allow SQL Server to access the storage account to store the backup files in the Storage Account.
Below are three different ways you can create the Credential.
Create Credential with T-SQL
CREATE CREDENTIAL [AzureBlobStorage] WITH IDENTITY = ' mssqltipssa', SECRET = '<Storage Account Access Key>';
Create Credential with DBATools in PowerShell
New-DbaCredential -SqlInstance winsql-2 -Name 'AzureBlobStorage' -CredentialIdentity ' mssqltipssa' -Password (ConvertTo-SecureString '<Storage Account Access Key>' -AsPlainText -force)
Create Credential with Invoke-SqlCmd
You can also use the variables defined in the storage setup script to create the credential on the source and target servers via PowerShell. Replace the server name and the appropriate variable name as needed for your servers.
# Create credential on the server where we take databases from Invoke-Sqlcmd -ServerInstance winsql-0 -Database master -Query $tSql2016 Invoke-Sqlcmd -ServerInstance winsql-0 -Database master -Query "select * from sys.credentials"
Once the Credential has been Created we are able to take the database backup directly to URL. This is very similar to a normal BACKUP DATABASE command, however, in this case, we need to use URL in place of FILE and specify the Credential we created a moment ago.
Here are two ways the backup can be created.
Backup Database to URL with T-SQL
BACKUP DATABASE MyDatabase
TO URL = ‘https://mssqltipssa.blob.core.windows.net/databasebackups/MyDatabase.bak'
WITH
COMPRESSION,
INIT,
FORMAT,
STATS = 5,
CHECKSUM,
CREDENTIAL = 'AzureBlobStorage'
;
GO
Backup Database to URL with DBATools in PowerShell
Backup-DbaDatabase -SqlInstance winsql-2 -AzureBaseUrl ‘https://mssqltipssa.blob.core.windows.net/databasebackups/’ -AzureCredential AzureBlobStorage -Type Full -Database MyDatabase
Backup to URL: SQL Server 2016 and Above
SQL Server 2014 and higher can natively backup to URL much like SQL Server 2012. However, with SQL Server 2014 the format for the CREATE CREDENTIAL statement changed. It no longer needs the Storage Account Name and key. In place of this, the name of the Credential needs to be the path to the container, and the SAS Token for the account needs to be supplied.
Below are two different ways you can create the Credential.
Create Credential with T-SQL
CREATE CREDENTIAL [https://mssqltipssa.blob.core.windows.net/databasebackups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<Shared Access Token>'
;
GO
Create Credential with DBATools in PowerShell
New-DbaCredential -SqlInstance winsql-4 -Name 'https://mssqltipssa.blob.core.windows.net/databasebackups' -CredentialIdentity 'SHARED ACCESS SIGNATURE' -Password (ConvertTo-SecureString '<Shared Access Token>' -AsPlainText -force)
Once the Credential has been Created we are able to take the database backup directly to URL. This is very similar to a normal BACKUP DATABASE command, however, in this case, we need to use URL in place of FILE and specify the Credential we created a moment ago.
Again, here are two ways the backup can be created.
Backup Database to URL with T-SQL
BACKUP DATABASE MyDatabase TO URL = 'https://mssqltipssa.blob.core.windows.net/databasebackups/MyDatabase.BAK' WITH INIT, FORMAT, COMPRESSION, CHECKSUM, STATS = 5 ; GO
Backup Database to URL with DBATools in PowerShell
Backup-DbaDatabase -SqlInstance winsql-4 -AzureBaseUrl https://mssqltipssa.blob.core.windows.net/databasebackups/ -Type Full -Database MyDatabase
Restoring Databases to Azure SQL Database Managed Instance
With the backups in place, it is now possible to restore them on to the Azure SQL Database Managed Instance. Whereas when restoring databases onto other SQL Servers there is a need to specify WITH MOVE when the storage subsystem differs from the source. Because the underlying storage system is abstracted, the Managed Instance will handle the distribution and location of the database and log files on available storage.
Before it is possible to restore the database(s) a credential needs to be created that will allow access to the Azure Blob Storage for the backup files. This credential uses the same syntax as that for SQL Server 2016 and above.
Create Credential with T-SQL
CREATE CREDENTIAL [https://mssqltipssa.blob.core.windows.net/databasebackups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<Shared Access Token>'
;
GO
Restore Database
Now it is a simple case of issuing a basic RESTORE DATABASE command to the Managed Instance.
RESTORE DATABASE MyDatabase FROM URL = 'https://mssqltipssa.blob.core.windows.net/databasebackups/MyDatabase.BAK' ; GO
With the databases now restored it is a simple case of making the changes to the applications that need to connect to the database. If the application you are using is not built to support the use of Azure Active Directory Authentication, then SQL Authentication will need to be used for connectivity.
It is also important to remember the connectivity options for Managed Instance, it is only accessible via the vNet that it has been deployed to.
Once the database has been migrated then you will need to migrate server scoped entities such as logins, linked servers, credentials, etc. that are needed for the functioning of the database in the context of its solution.
Next Steps
- Check out these additional resources:
About the author
John Martin is a Data Platform Engineer working in the financial sector as well as Director-at-Large for the PASS organisation and currently a Microsoft Data Platform MVP.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
