An Alternative to Sync Data from SQL Server to MySQL

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 -- insert action
    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 -- delete action
    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 -- update action
    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
        -- remove the last comma
        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.