MySQL to SQL Server: Migration at the Driver’s Seat

Alex Mihutski

Head of Database Migration Department, Ispirer Systems

Database migration is an inevitable challenge that all organizations face at some point. If a company has already decided for itself that it needs to migrate, at that moment two questions arise: where and how?

In the field of migration, as elsewhere, there are trends and tendencies. In recent years, migration from MySQL to SQL Server has been actively gaining momentum. Apparently, database migration is a sophisticated process that requires meticulous planning and a high level of expertise. Today, we will thoroughly examine this topic. We will talk about the advantages of moving in this direction, compare various migration methods, and focus on the key aspects of the MySQL to SQL Server migration direction.

Why Migrate MySQL to SQL Server? What are the benefits?

Let's look at the main benefits that changing a MySQL database to SQL Server will bring.

• Enterprise features. SQL Server often tends to be an appealing solution to many companies because it provides a number of enterprise features. This includes features like built-in reporting services, analytics services, and integration services. Owing to these features utilizing SQL Server becomes the most efficient.
• Scalability. Even though MySQL is known for its performance and scalability, SQL Server often provides advanced scalability options, particularly for very large databases and high-volume transactional systems.
• Integration with Microsoft Ecosystem. If a company is already heavily invested or is planning to do so in the Microsoft ecosystem, using SQL Server can provide tighter integration with other Microsoft products and services, such as Azure cloud services, Power BI for analytics, and .NET for development.
• Security. In the matter of security, most modern databases offer rich opportunities for data safety. However, SQL Server ranks first in this metric, as its security measures include advanced encryption capabilities, fine-grained access controls, and built-in auditing, which may be important for companies with strict security requirements.

SSMA or Ispirer Toolkit?

Microsoft SQL Server Migration Assistant is among various tools that streamline the process of transferring a database or restoring a backup. It simplifies the database migration journey by automating all aspects of the process, including schema and SQL statement conversion, as well as data migration. SSMA also provides support and guidance throughout the migration process.

Features of SSMA:

• Migration of SQL objects. The SQL Server Migration Assistant can convert SQL objects, including stored procedures, from MySQL to SQL Server. During the conversion process, SSMA takes the object definitions from MySQL, converts them to similar SQL Server objects, and then loads this information into the SSMA metadata. The tool converts stored procedures directly to Transact-SQL. In some cases, SSMA creates two stored procedures: one that implements the procedure, and another that is used for calling the implementing stored procedure. It's important to note that while SSMA attempts to convert your views and stored procedures, it might not translate all the code structures, and even where it does, the resulting T-SQL might require manual review or revision.
• Free to use. A significant advantage of SSMA is that it's available at no cost, and the required version can be directly downloaded from Microsoft.
• Straightforward data migration process. SSMA is easy to operate. The basic process for migrating a database consists of well-defined steps. Once you have downloaded and opened the Microsoft SQL Server Migration Assistant, you create a project and set the desired destination database. Next, you connect to your source database by selecting the provider and inputting any necessary login information. You then choose the data you want to load in SSMA for migration.
• Requires knowledge of Python, C# and SQL. Python expertise facilitates the creation of custom scripts, enabling tasks like data preprocessing and automation, thereby streamlining the migration process. Additionally, developers skilled in C# can utilize the SSMA SDK to tailor custom rules and assessments for specific migration scenarios. However, it's important to note that SSMA heavily relies on SQL for crucial functions such as schema analysis, data migration, and compatibility assessments between source and target databases. Thus, proficiency in SQL is imperative for understanding and resolving any migration-related issues efficiently.
• User interface. In fact, it is hard to call this tool a user-friendly solution. Programming forums are full of numerous complaints from users stating that the user interface of SSMA is complicated and hard to understand. The user interface is minimal and somewhat outdated, but it is certainly functional.
• Restricted migration directions. SSMA helps to automate the process of migrating databases to a limited range of databases. The target databases in this list include SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The tool is able to migrate from various database systems like Microsoft Access, DB2, MySQL, Oracle, and SAP ASE.

Now, let’s look at the other option. Ispirer Toolkit is a solution that converts both databases and applications automatically. As we are talking about MySQL to SQL Server migration today, we will mainly focus on the process of database migration.

SQLWays Wizard, which is a part of Ispirer Toolkit, is designed for heterogeneous database migration. It automatically migrates data, as well as the entire MySQL schema, including tables, indexes, stored procedures, functions, triggers, views, etc.

Ispirer Toolkit distinguishes itself from competitors through several key advantages:

• GUI and CLI modes. Ispirer Toolkit offers both a graphical user interface (GUI) and a command-line interface (CLI), providing users with adaptable options according to their liking. The GUI is designed for those who favor a graphical approach, whereas the CLI is suitable for scheduling tasks. This adaptability is a significant feature to take into account, depending on the user's preferences. Moreover, Ispirer Toolkit offers drag-and-drop UIs, which may be more user-friendly for individuals less familiar with programming languages compared to SSMA which requires some programming knowledge.
• Broad Source and Target Compatibility. Ispirer Toolkit can facilitate migration across over 20 source and target RDBMSs, making it versatile for a range of database environments. This versatility is beneficial for projects with intricate data structures, enabling Ispirer Toolkit to manage various scenarios efficiently.
• Selective Data Migration. Ispirer Toolkit provides the option for users to transfer selected data from certain tables. This function is particularly beneficial for large databases where only a portion of data needs to be transferred, thereby streamlining the migration process.
• Data Type Mapping. Ispirer Toolkit facilitates transitioning between MySQL and SQL Server databases by effectively mapping data types. Ispirer Toolkit supports mapping both for the entire database and for individual columns. Altering the names of columns, tables, and schemas provides adaptability to accommodate different database architectures.
• SQL Support. Ispirer Toolkit offers an economical approach for the migration of SQL objects. Its flexible pricing for smaller databases makes it a budget-friendly choice, particularly when contrasted with manual conversion techniques. Users have the ability to modify SQL object movements and customize the migration procedure to meet unique project needs.

Ispirer Toolkit can save hundreds of migration hours with such features as global data type mapping, local data type mapping, conversion of global variables, support of user-defined data type, ability to specify CHARACTER SET to load data, etc. All these features make it possible to optimize MySQL to SQL Server migration and make it as fast as possible.

Data Migration

Migrating data from MySQL to SQL Server efficiently requires a robust implementation to ensure seamless transition without compromising data integrity. Migration depends on various factors, namely:

• Data Types. MySQL and SQL Server support similar, but not identical data types. As a matter of fact, data type mismatch can affect code functionality. When migrating data, the most useful feature may be mapping data types for individual columns.
• Data Volume and Downtime. Large databases or databases with high transaction rates may require careful planning to minimize downtime during the migration process. Techniques such as replication, data chunking, or using migration tools can help mitigate these challenges.
• Testing and Validation. After migrating the data, it's crucial to thoroughly test and validate the migrated database to ensure that all data has been transferred correctly and that the application functions as expected with the new database.

Utilizing specialized migration tools such as Ispirer Toolkit can streamline the process by automating data migration. In fact, the speed of data migration from MySQL to SQL Server can reach 40 GB/h.

SQL Conversion

As a rule, migrating SQL code takes a lot of time even for the most experienced developer. Why? Because sql code includes a huge number of peculiarities, features unique to each database, which requires perseverance and attention. Added to all this is a gigantic amount of code, which stretches the transfer of the database schema for many months, or even years.

This is where migration solutions come to the rescue. SSMA is designed for schema migration, but in fact it can only handle simple migration tasks. SSMA is unlikely to be able to migrate a huge database with a bunch of specific SQL. It will take a lot of effort and time to follow up on what it wasn’t able to convert.

As for Ispirer Toolkit, this product is worthy of consideration. Since this is an expert system, the tool can cope with any database, regardless of its size and complexity. The product has been on the market for more than 20 years, which means that the tool has been tested by time and hundreds of migration projects.

What about a complex codebase? Of course, each database contains a certain number of unique source codes that cannot be overcome by an open source free converter. However, to migrate unique code structures, you can customize the Ispirer Toolkit and achieve even 99% migration automation for your MySQL to SQL Server migration project.

To sum it up, SSMA may definitely become an assistant in database migration. However, it will show maximum efficiency in small projects of moderate complexity. If you have a large and sophisticated MySQL database, migration may require a great deal of time and effort. In such a case you should consider more feature-rich tools, such as Ispirer Toolkit. Hundreds of options to configure your migration will help you achieve close to 100% automation, saving a lot of time on migration.

Summing up

In conclusion, the process of database migration is intricate, and choosing the appropriate tool is vital. After examining two notable solutions, the choice ultimately depends on the distinct needs of each project. Take into account the specific demands, volume, and complexity before choosing from the available options.

For those in search of a comprehensive all-in-one solution, Ispirer Toolkit is worth considering. Get a free 30-day trial on the website to test its capabilities and make a well-informed decision. This helps you decide if it fits your project and will enable you to achieve your data migration goals.