Lessons Learned Migrating from SQL Server to Amazon Aurora MySQL

One of Datavail’s customers, a healthcare organization that provides 24/7 emergency consultation and referral services for doctors in acute care hospitals, uses systems that are instrumental in effectively managing critical care resources and implementing quality improvement initiatives.

Its project involved migrating from the commercial database Microsoft SQL Server to the open-source solution MySQL, alongside the simultaneous redevelopment of the application running on top of the database, presenting a complex challenge. As DBAs joining the project in progress, our role extended beyond data migration to supporting the ongoing development of these vital systems.

Key Challenges

• Data Schema Differences: Migrating from Microsoft SQL Server to Amazon Aurora MySQL involved dealing with multiple applications and schemas with complex dependencies. The disparities between the SQL Server and Aurora MySQL schemas posed a significant challenge, necessitating meticulous adjustments to ensure data consistency and integrity.
• Compatibility Issues: Transitioning from SQL Server to AWS Aurora MySQL posed compatibility challenges, including discrepancies in table and column names, datatype disparities, and variations in case sensitivity. Overcoming these issues required careful consideration and extensive adaptations to ensure a smooth and seamless migration process.
• Data Security and Compliance: Handling sensitive personal health information (PHI) data encrypted in the SQL Server database presented a critical challenge. Maintaining data security and compliance with regulatory standards, such as HIPAA, required implementing encryption techniques and ensuring that the data remained confidential and secure throughout the migration process.

Solution

Our solution involved implementing a robust database migration and modernization strategy. The choice to leverage Amazon Aurora Serverless v2 for MySQL 8.0, a high-availability and scalable database solution, allowed us to modernize the client’s database infrastructure while ensuring data security and performance. One of the main challenges was to ensure schema conversion between MS SQL Server 2016 and Aurora MySQL.

Our approach was to copy the data to Aurora MySQL “as-is” using AWS Database Migration Service (DMS), then transform it to be compatible with the new application version with the help of custom transformation scripts.

We used a schema comparison tool to compare the source and target schemas in the development and UAT environments. Based on those differences, we created the SQL scripts to run against the converted SQL Server schema. This multi-pronged approach facilitated a smooth and successful migration process, ensuring the compatibility and integrity of the migrated data.

Implementation

Our implementation journey involved several crucial steps:

1. Choosing Aurora with MySQL 8.x on Serverless Architecture: This decision laid the foundation for our modernized database environment.
2. Transformation of PHI Data: We took extra precautions to ensure the secure transformation of PHI data during the migration process. Scripts to encrypt/decrypt the PHI data.
3. Schema Conversion and Data Types: Adapting SQL Server schemas and data types to Aurora MySQL was a meticulous task, involving fixes for date time formats, auto-increments, and missing indexes. Many objects had to be renamed and altered to account for differences in data types and column names. We also had to pre-populate lookup tables with new data, create the triggers, functions, stored procedures, views, and indexes.
4. Leveraging DMS for Data Migration: We attempted to utilize AWS DMS for data migration. However, we encountered problems when handling columns with different datatypes, tables that had to be renamed, blobs, and text data by leveraging transformation rules.
5. Phased Approach: We divided the migration into phases, starting with Dev, UAT, Pre-prod, and finally, a cutover to Production. This approach allowed us to validate each step thoroughly.
6. Schema Comparison and Data Validation: We invested significant effort in schema comparison and data validation to ensure data integrity.
7. Collaboration with Dev Teams: Working closely with development teams to fix SQL and objects was crucial for a successful migration.
8. Query Optimization: Query optimization and revisiting nested views, stored procedures, and functions were essential for performance optimization.
9. Cutover Planning: Multiple iterations of cutover planning helped us execute the final migration smoothly.
10. Database Best Practices: Throughout the migration process, we adhered to database best practices to facilitate a seamless transition.

Results and Feedback

Our efforts culminated in a successful migration from on-premises SQL Server to Aurora MySQL. This migration covered both the database and full-stack applications. The collaboration among multiple teams was a key factor in our success. Post-migration, our focus shifted to stability and performance tuning, along with robust backup and restore practices.

Key Takeaways:

• Schema Comparison Tool: While a schema comparison tool is valuable, it’s important not to rely solely on auto-generated scripts. These scripts often require additional work to be useful and should only serve as a starting point.
• Addressing auto_increment Sequencing: Be cautious of ID columns in MS SQL that begin with 0 or have duplicate IDs, as they can lead to auto_increment resequencing errors. You can address this by setting SESSION sql_mode=’NO_AUTO_VALUE_ON_ZERO’ or using a script to re-seed auto_increment values.
• Table-Valued Functions: MS SQL table-valued functions may not be compatible with MySQL. Collaboration with developers to identify alternatives is crucial.
• MySQL Warnings: While addressing MySQL warnings is important, be aware of exceptions like tinyint(1) with deprecated integer widths. Removing those may lead to application issues.
• Case Sensitivity: When migrating to a case-sensitive environment, set lower_case_table_names=1 in the new environment. Ensure you configure this before instance creation in Aurora, as it cannot be changed afterward.
• Naming conventions when dealing with multiple databases in a schema: You need to map those databases correctly to the destination. AWS transformation rules should be used accurately.

Benefits of AWS Aurora Serverless v2

One of the key factors contributing to the success of our migration was the choice of AWS Aurora Serverless v2 for MySQL. Here are some of the benefits we experienced:

• High Availability: Aurora offers high availability features that ensure minimal downtime and data redundancy.
• Automated Scaling: The automatic scaling capabilities of Aurora Serverless v2 enabled seamless adjustments to database capacity based on real-time demands, ensuring optimal performance without manual intervention.
• Security: Amazon Aurora’s encryption features helped us maintain the security of sensitive PHI data.
• Performance: Aurora’s performance optimization and compatibility with MySQL 8.0 contributed to a smooth transition.
• Cost Efficiency: Aurora Serverless v2’s auto-pause and auto-resume capabilities allowed for efficient resource utilization, reducing costs during periods of inactivity.
• Enhanced Monitoring: The enhanced monitoring and diagnostic tools provided by Aurora Serverless v2 facilitated comprehensive visibility into the database’s performance, enabling proactive management and optimization of resources.

In conclusion, the migration was a complex but successful endeavor. It required careful planning, collaboration, and adherence to best practices. By choosing Aurora MySQL on AWS, we not only achieved a seamless migration but also gained access to a high-performance, scalable, and secure database solution.

This blog was co-authored by: Todd Coker, Senior MySQL/MariaDB Database Administrator

Todd serves as a primary DBA on Datavail’s MySQL and MariaDB Global Practice Team. With over two decades of IT experience, he has collaborated with customers in diverse areas such as e-commerce, finance, entertainment, education, government, healthcare, and more. Throughout his career, Todd has not only managed databases but has also actively contributed to developing solutions, ranging from performance tuning to designing database architectures and executing complex heterogeneous migrations. Todd holds an MS degree in Information Technology from the University of North Texas and a BA in History from the University of Texas at Arlington.