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

by InduTeja Aligeti, Bhavani Akundi, and Sreenivas Nettem | on

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 Amazon Web Services Database Migration Service (Amazon Web Services 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 Amazon Web Services Database Migration Service (Amazon Web Services 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.

Amazon Web Services DMS is used for data migration and can enable database engine changes with minimal downtime. Amazon Web Services DMS can use a on-premise secondary availability group replica as a source for on-going replication, also known as Change Data Capture. Amazon Web Services 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 Amazon Web Services Schema Conversion Tool (Amazon Web Services 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 Amazon Web Services 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:

  1. Migrate the source schema with Amazon Web Services Schema Conversion Tool .
  2. Replicate data using Amazon Web Services DMS.

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

Prerequisites

Before we begin, you must complete the following prerequisites:

  1. 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.
  2. Configure an Amazon Aurora PostgreSQL instance as the target database engine in your Amazon Web Services account.
  3. Install the Amazon Web Services SCT on the source end and confirm that it’s able to connect to both the SQL Server listener and target Aurora PostgreSQL instance.
  4. Establish connectivity between source and target.

In addition, you must have basic knowledge of SQL Server replication and Amazon Web Services DMS.

Migrate the source schema with Amazon Web Services SCT

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

With the Amazon Web Services 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 Amazon Web Services 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 Amazon Web Services DMS

Amazon Web Services 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 Amazon Web Services DMS console. Refer to Using a PostgreSQL database as a target for Amazon Web Services Database Migration Service for more details on limitations, security requirements, and endpoint settings.

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

Monitor the Amazon Web Services DMS replication tasks

Monitoring is an important part of maintaining the consistency, accessibility, and performance of Amazon Web Services 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. Amazon Web Services provides multiple options for monitoring your Amazon Web Services DMS tasks. For example, you can use Amazon CloudWatch to collect, track, and monitor Amazon Web Services 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:

  • Monitoring Amazon Web Services DMS tasks
  • Logging task settings
  • How do I use Table Statistics to Monitor my task in Amazon Web Services Database Migration Service (DMS)?
  • Troubleshooting migration tasks in Amazon Web Services Database Migration Service
  • Logging Migration Hub API calls with Amazon Web Services CloudTrail
  • Setting up Amazon CloudWatch alarms for Amazon Web Services DMS resources using the Amazon Web Services CLI

Conclusion

In this post, we provided high-level steps that demonstrate how you can use Amazon Web Services 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 Amazon Web Services 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 Amazon Web Services. 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 Amazon Web Services.

Bhavani Akundi is a Lead Database Consultant at Amazon Web Services 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 Amazon Web Services 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 Amazon Web Services.