Hello Readers !
The below is a small blog on how do we migrate SQL Server Databases to Azure Managed instance using SQL native backups we take from our On-premise SQL servers. This is an offline method where you would require downtime throughout the activity till the connection is re-established to your new database in the cloud and hence not suitable for giant databases with businesses not willing to put up with a bigger downtime window. This can still be adopted for small databases based on your convenience.
Managed instances databases do not support "norecovery" mode in restoring databases which means that we cannot use a restore plan involving restoration of latest full, differential, subsequent log backups.
Lets get to it.
Step 1: Creating a Blob container in Azure storage account
Go to your storage account in azure portal and click on Containers (basically blob folders),
Here we would get to create a container.
You can refer my previous detailed blog series on storage account to learn more. Part 1 2 3
Step 2: Creating SQL credentials with the SAS key of the storage account for storage account.
The concept of "Credentials" has been there in MS SQL server space for long. It is used by SQL server to connect to resources outside SQL server, in our case the azure storage account where we have created the blob container.
The name of the credential we create should be the complete URL of the blob container which is going to be used for holding the backup files.
You can easily find it out by navigating to your storage account and by clicking the properties settings in the left pane.
Please check the top portion of my blog here to learn how to generate SAS token which contain the authentication information to connect to the storage account.
Below is a sample t-SQL script to create SQL credentials using the SAS token
Use master
go
CREATE CREDENTIAL [https://<Yourstorageaccount>.blob.core.windows.net/<Containername>] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' , SECRET = 'sv=2017-11-09&ss=bfqt&srt=sco&sp=rwdlacup&se=2028-09-06T02:52:55Z&st=2018-09-04T18:52:55Z&spr=https&sig=WOTifdfdfdfdfdf%2BwndxW2CQ7%2B5fHd7Is%3D'
Please make sure to remove the '?' sign in front of the SAS token . That is a common mistake many do when they are creating the credentials. We need to have the SQL credential created in both the source SQL server instance (If you are directly taking backup to azure storage account/blob container) and in the managed instance.
Step3: Backing up your databases to azure blob container from the Source DB instance
Use the regular backup T-SQL command only with a difference of "To URL" to write your backup set to azure blob container.Authentication for this backup command to write the backup onto azure storage, will be taken care by the Credential we created above with the container URL and the SAS token.
T-SQL command
Use master
go
BACKUP DATABASE <DBName> TO
The below is a small blog on how do we migrate SQL Server Databases to Azure Managed instance using SQL native backups we take from our On-premise SQL servers. This is an offline method where you would require downtime throughout the activity till the connection is re-established to your new database in the cloud and hence not suitable for giant databases with businesses not willing to put up with a bigger downtime window. This can still be adopted for small databases based on your convenience.
Managed instances databases do not support "norecovery" mode in restoring databases which means that we cannot use a restore plan involving restoration of latest full, differential, subsequent log backups.
Lets get to it.
Step 1: Creating a Blob container in Azure storage account
Go to your storage account in azure portal and click on Containers (basically blob folders),
Here we would get to create a container.
You can refer my previous detailed blog series on storage account to learn more. Part 1 2 3
Step 2: Creating SQL credentials with the SAS key of the storage account for storage account.
The concept of "Credentials" has been there in MS SQL server space for long. It is used by SQL server to connect to resources outside SQL server, in our case the azure storage account where we have created the blob container.
The name of the credential we create should be the complete URL of the blob container which is going to be used for holding the backup files.
You can easily find it out by navigating to your storage account and by clicking the properties settings in the left pane.
Please check the top portion of my blog here to learn how to generate SAS token which contain the authentication information to connect to the storage account.
Below is a sample t-SQL script to create SQL credentials using the SAS token
Use master
go
CREATE CREDENTIAL [https://<Yourstorageaccount>.blob.core.windows.net/<Containername>] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' , SECRET = 'sv=2017-11-09&ss=bfqt&srt=sco&sp=rwdlacup&se=2028-09-06T02:52:55Z&st=2018-09-04T18:52:55Z&spr=https&sig=WOTifdfdfdfdfdf%2BwndxW2CQ7%2B5fHd7Is%3D'
Please make sure to remove the '?' sign in front of the SAS token . That is a common mistake many do when they are creating the credentials. We need to have the SQL credential created in both the source SQL server instance (If you are directly taking backup to azure storage account/blob container) and in the managed instance.
Step3: Backing up your databases to azure blob container from the Source DB instance
Use the regular backup T-SQL command only with a difference of "To URL" to write your backup set to azure blob container.Authentication for this backup command to write the backup onto azure storage, will be taken care by the Credential we created above with the container URL and the SAS token.
T-SQL command
Use master
go
BACKUP DATABASE <DBName> TO
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname.bak' with compression
You may also keep Copy_only if you are doing your test migration and dont want to affect the backup schedule in your DB instance.It also supports stripping of big DB backups to multiple files for ease of management. All you need to do is adding as many "URL ='Backup foldername+filename' in the backup command like below.
Use master
go
BACKUP DATABASE <DBName> TO
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname1.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname2.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname3.bak'
with compression
Step4: Restoring the Backup from the Blob container into the managed instance
T-SQL commanduse master
go
RESTORE DATABASE <DBname> FROM URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/WideWorldImporters-Standard.bak'
If you have created stripped backup from step3, make sure to give the URLs of all your backup in the restore command.
use master
go
RESTORE DATABASE <DBname> FROM
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname1.bak',
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname2.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname3.bak'
You can view your restored database in SSMS or in the portal in your managed instance page.
You can also see the progress of your restoration using DMVs like below.
use master
go
Select percent_complete from sys.dm_exec_requests where session_id=<SPID of your restore session>
I hope you are able to find your restore session's SPID using Sp_who/Sp_who2 active.
For online restoration to minimize downtime, we have methods like SQL replication and Azure Database migration service (DMS), which is a premium service listed in Azure market place.
That brings us to the end of this blog. Hope you find it useful and simple. I will write on Online DB migration in future with all pre-requisites.
You may also keep Copy_only if you are doing your test migration and dont want to affect the backup schedule in your DB instance.It also supports stripping of big DB backups to multiple files for ease of management. All you need to do is adding as many "URL ='Backup foldername+filename' in the backup command like below.
Use master
go
BACKUP DATABASE <DBName> TO
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname1.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname2.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname3.bak'
with compression
Step4: Restoring the Backup from the Blob container into the managed instance
T-SQL commanduse master
go
RESTORE DATABASE <DBname> FROM URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/WideWorldImporters-Standard.bak'
If you have created stripped backup from step3, make sure to give the URLs of all your backup in the restore command.
use master
go
RESTORE DATABASE <DBname> FROM
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname1.bak',
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname2.bak' ,
URL = 'https://<YourStorageaccount>.blob.core.windows.net/<Yourblobcontainer>/Dbname3.bak'
You can view your restored database in SSMS or in the portal in your managed instance page.
You can also see the progress of your restoration using DMVs like below.
use master
go
Select percent_complete from sys.dm_exec_requests where session_id=<SPID of your restore session>
I hope you are able to find your restore session's SPID using Sp_who/Sp_who2 active.
For online restoration to minimize downtime, we have methods like SQL replication and Azure Database migration service (DMS), which is a premium service listed in Azure market place.
That brings us to the end of this blog. Hope you find it useful and simple. I will write on Online DB migration in future with all pre-requisites.
No comments:
Post a Comment