We use machine learning technology to do auto-translation. Click "English" on top navigation bar to check Chinese version.
Migrate from a Microsoft SQL Server AlwaysOn read-only replica to Amazon Aurora PostgreSQL with Amazon Web Services 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
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 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:
- Migrate the source schema with
Amazon Web Services Schema Conversion Tool . - Replicate data using Amazon Web Services DMS.
For more details, refer to
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 Amazon Web Services account.
- 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.
- 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
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
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
Lastly, create an Amazon Web Services DMS task to perform full load and ongoing replication. Refer to
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
-
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.
The mentioned AWS GenAI Services service names relating to generative AI are only available or previewed in the Global Regions. Amazon Web Services China promotes AWS GenAI Services relating to generative AI solely for China-to-global business purposes and/or advanced technology introduction.