Guidance with MariaDB migration to Azure SQL Server

Question

Hello,

I am currently working on migrating a MariaDB database hosted on AWS to Azure as part of a cloud consolidation program. While Azure Database for MariaDB seemed like a natural choice, Microsoft's planned retirement of this service by 2025 has led us to explore alternative options.

Initially, Azure Database for MySQL appeared to be the most suitable alternative; however, my organization does not support that platform. As a result, we are now considering migrating the data to an SQL Server instance. However, we are aware that there could be potential incompatibilities between MariaDB and SQL Server.

Based on my research, the primary incompatibilities that we need to account for are as follows:

Data Types: Differences in how MariaDB (e.g., DATETIME, TIMESTAMP) and SQL Server (e.g., DATETIME, SMALLDATETIME) handle date and time, particularly in relation to time zone support.

Table Engine: MariaDB uses the InnoDB storage engine by default, which does not have a direct equivalent in SQL Server. We will need to assess how certain engine-specific features, like full-text search and indexing, can be handled or replaced in SQL Server.

I have attached a more detailed list of the potential incompatibilities we have identified.

Could you please provide any guidance on how we might address or mitigate these incompatibilities during the migration? Any advice or best practices would be greatly appreciated.

Many thanks in advance for your assistance.

Best regards, Marc

Incompatibilities_between_SQLServer_MariaDB.txt

Answer 1

@Marc Hedgley
Thank you for the question and for using Microsoft Q&A platform.

Migrating from MariaDB to SQL Server can indeed present some challenges, but with careful planning and the right strategies, you can mitigate these incompatibilities. Here are some guidelines and best practices to help you through the process:

1. Data Types

• Date and Time Handling:
  • MariaDB: Uses DATETIME and TIMESTAMP with time zone support. SQL Server: Uses DATETIME, SMALLDATETIME, DATETIME2, and DATETIMEOFFSET.
  • Solution: Map MariaDB DATETIME and TIMESTAMP to SQL Server DATETIME2 or DATETIMEOFFSET to ensure precision and time zone support. AUTO_INCREMENT vs. IDENTITY:
    • Solution: Use SQL Server’s IDENTITY property for primary key fields. Tools like SQL Server Migration Assistant (SSMA) can help map AUTO_INCREMENT to IDENTITY.
  • Date and Time Types:
    • Solution: Map MariaDB DATETIME and TIMESTAMP to SQL Server DATETIME2 or DATETIMEOFFSET to handle precision and time zones. Use SSMA or custom scripts to ensure correct mapping.
  • Boolean Data Type:
    • Solution: Convert MariaDB TINYINT(1) to SQL Server BIT. This can be done using SSMA or during the data transformation phase.

2. Table Engine

• Storage Engine Differences:
  • MariaDB: Uses InnoDB by default, which supports features like full-text search and indexing.
  • SQL Server: Does not have a direct equivalent to InnoDB but supports similar features through its own mechanisms.
  • Solution:
    • Full-Text Search: Use SQL Server’s Full-Text Search feature.
    • Indexing: Review and recreate indexes in SQL Server to match the performance characteristics of your MariaDB setup.
• Foreign Key Constraints
  1. Handling Constraints:
     • Solution: Review and adjust foreign key constraints to match SQL Server’s enforcement. Pay special attention to cascading updates and deletes.

3. Schema and Data Migration

• Tools: Use tools like the SQL Server Migration Assistant (SSMA) for MySQL, which can help automate the migration process and handle schema conversion.
• Scripts: Write custom scripts to handle specific data transformations that SSMA might not cover.

4. Testing and Validation

• Data Integrity: Ensure that data integrity is maintained during the migration by performing thorough testing.
• Performance Testing: Conduct performance testing to compare the performance of your queries in SQL Server against their performance in MariaDB.

5. Application Changes

• Connection Strings: Update your application’s connection strings to point to the new SQL Server instance.
• Query Adjustments: Modify any application queries that rely on MariaDB-specific features or syntax to be compatible with SQL Server.

6. Query Syntax

LIMIT and OFFSET:

• Solution: Convert MariaDB’s LIMIT to SQL Server’s OFFSET-FETCH. This requires rewriting queries that use pagination.

1. JOINs:
   • Solution: Adjust JOIN syntax to match SQL Server’s handling, especially for advanced features like OUTER APPLY or CROSS APPLY.
2. Indexes
   1. Index Types:
      • Solution: Recreate MariaDB’s indexes in SQL Server. For full-text indexing, use SQL Server’s Full-Text Search. Spatial indexes may require specific handling or conversion.
3. Triggers
   1. Trigger Syntax:
      • Solution: Rewrite triggers to match SQL Server’s syntax. Note that SQL Server does not support triggers on views directly, so alternative solutions may be needed.
4. Stored Procedures and Functions
5. Syntax Differences:
   • Solution: Rewrite stored procedures, triggers, and functions to match SQL Server’s T-SQL syntax. Tools like SSMA can assist, but manual adjustments may be necessary.
6. Error Handling:
   • Solution: Replace MariaDB’s SIGNAL and RESIGNAL with SQL Server’s THROW or RAISEERROR. This requires manual rewriting of error handling logic.
7. Tools and Resources

• SQL Server Migration Assistant (SSMA): A tool to automate much of the migration process, including schema and data conversion.
• Custom Scripts: For specific transformations that SSMA cannot handle.
• Testing and Validation: Thoroughly test the migrated data and application functionality to ensure everything works as expected.

You can refer to the below links for more help:
https://www.geeksforgeeks.org/migrating-data-from-sql-server-to-mariadb/
https://mariadb.com/kb/en/moving-data-between-sql-server-and-mariadb/
https://hevodata.com/learn/mariadb-to-sql-server/
https://estuary.dev/mariadb-to-sql-server/
https://www.dbsofts.com/articles/mariadb_to_sql_server/

By following these guidelines, you can address the primary incompatibilities and ensure a smoother transition from MariaDB to SQL Server.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.