Migrating SQL Server databases to Amazon Elastic Compute Cloud (Amazon EC2) using AWS Application Migration Service (AWS MGN) is a significant step toward modernizing your infrastructure. However, the migration journey doesn’t end after the data has been successfully transferred.
Post-migration activities are crucial to make sure that your SQL Server environment is optimized, secure, and fully functional in its new home.
In this post, we show you essential post-migration tasks to perform after migrating your SQL Server database to Amazon EC2 and how to automate this activity by using Cloud Migration Factory on AWS (CMF), such as validating database status, configuring performance settings, and running consistency checks.
Additionally, we explore how the CMF solution can automate these essential tasks, providing efficiency, scalability, and heightened visibility to simplify and expedite your migration process.
Solution overview
The CMF Implementation Guide offers a comprehensive solution designed to automate and streamline the entire migration process. It provides a powerful implementation guide that allows you to manage, customize, and execute various migration tasks efficiently and at scale.
The following diagram shows a high-level architecture of the CMF automation.
The diagram shows a structured approach to database migration where the CMF automation engine manages the transition of the SQL Server database from the corporate data center to the AWS Cloud infrastructure.
The CMF automation process is organized into three main phases:
- Pre-migration preparation
- The actual migration through the automation engine
- Post-migration activities
This represents a common cloud migration scenario where organizations move their on-premises databases to a cloud infrastructure while maintaining security through a virtual private cloud (VPC) and maintaining the proper configuration through automated processes.
Use CMF automations for post-migration activities
The key feature of CMF relevant for the use case described in this post is CMF automation jobs. CMF automation jobs provide an efficient and scalable way to execute automated post-migration tasks on your SQL Server databases when migrating them to EC2 instances in the AWS Cloud.
Automation jobs within CMF are executed on designated automation servers, which can be hosted either in the AWS Cloud or on premises, depending on your organizational needs. These servers must be a Windows server, with SSM Agent installed, alongside Python and Microsoft PowerShell. Depending on your specific automation requirements, additional frameworks and tools can be installed on these servers. Refer to Build a migration automation server for details of the automation server build.
Prerequisites
To implement the solution, the automation server used when executing the CMF automation jobs must be running on Windows, with the following software installed:
- AWS Systems Manager Agent (SSM Agent)
- Python
- Microsoft PowerShell
The migration of the SQL Server failover cluster instance (FCI) is not supported by AWS MGN.
Create CMF automation jobs
To run the SQL Server post-migration scripts using CMF automation, you need to package the scripts. A CMF supported script package is a compressed .zip archive containing the following mandatory files in the root:
- Package-Structure.yml – Used to define the script’s arguments and other metadata, such as description and default name.
- [custom python script].py – This is the initial script that will be run when a job is submitted. This script can call other scripts and modules, which should be included in the archive. The name of this script must match the value specified in the
MasterFileNamekey in thePackage-Structure.yml
After the script package has been created and uploaded to CMF, it can now be used to run CMF automations using the CMF console. Refer to Run automation from console for details. The CMF automation job workflow to execute these post-migration scripts is shown in the following diagram.
Post-migration activities
When performing a lift-and-shift migration of your SQL Server database to a new environment, it’s important to thoroughly test and validate the migration to confirm everything is working as expected.
Using CMF automation jobs to perform the post-migration activities for SQL Server migrations provides the following benefits:
- Efficiency – Automate repetitive tasks, reducing manual effort and accelerating the migration process
- Consistency – Standardize migration procedures across your organization
- Scalability – Manage large-scale migrations with automated workflows
- Visibility – Gain real-time insights into your migration progress through comprehensive job tracking and logging
- Security – Use the robust security features of AWS to protect sensitive data during the migration process
- Customization – Tailor automation scripts to fit your specific migration requirements and organizational needs
The following are some of the common post-migration activities to execute:
- Linked server test – Linked servers are used to connect SQL Server to other database engines. After the migration, it’s crucial to test the connectivity of any linked servers to confirm they are still functioning properly. This test verifies that all linked servers are correctly configured and can communicate as expected. If connectivity issues arise, they need to be addressed immediately to maintain the integrity of your data flow. The recommended steps are as follows:
- Verify the linked server configurations.
- Test the connection to each linked server.
- Resolve any connectivity issues.
- Configure memory – The performance of SQL Server is heavily dependent on the amount of memory allocated to it. Be sure to configure the SQL Server memory settings to match the memory capacity of the new EC2 instance it has been migrated to. This configuration helps optimize performance, so SQL Server can operate efficiently in its new environment. The recommended steps are as follows:
- Review the available memory on the new server or instance.
- Adjust the SQL Server memory settings accordingly.
- Monitor performance for optimal configuration.
- Database status check – After migration, checking the status of each database is crucial. All databases should be in an ONLINE status to confirm they are operational. If any databases are in a status other than ONLINE, further investigation by a database administrator is necessary to resolve the underlying issues. The recommended steps are as follows:
- Check the status of each database.
- Identify databases not in ONLINE status.
- Investigate and resolve any issues.
- Database consistency checks – Running database consistency checks (DBCC) is essential to confirm there are no corrupted blocks within the databases. Addressing these types of problems early is important for the long-term health of your migrated databases. The recommended steps are as follows:
- Execute DBCC CHECKDB for each user database.
- Review the results for any signs of corruption.
- Address any issues identified by the DBCC checks.
- SQL Server/Always On listener connectivity test – Validate that connections can be successfully made to the SQL Server instance and the Always On availability group listeners. This confirms that your applications can properly access the databases. The recommended steps are as follows:
- Test connectivity to the SQL Server database.
- Verify the Always On listener is accessible.
- Troubleshoot and fix any connectivity problems.
- Always-On cluster health check – If you are using Always-On availability groups, perform a health check on the cluster to verify that the node status, synchronization, and other key metrics are functioning as expected. The recommended steps are as follows:
- Assess the health status of all nodes in the cluster.
- Check the synchronization status of the availability groups.
- Review any alerts or warnings and take corrective actions.
- Error logs validation – Reviewing SQL Server error logs post-migration is a good practice to identify any issues that might have occurred during or after the migration. Validating these logs helps in proactive identification and resolution of potential problems. The recommended steps are as follows:
- Access the SQL Server error logs.
- Review for any errors or warnings.
- Investigate and resolve any issues logged.
Best practices
When automating post-migration activities for SQL Server using the CMF, there are a few key best practices to keep in mind. These recommendations help make sure the scripts execute reliably, provide meaningful feedback, and integrate seamlessly with the CMF automation framework.
The following suggestions will help you maximize the efficiency and effectiveness of your automated post-migration workflows:
- Use Windows Authentication while connecting to the SQL instance from the automation server.
- Provide a summary message of the script’s outcome as the final output to the screen or sysout, as shown in the following screenshot. It provides a quick status, without the user having to read the full output log.
- Have appropriate return codes in the script to denote success or failure. A non-zero return will set the job status to
Failed.
Clean up
To avoid incurring future charges, delete all the resources you created as part of this post.
You can clean up your environment in two ways:
- If you used the implementation guide for CMF to deploy the solution, follow the uninstallation steps in the solution documentation. This will automatically remove all associated resources.
- For manual deployments, review and delete any resources you created. Then confirm on the AWS Management Console that all resources have been successfully deleted.
Failing to remove unused resources might result in ongoing charges to your account.
Conclusion
In this post, we showed how you can use CMF automation to perform post-migration activities. This approach can be extended for other database engines as well. Post-migration activities are critical to maintain the stability, performance, and integrity of your SQL Server environment. Completing these post-migration validation steps will help confirm your SQL Server databases are running smoothly in the new environment. As a best practice, you can automate these checks as part of your migration and deployment processes. A thorough validation post-migration can save you from potential issues in the future.
Let us know if you have any comments or questions. We value your feedback!
About the Authors
Asif Mithawala is a Senior Cloud Infrastructure Architect with AWS Professional Services, based in Stockholm, Sweden. He collaborates with clients to accelerate their transition to the cloud, designing customized solutions that drive meaningful business outcomes.
Bharath Kumar is a Delivery Consultant with AWS Professional Services. He specializes in migrating customer databases from on-premises data centers to AWS Cloud and modernizing commercial database engines to AWS open-source solutions.
Marcelo Fernandes is a Principal Database Architect within the AWS Professional Services team, bringing over 21 years of experience in the field of databases. Throughout his career, Marcelo has been dedicated to assisting customers with database-related challenges, including migration, design, and performance optimization.
Richik Mishra is a Sr. Cloud Architect with AWS Professional Services, based out of Stockholm, Sweden. He helps customers accelerate their cloud journey, implement innovative solutions, and bring agility to achieve faster customer business outcomes.
Source: Read More
