I have two databases on two servers. One is MSSQL and the other one is mysql . There are two similar table in two databses. How do I automatically update the mysql one the moment I update the MSSQL one?
Add a comment
|
2 Answers
You could use SQL Server CLR integration, create a trigger to update MySQL. There is a Sync Data from SQL Server to MySQL example on Code Project as a starting point.
Comments
Alternatively you can create a Linked Server
Run this script as a member of sysadmin group:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL'
, @srvproduct=N'MySQL'
, @provider=N'MSDASQL'
, @datasrc=N'MySQL'
, @provstr=N'DRIVER={MySQL ODBC 5.1 Driver};SERVER=Server;PORT=3306;DATABASE=DBName; USER=user;PASSWORD=password;OPTION=3;'
, @catalog=N'DatabaseName'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MYSQL'
, @locallogin = NULL
, @useself = N'False'
, @rmtuser = N'user'
, @rmtpassword = N'password'
GO
then use triggers on INSERT, DELETE and UPDATE to reflect your changes on the other server.
for example, INSERT:
CREATE TRIGGER dbo.ti_MyTable
ON dbo.MyTable
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO MySql.Database..MyTable (column1, column2, column3)
SELECT column1, column2, column3 FROM Inserted
END
GO
