Migrating SQL Server Databases to Azure Managed Instances with Native Backups

By:   |   Updated: 2018-04-19   |   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.

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author John Martin 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


Article Last Updated: 2018-04-19

Comments For This Article




Monday, February 12, 2024 - 9:37:08 AM - subahan Back To Top (91939)
Restore replace is not allowed so need to drop the db. and restore it again with new full backup.

Wednesday, June 3, 2020 - 5:57:43 AM - Braj Back To Top (85829)

Hi, 

I have migrated many databases to Azure Managed Instance a while ago from On Prem. Now developers required new copy of all migrated databases. 

How can I restore databases on Azure MI with overwite (WITH REPLACE;) option. Many access have to be provided agaain if do not overwrite. Its big mess

If Itry with 'WITH REPLACE' its does not seems to be supported. (Msg 41901, Level 16, State 2, Line 3One or more of the options (replace) are not supported for this statement in SQL Database Managed Instance. Review the documentation for supported options.)


Tuesday, January 14, 2020 - 2:56:31 AM - Azure Learner Back To Top (83760)

Hi,

I have a scenario to migrate on-premises SQL server 2016 Database migration to Azure SQL database managed instance. The on-premises database has both SQL and Windows logins. I am using Azure Database Migration service to migrate the database with SQL and windows logins. But migration failed while migrating windows logins with below error. (FYI : onpremdomainname and AAD domainname are different)

Failed to migrate login 'onpremdomainname\username'. Principal '_REDACTED_EMAIL_@AADdomainname' could not be resolved. Error message: ''















get free sql tips
agree to terms