Replicating a View from MSSQL to MYSQL part II

The stored procs are basically the same as when they were used for table to table replication.

One of the stored proc that we need to change is the ‘sp_addarticle’:

exec sp_addarticle @publication = N’repl_test_view’

, @article = N'repl_test_view'

, @source_owner = N'dbo'

, @source_object = N'repl_test_view'

, @type = N'indexed view logbased'

, @pre_creation_cmd = N'none'

, @ins_cmd = N'SQL'

, @del_cmd = N'SQL'

, @upd_cmd = N'SQL'

, @schema_option =0x8000000

, @status = 24

As you can see the major difference is at the @type variable, whereas we chose ‘logbased’ for the table to table replication. For indexed view replication it should say ‘indexed view logbased’, which really only makes sense.  I also had to change the @schema_option because we wanted to replicate a view, not a table.

I also added the stored proc of ‘sp_replicationdboption’

exec sp_replicationdboption @dbname= N'databasename'

, @optname = N'publish'

, @value = N'true'

GO

Easy enough, this saves the step of having to tell the MSSQL server that the database is indeed eligible for replication via the GUI.

Since I have not figured out a way for the initial replication to create the destination tables (I haven’t looked into it yet), I was forced to create the MYSQL tables to accept the replicated data. This brought me to another point of having to change the ‘datetime’ datatype from MSSQL to a ‘smalldatetime’ so that I would be able to then replicate over to MYSQL. 

For example, if I had a column named “date_turned_in”, and I wanted that as a ‘smalldatetime’ instead of the normal ‘datetime’, I would use CAST(table.date_turned_in AS smalldatetime) AS date_turned_in, I had to change the data types before replication, since the “allow data transformation” is set to false for the publication.

After all this was said and done, we are now replicating five views from a MSSQL server to 5 tables in a MYSQL server.

I am sure that there is more to come.

Replicating a View from MSSQL to MYSQL

     So what it took me to figure out MSSQL to MYSQL replication, we’re not going to use it, per se. Our goal was to condense a multidude of tables down to a single view, then replicate that view from our MSSQL server to a single table on the MYSQL side (which you can replicate a view). However the hitch in the giddy-up was the constraints of the view. The highlights were; it must have a non-clustered unique index, a view cannot contain an “outer join” (crap). There is a host of critera to make an index view in the first place so once we were past that we thought it was clear sailing. Wrong.

So here we sit, the minimal number of views that we can break the data down to, with holding to the constraints of the view is about 4-5. It looks like we will have to replicate the views as tables into MYSQL, then have the website condense them down to display on the page.

It’s not bad, just not what we wanted.

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