Migrating your MS SQL Database from on-premise to Azure

The below guide illustrates backup/restore method through URL to Azure Blog storage from on-premise MS SQL to Azure SQL Database. Moreover, there are multiple ways to migrate to Azure VM for example: Converting on-premises machine to Hyper-V VHDs and then upload to Azure Blob storage. Also, for minimum downtime you can use Always-On availability Group by adding Azure Replica to your on-premise Always-On Infrastructure transnational replication.

First, you need to create a storage account in Azure portal.

Second, you need create a container as shown below:

After that go to your storage homepage and under SETTINGS choose “Shared access signature” SAS:

A shared access signature (SAS) is a URI that grants restricted access rights to Azure Storage resources. You can provide a shared access signature to clients who should not be trusted with your storage account key but whom you wish to delegate access to certain storage account resources. By distributing a shared access signature URI to these clients, you grant them access to a resource for a specified period of time. By default the access allowed is 8 hours !

On the bottom of the page choose “Generate SAS and connection string” …..copy the contents of the URL’s.

Now access your on-premise SQL Server Instance using SQL Server Management Studio and create a credential:

CREATE CREDENTIAL

[https://STORAGE_ACCOUNT.blob.core.windows.net/CONTAINER_NAME] WITH IDENTITY= ‘shared access signature’,SECRET=’SAS_TOKEN’

// the highlighted in yellow are required to be replaced with actual values

CREATE CREDENTIAL

[https:emostg.blob.core.windows.net/emoblob] WITH IDENTITY= ‘shared access signature’,SECRET=’sv=2017-11-09&ss=b&srt=sco&sp=rwdlac&se=2018-08-18T15:41:41Z&st=2018-08-18T07:41:41Z&spr=https,http&sig=WSi5e21DOygLtdO%2B2NJ%2FacKhlqJP6id1URIs%2F4oWCdc%3D’

Now you can take backup from a database on premise to your Azure blog storage, in management studio using the GUI:

In the backup choose “URL”

The following is the generated T-SQL statement:

BACKUP DATABASE [AdventureWorks2014] TO  URL = N’https://XXXstg.blob.core.windows.net/XXXblob/advworks2014.bak’ WITH NOFORMAT, NOINIT,  NAME = N’AdventureWorks2014-Full Database Backup’, NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Then in Azure Portal execute the following t-sql:

RESTORE DATABASE [AdventureWorks2014]

FROM URL =’https://XXXstg.blob.core.windows.net/XXXblob/advworks2014.bak’

WITH REPLACE, STATS=5