Thursday, August 28, 2008

Configuring Linked Server from SQL Server to MySQL

Recently I got a project in which there was a Migration Task to be done from MYSql to SQL Server. Prior to this I never worked in MySQL and I thought it would be just simple as creating LinkedServer to mySQL and pull the data to SQL Server from SQL Server itself. So I decided to create linked server from SQL Server to My SQL and pull the data using OPENQuery Function. I tried to configure linked server in SQL Server and then realized that it is not simple as that. I had to lot of steps like download driver, install it , configure the provider etc. As usual I searched in net and found a thread which neatly mentioned the steps ( SQLServerCentral.com). This thread really solved my problem in hours. Of course the thread was good enough for an expert (indirect way of saying that am an expert ), but for a newbie it may be bit confusing. So I thought to make a comprehensive document which has all the step by step screen shots so that the process will be easier. Having said that, I am not taking any credit for this document, the whole credit goes to Jim Dillon who posted his solution in the above mentioned site.

Tested Environment :
Operating System : Windows XP, Windows 2000, Windows 2003
SQL Server : SQL Server 2005, (Note : I have not tested SQL Server 2000 but I feel it should work)
MySQL : MySQL Server 5.0
MySQL ODBC : ODBC 3.51

High level Steps

• Download and Install ODBC Driver from MYSQL Site
• Create ODBC DSN
• Create Linked Server in SQL Server
• Configure Provider
• Create linked server
• Enable OpenQuery in SQL Server instance
• Create Openquery script

Step 1 : Download MySQL ODBC Connector
Download MySQL Connector/ODBC 3.51 from : http://dev.mysql.com/downloads/connector/odbc/3.51.html



Download the ODBC connector for your Operating System. I have windows xp , so I will download Windows MSI installer (x86)




















Step 2 : Install ODBC connector

Double click the downloaded file








































At last screen you will get a Finish button and click that. Now You have installed ODBC connector for MySQL.


Step 3 : Create System DSN for MYSql ODBC Driver

Windows Start --- >> Run --- >> odbcad32

Or

Windows Start --- >> Control Panel ->Performance and Maintenance (in XP) --- >> Administrative Tools -> Data Sources (ODBC)













































































Step 3 : Create Linked Server in SQL Server 2005.

Change the properties of the Provider
Select the properties as shown in the screens



























--======================================================================================================

Script to Create Linked Server
It is always better to create linked server using Script than GUI. You have more control and script of course is more readable. I am not giving the screen shot for this steps. Copy paste the script to a query analyzer change DSN Name, Username, Password and run.

--======================================================================================================
/****** Object: LinkedServer [LinkedServerTOMySQL] Script Date: 08/28/2008 11:40:28 ******/
EXEC master.dbo.sp_addlinkedserver
@server = N'LinkedServerTOMySQL',
@srvproduct=N'MySQLDatabase',
@provider=N'MSDASQL',
@datasrc=N'MYSQLODBCConnection'

Parameter Explanation

@server = N'LinkedServerTOMySQL', -- Linked server name (it can be anything)
@srvproduct=N'MySQLDatabase', -- Keep as it is
@provider=N'MSDASQL', -- Keep as it is
@datasrc=N'MYSQLODBCConnection' --DSN Name Created in Step 3 (very important)




/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LinkedServerTOMySQL',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'root',
@rmtpassword='change thepasswordhere'

GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'use remote collation', @optvalue=N'true'

--======================================================================================================

Enable Distributed Queries (OpenQuery) in SQL Instance
By default in SQL Server 2005 Distributed Queries are disabled. You have to enable it using the following script. (Note : In SQL Server 2000 no need to run this script)


Copy this script to a query analyzer and run
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE


Finally, Test the Linked Server and Run a OpenQuer against the LinkedServer Configured


Check the following Screen


















Restart Database Engine and SQL Server Agent

Once you have completed all the steps you may restart the services. I have not tested this whether you need to restart it or not


Summary :
If anyone feels some screen shots are missing please drop a comment.

2 comments:

Anonymous said...

Thanks for that great post Madhu. You saved me hours of work. I ran though your steps and everything is working perfectly. I do have a questions for you though.

Being a rookie at this stuff (far from an expert), my original intent was to use SQL Server (2008) to connect to the MySQL server without using a linked server so that when I open SQL Server I would just connect right to the server that has MySQL on it (i.e. without connecting to my local or otherwise and then needing to do an openquery statement for all my queries...). Is this even possible? I guess what I am trying to avoid is having to code in t-sql and in the mysql syntax within the openquery statements as I am not familiar with the mysql syntax.

Note...my linked server worked without having to stop and restart the service.

Thanks,
Phil

Greg said...

Thanks for the post...

A situation that may come up for some users as it did me.

If you are running a x64 bit OS system and your sql05 is the x34 bit version. You must run the ODBC connector manager for a 32 bit system. SQL05 will not recognize the x64 bit driver DSN name you created for the odbc connection.

First make sure the x32 bit driver is installed.

Then type the following into your run prompt: C:\WINDOWS\SysWOW64\odbcad32.exe

you can then follow the above steps and your connection should be solid.

 
Locations of visitors to this page