Amazon Relational Database Service (Amazon RDS) Custom for SQL Server is a managed database service that allows customization of the underlying operating system and includes the ability to bring your own licensed SQL Server media or use SQL Server Developer Edition while providing the time-savings, durability, and scalability benefits of a managed database service. In this post, we present a practical approach to one of the most significant challenges organizations face when adopting RDS Custom: migrating large datasets from SQL Server on Amazon Elastic Compute Cloud (Amazon EC2) to Amazon RDS Custom for SQL Server efficiently and cost-effectively.
This post describes a tailored lift-and-shift strategy that leverages RDS Custom’s unique capabilities to simplify your database migration. By using SQL Server’s native detach and attach method combined with EBS snapshots, you can migrate your databases without requiring Amazon Simple Storage Service (Amazon S3) or AWS Database Migration Service (AWS DMS). Our tested approach provides a seamless transition path that emphasizes both efficiency and speed.
Organizations can use this solution to simplify complex data migrations while minimizing business disruption. We provide detailed steps, practical insights, and best practices to help you successfully migrate your SQL Server databases from EC2 to RDS Custom. This approach helps you maintain data integrity and take full advantage of RDS Custom’s managed database capabilities.
Solution overview
The migration process uses AWS native tools to maintain data integrity and minimize downtime. The complete process follows these steps:
- Create an EBS snapshot of your SQL Server database volume on the EC2 instance.
- Restore the snapshot to a new EBS volume.
- Pause RDS Custom automation to allow manual configuration.
- Attach the new EBS volume to your RDS Custom host instance.
- Connect to the RDS Custom for SQL Server host and copy the database files from the attached EBS volume to the D drive of RDS SQL Server Custom Instance.
- Attach the databases to RDS Custom SQL Server instance to complete the migration.
- After the migration, clean up by detaching the temporary volume and resuming RDS Custom automation.
This methodology offers several advantages:
- Database migration with minimal downtime
- Minimizes operational disruptions
- Maintains data integrity throughout the process
- Provides a seamless transition from a self-managed EC2 instance to the RDS Custom for SQL Server platform
By adhering to this procedure, organizations can efficiently migrate their SQL Server databases while potentially enhancing performance, reliability, and manageability of their database workloads. This approach exemplifies a strategic shift from self-managed infrastructure to a more optimized, managed database environment.
Prerequisites
To implement this solution, you need to complete the following prerequisite steps:
- Create an EC2 instance with an EBS volume.
- Create a RDS Custom for SQL Server instance.
- The target SQL Server version should be equal to or higher than the source EC2 SQL Server version.
Note: Review the AWS Documentation for supported regions and DB Engines for RDS Custom for SQL Server. Make sure you consider Database edition compatibility for the migration.
For this post, we migrate from SQL Server 2022 Standard Edition running on EC2 to RDS Custom for SQL Server 2022 Standard Edition.
Create a snapshot of the EBS volume
Complete the following steps to create a snapshot of the EBS volume that stores your databases:
Note: Stop all incoming write traffic to EC2 SQL Server to ensure a consistent snapshot.
- On the Amazon EC2 console, in the navigation pane, choose Volumes.
- Select your EBS volume, and on the Actions menu, choose Create snapshot.
- Add a description and then choose Create snapshot.
Create a volume from the EBS snapshot
Once the snapshot is created, complete the following steps:
- On the Amazon EC2 console, in the navigation pane, choose Snapshots.
- Select the snapshot you created, and on the Actions menu, choose Create volume from snapshot.
- Select your desired volume settings. You must select the Availability Zone that is the same Availability Zone that your RDS Custom for SQL Server DB instance is in. Select the primary Availability Zone in case the RDS Instance is in a Multi-AZ deployment.
- Choose Create volume. Note down the newly created EBS Volume name for later use.
Pause automation
This step is necessary prior to the migration. Pause the RDS Custom automation.
Attach the restored volume to the RDS Custom for SQL Server EC2 host instance
Complete the following steps to carry out the migration by attaching the newly restored EBS volume to the RDS Custom for SQL Server underlying EC2 host instance.
- On the Amazon EC2 console, in the navigation pane, choose Volumes.
- Select your previously noted EBS volume, and on the Actions menu, choose Attach volume.
- For Instance, choose the RDS Custom for SQL Server underlying EC2 host instance.
- For Device name, specify your device.
- Choose Attach volume.
Connect to underlying EC2 Instance of RDS Custom for SQL Server instance
To connect to the RDS Custom for SQL Server EC2 host instance, refer to Connecting to your RDS Custom DB instance using RDP.
Copy database files to RDS Custom for SQL Server
After you’re connected to the RDS Custom for SQL Server EC2 host instance, you can copy the files from the newly restored and attached EBS volume to the existing D drive. Ensure you do not copy any system database files from the EBS volume. Complete the following steps:
- When you’re connected, navigate to the Folder
- Navigate to the drive of the newly attached EBS volume.
- Copy the data (.mdf) and log (.ldf) files of user databases that you plan to migrate.
- Navigate to the path D:rdsdbdataDATA, and paste these files.
Attach the databases
Now you can connect to SQL Server and attach the new databases to fully migrate them from SQL Server running on an EC2 instance to an RDS Custom for SQL Server DB instance. Complete the following steps:
- Connect to SQL Server Management Studio. You can use the same primary user credentials that were created upon deployment of your RDS Custom for SQL Server DB instance.
- Right-click Databases, then choose Attach.
- Choose Add, and then choose the database files and database log files from the D drive that you want to attach.
- After the databases are added, choose OK.
Now you have successfully migrated your databases from SQL Server running on EC2 to RDS Custom for SQL Server.
Detach EBS volume
After the successful migration, detach the EBS volume.
To detach the volume, complete the following steps:
- On the Amazon EC2 console, in the navigation pane, choose Volumes.
- Select the restored EBS volume that is attached to the underlying EC2 host instance, and on the Actions menu, choose Detach volume.
- Choose Confirm detach.
Unpause automation
Once you have successfully migrated, unpause the RDS Custom automation.
Additional Considerations
During the EBS volume restore, you might experience performance degradation due to lazy loading. Lazy loading means the volume is immediately available but data blocks are loaded from Amazon S3 only when they are first accessed, which can cause initial I/O operations to have higher latency. To mitigate this impact on critical databases, you can perform full table scans (like SELECT *) on important tables immediately after the migration to pre-load the data into memory and ensure optimal performance.
Additionally, we have demonstrated a solution to only migrate user databases, which doesn’t cover peripheral SQL Server objects such as SQL Server Agent jobs, linked servers, and so on.
Clean up
To avoid incurring future charges, delete the resources you created as part of this post:
- To delete the newly restored EBS Volume, refer to Delete an Amazon EBS Volume.
- To delete the EBS Snapshot, refer to Delete an Amazon EBS Snapshot.
Conclusion
This post demonstrates how to migrate SQL Server user databases from EC2 to RDS Custom for SQL Server using EBS snapshots and SQL Server’s attach functionality. This approach offers a faster, more cost-effective alternative to traditional migration methods, especially for large databases. You can adapt this solution to meet your specific business needs and migration requirements. Leave your feedback in the comments section.
About the Authors
Nirupam Datta is a Senior Cloud Support DBE at AWS. He has been with AWS for over 5 years. With over 12 years of experience in database engineering and infra-architecture, Nirupam is also a subject matter expert in the Amazon RDS core systems, Amazon RDS for SQL Server, and Amazon Aurora MySQL. He provides technical assistance to customers, guiding them to migrate, optimize, and navigate their journey in the AWS Cloud.
Dipin Sahadevan is a Cloud Support Engineer working with AWS for over 3 years. Dipin is also a subject matter expert in Amazon RDS for SQL Server. He has 15 years of experience working with relational databases. At AWS he works with external customers to handle a variety of scenarios, such as troubleshooting RDS infrastructure and authoring and improving internal documentation.
Cade Kettner is a Cloud Support Engineer who has worked with the AWS Database Team for nearly 2 years. In his role, he provides technical assistance with AWS Services including RDS MySQL, RDS MariaDB, RDS SQL Server, Aurora MySQL, and AWS DMS to help troubleshoot technical issues and provide tailored solutions for customers.
Source: Read More
