Eu tenho um banco de dados mysql atuando como nosso backend websever. Temos o nosso servidor mssql de produção que gostaríamos que os dados fossem enviados para o nosso servidor mysql.
Eu fiz com sucesso o servidor mysql uma fonte de dados do sistema ODBC.
Eu posso fazer uma instrução insert update na tabela mysql da nossa tabela mssql, então eu sei que posso de fato conectar ao servidor mysql via conexão ODBC do sistema.
Eu configurei uma publicação local usando:
-- 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
Eu adicionei um agente de instantâneo:
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
Eu adicionei uma assinatura para um servidor não mssql:
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
Também configurei um agente de assinatura por push:
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
Tudo é criado sem problemas, certifico-me de ter pelo menos um artigo selecionado (que existe apenas uma tabela chamada "TestTable".
Ao executar o monitor de replicação, recebo os erros de:
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
Estou estragando, acho que no "@subscriber_provider" e "@subscriber_datasrc". Se isso não puder ser feito por meio de replicação, teremos que configurar algum tipo de trabalho do SQL Agent para fazer a atualização / cópia.
Qualquer ajuda é bem-vinda.
Update1
Eu fui bem sucedido na replicação inicial do "TestTable". Eu tive que usar o proc armazenado de sp_addarticle para que a replicação não soltar a tabela mysql na conexão.
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
A replicação agora está reclamando de uma chave única do dupe na minha tabela. Eu acho que ainda tenho mais coisas para fazer com o procedimento add article stored.
Update2
Receber erro abaixo durante a replicação inicial
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' -- 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
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
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
}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
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
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
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'%pre%%pre%%pre%}%pre%%pre%%pre%%pre%%pre%', timecol = {ts '2010-12-' at line 1 (Source: MSSQL_REPL, Error number: MSSQL_REPL20046)
Get help: http://help/MSSQL_REPL20046
%pre%', timecol = {ts '2010-12-' at line 1 (Source: MSSQL_REPL, Error number: MSSQL_REPL20046)
Get help: http://help/MSSQL_REPL20046
Em seguida, os erros subsequentes referem-se a uma chave duplicada.