Replicating data from MS SQL Server to MySQL

I managed to replicate data from MS SQL Server to MySQL using an SQL Server Agent job.

I tried to use a trigger to directly insert data into MySQL via a trigger, but that just didn't work. For more info, see my earlier post.

My approach was to create another table, which was exactly the same as the original table on MS SQL Server, except for the identity property which I left disabled. I then created a trigger that would copy rows from the original table to the copy like this:
CREATE TRIGGER [dbo].[trgMyTable]
ON [dbo].[mytable]
AFTER INSERT
AS
BEGIN
INSERT INTO mytable_repli (qid, qname) SELECT qid, qname FROM inserted;
END

I tested it by inserting a row into mytable, and sure enough, it appeared in mytable_repli.

I then installed the MySQL ODBC driver 5.1.5 from the MySQL website.

I then configured a System DSN by going to Administrative Tools - Data Sources - Add. Select the MySQL ODBC 5.1 Driver and click Finish. In the dialog box that pops up, enter a data source name (you will need to remember this for later; I'm calling this Nitin), server name (localhost for me, as I'm running XAMPP on the same computer), user name, password, and database name.

The next step was to add a Linked Server using the Wizard in Server Management Studio: Server Objects - Linked Servers... right-click and select New Linked Server. I selected the Microsoft OLE DB Provider for ODBC Drivers, entered the Data source from the System DSN setup earlier (mine is called Nitin). I entered Nitin for the Linked server and Product name, and clicked Ok.

An optional step is to go to Server Objects - Linked Servers - Providers and go to properties for MSDASQL to check the "Nested queries", "Level zero only", "Allow inprocess", and "Supports Like operator" option.

Finally, I created an SQL Server Agent Job. You have to start the SQL Server Agent if it is not running (right-click and select Start), and create a new Job (right-click on SQL Server Agent - Jobs, and select New Job). Enter a name for the job, and click on the Steps page (left of the dialog box). Click the New button, type in a Step name, select the Database in which the SQL Server table is located and write a T-SQL script to copy the values across to the MySQL table like this:
declare @maxid int;

select @maxid = max(qid) from mytable_repli;

insert into Nitin...qtable(qname)
select qname from mytable_repli
where qid <= @maxid;

delete from mytable_repli
where qid <= @maxid;

In the above T-SQL, note that qtable is the name of my database table on the MySQL server and I'm accessing it with the name Nitin defined earlier in the Linked Server setup.

Click Ok for the Job Step Properties dialog box and go to the Schedules page of the Job Properties dialog box. Click New and define how frequently you want the replication to occur (about once every 15 minutes should seem reasonable, unless you need it to occur more frequently).

If you do not configure a schedule, you need to run the job manually (do this by right-clicking on the job and selecting Start Job At Step). You might also want to do this via the trigger created earlier using sp_start_job - this will cause the row to appear immediately in MySQL. To do this, add the following line below the insert statement in your trigger (replace 'mytable_repli_job' with the name you've specified when creating the SQL Server Agent job):
EXEC msdb.dbo.sp_start_job @job_name='mytable_repli_job';