In the previous post, Migrate Amazon RDS for SQL Server to Azure SQL Managed Instance - Overview (microsoft.com), we have introduced an overview of the series and summarized different migration options to migrate from Amazon RDS for SQL Server to Azure SQL Managed Instance.
In this post, we shall demonstrate how to use the SQL Server native backup and restore features to migrate from Amazon RDS for SQL Server to Azure SQL Managed Instance.
This involves performing a full backup on the source instance and restoring the same backups to the target SQL Managed Instance.
Note that Amazon RDS for SQL Server currently does not support extraction of the transaction log backups and hence does not allow a PITR (Point in Time Restore) technique allowing to restore database to any particular moment in time.
On Azure SQL Managed Instance, the native backup and restore functionality is supported with the help of GUI, T-SQL commands, Powershell commands or REST API.
SQL Server has three database recovery models - Full, Bulk-Logged and Simple. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.
You can use native backup restore method to migrate the databases from a SQL Server instance to an Azure SQL Managed Instance.
Many of our customers use native SQL Server backup and restore features to migrate to Azure SQL Managed Instance. When using the native SQL Server backup and restore functionality, you can simplify the database migration process by performing a full backup restore on the target SQL Managed Instance.
Note: RDS SQL Server doesn’t support taking log backups.
In this post, we shall walk you through a two-step process that enables you to migrate SQL Server databases to Azure SQL Managed Instance. The only currently available step includes transferring a one-time full backup to Azure Blob Storage to create a replica of your SQL Server in SQL managed Instance.
Amazon RDS for SQL Server supports native backup and restore for Microsoft SQL Server databases. It lets you backup individual databases from an SQL Server RDS instance. The native backup and restore functionality are supported with the help of stored procedures. These stored procedures let you create a differential or full backup of your Amazon RDS for SQL Server DB instances and store the backup files on Amazon Simple Storage Service (Amazon S3). You can restore these backups to on-premises instance that is running SQL Server or Azure SQL Managed Instance (which natively supports restoring the FULL backup only).
Prerequisites:
Before you get started, you must complete the following prerequisites:
The migration solution consists of two stages.
In stage one, we use AZcopy for transferring one-time full backup to Azure blob storage and then restore backup to the SQL Server database on SQL Managed Instance:
Stage 1.1: Backing up the SQL Server database from the on-premises source
Take a full backup of the RDS SQL Server database using the following code:
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws:s3:::mybucketname/mydatabase_full.bak',
@overwrite_s3_backup_file=1,
@type='FULL';
AzCopy is a command-line tool to manage and copy blobs or files to or from a storage account. It also allows you to sync storage accounts and move files from many different sources to a lot of different destinations. In our case we shall be moving files from Amazon S3 to Azure storage.
First, you will need to install AzCopy on your machine. After that, you will need to authorize AzCopy with Microsoft Azure and AWS.
AzCopy uses an access key, and secret to authenticate with AWS S3. For the destination Blob storage account, you can use any of the available authentication options (SAS token, or Azure Active Directory authentication).
Stage 1.2: Copy data from S3 to Blob storage using AzCopy.
AzCopy is a command-line tool to manage and copy blobs or files to or from a storage account. It also allows you to sync storage accounts and move files from many different sources to a lot of different destinations. In our case we shall be moving files from Amazon S3 to Azure storage.
First, you will need to install AzCopy on your machine. After that, you will need to authorize AzCopy with Microsoft Azure and AWS.
AzCopy uses an access key, and secret to authenticate with AWS S3. For the destination Blob storage account, you can use any of the available authentication options (SAS token, or Azure Active Directory authentication).
Storage type |
Supported method |
Blob storage |
Azure AD and SAS |
Blob storage (hierarchical namespace) |
Azure AD |
File storage |
SAS only |
Step 1.3: Login to AzCopy using Azure AD
Login to a VM to run the AzCopy from the powershell. On Powershell, authenticate your identity by using the AzCopy login command. AzCopy uses your Azure AD account to authorize access to data in Blob storage. You will also need one of these permissions in Azure AD:
azcopy login
Run https://microsoft.com/devicelogin on your machine’s browser and enter the code provided to authenticate.
Stage 1.4: Login to AzCopy using SAS token & Get AWS access key and secret access key
To create a new key, got to IAM Management from your AWS Console, select the user, and click Create Access key button. Ensure you copy the secret key id and the secret access key as it would be required to authenticate to AWS.
You'll need to grab an Access Key ID and AWS Secret Access Key from Amazon Web Services. If you're not sure how to retrieve those, check out the AWS docs. From there, it's as easy as setting a few environment variables.
set AWS_ACCESS_KEY_ID=<my_key>
set AWS_SECRET_ACCESS_KEY=<my_secret_key>
S3 bucket URL |
|
Source endpoint |
myrdsinstance.cmaee3eebtjd.us-east-1.rds.amazonaws.com |
Target endpoint |
mytargetsqlinstance.780f4aa8c17b.database.windows.net |
Azure Blob storage URL |
https://mystorageaccountname.blob.core.windows.net/mycontainer1 |
SAS Key |
sv=2019-02-02&ss=b&srt=sco&sp=rl&se=2023-12-02T00:09:14Z&st=2019-11-25T16:09:14Z&spr=https&sig=92kAe4QYmXaht%%3D |
The following example to Copy an object from AWS S3 bucket:
azcopy copy “https://s3.amazonaws.com/mybucket/myobject” “https://mystorageaccount.blob.core.windows.net/mycontainer/myblob?<SAS>”
The following example to Copy a directory from AWS S3 bucket:
azcopy copy “https://s3.amazonaws.com/mybucket/mydirectory' 'https://mystorageaccount.blob.core.windows.net/mycontainer/mydirectory? <SAS>” --recursive=true
Now, Copy the backup file from AWS S3 bucket directory to Azure blob storage container. This example command recursively copies data from your s3 bucket to a blob container. A fictitious SAS token is appended to the end of the container URL.
azcopy copy “https://mybucketname.s3.amazonaws.com/mydatabase_full.bak" "https://mystorageaccountname.blob.core.windows.net/mycontainer1/?sv=2019-02-02&ss=b&srt=sco&sp=rl&se=2023-12-02T00:09:14Z&st=2019-11-25T16:09:14Z&spr=https&sig=92kAe4QYmXaht%2Fgjocqwerqwer41s%3D" --recursive=true
Stage 1.5: Restoring the database backup file to the target SQL Managed Instance
After you transferred the file to Azure blob storage, restore a full database backup by executing the following T-SQL code.
RESTORE DATABASE [mydatabase_Recovery] FROM URL = 'https://mystorageaccountname.blob.core.windows.net/mycontainer1/mydatabase_full.bak', WITH RECOVERY;
Given that currently SQL Managed Instance does not support NORECOVERY option, there is no possibility of restoring any other backup types besides FULL on it.
In this blog post, we have discussed SQL Server backup and restore option and AZCopy to migrate RDS SQL Server databases to Azure SQL Managed Instance.
In the next post, we will dive into using Azure Data Factory option for migration from Amazon RDS for SQL Server to Azure SQL Managed Instance.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.