RateControl's questions

I have a mysql database acting as our websever backend. We have our production mssql server that we would like data pushed from, to our mysql server.

I have successfully made the mysql server an ODBC system datasource.

I can do an insert update statement into the mysql table from our mssql table, so I know I can indeed connect to the mysql server via the system ODBC connection.

I have setup a local publication using:

    -- 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

I have added a snapshot agent:

    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

I have added a subscription for a non-mssql server:

use [repl_test]
exec sp_addsubscription @publication = N'Repl_test'
, @subscriber = N'MYSQL'
, @destination_db = N'TestTable'
, @subscription_type = N'Push'
, @sync_type = N'automatic'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 1

I have setup a push subscription agent also:

exec sp_addpushsubscription_agent @publication = N'Repl_test'
, @subscriber = N'MYSQL'
, @subscriber_db = N'TestTable'
, @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 = 0
, @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

Everything gets created without a hitch, I make sure that I have at least one article selected (which there is only one table called "TestTable".

When running the replication monitor I get the errors of:

Error messages:
The process could not connect to Subscriber 'MYSQL'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084
 Data source name not found and no default driver specified (Source: MSSQLServer, Error number: IM002)
Get help: http://help/IM002

I am screwing up, I think at the "@subscriber_provider" and "@subscriber_datasrc". If it can't be done via replication we'll have to setup some sort of SQL Agent job to do the updating/copying.

Any help is welcomed.

Update1 I have been successful in the initial replication of the "TestTable". I had to use the stored proc of sp_addarticle in order for the replication to NOT drop the mysql table on connection.

   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

The replication is now complaining of a dupe unique key in my table. I think I still have more playing around to do with the add article stored proc.

Update2 Receive error below during initial replication

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"MSREPL7" set xactts = _binary'\0\0\0}\0\04\0\0\0\0', timecol = {ts '2010-12-' at line 1 (Source: MSSQL_REPL, Error number: MSSQL_REPL20046)
Get help: http://help/MSSQL_REPL20046

Then subsequent errors are about a duplicate key.

Had a power outage last night, still cleaning up from that. I have a file server that doesn't know it's part of the domain (getting the error: windows cannot connect to the domain either because the domain controller is down). I read that "resetting" the machine account the bring it back into the domain will do the trick.

Since this is the file server for the entire office, I would like to be sure that I won't bork the umpteen number of shares on this server.

Thanks!

I am in charge of a smaller DB 300+ hundred megs 100'ish tables about 45 users hit it throughout the work day. Mostly reads, but a fair number of updates and inserts. I have been slowing learning the structure of the DB in order to get some performance out of it. I have heard that taking a look at indexes is a good place to start. All most all of the indexes for the tables of said DB are clustered, a few of them are non-clustered.

Is there any speed advantage over clustered vs non-clustered? I have a maintenance plan (yes yes, I know) that re-orgs and rebuild the indexes nightly before diff backups, is this good enough for now, until I get a better grip on index formation and utilization?

Is/are there (a) script(s) that will help me view 'performance' of the various indexes? Just how big of can of worms did I get my self into?