MSSQL and mysql database synchronization using C #

Question

I have two databases on two servers. One of them is MSSQL and the other is mysql. Two data tables have two similar tables. How to automatically update mysql at the time of MSSQL update?

Answer 1

You can use SQL Server CLR integration , create a trigger for updating MySQL. As a starting point, there is Sync data from SQL Server to MySQL in the Code Project .

Answer 2

Alternatively you can create Linked Server

Run this script as a member of the group sysadmin:

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

INSERT, DELETE UPDATE, .

, 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