Problem
I had the chance to work on a project to create a new SQL Server database by moving data from an existing MySQL database. The process required me to go through the MySQL database and create scripts for each database object to be created in the SQL Server database. After the database was created in SQL Server, I needed a process to move the data an initial time as well as set up a process to make updates to the SQL Server database from the updates occurring in the MySQL database.
Solution
After some research on Google, I discovered a really useful program, SQL Server Migration Assistant for MySQL. This tool can be used to migrate data from MySQL to SQL Server database and convert the database structure from MySQL to SQL Server. This tool can be downloaded here.
Here are some additional related articles on this tool:
- SQL Server Migration Assistant for MySQL (MySQLToSQL)
- Planning your migration from MySQL to SQL Server
- SQL Server Migration Assistant 2008 SSMA
After the initial load, I needed to build a process to update the SQL Server database for the data changes occurring in the MySQL database.
Ongoing Data Updates from MySQL to SQL Server
This tip focuses on solving some issues that arise when you want to automate the merging of the data once the migration is complete. It is impracticable to continue utilizing the migration assistant once the database has been moved to a new SQL Server database. If the MySQL data can be queried from SQL Server Management Studio (SSMS) then the data can be merged by using a MERGE T-SQL statement in a SQL Server. We will look at how this can be done by creating a linked server from SQL Server to the MySQL database.
Creating a Linked Server
To query the data on the MySQL database, a linked server can be created in SSMS and after the linked server is created, you just have to create a MERGE statement, easy right? This is where a few problems came up that I would like to share.
Let us start by creating an ODBC Data source that will be used by the linked server.
Create the ODBC Data Source
A linked server needs to use an ODBC Data source and needs to be configured on the system where you will be working with SSMS.
Follow the steps below to create the ODBC Data source:
- Open Control Panel, go to Administrative Tools and open the ODBC Data Sources (64-bit) and the below window will open
- On the System DSN tab click the Add button
On the Create New Data Source popup (image below), select the MySQL ODBC 8.0 ANSI Driver and click Finish. If you do not have an ODBC Driver installed for MySQL, you can download and install the driver from: MySQL Product Archives.
Complete the textboxes (image below) in yellow as follows:
- Data Source Name: Choose a name you want for your connection that you will use when configuring the Linked server.
- TCP/IP Server: The server where your MySQL database is running. Use localhost when it is running on your local system.
- User: The user that is used to log in to the MySQL database.
- Password: The password for the above user.
- Database: This field can be kept empty.
Click on the Test button to test the connection. If the connection test is successful, you should get a Connection Successful message and click OK.
Click on the OK button to create the ODBC Data source and it should be visible in the ODBC Data Source Administrator Window’s System DSN tab.
Create the linked server
In SSMS, log in to SQL Server where you will be migrating the data. At this stage, it is assumed the database has already been created in SQL Server.
The database used for this tip will be the HRDatabase, which contains two tables: Companies and Employees.
In SSMS Object Explorer, expand the SQL Server that you are logged in to, expand Server Objects, expand Linked Servers, and if you have any previously created linked servers they will be visible here. In this case, we will be creating the first linked server on this SQL Server.
Right-click on the Linked Servers folder and select New Linked Server which will open up the new linked server window.
Below is the New Linked Server window, complete the textboxes as highlighted in yellow.
On the General page:
- Linked Server: Choose a name you want to use for the linked server
- Server type: Choose other data source
- Provider: Choose Microsoft OLE DB Provider for ODBC Drivers, as you have created the ODBC Connection in the first part of this tip
- Product name: Choose any name your want
- Data source: Use the name of the ODBC Data Source as in the first part of this tip
- Provider string: leave blank
- Location: leave blank
- Catalog: leave blank
On the Security page:
- For a login not defined in the list above, connections will:
- Be made using this security context:
- Remote Login: Use what was used in the ODBC Data Source config above
- With password: Use the password used in the ODBC Data Source config above
- Be made using this security context:
On the Server Options page:
- All options on this page can be left as is.
Next, click OK to create the linked server. If the login details are wrong, you will be presented with an error.
After you have created the new linked server, it should be visible in the list below the Linked Server folder. If you expand the newly created linked server, you should see the databases on the MySQL server via the linked server as shown below.
Query the MySQL database
Now that the linked server to the MySQL database has been created and you have the details for queries:
- Linked server: MYSQL_CONNECTION
- Database: hrdatabase
- Table: companies
This select query should be straightforward:
SELECT * FROM MYSQL_CONNECTION.hrdatabase..companies
When running the above query, we get the following error:
The OLE DB provider “MSDASQL” for linked server “MYSQL_CONNECTION” supplied inconsistent metadata for a column.
The column “CreateDate” (compile-time ordinal 6) of object “‘hrdatabase’.’companies'” was reported to have a
“DBTYPE” of 133 at compile time and 135 at run time.
We will cover how to get around this error below, but another option is to use the OPENQUERY syntax. See OPENQUERY (Transact-SQL) for more details.
The syntax is as follows:
OPENQUERY (<linked_server>,'query')
The query could be rewritten as follows:
SELECT * FROM OPENQUERY(MYSQL_CONNECTION, 'SELECT * FROM hrdatabase.companies')
See the result set below which works perfectly.
The error we got above, was due to the CreateDate column. After spending some time to try and solve the error, I managed to find the problem.
The first sentence of the error reads:
If we open the window where the ODBC connection was set up: MySQL Connector/ODBC Data Source Configuration and click on the Details button, then on the Metadata tab and you will find an option at the bottom: Don’t use INFORMATION_SCHEMA for metadata. Check the check-box next to it and click the OK button. See the image below.
The above option (Don’t use INFORMATION_SCHEMA for metadata), was implemented to fix the bug in the MySQL Connector/ODBC: “A buffer overrun inside SQLColumns() caused Connector/ODBC to unexpectedly halt.”
Now you can try the normal select query again on the companies table and see the result.
SELECT * FROM MYSQL_CONNECTION.hrdatabase..companies
SQL operations to sync the data
Below are some SQL statements that can be used to sync the data from the MySQL (Linked) server (source) to the MS SQL Server (target) database.
This is the target server to sync the data to:
-- Drop the table if it exists
DROP TABLE IF EXISTS Companies_MySQL
-- Create the table
CREATE TABLE Companies_MySQL(
Id int IDENTITY(1,1) NOT NULL,
CompanyName varchar(80) NOT NULL,
CompAddress varchar(80) NOT NULL,
CompContactNo varchar(20) NOT NULL,
IsActive bit NULL,
CreateDate datetime2(0) NULL,
CONSTRAINT PK_companies_Id PRIMARY KEY CLUSTERED (Id)
)
Use an INSERT..SELECT statement to update the data in the target table that was created above:
-- Set the identity insert to on and insert records from the Linked server
SET IDENTITY_INSERT Companies_MySQL ON
INSERT INTO dbo.Companies_MySQL (Id, CompanyName, CompAddress, CompContactNo, IsActive, CreateDate)
SELECT
Id
, CompanyName
, CompAddress
, CompContactNo
, IsActive
, CreateDate
FROM MYSQL_CONNECTION.hrdatabase..companies
SET IDENTITY_INSERT Companies_MySQL OFF
-- Check if data is in new table
SELECT * FROM dbo.Companies_MySQL
Use a MERGE SQL Statement to do the below operation to the target table:
- UPDATE
- INSERT
- DELETE
-- Merge statement to sync the two tables
BEGIN
DECLARE
@Commit bit = 1
SET IDENTITY_INSERT dbo.Companies_MySQL ON
BEGIN TRAN
MERGE dbo.Companies_MySQL tgt
USING (SELECT * FROM MYSQL_CONNECTION.hrdatabase..companies) src
ON (src.Id = tgt.Id)
WHEN MATCHED AND EXISTS (SELECT
src.Id
,src.CompanyName
,src.CompAddress
,src.CompContactNo
,src.IsActive
,src.CreateDate
EXCEPT SELECT
tgt.Id
,tgt.CompanyName
,tgt.CompAddress
,tgt.CompContactNo
,tgt.IsActive
,tgt.CreateDate
)
THEN UPDATE SET
tgt.CompanyName= src.CompanyName
,tgt.CompAddress= src.CompAddress
,tgt.CompContactNo= src.CompContactNo
,tgt.IsActive = src.IsActive
,tgt.CreateDate= src.CreateDate
WHEN NOT MATCHED BY TARGET
THEN INSERT (
Id
,CompanyName
,CompAddress
,CompContactNo
,IsActive
,CreateDate
) VALUES (
src.Id
,src.CompanyName
,src.CompAddress
,src.CompContactNo
,src.IsActive
,src.CreateDate
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT
$action,
inserted.*,
deleted.*;
IF (@Commit=0)
BEGIN
ROLLBACK TRAN;
END
ELSE
BEGIN
COMMIT TRAN;
END
;
SET IDENTITY_INSERT dbo.Companies_MySQL OFF
END
-- Check if data is in new table
SELECT * FROM dbo.Companies_MySQL
Conclusion
In this tip, we worked through setting up a linked server on a SQL Server instance to be able to do SQL queries from a MySQL database and highlighted some of the issues that might be encountered in the process.
We also looked at some SQL statements to create a target table, do an INSERT from a SELECT query and also creating a MERGE statement to do the syncing of the data between the MySQL (linked) server and the SQL Server databases.
Next Steps
