Linked Server: SQL Server to MySQL

Using SSIS is a great way to perform ETL operations and bulk load or process data coming from multiple databases, especially if various database vendors are involved. However, setting up a full SSIS package just to do a quick query, import, or join is cumbersome and time consuming. Thankfully, setting up a Linked Server in SQL Server can be a straightforward task to keep productivity high in these types of situations.

The Goal

In this example, we will see how to set up a link to a MySQL instance using SQL Server. By doing this, we will have access to write queries against a remote MySQL database any time data access is needed. We will even be able to JOIN against tables in our SQL Server database to provide quick analysis across disparate database engines.

Installing the MySQL driver

The first step to setting up the linked server is to download and install the MySQL ODBC driver. For this, go to the MySQL ODBC driver download page and select the driver that meets your OS specifications.

Verifying the driver install

After installing the driver, check the ODBC data sources to make sure the driver was successfully installed. The easiest way to do this is to search for ‘ODBC’ from the Windows Start Menu and select either the 32-bit or 64-bit option depending on which driver was installed.

From the ODBC Data Source Administration window, select the ‘Drivers’ tab to verify the install. Take note of the driver name; this exact string will be used later when setting up the linked server.

Setting up the Linked Server

Now that the driver is installed, we are ready to set up the linked server. For this, open SQL Server Management Studio and connect to the local server. Expand Object Explorer tree for the connection to find the ‘Linked Servers’ section (Server Objects -> Linked Servers)

Right click on ‘Linked Servers’ and click ‘New Linked Server…’

This will bring up the ‘New Linked Server’ dialog.

Give the linked server a name (in my example ‘DATAWAREHOUSE’) . Since we are linking to a MySQL instance, select ‘Other data source’ and fill out as follows:

  • Provider: Microsoft OLE DB Provider for ODBC Drivers
  • Product name: MySQL

Now we need to fill out the Provider string for the connection. For this example, my provider string is below:

Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=testapp;PWD=test;UID=jon;OPTION=134217728;SERVER=my_remote_server;PORT=3306)

Notice the driver name is exactly the name of the driver from the ODBC driver installation. The MySQL ODBC driver has many options available for the provider string. We will only concentrate on the few needed for a basic connection, however, you should take a look at the full list here in order to make sure your connection does not need additional values.

One additional parameter for us connecting from SQL Server is the OPTION parameter. For connections from SQL Server, we set this to the recommended value of 134217728. Be sure to see the full list of recommended parameter options depending on your configuration.

One final step before creating the linked server is to change make sure that ‘RCP’ and ‘RCP Out’ are set to True on the ‘Server Options’ tab.

Click ‘OK’ to created the linked server. The server should now appear in the Object Explorer.

An alternative to creating a linked server is to script the creation.

EXEC master.dbo.sp_addlinkedserver @server = N'DATAWAREHOUSE', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=testapp;OPTION=134217728;SERVER=my_remote_server;PORT=3306)'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DATAWAREHOUSE',@useself=N'False',@locallogin=NULL,@rmtuser=N'jon',@rmtpassword='test'

GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DATAWAREHOUSE', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Notice that we set up the login information for the remote server using sp_addlinkedsrvlogin instead of placing it in the Provider string of sp_addlinkedserver.

How to query the linked server

Now that the linked server is successfully set up, we can execute queries against it:

EXEC(
    'SELECT
    *
    FROM
    company_sales'
) at DATAWAREHOUSE

A second method is to use OPENQUERY in order to be able to reference the linked server in a FROM clause just as if it were a normal table. This allows for the use of JOIN between two completely separate databases.

SELECT
    *
FROM 
    OPENQUERY(DATAWAREHOUSE,'select * from company_sales') c
JOIN
    legacy_companies ON c.company_id = legacy_companies.company_id

Or to insert the results into a local table.

SELECT
    *
INTO 
    sales
FROM 
    OPENQUERY(DATAWAREHOUSE,'select * from company_sales') c

Conclusion

Using ODBC drivers and SQL Server Linked Servers provides us with a powerful way to access a remote database in a convenient manner within queries and stored procedures on our local database. It may not completely replace your need for SSIS, but does provide a faster solution for querying and pulling in information.