Migrate SQL Server to Azure SQL Managed Instance using Backup and Restore Method

Azure SQL Managed Instance is one of Azure’s PaaS offerings. Azure SQL Managed Instance is one of the solutions where we can directly migrate the SQL server databases with minimum changes to no changes. It supports almost all the features of the SQL Server.

Migrating to Azure SQL Managed instance helps in utilizing many advanced features of Azure like

1. Built-in High Availability with 99.99% availability
2. Automated backups where we can store the backups with long-term retention till 10 years.
3. Automated patching. Azure SQL Managed Instance always runs at the top version of the SQL server Database engine.

To migrate the SQL server databases to Azure SQL Managed Instance, we have multiple options like

• Migration using Azure Database Migration Service (Classic)
• Migration using Azure Data Studio (online and offline)
• Backup and Restore Method
• LRS (Log Replay Service)

One of the options is to migrate using the “Backup and restore” method which is an offline method where we require downtime of the SQL server as we take one complete full backup which doesn’t accept any transactions post migration which will result In the data loss.

To migrate the SQL server database to the Azure SQL managed instance using the backup and restore method, we need to back up the database to the Azure storage account. Azure SQL Managed instance only supports backup and restore from the Azure storage account. We cannot restore the database on the Azure SQL Managed Instance if the backup file is stored on the local machine or the server.

Before migrating to Azure SQL MI, we need to have the prerequisites in place. The following are the prerequisites that are required before we perform the migration.

• A storage account/container URL to back up the database.
• A credential on the on-prem SQL server to back up the database to a storage account and the same credential on Azure Managed Instance to restore the Database from the URL.
• SAS Token from Azure Storage Account

Once the storage account is in place, we need to generate the SAS token which will be used to create the credential on the storage account. The SAS token will have all the required authorization to write the file onto the storage account. The credential will be used to take the backup of the database and store it in the Azure storage account.

To generate the SAS token

1. StorageAccount->settings->Shared Access Signature
2. Choose the necessary permissions and specify the expiration time for the SAS token. Then, initiate the generation process by clicking on the "Generate SAS token" button.

3.Copy the SAS key.

[ NOTE: Make sure to remove the question mark(?) symbol at the beginning of the key]

Now to create the backup of the SQL server database, we will Create a Credential using the SAS token on the on-prem SQL server.

DATABASE BACKUP:

Syntax:

CREATE CREDENTIAL <CREDENTIAL_NAME>

WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

, SECRET = '<SAS TOKEN> '

GO        

EX:

CREATE CREDENTIAL [https://<storageaccount_name>.blob.core.windows.net/container]

WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

, SECRET = '<SAS TOKEN> '

GO        

Here we have created the credential name with the URL of the Blob storage container.

To get the URL of the storage account container

[ StorageAccount ->containers ->properties]

Now, backup the Database to the URL, which will be migrated to the Azure SQL Managed Instance.

Syntax

BACKUP DATABASE [<Databasename>]TO URL = N'https://<storage accountname>.blob.core.windows.net/<container>/<file name>'

WITH CHECKSUM        

EX:

BACKUP DATABASE [AdventuresWorks] TO URL = N'https://<storageaccount>.blob.core.windows.net/container/AdventuresWorks.bak'

WITH CHECKSUM        

·         This will create a backup in the specified URL.

Note: We need to backup using the parameter CHECKSUM which is mandatory

To Restore the Database on the Azure SQL Managed Instance

DATABASE RESTORE:

• Now Connect to the Azure SQL Managed Instance using SSMS.
• Now Create the same credential on the Managed Instance we created on the on-prem SQL server.

Syntax:

CREATE CREDENTIAL <CREDENTIAL_NAME>

WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

, SECRET = '<SAS TOKEN> '

GO        

EX:

CREATE CREDENTIAL [https://<storageaccount_name.blob.core.windows.net/container]

WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

, SECRET = '<SAS TOKEN>  '

GO        

Now Restore the Database from the .bak file which is stored on the storage account.

Syntax:

RESTORE DATABASE [Databasename] FROM URL =

  'https://<storage account name>.blob.core.windows.net/<container>/<file name>'        

EX:

RESTORE DATABASE [AdventuresWorks] FROM URL = N'https://<storageaccount_name.blob.core.windows.net/container/AdventuresWorks.bak'        

 Note:

The capability to back up SQL Server databases to a URL or a storage account was introduced in SQL Server 2012. However, the feature to create or generate backups using a Shared Access Signature (SAS) token was introduced in SQL Server 2016. If your SQL Server instances are running versions prior to SQL Server 2016, you can still transfer backup files to Azure Storage by manually copying them from on-premises and pasting them into the designated container within the storage account.