Migrating SQL Server databases from Microsoft Azure to AWS in near-real time with CloudBasic

There are multiple ways to migrate  SQL Server databases hosted in  Microsoft Azure into Amazon Relational Database Service (Amazon RDS) for SQL Server. For use cases such as migrating from SQL Server on an Azure virtual machine to Amazon RDS for SQL Server or SQL on Amazon Elastic Compute Cloud (Amazon EC2), you can use AWS Database Migration Service (AWS DMS). When it comes to migrating Azure SQL databases, because they don’t support change data capture (CDC), instead of AWS DMS, you can use a technology partner solution called CloudBasic for Amazon RDS for SQL Server read replicas and disaster recovery. In addition to migration, you can also use CloudBasic for deploying and testing cross-Region Amazon RDS for SQL Server disaster recovery in an AWS environment (prior to migration cutover). CloudBasic is available on AWS Marketplace.

In this post, we highlight a solution that uses CloudBasic and other AWS services to facilitate a seamless, near-zero-downtime migration of Azure SQL databases into Amazon RDS for SQL Server. The solution also comes with a deployment option of enabling fully automated Amazon RDS for SQL Server cross-Region disaster recovery. This migration strategy is applicable to the following use cases:

• Migrating databases from a Azure SQL virtual machine to Amazon RDS for SQL Server or SQL on Amazon EC2.
• Migrating Azure SQL databases or an elastic pool to Amazon RDS for SQL Server or SQL on Amazon EC2.

All SQL Server editions in Amazon RDS, including SQL Server Enterprise, Standard, and Web, are supported. Moreover, CloudBasic facilitates seamless migration between SQL Server versions and editions. Sample use cases include:

• Modernization – Migration from SQL Server Enterprise 2012 on Azure to the latest SQL Server Enterprise 2019 on Amazon RDS
• TCO Reduction – Migration downgrade from SQL Server Enterprise on Azure to SQL Server Standard on Amazon RDS, deploying multiple in-Region and cross-Region read replicas

In addition, you can orchestrate a fully automated cutover from Azure to AWS plus fully automated cross-Region disaster recovery within AWS, by integrating CloudBasic with other AWS services, such as AWS Lambda and Amazon Route 53 (chained Azure[Region]-AWS[Region A] – AWS[Region B] replication), prior to final cutover from Azure to AWS. Whenever ready, you cut over to AWS by performing a planned failover. At time of cutover to primary AWS [Region A], the fully automated AWS cross-Region solution (AWS[Region A] – AWS[Region B]) is fully operational. 

Solution overview

The following diagram illustrates the solution architecture.

CloudBasic is a cloud-native solution, which is launched into your Amazon Virtual Private Cloud (Amazon VPC). You operate with CloudBasic’s web console and API just like you would with any other AWS service. The following screenshot shows a view of read replicas on the CloudBasic console.

Configuring migration and cross-Region RDS SQL Server disaster recovery with CloudBasic

You can quickly configure new migration and cross-Region read replicas from any version or edition of SQL Server in Azure into any version or edition of SQL Server in AWS, in most cases without the need to select advanced options or any custom scripting. In complex migration and disaster recovery deployment cases, advanced configuration options and scripting capabilities are available in the advanced section of the CloudBasic console.

CloudBasic automatically handles any necessary schema conversions, accounts for SQL Server version and edition incompatibilities, and creates SQL Server read replicas in Amazon RDS for SQL Server or Amazon EC2 in a fully automated manner. The following screenshot shows a replication job’s connection configuration on the CloudBasic console.

If you’re migrating Azure SQL databases, which don’t support CDC, CloudBasic defaults the tracking of changes method to Change Tracking. You can use other tracking methods in the cross-Region disaster recovery part of the architecture.

The following screenshot shows the configuration of a replication job in CloudBasic with the Change Tracking method.

For a continuous replication mode to work, all the tables in the source database need to have primary keys (PKs) or unique indexes. Before a continuous replication is started, the Quick Setup wizard analyzes all the tables and lists tables without PKs or unique indexes. At this point, you can create PKs for the listed tables or choose to proceed as is. However, after the initial replication and seeding is complete, the tables without PKs or unique indexes are ignored for continuous data replication.

After the initial database seeding is complete, the replication job is transitioned to continuous change tracking with a connection pool defaulted to an automatically determined size based on CloudBasic instance size and database size. To further fine-tune the replication process, and to support larger databases and high rate of transactions, you can increase or decrease the connection pool size to lower latency or reduce workload on the primary, respectively (see the following screenshot).

Ongoing monitoring of migration pending data and schema changes is facilitated by runtime reports, runtime logs, and system alerting. The following screenshot highlights the replica count of the source and target datasets and timestamp of the last sync. The pending changes column displays the number of records yet to be replicated.

Database schemas are tracked for changes. New tables, stored procedures, views, functions, constraints, foreign keys, new columns, altered columns changes, and more are all replicated. 

Summary

Performing Azure SQL to AWS migration by leveraging Cloud Basic’s cloud-native Amazon RDS for SQL Server disaster recovery solution delivers great advantages over traditional migration tools. With CloudBasic, the initial focus is on achieving intercloud disaster recovery on a database level via creating Amazon RDS for SQL Server read replicas. This approach allows extended testing to be performed against the actual AWS environment over a prolonged period of time. You can cut over whenever you’re ready by performing a planned disaster recovery failover.

To deploy CloudBasic from AWS Marketplace, visit AWS Marketplace. For deployment instructions, see the CloudBasic deployment guide.

In addition, you can use CloudBasic to deploy cross-Region RDS SQL Server read replicas to achieve disaster recovery within the AWS environment.

About CloudBasic

CloudBasic is a cloud technology firm headquartered in Irvine, California. It’s an AWS advanced technology partner that has achieved AWS Workloads Competency status for its CloudBasic for Amazon RDS SQL Server read replicas and disaster recovery cloud-native solution.

About the Authors

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with the AWS Database Services Organization (DBSO), Customer Advisory Team (CAT). He has led large scale Database Migration & Modernization engagements for Enterprise Customers to move their on-premises database environment to Multi Cloud based database solutions.

RJ Petroff is the Product Manager, Founder and CEO of CloudBasic. Although remaining deeply involved in product design and management, he created CloudBasic from the ground up, and evolved it into an enterprise cloud product company with international customers across some of the most demanding verticals. Prior to founding CloudBasic, RJ was involved in fintech product development and disaster recovery management at leading wall street firms.