Microsoft SQL Server to MySQL: a cloud journey made easy

Alex Mihutski

Head of Database Migration Department, Ispirer Systems

Migrating from SQL Server to MySQL hosted on the cloud can feel like a burden. Once the decision to migrate a database to the cloud is made, a bunch of concerns about the migration process start to emerge: security concerns, cost concerns, performance concerns and others. The reason for that is clear - every business-critical action brings a number of risks that may derail your business. So let’s jump in - why do companies migrate their databases to clouds? How to set up the migration process from MSSQL to MySQL? What are the benefits of this transition? How to migrate SQL Server in the smoothest possible way?

In fact, 75 percent of databases are expected to be deployed in or migrated to the cloud this year, according to a Gartner’s report. It has to be for a reason.

Why migrate to a cloud?

There are various reasons why you might consider migrating your database to the cloud. Among them are the following:

• Reduced total cost of ownership (TCO). Shifting to the cloud offers a key incentive due to reduced operating expenses. Research from Deloitte reveals that a significant 55% of a company's annual IT budget is spent on internal maintenance. Cloud migration lets businesses tap into the efficiency of larger scale operations. Major cloud hosts like AWS, Azure, and GCP can trim costs for maintaining, powering, cooling, and staffing servers per unit, unlike private data centers.
• Enhanced Security. Skillfully implemented, the cloud can outshine on-premises network systems in terms of security. The cloud encompasses an array of systems, networks, and applications that demand meticulous and secure configuration and upkeep, guided by the "shared responsibility". Within this model, the burden falls on you to ensure your segment of the cloud remains secure. Noteworthy cloud providers, like Amazon, have laid the foundation of their cloud services with an unwavering focus on security within this model. AWS also has an array of compliance certifications including ISO27001, PCI-DSS, AICPA/SOC, and HIPAA, which signifies that if your organization holds specific compliance requisites or apprehensions, you can confidently rely on your data's safeguarding in the cloud.
• Reliability and stability. With regular backups, automated failover mechanisms, and advanced disaster recovery solutions, cloud databases minimize the risk of data loss and downtime. Scalability features allow databases to effortlessly accommodate growing workloads, maintaining optimal performance even during spikes in usage. Additionally, managed services from reputable cloud providers offer round-the-clock monitoring and support, further enhancing the stability and reliability of your database infrastructure.
• Scalability. It is much easier to grow and to keep (and improve) the quality of your work when using cloud-based solutions. Moreover, it is then your cloud provider that facilitates the growth of your infrastructure. What’s even better is that your cloud can adjust the level of assets needed for the amount of traffic you’re exposed to. In other words, clouds make it possible that scalability is instant.

Types of Cloud Migration

The six most common scenarios of cloud migration are rehosting, replatforming, repurchasing, refactoring, retaining, and retiring.

• Rehosting. Rehosting, also known as "lift and shift," is like moving a familiar piece of furniture to a new room. You take your application and place it in a cloud provider's space without changing much. It's like a stealthy move – the app keeps doing its job for your team or customers, but now it lives in a cloud home, cared for by experts.
• Replatforming also takes an on-premises application and moves it to a cloud provider’s infrastructure. In the replatforming process, however, the application is updated to take advantage of new technology or services available from the provider. For example, when moving a Human Capital Management application (HCM) from your own data center infrastructure to the cloud, you might replace a labor-intensive and older data management environment with an autonomous database that does automatic updates and offers built-in machine learning models.
• Repurchasing involves swapping out an application that runs on your own on-site data center for a cloud-powered Software-as-a-Service (SaaS) app created by a provider. Your team accesses this app using a web browser. Imagine moving from a self-owned ERP application to a cloud-based enterprise ERP service that you pay for on a subscription basis. This service automatically gets upgraded with new features a few times each year. While it might sound simple and straightforward, it requires time and careful planning. You need to align the new app's functions with your business processes, which may mean making changes to how your team works. It might even require organizational change to embrace the industry's best practices embedded in these cloud tools.
• Refactoring entails migrating an application to the cloud with the goal of updating its structure to leverage the inherent advantages of cloud-native capabilities. Consider a scenario where your organization has crafted a monolithic application over time. It still gets the job done, but adapting it for evolving customer demands or fresh opportunities is a struggle. Through refactoring, you have the chance to transform it into a microservices architecture. This shift makes developing, testing, and launching new features a breeze. Alternatively, you could integrate in-database analytics, streamlining analytical tasks without shuffling data across your system. This move amplifies the value you extract from your data.
• Retaining means you’ve looked closely at what it will take to move an application to the cloud and have determined that, for now, a move doesn’t make sense. Various factors could contribute to this decision. It could be that your application thrives on low latency, aligning better with an on-premises setup. Alternatively, strict data residency regulations might deter you from embracing a cloud provider's data center. Or, upon thorough analysis, you realize that the expenses and exertion of migrating eclipse the immediate benefits. Regardless of the rationale behind postponing cloud migration, it's a prudent approach to periodically reassess the situation. Cloud providers consistently expand their reach with new data centers worldwide, introduce fresh models to address data control concerns, and refine the migration process for enhanced efficiency.
• Retiring occurs when you closely examine an on-premises application and recognize that its functions are seldom utilized or have become obsolete. Saying goodbye to these applications streamlines the cloud migration journey by trimming redundancy or outdated processes that drain your resources without yielding benefits. However, retiring an application necessitates careful consideration and preparation. This is because there might be interdependencies with other applications that must be addressed before shutting it down.

Process of Cloud Migration

As noted previously, every enterprise takes its unique journey to the cloud. But, each journey requires several crucial steps. In case of moving to a cloud these steps are assessment, planning, migration itself, and cutover. Before we start reviewing the steps required for transferring your SQL Server database to MySQL, let’s dwell on the general approach to cloud migration.

Migration Assessment

• Inventory of apps and dbs. To scope your migration, you should first understand how many items, such as apps and hardware appliances, exist in your current environment, along with their dependencies. Building the inventory is a non-trivial task that requires a significant effort, especially when you don't have any automatic cataloging system in place. So, it is crucial to inventory all your apps and catalog them according to their dependencies and properties.
• Train and educate your team on Cloud Platform. Successful management of your future database hosted on cloud requires special expertise of your team. If necessary, provide additional training.
• Calculation of total cost of ownership (TCO) for a cloud environment. Having a clear view of all the resources that you need in a new environment allows you to compare a total cost of ownership model of your current environment with a new one on cloud.

Planning the migration

• Determine the migration order. After thoroughly assessing your current environment, you should decide on the starting order for moving your apps and dbs.
• Create a list of all necessary modifications to the existing applications or data.
• Create a roadmap for the migration process, detailing the workloads that need to be migrated and their migration order.

Migration process

Migration scenarios are rarely used in pure form in practice. As a rule, they are combined. Usually the Ispirer team deals with projects similar to refactoring, so the information in this paragraph is most relevant to such a scenario.

• Schema and APP migration. One of the critical steps in this migration process is the seamless transition of the database schema, as well as the migration of procedural extensions of SQL languages (for example, Transact-SQL to SQL in MySQL syntax when migrating SQL Server to MySQL). To ensure efficiency and accuracy, it's recommended to opt for a suitable migration method aligned with the overall plan. Simplifying this intricate task is made possible through the utilization of tools like the Ispirer Toolkit. This versatile tool automates the migration of the database schema, significantly reducing the need for manual intervention. The level of manual effort required is tailored to the specific nuances of your database, streamlining the process while maintaining precision.
• Application changes. The integration of the application into the new cloud environment necessitates careful attention to detail. All necessary modifications should be meticulously incorporated into the application as per the predefined plan. This stage is crucial to ensure that the application seamlessly adapts to the new database structure and environment. By adhering to the plan, you guarantee a harmonious synchronization between the migrated schema and the application.
• Functional testing and refactoring. After application changes are implemented, it's imperative to subject the entire system to rigorous functional testing. This phase not only validates the successful execution of the modifications but also identifies any potential glitches or inconsistencies. Refactoring, if required, ensures that the application operates optimally in the new cloud environment.
• Optimization process. The migration journey provides an excellent opportunity to optimize the performance of the database. Fine-tuning the system's performance in the cloud environment can lead to enhanced efficiency and responsiveness. Through careful analysis and strategic adjustments, you can ensure that your applications perform optimally while leveraging the capabilities of the cloud infrastructure.
• Data migration. One of the most critical aspects of the migration process is the seamless transfer of data. Depending on the unique requirements of your applications, data migration can be approached in various ways. Leveraging replication techniques further minimizes potential downtime, ensuring continuous availability of data as it's copied to the cloud.

Cutover

This is the final step when migrating databases to a cloud. This step basically includes switching the application to work with the new database.

How to migrate databases to the cloud with Ispirer Toolkit?

Moving from general to specific, now we will look at cloud migration in more detail based on the Microsoft SQL Server to MySQL migration using Ispirer Toolkit.

• Step 1: Assess Your Database
  Before embarking on a migration journey, take time to thoroughly assess your existing database. It is vital to fully understand its schema, data volume, performance characteristics, and any dependencies it has within your application stack. This assessment lays the foundation of a successful migration strategy. Ispirer Assessment Toolkit is a solution designed to automatically gather all the critical information about applications and databases that is necessary for migration. It also indicates an expected automation rate based on the analysis, so you can understand the potential effectiveness of Ispirer Toolkit for migrating your specific database.
• Step 2: Set Up Cloud Environment
  1. Create a Cloud environment to host your migrated database. This involves provisioning the necessary resources, such as virtual machines and storage, and configuring networking settings to ensure secure communication between your application and the database.
  2. Configure your cloud environment so that it accepts a connection with an external IP (if necessary, specify the IP of the computer on which the migration will be performed).
• Step 3: Schema Migration.
  Once your environment is set up, it's time to migrate a database schema. SQLWays Wizard, which is a part of Ispirer Toolkit, provides the ability to automatically migrate a schema from a SQL Server database to MySQL. Including the cases when the target database is in the cloud.
  In order to set up the migration, follow the steps described below:
  1. Specify the connection to the source database.
  2. Specify the connection to the MySQL database. If necessary, provide the SSL certificate in the tool.
  3. Select objects/schemes for migration according to the migration plan.
  4. Convert all objects. SQLWays Wizard allows you to automatically upload converted objects to the database on the cloud. Thus, all the converted schema objects will already exist in the cloud.
  Based on reports and logs, make sure that all objects have been successfully created in the target MySQL database. In case of any errors, they can be eliminated in one of the following ways: by changing the settings of the SQLWays Wizard (do not hesitate to contact Ispirer support agents to speed up the process of correct configuration), by customizing the SQLWays, or manually.

Learn more about how to migrate from SQL Server to MySQL here:



• Step 4: Application modification.
  Changing the database management system(SQL Server to MySQL) as well as storing the database on a cloud leads to inevitable changes in the application. It is necessary not only to change the connection to the database, but also to change all work with it, which includes calls to all business logic objects from the database as well as the conversion of the entire T-SQL to SQL in MySQL syntax.
  1. Conversion of all embedded SQL statements in the application (if any). Ispirer provides NglFly Wizard which is capable of converting embedded SQL from Transact-SQL to another SQL language in automatic mode. With this tool, you can directly convert all embedded statements without even taking them out of the application code.
  2. If the application uses an API that supports MySQL, such as JDBC or ODBC, then these changes are easy to make manually. Other instances require more complex activities, the scope of which depends on the specific situation.
• Step 5 Functional and performance testing.
  It goes without saying that testing is necessary to ensure high performance and proper operation of the migrated system. Database migration always comes along with functional and performance testing. Both of them are an integral part of the migration process. It allows you to identify all potential problems with the performance and resolve them.
  It should be noted that functional, as well as performance testing, should be performed with data. SQLWays Wizard allows you to migrate data between different database systems, as well as upload data to the cloud. To optimize the entire migration process, we recommend combining data migration for testing with trial downloads prior to production migration.
  Functional testing involves calling scripts for working with database objects from the application. It allows you to identify all logical conversion errors. In order to simplify functional and performance testing, we recommend using test cases on which the source database was tested.
• Step 6: Data migration
  1. Data migration is a very important stage in database migration.
  2. Choose the appropriate data migration option (offline or online migration), taking into account the possible database and application downtime.
  3. Conduct test migrations and identify all problem areas and ways to solve them.
  4. Automate the process (write all scripts and scripts to run the migration).
  5. Conduct a prod migration.
• Step 7: Cutover
  After the database is converted, deployed, configured, and finally the data is transferred, you need to switch the application to work with the new MySQL database in the cloud.

Benefits of Cloud Migration with Ispirer Toolkit

Migrating from SQL Server to a new database presents various options, with Ispirer standing out for several key reasons:

• 20+ years on the market
• Dozens of successfully implemented SQL Server to MySQL migration projects.
• The highest level of schema conversion compared to other migration tools. You can expect that most of the business logic will be converted automatically without errors, which significantly reduces the time for the most time-consuming migration process - schema conversion. The level of automatic schema conversion out of the box can reach 90 percent (depends on the complexity of the source code). Ample opportunities for customization of Ispirer Toolkit for a specific project. The toolkit provides its users with extensive opportunities for customization of the Ispirer Toolkit. Also, if necessary, we can correct the already existing conversion rules to increase the conversion rate.
• Compatibility with cloud technologies. The tool is fully optimized for cloud migration and supports transitions to the most popular cloud platforms, including Google Cloud Platform (GCP), Amazon Web services (AWS), Microsoft Azure with the same efficiency as for on-premises transitions.
• Data Migration. Ispirer toolkit allows you to migrate data as part of cross-database migrations. The migration speed can reach 50 GB per hour, which allows you to migrate even large databases with minimal latency and downtime.
• Cost Efficiency. Experience the advantage of project-based licensing with Ispirer Toolkit, where you only pay for what you need based on the scope of your migration project. Unlike fixed price models found in other tools, our approach ensures that you're not burdened with overpayments. With Ispirer Toolkit, your costs align with the scale and complexity of your specific migration. Thus, you get a cost-effective and flexible solution tailored to your requirements.

In the dynamic landscape of database migration, a seamless and efficient transition is key to harnessing the full potential of your data in the cloud. To complete your migration journey from SQL Server to MySQL in the cloud, consider leveraging the Ispirer Toolkit. With its advanced capabilities in automated database migration, the Toolkit can significantly streamline the migration process, ensuring data accuracy, minimizing downtime, and preserving the integrity of your application. Embrace the future of data management with confidence, backed by the robust features and support provided by the Ispirer Toolkit. Your journey to a cloud-powered MySQL ecosystem starts here.