Access MySQL data from SQL Server via a Linked Server

Problem

There are often times when you need to access data from MySQL from within SQL Server.  You could export and import the data, but another option is to create a linked server from SQL Server to MySQL. In this tip we walk through how this can be done for MySQL from SQL Server.

Solution

In this tip, we will show how SQL Server can access MySQL data via a linked server.  You can get a copy of the latest version of Connector/ODBC binaries and source from: http://dev.mysql.com/downloads/connector/odbc/.

Before installing the Connector/ODBC driver on Windows:

• Make sure your Microsoft Data Access Components (MDAC) are up to date.
• Make sure you have the Microsoft Visual C++ 2013 Redistributable Package on your system.

The steps to complete the installation are very simple, just follow the installation steps in the tool.

Once the driver has been installed you will see the driver in the ODBC Data Source Administrator.

To connect from SQL Server we will create a connection via a Linked Server, before that we need to understand the connection string we will use.

For example:

The table below has the parameters that can be used for the connection string.

Recommended Connector/ODBC Option Values for Different Configurations

The option argument is used to tell Connector/ODBC that the client isn’t 100% ODBC compliant.

Create a SQL Server Linked Server to MySQL

In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server. On the General page do the following:

1. Linked server – type the name of the MySQL server you want to link to.
2. Server type – select Other data source
3. Provider – specify an OLE DB Provider, in this case I selected Microsoft OLE DB Provider for ODBC Drivers
4. Provider string – enter the connection string we discussed above Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=database_name;OPTION=134217728;PWD=user_password;UID=user_identification;SERVER=server_name)

On the Server Options page we need to adjust the RPC and RPC Out settings to True. Think about an RPC (Remote Procedure Call) as being a stored procedure being run remotely from server 1 to linked server 2.

You can also create a linked server using this T-SQL command:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
@server = N'MYSQL'
,@srvproduct=N'MySQL'
,@provider=N'MSDASQL'
,@provstr=N'Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=test;OPTION=134217728;PWD=P@ssw0rd;UID=test;SERVER=srvtest'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'true'
GO

Access MySQL data from SQL Server

After the linked server has been created you will be able to see the remote objects in the object explorer.

We can also run a query to access the data.

Connector/ODBC Performance

The Connector/ODBC driver has been optimized to provide very fast performance. If you experience problems with the performance of Connector/ODBC or notice a large amount of disk activity for simple queries, there are a number of aspects to check:

• Ensure that ODBC Tracing is not enabled. With tracing enabled, a lot of information is recorded in the tracing file by the ODBC Manager. You can check, and disable tracing within Windows using the Tracing panel of the ODBC Data Source Administrator.
• Make sure you are using the standard version of the driver and not the debug version. The debug version includes additional checks and reporting measures.
• Ensure that the driver trace (option value 4) and query logging (option value 524288) are not enabled.

Next Steps

• More about option parameters here.
• Connector/ODBC errors FAQ.
• Learn about how to export data from MySQL to SQL Server from this tip.