Introduction
My company has the need to synchronize data on a real time basis from MS SQL Server 2008 to a MySQL database. At first I thought that it could be done with table triggers after setting up a linked server to a MySQL table, and then it would not be a big problem.
So I downloaded and installed the MySQL ODBC Connector for Windows 7. I set up an ODBC data source and then a linked server, but I got this error message:
I googled and googled and could not fix this issue, even after I re-installed SQL Server (you would be much appreciated if you let me know how to fix it by sending an email to to_scottleo@yahoo.com).
I had to turn to a work-around.
The solution
My idea is to write a trigger to call a CLR Stored Procedure which calls a Web Service. The Web Service will perform the MySQL data manipulation.
Let’s start.
Step 1: Download and install MySQL Connector/.NET for Windows. Create a Class Library in Visual Studio 2008. Let’s name it MySqlDataManipulation.
using System;
using MySql.Data.MySqlClient;
namespace MySqlDataManipulation
{
public class MySqlData
{
private MySqlConnection _conn;
public MySqlData(string sConnStr)
{
_conn = new MySqlConnection(sConnStr);
}
public int ExecuteNonQuery(string sSqlStmt)
{
_conn.Open();
MySqlCommand cmd = new MySqlCommand(sSqlStmt, _conn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sSqlStmt;
int returnCode = cmd.ExecuteNonQuery();
return returnCode;
}
}
}
Build it and copy the DLL file to the web bin directory for our Web Service to use it.
Step 2: Create an ASP.NET Web Service.
using System;
using System.Web.Services;
using MySqlDataManipulation;
using System.Web.Configuration;
[WebService]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebSyncService : System.Web.Services.WebService {
public WebSyncService () {
}
[WebMethod]
public void SyncMySQLData(string sSqlStmt)
{
String sMySqlConnStr =
WebConfigurationManager.ConnectionStrings["SyncMySQL"].ToString();
MySqlData d = new MySqlData(sMySqlConnStr);
d.ExecuteNonQuery(sSqlStmt);
return;
}
}
Step 3: Use Visual Studio 2008 to build a CLR Stored Procedure assembly.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MySQLRealTimeSync.com.xxxxx.www;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SaveData(String sSqlStmt)
{
WebSyncService sync = new WebSyncService();
sync.SyncMySQLData(sSqlStmt);
}
}
Before compiling, set the project build output to generate the serialization assembly to “On”. After compiling, we’ll get two files: MySQLRealTimeSyncStoredProcedure.dll and MySQLRealTimeSyncStoredProcedure.XmlSerializers.dll.
Then in SQL Server Management Studio, register the new assemblies MySQLRealTimeSyncStoredProcedure, MySQLRealTimeSyncStoredProcedure.XmlSerializers against these two DLLs, respectively. Remember to set "Permission set" to "External access". It is also probably needed to run the following codes depending on the database settings.
EXEC sp_configure 'clr enabled', 1
reconfigure;
GO
EXEC ('ALTER DATABASE dbname SET TRUSTWORTHY ON')
GO
Step 4: Write a regular Stored Procedure.
CREATE PROCEDURE MySqlDataSync
@sSqlStmt nvarchar(255)
AS EXTERNAL NAME MySQLRealTimeSyncStoredProcedure.StoredProcedures.SaveData;
GO
Step 5: Write a table trigger.
USE MyDatabase
IF OBJECT_ID('MySchema.TR_SyncToMySQL', 'TR') IS NOT NULL
DROP TRIGGER MySchema.TR_SyncToMySQL
GO
SET NOCOUNT ON
GO
CREATE TRIGGER MySchema.TR_SyncToMySQL ON MySchema.MyTable
FOR INSERT, DELETE, UPDATE
AS
BEGIN
DECLARE
@InsertCount integer,
@DeleteCount integer,
@TableName varchar(255),
@MySQLTableName varchar(255),
@FieldName nvarchar(255),
@IdentityColName nvarchar(255),
@IdentityColVal nvarchar(255),
@OldValue nvarchar(max),
@NewValue nvarchar(max),
@ColCount bigint,
@ColTotal bigint,
@SqlString varchar(max),
@SqlStringTemp varchar(max),
@HasModifiedCol bit
SET @MySQLTableName = 'MySQLTableName'
SET @InsertCount = (SELECT Count(*) FROM INSERTED)
SET @DeleteCount = (SELECT Count(*) FROM DELETED)
SELECT @TableName = OBJECT_NAME(PARENT_OBJ)
FROM SYSOBJECTS
WHERE id = @@PROCID
SELECT @ColTotal = COUNT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
SET @ColCount = 0
SELECT @IdentityColName = name
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND
COLUMNPROPERTY(id, name, 'IsIdentity') = 1
IF OBJECT_ID('tempdb..#myTemp') IS NOT NULL
DROP TABLE #myTemp;
CREATE TABLE #myTemp (FieldValue nvarchar(max));
IF @InsertCount > @DeleteCount
BEGIN
DECLARE @ValueString nvarchar(max)
WHILE ((SELECT @ColCount) < @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name)
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName = @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myInserted1') IS NOT NULL
DROP TABLE #myInserted1;
SELECT * INTO #myInserted1 FROM INSERTED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' +
@FieldName + ' AS nvarchar(max)) FROM #myInserted1';
EXEC(@SqlStringTemp);
SET @NewValue = (SELECT FieldValue FROM #myTemp);
IF @NewValue IS NULL SET @NewValue = ''
IF @SqlString IS NULL
SET @SqlString = 'INSERT ' + @MySQLTableName+ ' (';
IF @ValueString IS NULL
SET @ValueString = ') VALUES ('
SET @SqlString = @SqlString + @FieldName + ', '
SET @ValueString = @ValueString + '''' + @NewValue + ''', '
END
EXEC dbo.MySqlDataSync REPLACE(@SqlString + @ValueString + ')', ', )', ')')
END
ELSE IF @InsertCount < @DeleteCount
BEGIN
WHILE ((SELECT @ColCount) <= @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name)
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName <> @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myDeleted1') IS NOT NULL
DROP TABLE #myDeleted1;
SELECT * INTO #myDeleted1 FROM DELETED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' +
@IdentityColName + ' AS nvarchar(max)) FROM #myDeleted1';
EXEC(@SqlStringTemp);
SET @IdentityColVal = (SELECT FieldValue FROM #myTemp);
END
EXEC dbo.MySqlDataSync 'DELETE FROM ' + @MySQLTableName+ ' WHERE ' +
@IdentityColName + ' = ''' + @IdentityColVal + ''''
END
ELSE IF @InsertCount = @DeleteCount
BEGIN
SET @HasModifiedCol = 0
WHILE ((SELECT @ColCount) < @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name)
FROM syscolumns
WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName = @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myInserted') IS NOT NULL
DROP TABLE #myInserted;
SELECT * INTO #myInserted FROM INSERTED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' +
@IdentityColName + ' AS nvarchar(max)) FROM #myInserted';
EXEC(@SqlStringTemp);
SET @IdentityColVal = (SELECT FieldValue FROM #myTemp);
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' +
@FieldName + ' AS nvarchar(max)) FROM #myInserted';
EXEC(@SqlStringTemp);
SET @NewValue = (SELECT FieldValue FROM #myTemp);
IF @NewValue IS NULL SET @NewValue = ''
IF OBJECT_ID('tempdb..#myDeleted') IS NOT NULL
DROP TABLE #myDeleted;
SELECT * INTO #myDeleted FROM DELETED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' +
@FieldName + ' AS nvarchar(max)) FROM #myDeleted';
EXEC(@SqlStringTemp);
SET @OldValue = (SELECT FieldValue FROM #myTemp);
IF @OldValue IS NULL SET @OldValue = ''
IF @SqlString IS NULL
SELECT @SqlString = 'UPDATE ' + @MySQLTableName+ ' SET '
IF @NewValue <> @OldValue
BEGIN
SET @HasModifiedCol = 1
SET @SqlString = @SqlString + @FieldName + ' = ''' + @NewValue + ''', '
END
END
SELECT @SqlString = LTRIM(REVERSE(@SqlString))
SELECT @SqlString = REVERSE(SUBSTRING(@SqlString, 2, LEN(@SqlString)))
SELECT @SqlString = @SqlString + ' WHERE ' +
@IdentityColName + ' = ''' + @IdentityColVal + ''''
IF @HasModifiedCol = 1
BEGIN
PRINT @SqlString
EXEC dbo.MySqlDataSync @SqlString
END
END
END
GO
Here it is assumed that the MySQL table has the same structure and table fields as the SQL Server table. And a catch here about the trigger is that it can be used for any table without any other changes except for the table names.