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.
Data Sources appletAfter 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)
Create Publication WizardOne 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.)
HeterogeneousYou 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.
Select tablesOnce 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)
New subscriberFrom 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.
Publishing and DistributionYou 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.
New... buttonThe 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.
ODBC Data SourceYou 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.
Subscriber Login boxYour 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)
DSN is availableYou 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.