One of the biggest things I have to do as part of my company’s transition to using mySQL is the migration of the existing data to the mySQL platform. The problem was getting Microsoft’s SQL Server 2005 and mySQL to talk in a straightforward and flexible manner. The main issue is that SQL Server 2005 does not provide ODBC drivers out of the box.
This IMO is a big backward step for SQL Server 2005. If you use the SQL Server Management Studio interface there is .NET ODBC driver available as a data-source when trying to import data but when you try to make ODBC as your data-sink these options are no longer available. You get this issue when using SSIS also so I went on another tack - linked servers.
Linked servers are simply connections to other data sources or data sinks that are maintained by the Microsoft SQL Server. These can be local or remote and are a flexible and powerful way of making data that is spread across multiple servers available. You big advantage is that you could address the data with SQL statements directly as if they were on the same machine. Linked Servers could be ODBC data-sources as long as the appropriate ODBC drivers were available.
So I went to mySQL and got the mySQL ODBC Driver. After installing the driver on the server (mySQL has detailed installation instructions here), setting up the linked server was pretty straightforward -
EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; DATABASE=DB_Name; USER=UserName; PASSWORD=Password; OPTION=3'
Now you have set up a linked mySQL server using ODBC. Since this is an ODBC connection selecting and inserting data to and from it is slightly different. You use the openquery function like so -
Selecting Data - select * from openquery(MySQL, 'select * fromdb_name.table_name') Inserting Data - INSERT into openquery(MySQL,'select * from db_name.table_name') select * from db_name.dbo.table_name