One way transactional replication from MSSQL 2005 to MYSQL 5.x

At my place of work, we ran into what should be a normal problem. We use mssql 2005 for our internal operations, however we use mysql for our database backend. We wanted to update the changes that happened on the mssql server up to the mysql server so that the website would reflect the changes.

**This how-to only applies to Windows XP, MSSQL Server 2005, MYSQL 5.1.x and MYSQL ODBC connecter 5.1**
You must first allow the account that you will be using for the connection access to the mysql database. Since I was only testing, this is the command I issued on the Linux box that is hosting the MYSQL server

mysql> GRANT ALL PRIVILEGES ON *.* to 'username@windowsmachinename.domain' IDENTIFIED BY 'password';

So if I was using "root" as the username, "lemon" as the machine name and "orange" as the domain name, with the password of "home1234" it would look like this: 

mysql> GRANT ALL PRIVILEGES ON *.* to root@lemon.orange.local IDENTIFIED BY 'home1234';

Once that is done, then you must visit the mysql ODBC driver website and download the correct driver for the MSSQL server that  you are using.

After installing the driver, you must open up the ODBC connections on your machine. Once there, you must add the MYSQL ODBC driver as a system dsn.
My configuration is shown below for the MYSQL ODBC connector.

After you have successfully tested that the connection is valid, open up SSMS, connect to the database engine. Once you are connected, expand the "replication", right-click on "Local Publications", select "Publisher Properties". Once that window is up click on "Publication Databases", then select the requested database as transactional.

Expand "linked servers", then expand providers, right click on "MSDASQL", in the pop-up, select the choices: Nested queries, level zero only, allow inprocess, supports 'like' operator

After that, fire up a new query window and copy/paste the below code.

The steps below
--step 1
-- Adding the transactional publication
use [repl_test]
exec sp_addpublication @publication = N'Repl_test'
, @description = N'Transactional publication of database'
, @sync_method = N'concurrent_c'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'false'
, @allow_anonymous = N'true'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'true'
, @allow_sync_tran = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 0
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'true'
, @autogen_sync_procs = 'false'
GO
--add the article to the publication
exec sp_addarticle @publication = N'Repl_test'
, @article = N'TestTable'
, @source_owner = N'dbo'
, @source_object = N'TestTable'
, @type = N'logbased'
, @pre_creation_cmd = N'none'
, @ins_cmd = N'SQL'
, @del_cmd = N'SQL'
, @upd_cmd = N'SQL'
, @schema_option = 0x20025081
, @status = 24
GO

--add all of the columns to the article
exec sp_articlecolumn @publication = N'Repl_test'
, @article = N'TestTable'
, @refresh_synctran_procs = 1
GO

--end step1

--step2
--add the publication snaphot
exec sp_addpublication_snapshot @publication = N'Repl_test'
, @frequency_type = 4
, @frequency_interval = 4
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 4
, @frequency_subday_interval = 1
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 1
GO
--end step2

--step3
--add the subscriber(s)
use [repl_test]
exec sp_addsubscription @publication = N'Repl_test'
, @subscriber = N'mysqltest'
, @destination_db = N'repl_test'
, @subscription_type = N'Push'
, @sync_type = N'automatic'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 3
GO

--add the pushing subscription agent
exec sp_addpushsubscription_agent @publication = N'Repl_test'
, @subscriber = N'mysqltest'
, @subscriber_db = N'repl_test'
, @job_login = null
, @job_password = null
, @subscriber_security_mode = 0
, @subscriber_login = N'root'
, @subscriber_password = 'PASSWORD'
, @subscriber_provider = N'MSDASQL'
, @subscriber_datasrc = N'mysqltest'
, @frequency_type = 64
, @frequency_interval = 1
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20101202
, @active_end_date = 99991231
, @enabled_for_syncmgr = N'False'
, @dts_package_location = N'Distributor'
GO
--end step3

A brief breakdown of the arguments is as follows, I'll only be touching on the ones that I think are big.

@publication is the name that you want to use for the publication, must be the same throughout the stored procs.

In sp_addpublication
@sync_method should be concurrent_c, that will get  transactional replication
@allow_push must be true in order for the push subscription to be true
@enable_for_het_sub must be set to true, enable for heterogeneous subscriber

In sp_addarticle
@type must be logbased if you want transactional repl (which we do as we set the @sync_method to concurrent_c)
@pre_creation_cmd must be set to none. If this is not set to none, replication will drop the mysql table, then error out stating "no such table"
@schema_option You will have to look at the BoL for the various options, use google to add them together to get the final product.
The 3 command variables we just want to mirror the SQL commands

In sp_articlecolumn
Not sure if this is actually needed or not :)

In sp_addpublication_snaphot
These should be set to your requirements.

In sp_addsubscription
@subscriber should be the system dsn that you set up, at the start of this page
@update_mode should be set to read only, that ensures one-way replication from MSSQL to MYSQL
@subscriber_type should be set to 3(OLE DB) I have not tried it set to 1(ODBC)

In sp_addpushsubscription
@subscriber should be the system dsn again
@subscriber_login should be set to the login from the "grant all privileges" step up above
@subscriber_provider should be MSDASQL
@subscriber_datasrc should be set to the system dsn  again
@enable_for_syncmgr set to false
@dts_package_location set to Distributor since this is a push replication, everything is run from the publisher/distributor


*** Please this is what worked on my box, if it doesn't work, you can try posting here and we may be able to work it out. ***
Please check out BoL for the full list of options for all of the stored procs
If you are on twitter, try the hashtag #sqlhelp
Try posting to ServerFault