By Joshua Hoskins
August 7, 2007, 1:35 PM PDT
Takeaway: This blog post is also available in PDF form in a TechRepublic download. While data replication between Microsoft SQL Server 2000 and MySQL databases is not technically supported by either application, it is possible if you follow the correct steps. This TechRepublic How do I… walks you through the complicated procedure necessary to make data replication [...]
This blog post is also available in PDF form in a TechRepublic download.
While data replication between Microsoft SQL Server 2000 and MySQL databases is not technically supported by either application, it is possible if you follow the correct steps. This TechRepublic How do I… walks you through the complicated procedure necessary to make data replication a reality.
Data replication between SQL Server 2000 and MySQL is actually not difficult as long as you follow the steps in order. The first step is to download and install the MySQL ODBC driver. Once this is downloaded you will need to install it on your running SQL Server box. (It does not require a reboot.)
After the driver is installed, you will need to set up your ODBC / DSN connection. This is done from within the Data Sources (ODBC) applet in Administrative Tools (Figure A). You will need to specify your MySQL Server, name for the DSN, authentication information for the MySQL Server, and the database name you wish to replicate data to. You will also need to make sure your SQL Server is added to your Enterprise Manager console by name and not an alias such as “local” or “.”. You cannot configure replication in Enterprise Manager using an alias.
Figure A
Data Sources applet
After setting up the ODBC connection, you will need to create your publication in SQL Server. If you already have a publication, you can base your new publication on it, but you will want to create a new publication specifically for this replication. It would be best if you can make your SQL Server publication server its own distributor. Troubleshooting this replication can be a lot of work, and it simplifies things to have it all in one location. (
Figure B)
Figure B
Create Publication Wizard
One change you will need to make to your publication is to set it for Heterogeneous data sources, as shown in
Figure C. This setting is what allows the replication to be sent a MySQL Server. (This feature is not present in SQL Server 2005.)
Figure C
Heterogeneous
You will then select your tables as normal (
Figure D). However, some data types do not match equally between MySQL and SQL Server. Be careful when choosing what columns to replicate. Replicating only the minimal data needed by the MySQL Server is the way to go. If you have problems once your replication is set up, look at the table with errors and compare the actual data type SQL Server is using for each column and the one MySQL is using. You can usually fix a lot of your errors by doing this.
Figure D
Select tables
Once your publication is created, you will need to exit the Create And Manage Publications menu. You cannot push your subscription just yet. You still need to make a new Subscriber using the DSN we created earlier. (
Figure E)
Figure E
New subscriber
From here, you will need to bring up the Publishing And Distribution menu, as shown in
Figure F. You can access this menu by right-clicking on your SQL Server’s name in Enterprise Manager, choosing Properties, and going to the Replication tab.
Figure F
Publishing and Distribution
You will then need to go to the Subscribers tab of the Publisher And Distributor properties (
Figure G). From here, you can see other SQL Server servers you have in your environment; unfortunately, it does not automatically see your DSN connection to your MySQL Server. You will need to click the New… button to add it.
Figure G
New… button
The Enable New Subscriber prompt will pop up. You will need to choose ODBC Data Source, as shown in
Figure H. This will allow you to use the DSN created earlier to be your new subscriber.
Figure H
ODBC Data Source
You can then select the DSN you wish to use. (It will be the one you set up earlier and have a description of (MySQL ODBC 3.51 Driver.) You will also need to put a MySQL account in the Subscriber Login box. (
Figure I) This account will need a high level of permissions in the MySQL database, as it will not only be creating, updating, and deleting data but will also be used to change and generate schema when necessary.
Figure I
Subscriber Login box
Your new subscriber is now available. It will have the name of the DSN you used for its creation. It is good to keep this in mind when naming your DSNs, as this will be the only name SQL Server will recognize as a reference to this server. (
Figure J)
Figure J
DSN is available
You can go into the properties of the subscriber (by clicking the ellipsis by its name). You can change the account information you use to connect in here. (
Figure K) This can be needed if the password changes on the MySQL side or if, for some reason, you need to change accounts.
Figure K
Subscriber properties
You can then go to the Push Subscription wizard (available by right-clicking on your publication and selecting Push Subscription). You will then see the new subscriber you created available under the Enabled Subscribers section. Select the subscriber you created and complete the wizard as you normally would with any other server. You are now replicating between SQL Server and MySQL! (
Figure L)
Figure L
Push Subscription wizard
Unfortunately, the process of replicating between the two systems is not foolproof. Every time the connection between the systems is broken (whether by rebooting, network problems, or simply not running the distribution agent continuously), you will have the possibility of receiving the dreaded Invalid Cursor State error. Luckily, there is an easy workaround. Simply create the MySQL system as a linked server (using the Microsoft OLE DB Provider for ODBC Drivers and the DSN you created). Then, create a replication alert for this error that will run a distributed transaction to the MySQL server instructing it to drop the table MSRepl7 and then restarting the distribution agent. This quick and simple fix will work wonders for your replication stability. (
Figure M)
Figure M
SQL Server Enterprise Manager
