将数据从 SQL Server 同步到 MySQL 的替代方案
它使用动态触发器、CLR 存储过程和 Web 服务，将 MS SQL Server 中的数据同步到 MySQL。
引言
我的公司需要实时地将 MS SQL Server 2008 中的数据同步到 MySQL 数据库。 最初，我认为可以通过在设置到 MySQL 表的链接服务器后使用表触发器来完成，这样就不会有大问题。
所以我下载并安装了 Windows 7 上的 MySQL ODBC Connector。 我设置了一个 ODBC 数据源，然后是一个链接服务器，但我收到了这个错误消息
我在网上搜索了很久也无法修复这个问题，即使在重新安装 SQL Server 之后（如果您能告诉我如何修复它，请发送电子邮件至 to_scottleo@yahoo.com，我将非常感激）。
我不得不转向一种解决方法。
解决方案
我的想法是编写一个触发器来调用一个 CLR 存储过程，该存储过程调用一个 Web 服务。 Web 服务将执行 MySQL 数据操作。
让我们开始吧。
步骤 1：下载并安装适用于 Windows 的 MySQL Connector/.NET。 在 Visual Studio 2008 中创建一个类库。 我们将其命名为 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;
}
}
}
构建它并将 DLL 文件复制到 Web bin 目录，供我们的 Web 服务使用。
步骤 2：创建一个 ASP.NET Web 服务。
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;
}
}
步骤 3：使用 Visual Studio 2008 构建一个 CLR 存储过程程序集。
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);
}
}
在编译之前，将项目构建输出设置为生成序列化程序集为“打开”。 编译后，我们将获得两个文件：MySQLRealTimeSyncStoredProcedure.dll 和 MySQLRealTimeSyncStoredProcedure.XmlSerializers.dll。
然后在 SQL Server Management Studio 中，针对这两个 DLL 文件分别注册新的程序集 MySQLRealTimeSyncStoredProcedure、MySQLRealTimeSyncStoredProcedure.XmlSerializers。 请记住将“权限集”设置为“外部访问”。 根据数据库设置，可能还需要运行以下代码。
EXEC sp_configure 'clr enabled', 1
reconfigure;
GO
EXEC ('ALTER DATABASE dbname SET TRUSTWORTHY ON')
GO
步骤 4：编写常规存储过程。
CREATE PROCEDURE MySqlDataSync
@sSqlStmt nvarchar(255)
AS EXTERNAL NAME MySQLRealTimeSyncStoredProcedure.StoredProcedures.SaveData;
GO
步骤 5：编写表触发器。
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
这里假设 MySQL 表具有与 SQL Server 表相同的结构和表字段。 并且关于触发器的一个注意事项是，它可以用于任何表，而无需进行任何其他更改，只需更改表名即可。