Migrating from MSSQL to MySQL


MySQL is the world's most popular open-source database — easy to use while still delivering performance, reliability, and scalability. It's the first choice for web-based applications, used by Facebook, Twitter, YouTube, Yahoo!, Wikipedia, and thousands of midsized companies, and embedded in the majority of software products.

Why Migrate

Since Oracle Corporation's acquisition of MySQL, it has received special focus in development, reflected in the steady stream of updates. One of the biggest benefits cited by users in surveys is low cost — running and maintaining MySQL costs a fraction of other databases.

Unlike MS SQL Server, MySQL runs on almost every operating system — Linux, Windows, macOS, Solaris, and more — so customers aren't locked into a single platform. MySQL's easy installers get you up and running in minutes, and self-administration features like automatic database extension, auto restart, and dynamic configuration changes make ongoing maintenance easy for DBAs.

Key MySQL Features

Open source and commercial licenses; available on all major 32/64-bit platforms; ANSI SQL, subqueries, joins, cursors, and prepared statements; stored procedures, triggers, and user-defined functions; updateable views; ACID transactions with commit/rollback; distributed transactions; row-level locking; server-enforced referential integrity; strong data type support; robust indexing (clustered, B-Tree, hash, full-text); replication; high availability with auto failover; partitioning; VLDB (terabyte) capable; online backup with point-in-time recovery; auditing; a built-in job scheduler; and drivers for ODBC, JDBC, .NET, PHP, and more.

Preparing for MS SQL Server to MySQL Migration

  1. Document the source MS SQL Server database objects — user accounts, tables, indexes, integrity constraints, and foreign key relationships.
  2. Create the target MySQL database with the proper size.
  3. Choose your method for moving tables, indexes, constraints, and relationships — the MySQL native migration workbench, a third-party tool like Data Loader, or custom programs using OLE DB Providers, ODBC drivers, or a .NET provider.

Both MS SQL Server and MySQL provide OLE DB Providers, ODBC drivers, and .NET providers for different platforms. Writing programs from scratch can be difficult, especially with a large number of tables — you'll need to carefully choose comparable datatypes.

Datatypes with an Exact Match

The following MS SQL Server datatypes have an exact match in MySQL:

BINARY · BIT · CHAR · CHARACTER · DATETIME · DEC, DECIMAL · FLOAT · DOUBLE PRECISION · INT, INTEGER · NCHAR · NVARCHAR, NCHAR · NUMERIC · REAL · SMALLINT · TEXT · TIMESTAMP · TINYINT · VARBINARY · VARCHAR

Datatypes Without an Exact Match

These SQL Server datatypes don't have an exact MySQL equivalent, but can be mapped as follows:

SQL Server MySQL
IDENTITY AUTO_INCREMENT
NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF8
SMALLDATETIME DATETIME
MONEY DECIMAL(19,4)
SMALL MONEY DECIMAL(10,4)
UNIQUEIDENTIFIER BINARY(16)
SYSNAME CHAR(256)

Data Loader from Interface Computers does all of this database migration from MSSQL to MySQL automatically, including automatic datatype matching. It just takes three steps: define the source database, define the target database, and select tables.

Download the Free Trial Edition