Migrate from a Microsoft SQL Server AlwaysOn read-only replica to Amazon Aurora PostgreSQL with AWS DMS

Customers are opting to replicate their critical workloads to Amazon Aurora PostgreSQL-Compatible Edition for ease of use, minimal operational overhead and the ability to break free from commercial licenses. In this post, we explain the high-level steps to migrate data from a highly available on-premises Microsoft SQL Server AlwaysOn secondary read-only database to Amazon Aurora PostgreSQL-Compatible Edition with AWS Database Migration Service (AWS DMS).

When your SQL Server mission-critical workloads are running on the primary SQL Server AlwaysOn replica, diverting the replication load to a secondary availability group replica gives better replication performance by avoiding SQL read contentions on the primary replica that are introduced by the replication. We can use AWS Database Migration Service (AWS DMS) change data capture (CDC) to migrate data in near-real time. This also ensures the transactions are read from the source read replica endpoint in order to migrate to Amazon Aurora PostgreSQL efficiently.

AWS DMS is used for data migration and can enable database engine changes with minimal downtime. AWS DMS can use a on-premise secondary availability group replica as a source for on-going replication, also known as Change Data Capture. AWS DMS Change Data Capture (CDC) can start and stop from a specific point in time. It also provides object-level replication, which enables you to choose the objects that will be replicated to the target. For converting the database code from one database engine to another, use AWS Schema Conversion Tool (AWS SCT).

Key benefits of using a secondary availability group read-only replica

The following are the key benefits of using a secondary availability group read-only replica:

For any mission-critical workloads, having secondary workloads will offload the primary replica data load
It expands your overall processing for the systems that host readable secondary replicas
Temporary statistics on a readable secondary database enhance read-only queries
To remove blocking contention on the secondary databases, read-only workloads use row versioning
For both disk-based and memory-optimized table types on the secondary replica, DML operations are allowed on table variables

Solution overview

The following architectural diagram depicts the migration from SQL Server AlwaysOn Read Replica to Amazon Aurora PostgreSQL. A SQL Server AlwaysOn availability group consists of a primary replica and a secondary replica. The secondary replica must be configured using synchronous availability group replication. When the secondary availability group replica is configured using synchronous availability group replication, the transactions in the active transaction log are an exact copy of the primary replica. Each SQL Server AlwaysOn replica is a standalone server with its own dedicated system resources. When AWS DMS connects with SQL Server AlwaysOn using the availability group listener fully qualified domain name through the source endpoint, providing ApplicationIntent=readonly in the extra connection attributes will route the connection to the secondary replica. The primary database is available online for the application during the migration activity.

The solution is implemented with the following key steps:

Migrate the source schema with AWS Schema Conversion Tool.
Replicate data using AWS DMS.

For more details, refer to SQL Server to Aurora PostgreSQL Migration Playbook.

Prerequisites

Before we begin, you must complete the following prerequisites:

Configure your source on-premises or on Amazon Elastic Compute Cloud (Amazon EC2) with a SQL Server database in an AlwaysOn availability group to configure the availability group to enable read-only routing.
Configure an Amazon Aurora PostgreSQL instance as the target database engine in your AWS account.
Install the AWS SCT on the source end and confirm that it’s able to connect to both the SQL Server listener and target Aurora PostgreSQL instance.
Establish connectivity between source and target.

In addition, you must have basic knowledge of SQL Server replication and AWS DMS.

Migrate the source schema with AWS SCT

The key points when migrating your databases from one database platform to another database platform (heterogenous migration) includes code conversion and data migration. AWS SCT and AWS DMS are reliable tools to simplify this migration process.

With the AWS SCT user interface, you can automatically convert the database schema from the source to target instance. If any part of the code from the source database can’t be converted automatically, the AWS SCT also provides guidance on how you can create an equivalent schema in your target database through playbooks.

For additional information on best practices to migrate SQL Server to Amazon Aurora PostgreSQL-Compatible Edition, refer to Migrate SQL Server to Amazon Aurora PostgreSQL using best practices and lessons learned from the field.

Replicate data using AWS DMS

AWS DMS can use a self-managed secondary availability group replica as a source for ongoing replication (change data capture) starting in version 3.4.7.

You first create a source endpoint to connect to the secondary availability group read replica. Refer to Working with self-managed SQL Server AlwaysOn availability groups for the requirements, extra connection attributes for the source endpoint, and limitations when using a secondary availability group replica. You can use either a listener fully qualified domain name (HA) or a secondary replica fully qualified domain name (non-HA) with the extra connection attribute applicationIntent=ReadOnly for the source server name.

Then you create the target endpoint to connect to the Aurora PostgreSQL instance using the AWS DMS console. Refer to Using a PostgreSQL database as a target for AWS Database Migration Service for more details on limitations, security requirements, and endpoint settings.

Lastly, create an AWS DMS task to perform full load and ongoing replication. Refer to Creating tasks for ongoing replication using AWS DMS for more details.

Monitor the AWS DMS replication tasks

Monitoring is an important part of maintaining the consistency, accessibility, and performance of AWS DMS, especially for large migrations. Monitoring helps you identify variances and trigger notifications based on the threshold metrics that were configured. To debug any failures, the monitoring data is very useful. AWS provides multiple options for monitoring your AWS DMS tasks. For example, you can use Amazon CloudWatch to collect, track, and monitor AWS resources using metrics. It’s very important to check if the tasks are created with appropriate endpoints and required mapping rules across the schemas and database objects. For more information about monitoring methods and items to monitor, refer to the following resources:

Conclusion

In this post, we provided high-level steps that demonstrate how you can use AWS DMS to replicate data from an AlwaysOn read-only replica to Amazon Aurora PostgreSQL-Compatible Edition. You can migrate your mission-critical on-premises databases seamlessly using AWS DMS with minimal downtime. If you have business-critical databases running on an on-premises SQL Server Always On availability group, you can replicate the data from a secondary read-only replica to Amazon Aurora PostgreSQL-Compatible Edition with near-zero downtime.

About the authors

InduTeja Aligeti is a Senior Lead Database Consultant at AWS. She has over 18 years of experience working with Microsoft technologies with a specialization in SQL Server. She focuses on helping customers build highly available, cost-effective database solutions and migrate their large-scale SQL Server databases to AWS.

Bhavani Akundi is a Lead Database Consultant at AWS Professional Services. She has vast experience working with Microsoft technologies with a specialization in SQL Server. She works closely with customers to help them migrate and modernize their database workloads using cost-effective solutions.

Sreenivas Nettem is a Lead Database Consultant at AWS Professional Services. He has over 18 years of experience working with Microsoft technologies with a specialization in SQL Server. He works closely with customers to help migrate and modernize their databases to AWS.