关于数据同步的几种实现

最新推荐文章于 2024-09-05 10:00:22 发布
最新推荐文章于 2024-09-05 10:00:22 发布
阅读量10w+ 收藏 83
点赞数 11
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xuemoyao/article/details/14002209
版权

关于数据同步的几种实现

概述

关于数据同步主要有两个层面的同步，一是通过后台程序编码实现数据同步，二是直接作用于数据库，在数据库层面实现数据的同步。通过程序编码实现数据同步，其主要的实现思路很容易理解，即有就更新，无则新增，其他情况日志记录，就不做过多的介绍，这里主要讲述的是第二个层面的数据同步，即在数据库层面实现数据同步。

数据库层面的数据库同步主要有三种方式：通过发布/订阅的方式实现同步，通过SQL JOB方式实现数据同步，通过Service Broker 消息队列的方式实现数据同步。

下面分别就这三种数据同步方式，一一详解。

1.    通过发布/订阅的方式实现同步

发布/订阅是Sql Server自带的一种数据库备份的机制，通过该机制可以快速的实现数据的备份同步，不用编写任何的代码。

此种数据同步的方式存在的以下的一些问题：

  1. 表结构不能更改，同步双方的表结构必须一致，一旦表结构发生更改需要重新生成数据库快照。
  2. 对于大数据量的同步没有可靠的保证。
  3. 网络不稳定的情况下同步也不能保证。

总的来说，这种数据备份同步的方式，在表结构一致、数据量不是特别大的情况下还是非常高效的一种同步方式。

网上有很多的关于如何使用发布/订阅的方式实现数据同步的操作示例，这里就不再重复的演示了，有兴趣想要了解的朋友可以参考下面这篇文章：

http://kb.cnblogs.com/page/103975/

2.    通过SQL JOB方式实现数据同步

通过Sql Job定时作业的方式实现同步其基本原理就是通过目标服务器和源服务器的连接，然后通过编写Sql语句，从源服务器中读取数据，再更新到目标服务器。

这种数据同步的方式比较灵活。创建过sql定时作业之后，主要需要执行以下关键的两步。

2.1     创建数据库连接(一般作为定时作业执行的第一步)

不同数据库之间的连接可以通过系统的存储过程实现。下面就直接用一个示例来讲一下如何创建数据库连接。

--添加一个连接

--系统存储过程sp_addlinkedserver 参数：

----------------------1：目标服务器的IP或别名，本例中为：'WIN-S1PO3UA6J7I'；----------------------2：'' (srvproduct,默认)；

----------------------3：'SQLOLEDB'（provider,默认值）；

----------------------4：目标服务器的IP或别名（datasrc），本例中为：'WIN-S1PO3UA6J7I'

exec sp_addlinkedserver 'WIN-S1PO3UA6J7I','','SQLOLEDB','WIN-S1PO3UA6J7I'

--添加登录用户连接

--系统存储过程sp_addlinkedsrvlogin 参数：

----------------------1：目标服务器的IP或别名，本例中为：'WIN-S1PO3UA6J7I'；

----------------------2：'false',默认值；

----------------------3：null,默认值；

----------------------4：'sa'，登录用户名；

----------------------5：'pass@word1'，登录密码；

exec sp_addlinkedsrvlogin 'WIN-S1PO3UA6J7I','false',null,'sa','pass@word1'

创建数据库连接主要用到了以上的两个存储过程，但是在实际操作的过程中可能会遇到“仍有对服务器XXX的远程登录或连接登录问题”这样的问题，如果遇到此类问题，在执行上边的添加连接和登录用户连接之前还需要先删除某个已存在的链接，具体如下：

--系统存储过程sp_droplinkedsrvlogin 参数：

----------------------1：目标服务器的IP或别名，本例中为：'WIN-S1PO3UA6J7I'；----------------------2：null

exec sp_droplinkedsrvlogin 'WIN-S1PO3UA6J7I',null

--系统存储过程sp_dropserver 参数：

----------------------1：目标服务器的IP或别名，本例中为：'WIN-S1PO3UA6J7I'

exec sp_dropserver 'WIN-S1PO3UA6J7I'

2.2     使用SQL 语句 实现数据同步

主要的同步思路：

1：在目标数据库中先清空要同步的表的数据

2：使用insert into Table (Cloumn....) select Column..... from 服务器别名或IP.目标数据库名.dbo.TableName 的语法将数据从源数据库读取并插入到目标数据库

Truncate  table  Org_DepartmentsExt –删除现有系统中已存在的部门表

insert into Org_DepartmentsExt –从名为WIN-S1PO3UA6J7I的服务器上的DBFrom数据库上获取源数据，并同步到目标数据库中

     (

      [DeptID]

      ,[DeptStatus]

      ,[DeptTel]

      ,[DeptBrief]

      ,[DeptFunctions] 

     )

SELECT [DeptID]

      ,[DeptStatus]

      ,[DeptTel]

      ,[DeptBrief]

      ,[DeptFunctions]

  FROM [WIN-S1PO3UA6J7I].[DBFrom].[dbo].[Org_DepartmentsExt]

以上这两步便是通过SQL Job实现数据同步的关键步骤，在完成以上两步之后，如果没有其他的表要进行同步，则可创建同步计划以完善定时作业。带作业创建完后，便可以执行。

这里主要只是演示了通过Sql Job方式实现数据同步的关键步骤。网上有很多具体的实例演示。有兴趣的朋友可以参考以下文章进行练习检验：

http://www.cnblogs.com/tyb1222/archive/2011/05/27/2060075.html

3.    通过SQL Server Service Broker 消息队列的方式实现数据同步

3.1 SQL Server Service Broker概述

SQL Server Service Broker 是数据库引擎的组成部分，为 SQL Server 提供队列和可靠的消息传递。既可用于使用单个 SQL Server 实例的应用程序，也可用于在多个实例间分发工作的应用程序。

在单个 SQL Server 实例内，Service Broker 提供了一个功能强大的异步编程模型。数据库应用程序通常使用异步编程来缩短交互式响应时间，并增加应用程序总吞吐量。

在多个SQL Server实例之间Service Broker 还可以提供可靠的消息传递服务。Service Broker 可帮助开发人员通过称为服务的独立、自包含的组件来编写应用程序。需要使用这些服务中所包含功能的应用程序可以使用消息来与这些服务进行交互。Service Broker 使用 TCP/IP 在实例间交换消息。Service Broker 中所包含的功能有助于防止未经授权的网络访问，并可以对通过网络发送的消息进行加密。

3.2 具体的实现演示

在这一小节里，主要是通过一个完整的数据同步的流程向大家演示，如何实现同一个数据库实例不同数据库的数据同步。关于不同的数据库实例间的数据库的数据同步整体上跟同一个实例的数据库同步是一样的，只不过在不同的数据库实例间同步时还需启用传输安全、对话安全，创建路由、远程服务绑定等额外的操作。

这里边用到了大量的SQL Server XML的东西，如果有不理解的地方可以参考以下链接：http://www.cnblogs.com/Olive116/p/3355840.html

这是我在做技术准备时，自己的一点学习记录。

下面就是具体的实现步骤：

3.2.1为数据库启动Service Broker活动

    这一步主要是用来对要进行数据同步的数据启用Service Broker 活动，并且授信。

USE master
GO
--如果数据库DBFrom、DBTo不存在，则创建相应的数据库
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name ='DBFrom')
CREATE DATABASE DBFrom
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name ='DBTo')
CREATE DATABASE DBTo
GO
--分别为该数据库启用Service Broker活动并且授权信任
ALTER DATABASE DBFrom SET ENABLE_BROKER
GO
ALTER DATABASE DBFrom SET TRUSTWORTHY ON
GO
ALTER AUTHORIZATION ON DATABASE::DBFrom To sa
GO
ALTER DATABASE DBTo SET ENABLE_BROKER
GO
ALTER DATABASE DBTo SET TRUSTWORTHY ON
GO
ALTER AUTHORIZATION ON DATABASE::DBTo TO sa
GO

 

3.2.2 创建数据库主密匙

这一步主要用来创建数据库主密匙，上边有提到Service Broker可以对要发送的消息进行加密。

Use DBFrom
go
create master key
encryption by password='pass@word1'
go

Use DBTo
go
create master key
encryption by password='pass@word1'
go

 

3.2.3 创建消息类型、协定

这里主要用来创建消息类型和消息协定，源数据库和目标数据库的消息类型和协定都要一致。

Use DBFrom
go
--数据同步—消息类型
create message type [http://oa.founder.com/Data/Sync]
validation=well_formed_xml
go
--数据同步--错误反馈消息类型
create message type [http://oa.founder.com/Data/Sync/Error]
validation=well_formed_xml
go
--数据同步协议
create contract[http://oa.founder.com/Data/SyncContract]
(
[http://oa.founder.com/Data/Sync]
sent by initiator,
[http://oa.founder.com/Data/Sync/Error]
sent by target
)
go

Use DBTo
go
--数据同步—消息类型
create message type [http://oa.founder.com/Data/Sync]
validation=well_formed_xml
go
--数据同步--错误反馈消息类型
create message type [http://oa.founder.com/Data/Sync/Error]
validation=well_formed_xml
go
--数据同步协议

create contract[http://oa.founder.com/Data/SyncContract]
(
[http://oa.founder.com/Data/Sync]
sent by initiator,
[http://oa.founder.com/Data/Sync/Error]
sent by target
)
Go

 

创建过之后效果如下图：

                       

3.2.4 创建消息队列

    这里主要用来创建消息队列，源数据库和目标数据库都要创建，队列名字可以自主命名。

use DBFrom
go
create queue [DBFrom_DataSyncQueue]
with status=on
go

use DBTo
go
create queue [DBFrom_DataSyncQueue]
with status=on
go

 

创建之后效果如下图：

 

3.2.5 创建数据同步服务

这里我们通过利用上边创建的消息协定和消息队列来创建数据同步的服务。

use DBFrom
go
create service [http://oa.founder.com/DBFrom/Data/SyncService]
on queue dbo.[DBFrom_DataSyncQueue]([http://oa.founder.com/Data/SyncContract])
go

--数据同步服务
use DBTo
go
create service [http://oa.founder.com/DBTo/Data/SyncService]
on queue dbo.[DBFrom_DataSyncQueue]([http://oa.founder.com/Data/SyncContract])
go

 

    创建后效果如下图：

 

3.2.6 在源数据库上创建服务配置列表

这里需要在源数据库上创建一个服务配置列表，主要用来保存之前创建过的服务名称，本例只是用来演示，所以只创建了一个服务，只能是同步一个数据表，如果有多个数据表需要同步，则需创建多个服务，所以这里创建一个服务配置列表，用来存储多个服务的服务名称。

需要注意的是，下面的脚本在执行完创建表的操作之后又插入了一条数据，也就是上边我们创建的服务名，如果有多个服务的话，依次插入该表即可。

use DBFrom
go
--同步数据--目标服务配置
create table SyncDataFarServices
(
ServiceID uniqueidentifier,
ServiceName nvarchar(256)
)
go
--将上边创建的服务名，插入此表中
insert into SyncDataFarServices (ServiceID,ServiceName)
values
(NEWID(),'http://oa.founder.com/DBTo/Data/SyncService')
go

 

效果如下图：

 

 

3.2.7 发送数据同步消息

    这里创建了一个存储过程主要用来发送同步消息，该消息内容主要包括操作类型、主键、表名、正文内容，分别对应@DMLType,@PrimaryKeyField,@TableName,@XMLData。然后通过创建一个游标来条的读取上边创建的服务列表中的列表信息，向不同的服务发送消息。

   

 Use DBFrom
go
--发送同步数据消息
Create procedure UP_SyncDataSendMsg
(
@PrimaryKeyField nvarchar(128),
@TableName nvarchar(128),
@DMLType char(1),
@XMLData xml
)
as
begin
   SET @XMLData.modify('insert <DMLType>{sql:variable("@DMLType")}</DMLType>  as first into /');
    SET @XMLData.modify('insert <PrimaryKeyField>{sql:variable("@PrimaryKeyField")}</PrimaryKeyField>  as first into /');
    SET @XMLData.modify('insert <Table>{sql:variable("@TableName")}</Table> as first into /');
    DECLARE FarServices CURSOR FOR SELECT ServiceName FROM SyncDataFarServices;  
   open FarServices
      declare @FarServiceName nvarchar(256);
      fetch FarServices into @FarServiceName;
      while @@FETCH_STATUS=0
        begin
           begin Transaction
             declare @Conv_Handler uniqueidentifier
             begin DIALOG conversation @Conv_Handler --开始一个会话
             from service [http://oa.founder.com/DBFrom/Data/SyncService]
             to service @FarServiceName
             on contract [http://oa.founder.com/Data/SyncContract];          
             send on conversation @Conv_Handler
             Message type [http://oa.founder.com/Data/Sync](@XMLData);
             fetch FarServices into @FarServiceName;
             commit;
        end
    close FarServices;
    deallocate FarServices;
end
go

 

 

3.2.8 创建数据同步异常信息记录表

这里创建该表主要用来记录在数据同步过程中出现的异常信息。

use DBFrom
go
create Table dbo.SyncException
(
ErrorID uniqueidentifier,
ConversationHandleID uniqueidentifier,
ErrorNumber int,
ErrorSeverity int,
ErrorState int,
ErrorProcedure nvarchar(126),
ErrorLine int,
ErrorMessage nvarchar(2048),
MessageContent nvarchar(max),
CreateDate DateTime
)
go


--修改异常信息记录表
alter table dbo.SyncException
add
PrimaryKeyField nvarchar(128),
TableName nvarchar(128),
DMLType char(1),
DBName nvarchar(128)
Go

 

 

效果如下图：

 

3.2.9 数据同步反馈

这里主要用来在源数据库中接收队列中的消息，将同时出错的信息，解析一下，然后插入到异常信息记录表里边。

--数据同步回馈

use DBFrom
go
create procedure UP_SyncDataFeedback
as
begin
set nocount on
--会话变量声明
declare @ConversationHandle uniqueidentifier;--会话句柄
declare @Msg_Body nvarchar(max);
declare @Msg_Type_Name sysname;
--变量赋值
while(1=1)
begin
  begin transaction
     --从队列中接收消息
     waitfor
     (
        receive top(1)
        @Msg_Type_Name=message_type_name,
        @ConversationHandle=[conversation_handle],
        @Msg_Body=message_body
        from dbo.[DBFrom_DataSyncQueue]
     ),timeout 1000
     --如果接收到消息处理，否则跳过
     if(@@ROWCOUNT<=0)
        break;
     if @Msg_Type_Name='http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
         end conversation @ConversationHandle;
     else if @Msg_Type_Name='http://oa.founder.com/Data/Sync/Error'
         begin
             declare @DataSource xml;
             set @DataSource=Convert(xml,@Msg_Body);
             insert into dbo.SyncException(ErrorID,ConversationHandleID,ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage,
PrimaryKeyField,TableName,DMLType,MessageContent,DBName,CreateDate)
             select
             NEWID(),@ConversationHandle,
             T.c.value('./@ErrNumber','INT'),
             T.c.value('./@ErrSeverity','INT'),
             T.c.value('./@ErrState','INT'),
             T.c.value('./@ErrProcedure','Nvarchar(126)'),
             T.c.value('./@ErrLine','INT'),
             T.c.value('./@ErrMessage','nvarchar(2048)'),
             T.c.value('./@PrimaryKeyField','nvarchar(128)'),
             T.c.value('./@TableName','nvarchar(128)'),
             T.c.value('./@DMLType','char(1)'),
             T.c.value('./@MessageContent','nvarchar(max)'),
            T.c.value('./@DBName','nvarchar(128)'),
             GETDATE()
             from @DataSource.nodes('/row') as T(c);
          end
     else if @Msg_Type_Name='http://schemas.microsoft.com/SQL/ServiceBroker/Error'
         end conversation @ConversationHandle;
  commit Transaction;
end
end
commit;
go

 

3.2.10对Service Broker队列使用内部激活，并指定将调用的存储过程

    这里主要用来激活源数据库的消息队列，并为其指定调用的存储过程，即上边3.2.9 中创建的存储过程。

--对Service Broker队列使用内部激活，并指定将调用的存储过程
use DBFrom
go
alter queue dbo.DBFrom_DataSyncQueue with activation
(
  status=on,
  max_queue_Readers=1,
  procedure_name=UP_SyncDataFeedback,
  execute as owner
);
Go

 

3.2.11 在源数据库中为需要同步的数据表创建触发器

这里就以用户表为例，具体操作如下，这里通过查询系统的Inserted和Deleted临时表来判断执行同步的操作类型是更新（U）、新增（A）还是删除（D），最后调用3.2.7 中创建的存储过程来对数据进行处理并发送。

use DBFrom
Go
--用户信息同步
Create Trigger UT_DataSync_Users
on dbo.Org_Users
after insert,update,delete
as
set nocount on ;
--变量声明
declare @PrimaryKeyField nvarchar(128),@TableName nvarchar(128),@DMLType char(1);
declare @InsertCount int ,@DeleteCount int ;
declare @XMLData xml;
--变量赋值
set @PrimaryKeyField='ID' --组合主键，多个主键使用","隔开
set @TableName='Org_Users'
set @InsertCount=(select COUNT(*) from inserted)
set @DeleteCount=(select COUNT(*) from deleted)
if @InsertCount=@DeleteCount and @InsertCount<>0  ----Update
  begin
  select @XMLData=(select * from inserted For xml raw,binary base64,ELEMENTS XSINIL);
  set @DMLType='U';
  end
else if(@InsertCount<>0 and @DeleteCount=0) ----Insert
  begin
  select @XMLData=(select * from inserted for xml raw ,Binary base64,ELEMENTS XSINIL)
  set @DMLType='A';
  end
else----Delete
  begin
      select @XMLData=(select *from deleted for xml raw,binary base64,ELEMENTS XSINIL)
      set @DMLType='D';
  end
 if(@XMLData is not null)
 begin
     exec UP_SyncDataSendMsg @PrimaryKeyField,@TableName,@DMLType,@XMLData;
 end
 go

 

3.2.12 目标数据库中创建，字符分割函数

该函数主要是用来进行字符分割，用来处理主键有多个字段的情况。

--目标数据库

use DBTo
go
--转换用‘,'分割的字符串@str
create Function dbo.uf_SplitString
(
@str nvarchar(max),
@Separator nchar(1)=','
)
returns nvarchar(2000)
as
begin
   declare @Fields xml;--结果字段列表
  declare @Num int;-----记录循环次数
  declare @Pos int;-----记录开始搜索位置
   declare @NextPos int;--搜索位置临时变量
   declare @FieldValue nvarchar(256);--搜索结果
   set @Num=0;
   set @Pos=1;
   set @Fields=CONVERT(xml,'<Fields></Fields>');
   while (@Pos<=LEN(@Str))  
      begin
        select @NextPos=CHARINDEX(@Separator,@Str,@Pos)
        if(@NextPos=0 OR @NextPos is null)
            select @NextPos=LEN(@Str)+1;
        select @FieldValue=RTRIM(ltrim(substring(@Str,@Pos,@NextPos-@Pos)))
        select @Pos=@NextPos+1
        set @Num=@Num+1;
        if @FieldValue<> ''
           begin
             set @Fields.modify('insert <Field>{sql:variable("@FieldValue")}</Field> as last into /Fields[1]');           
           end   
      end
      return Convert(nvarchar(2000),@Fields);
end
go

 

3.2.13 将解析过的消息信息，根据操作类型的不同同步到数据表中

    这是所有的数据同步中最关键也是最复杂的一步了，在整个开发的过程中，大部分时间都花在这上边了，具体的操作都在下面解释的很清楚了。

--将XML数据源中的数据同步到数据表中(包括增删改)

Use DBTo
go
create function dbo.UF_XMLDataSourceToSQL
(
   @DataSource XML,--数据源
   @TableName varchar(128),--同步数据表名称
   @PrimaryKeyField varchar(128),--需要同步的表的主键，主键为多个时用‘,'隔开
   @DMLType char(1) --A:新建；U:编辑；D：删除
)
returns nvarchar(4000)
as
begin
    --变量声明及数据初始化
    --声明数据表@TableName列Column相关信息变量
    declare @ColumnName nvarchar(128),@DataType nvarchar(128),@MaxLength int;
    --声明用于拼接SQL的变量
   declare @FieldsList nvarchar(4000),@QueryStatement nvarchar(4000);
    declare @Sql nvarchar(4000);
    declare @StrLength int;
    --变量初始化
    set @FieldsList='  ';--初始化变量不为null，否则对变量使用'+='操作符无效
  set @QueryStatement='  ';
    --主键信息，根据参数求解如：<Fields><Field>ID1</Field><Field>ID2</Field></Fields>
    declare @PKs xml;
    --当前字段是否主键-在‘更新’，‘删除’同步数据时使用
    declare @IsPK nvarchar(128);
    --初始化游标--游标内容包括目标数据表TableName列信息
    DECLARE ColumnNameList CURSOR FOR SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND 
DATA_TYPE<>'xml';
   --数据处理
    if @DMLType='A'--插入数据
       begin
          open ColumnNameList
             fetch ColumnNameList into @ColumnName,@DataType,@MaxLength;
             while @@FETCH_STATUS=0
               begin
               --判断数据源列中是否存在属性：@ColumnName
               --判断数据源列中是否存在--元素：@ColumnName
               If @DataSource.exist('/row/*[local-name()=sql:variable("@ColumnName")]')=1
                 begin
                    --拼接SQL
                    set @FieldsList+=(@ColumnName+',');
                   set @QueryStatement+=('T.c.value(''(./'+@ColumnName+'[not(@xsi:nil)])[1]'','''+@DataType);--元素读取(包含空值情况)
                    if @MaxLength is not null and @MaxLength<>-1
                       begin
                          set @QueryStatement+='('+CONVERT(nvarchar,@MaxLength)+')';
                        end
                    else if @MaxLength=-1 and @DataType<>'xml'--已调整
                       begin
                          set @QueryStatement+='(MAX)';
                       end
                    set @QueryStatement+=(''') as '+@ColumnName+',');
                 end
                 fetch ColumnNameList into @ColumnName,@DataType,@MaxLength
               end
        close ColumnNameList;
        deallocate ColumnNameList;
        set @StrLength=LEN(@FieldsList);
        --去掉@FieldsList结尾的’,'
       set @FieldsList=SUBSTRING(@FieldsList,1,@StrLength-1);       
        set @StrLength=LEN(@QueryStatement);
        --去掉@QueryStatement结尾的’,'
        set @QueryStatement=SUBSTRING(@QueryStatement,1,@StrLength-1);
              set @Sql=N'insert into '+@TableName+'('+@FieldsList+') select '+@QueryStatement+' from @DataSource.nodes(''/row/'') as T(c)';
       end

   

   
    else if @DMLType='U'--更新数据
        begin
           --更新语句where 后的条件表达式
           declare @Condition nvarchar(1000);
           set @Condition='  ';
           set @PKs=CONVERT(xml,dbo.uf_SplitString(@PrimaryKeyField,','));
           Open ColumnNameList
                fetch ColumnNameList into @ColumnName,@DataType,@MaxLength;
               while @@FETCH_STATUS=0
                begin
                --判断数据源列中是否存在元素：@ColumnName
                  if @DataSource.exist('/row/*[local-name()=sql:variable("@ColumnName")]')=1
                  begin
                     set @IsPK=null;
                     SELECT @IsPk=Fs.F FROM (SELECT T.c.value('.[text()]','Nvarchar(128)') AS F FROM @PKs.nodes('/Fields/Field') AS T(c))Fs Where Fs.F=@ColumnName
                     if @IsPK is null or @IsPK=''
                     begin
                       --非主键，更新字段值
                       set @FieldsList+=(@ColumnName+'=Source.'+@ColumnName+',');
                     end
                     else
                     begin
                        --主键，作为要更新条件
                       set @Condition+=@TableName+'.'+@ColumnName+'=Source.'+@ColumnName+' And ';
                     end
                     --XML查询
                     set @QueryStatement+=('T.c.value(''(./'+@ColumnName+'[not(@xsi:nil)])[1]'','''+@DataType);--元素读取（包含空值情况)
                     if @MaxLength is not null and @MaxLength<>-1
                        begin
                           set @QueryStatement+='('+CONVERT(nvarchar,@MaxLength)+')';
                        end
                     else if @MaxLength=-1 and @DataType<>'xml'
                        begin
                           set @QueryStatement+='(max)';
                        end
                      set @QueryStatement+=(''') as '+@ColumnName+',');
                  end
                  fetch ColumnNameList Into @ColumnName,@DataType,@MaxLength
                end
            close ColumnNameList;
            Deallocate ColumnNameList;          
            --去掉@FieldsList结尾的','
            set @StrLength=LEN(@FieldsList);
            set @FieldsList=SUBSTRING(@FieldsList,1,@StrLength-1);        
         --去掉@QueryStatement结尾的','
         set @StrLength=LEN(@QueryStatement);
         set @QueryStatement=SUBSTRING(@QueryStatement,1,@StrLength-1);
         --去掉@Condition结尾的‘and'
         set @StrLength=LEN(rtrim(@Condition));
         set @Condition=SUBSTRING(rtrim(@Condition),1,@StrLength-3);           
            set @Sql=N'USE DBTo ; update '+@TableName+' set '+@FieldsList+' from (select '+@QueryStatement+' 
            from @DataSource.nodes(''/row'') as T(c)) Source where '+@Condition;
    end  
    else if @DMLType='D' --删除数据
       begin
         --更新语句where后的条件表达式
         declare @LinkField nvarchar(1000);
         set @LinkField='  ';
         set @PKs=CONVERT(xml,dbo.uf_SplistString(@PrimaryKeyField,','));
         open ColumnNameList
            fetch ColumnNameList into @ColumnName,@DataType,@MaxLength;
            while @@FETCH_STATUS=0
            begin
            if @DataSource.exist('row/*[local-name()=sql:variable("@ColumnName")]')=1
             begin
              set @IsPK=null;--初始化
              --当前字段是否为主键
              select @IsPK=Fs.F from (select T.c.value('.[text()]','nvarchar(128)') as F from @PKs.nodes('/Fields/Field') as T(c))Fs where Fs.F=@ColumnName
              --主键
              if @IsPK is not null and @IsPK<>''
              begin
                 --主键删除条件
                 set @LinkField+='Target.'+@ColumnName+'=Source.'+@ColumnName+' And ';
                 --XML 查询
                 set @QueryStatement+=('T.c.value(''(./'+@ColumnName+'[not(@xsi:nil)])[1]'','''+@DataType);--元素读取（包含空值情况）
                if(@MaxLength is not null and @MaxLength<>-1)
                   begin
                      set @QueryStatement+='('+CONVERT(nvarchar,@MaxLength)+')';
                   end
                else if @MaxLength=-1 and @DataType<>'xml'
                   begin
                   set @QueryStatement+='(max)';
                  end
                set @QueryStatement+=(''') as '+@ColumnName+',');
              end 
             end
            fetch ColumnNameList into @ColumnName,@DataType,@MaxLength
            end
            close ColumnNameList;
            deallocate ColumnNameList;         
            --去除@QueryStateMent结尾的','
            set @StrLength=LEN(@QueryStatement);
            set @QueryStatement=SUBSTRING(@QueryStatement,1,@StrLength-1);          
            --去除@LinkField 结尾的’Add‘
            set @StrLength=LEN(rtrim(@LinkField));
            set @LinkField=SUBSTRING(rtrim(@LinkField),1,@StrLength-3);         
            set @Sql=N'Delete from '+@TableName+' from '+@TableName+' as Target inner join (select '+@QueryStatement+ ' from @DataSource.nodes(''/row'') as T(c))
 Source on '+@LinkField;
     end    
         Return @Sql--'hello'
end
go

 

3.2.14 解析并处理从队列中读取的消息

这里主要用来读取队列中的消息，并将消息进行处理，最终处理成一定的格式，并调用3.2.13中的存储过程，将数据同步到数据库中。

--将数据同步到数据表中
create procedure UP_SyncDataToTable
as
begin
set nocount on
--会话变量声明
declare @ConversationHandle uniqueidentifier;--会话句柄
declare @Msg_Body nvarchar(max);
declare @Msg_Type_Name sysname;
declare @ErrorNumber int ;
--变量赋值
while(1=1)
  begin
    begin transaction
       --从队列中接收消息
       waitfor
       (
         receive top(1)
         @Msg_Type_Name=message_type_name,
         @ConversationHandle=[conversation_handle],
         @Msg_Body=message_body
--         from dbo.[DBTo_DataSyncQueue]
         from dbo.[DBFrom_DataSyncQueue]
       ),timeout 500
       --如果接收到消息-处理，否则跳过
       if @@ROWCOUNT<=0
         begin
           break;
        end           
       if @Msg_Type_Name='http://oa.founder.com/Data/Sync'
       begin
         --声明变量
         declare @DMLType char(1);
         declare @PrimaryKeyField nvarchar(128),@TableName nvarchar(128),@Sql nvarchar(4000);
         declare @DataSource xml
        --受影响的行数
         declare @EffectRowCount int;
        declare @ErrMsg xml;
            begin try
               --变量赋值
               set @DataSource=convert(xml,@Msg_Body);--数据源
               set @PrimaryKeyField=@DataSource.value('(/PrimaryKeyField)[1][text()]','nvarchar(128)');--主键列表
               set @TableName=@DataSource.value('(/Table)[1][text()]','nvarchar(128)');--操作数据表
               set @DMLType=@DataSource.value('/DMLType[1][text()]','char(1)');--操作类型
               set @Sql=dbo.UF_XMLDataSourceToSQL(@DataSource,@TableName,@PrimaryKeyField,@DMLType);
               exec sp_executesql @Sql,
               N'@DataSource XML',
               @DataSource;
            end try
            begin catch
               declare @DBName nvarchar(128)
               select @DBName=Name from master..SysDataBases where dbid=(select dbid from master..sysprocesses where spid=@@SPID)
               set @ErrorNumber=ERROR_NUMBER();
               set @ErrMsg=(select ERROR_NUMBER() as ErrNumber,
                                ERROR_SEVERITY() as ErrSeverity,
                                ERROR_STATE() as ErrState,
                               ERROR_PROCEDURE() as ErrProcedure,
                                ERROR_LINE() as ErrLine,
                                ERROR_MESSAGE() as ErrMessage,
                                @PrimaryKeyField as PrimaryKeyField,
                                @TableName as TableName,
                                @DMLType as DMLType,
                                @Msg_Body as MessageContent,
                                @DBName as DBName
                           for XML raw);
                  --GOTO 错误处理标签
                  goto Err_Handle;   
            end catch
            --结束会话
            End Conversation @ConversationHandle
            if @ErrorNumber is not null
            begin        
               --错误处理区域
               Err_Handle:
                  if @ErrMsg is not null
                  begin
                    declare @test nvarchar(128);
                    --发送失败消息
                    send on conversation @ConversationHandle
                    message type [http://oa.founder.com/Data/Sync/Error](@ErrMsg)
                  end
                  --结束会话
               end conversation @ConversationHandle
               --break;
               --回滚--不可回滚，否则将无法发送失败消息
               --GoTO  Err_Lab；
            end 
        end 
    commit transaction
       end
end
go

 

3.2.15 对目标数据库的消息队列进行内部激活

这里主要是用来激活目标数据库的消息队列，主要用来实现数据的同步以及同步出错的错误信息的反馈。

--对Service Broker队列使用内部激活,并指定将要调用的存储过程
use DBTo
go
--alter Queue dbo.[DBTo_DataSyncQueue] with activation
alter Queue dbo.[DBFrom_DataSyncQueue] with activation
(
  status=on,
  max_queue_readers=1,
  Procedure_name=UP_SyncDataToTable,
  Execute as self
)
Go

 

    完成以上这些步骤以后，就可以实现同一数据库实例上两个不同的数据库之间的数据同步。即如果DBFrom数据库中的Org_Users中的某一条信息发生变化，会马上的自动同步到DBTo数据库中的Org_Users 表。如果是想要实现不同的数据库实例间的数据库的表的同步，则可以参考以下链接：

http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html

在创建启用传输安全、对话安全，创建路由、远程服务绑定等额外的操作之后，剩下的操作跟在同一数据库实例中的操作是一样的。

       此外，本文还参考了如下的链接：

http://www.cnblogs.com/downmoon/archive/2011/04/05/2005900.html

       希望可以给大家一些启发和帮助。具体的源码有兴趣的朋友可以留下邮箱。

 

墨遥
关注
专栏目录
【微服务】springboot3 集成 Flink CDC 1.17 实现mysql数据同步
congge_study的博客
10-13 7001
springboot3 集成 Flink CDC 1.17 实现mysql数据同步
【大数据】Flink CDC 实时同步mysql数据
最新发布
congge
10-07 8605
Flink CDC 实时同步mysql数据
数据同步方式介绍
sinat_36024346的博客
02-02 889
日志采集 数据库采集 直连同步 通过规范好的接口和动态连接库的方式直接连接业务库，例如，通过ODBC/JDBC进行直连 优点：配置简单，实现容易，比较适合操作业务系统的数据同步 缺点：会对业务库产生较大压力，如果有主备策略可以从备库进行抽取，此方式不适合直接从业务库到数仓的情景 数据文件同步 通过约定好的文件编码、大小、格式等，从源系统生成数据文本文件，利用FTP等传输方式传输至目标系统后，加载到目标数据库中，完成数据同步 为了防止丢包等情况，一般会附加一个校验文件，检验文件
Elasticsearch-数据同步
山丘i
11-28 1354
elasticsearch中的酒店数据来自于mysql数据库，因此mysql数据发生改变时，elasticsearch也必须跟着改变，这个就是elasticsearch与mysql之间的数据同步。 3.1.思路分析 常见的数据同步方案有三种： 同步调用 异步通知 监听binlog 3.1.1.同步调用 方案一：同步调用 基本步骤如下： hotel-demo对外提供接口，用来修改elas...
数据同步是如何实现的？为什么需要数据同步？
oOBubbleX的博客
09-05 1030
实时数据同步，确保数据的一致性和实时更新性，将有利于企业各部门之间高效协作，从而支持企业的业务运作和决策。本文将介绍数据同步概念、数据同步的步骤、数据同步的方式，从而加深对数据同步的认识。
一款国产开源数据同步中间件，支持多种数据源和预警功能
欧菲丽的博客
05-10 1105
DBSyncer是一款开源的数据同步中间件，提供MySQLOracleSqlServerPostgreSQLKafkaFileSQL等同步场景。支持上传插件自定义同步转换业务，提供监控全量和增量数据统计图、应用性能预警等。
数据的同步方法
weixin_34406061的博客
08-02 99
2019独角兽企业重金招聘Python工程师标准>>> ...
SQL Azure(十) SQL Azure Data Sync数据同步功能(上)
weixin_34203426的博客
05-29 168
　　首先举个简单的例子：如果某个大型生产企业包含有2个子系统： -　　有一个《产品生产系统》放在企业内部部署，在企业内部办公的员工(生产员工)都可以通过内网来访问该系统。后台的数据库是局域网内的SQL Server 2008 R2。 -　　另外有一个《产品库存系统》放在Windows Azure平台上运行。在企业外部的员工，比如销售人员可以通过手机、平...
不同数据源之间数据同步的几种实现方式
weixin_49034832的博客
06-30 2367
SyncNavigator是一款功能强大的数据库同步软件，适用于SQLSERVER, MySQL，具有自动/定时同步数据、无人值守、故障自动恢复、同构/异构数据库同步、断点续传和增量同步等功能，支持Windows xp以上所有操作系统，适用于大容量数据库快速同步。 安装包下载地址：https://www.syncnavigator.cn/Setup.zip 帮助文档地址：https://www.syncnavigator.cn/Help_zh-CN.chm Web文档地址：https://www...
mysql数据迁移与同步常用解决方案总结
congge
05-10 6063
mysql数据迁移与同步常用解决方案总结
同步 实现同步的三种方式
zs1342084776的博客
08-11 1万+
/** * 多线程共享数据：多个线程有可能同时处理同一个资源 * 同步：多个线程在同一时间段内只能有一个线程执行指定代码，其它线程要等此线程执行完成之后才可以继续执行 */ /** * 1.多线程共享数据时会发生线程不安全的情况 * 2.多线程共享数据必须使用同步 * 3.实现同步的三种方式： * 1)同步代码块 * synchronized（要同步的对...
三方数据同步方案
yongyuai的博客
05-18 1784
如何实现一个简单的数据同步组件
glmapper_2018
06-18 226
目前关于数据同步的组件开源社区有很多，如：Flink CDC, DataX, seaTunel，Kattle 等等，大体上可以分为两种：基于日志的和基于 JDBC 的。这些同步组件在进行整库同步或者 schema 差异性不大的情况下通过可视化界面或者配置文件映射的方式可以直接达到我们库-库的同步诉求，但是对于一些定制化较大的场景处理起来还比较麻烦。为了满足这样的一个场景，笔者写了一个小的同步组件。...
功能设计:设计一个数据同步的方案(p6spy+注解+AOP)
liuhao192的博客
07-02 691
这个方案只完成了一个简单的demo，估计完成了80%后，由于方案的修改，这个部分功能也就作废了，打算写篇文章记录一下，大致的思路 用到的两个知识点： P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。 通过P6Spy我们可以对SQL语句进行拦截，相当于一个SQL语句的记录器，这样我们可以用它来作相关的分析 Java 注解（Annotation）又称 Java 标注，是 JDK5.0 引入的一种注释机制。 Java 语言中的类、方法、变量、参数和包等都可以被标注。和 Javadoc
几种常见的数据同步方式
热门推荐
技术专家
05-19 1万+
数据仓库的特性之一是集成，即首先把未经过加工处理的、不同来源的、不同形式的数据同步到ODS层，一般情况下，这些ODS层数据包括日志数据和业务DB数据。对于业务DB数据而言(比如存储在MySQL中)，将数据采集并导入到数仓中(通常是Hive或者MaxCompute)是非常重要的一个环节。 那么，该如何将业务DB数据高效准确地同步到数仓中呢？ 一般企业会使用两种方案： 直连同步 实时增量同步(数据库日志解析) 其中直连同步的基本思路是直连数据库进行SELECT，然后将查询的数据存储到本地文件作为中间存
聊聊数据同步方案
Java后端技术栈
09-20 1290
本文主要讨论数据同步方案，并对canal做了简单介绍。同时也对binlog的解析和mysql协议简单介绍希望能了解这种CDC的基本原理。
常见的几种数据同步方案
很多时候犯错都是在不知情的情况下发生的
03-03 1936
感兴趣的可以深究，这里只想说明：对大数据量处理，包括数据提取，数据加载，增量数据同步，可以借助这些工具，ETL工具提供了一些可视化的组件+配置具体的链接类型。但是因为引入新的组件，在多数据源的情况下，不可避免的带来系统的复杂性。这种就是常见的SQL脚本，常用于数据割接，错误数据修改，包括配置数据，业务字段，运维手工调整异常数据等。如上，比如：例子中的触发器是在tb_order表中插入新数据时触发的，将新数据同步到tb_order_his表中（读者可以根据需要调整触发器的触发时机和逻辑）
Sqlserver事务发布实现数据同步功能
zyz的博客
06-11 513
这里以sqlserver2008的事务发布功能为例，对发布订阅的方式简要介绍一下操作流程 首先，在要发布的数据库上创建一个我们的产品数据库（这里叫做dnt_new）,然后在该数据库实例的左侧导航的“复制”--“本地发布”上击右键，然后选择“新建发布”，如下： 这样，系统就会启动‘发布向导’来引导大家，这里我们在欢迎界面下点击"下一步”，然后在当前窗口中选择要发布的数据库，如下： 点击下一步，然后在接下来的窗口中选择“事务发布”，如下图： ...
如何实现数据自动化的实时同步？
文件数据安全交换
11-10 1128
企业在日常业务中，比如总分支机构之间、数据中心之间、不同节点之间、跨国业务之间等，都需要将文件及时的传输，以供协同使用。所以，很多企业会选择一些同步工具或软件。
SQL2008事务发布实现数据同步教程
在SQL Server 2008中，事务发布是一种用于实现实时数据同步的方法，它允许更改在一个数据库（发布服务器）上的数据实时地反映到另一个数据库（订阅服务器）上。这个过程涉及到以下几个关键步骤： 1. **创建发布**：...
评论 24
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值