关于数据同步的几种实现

最新推荐文章于 2024-04-24 07:50:49 发布
最新推荐文章于 2024-04-24 07:50:49 发布
阅读量6.4k 收藏 8
点赞数

概述

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

数据库层面的数据库同步主要有三种方式：通过发布/订阅的方式实现同步，通过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 活动，并且授信。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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可以对要发送的消息进行加密。

1
2
3
4
5
6
7
8
9
10
11
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 创建消息类型、协定

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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 创建消息队列

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

1
2
3
4
5
6
7
8
9
10
11
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 创建数据同步服务

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

1
2
3
4
5
6
7
8
9
10
11
12
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 在源数据库上创建服务配置列表

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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。然后通过创建一个游标来条的读取上边创建的服务列表中的列表信息，向不同的服务发送消息。

   

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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 创建数据同步异常信息记录表

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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 数据同步反馈

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

--数据同步回馈

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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,<br>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 中创建的存储过程。

1
2
3
4
5
6
7
8
9
10
11
--对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 中创建的存储过程来对数据进行处理并发送。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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 目标数据库中创建，字符分割函数

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

--目标数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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 将解析过的消息信息，根据操作类型的不同同步到数据表中

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
--将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 <br>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))<br> Source on ' +@LinkField;
      end   
          Return @Sql-- 'hello'
end
go

 

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
--将数据同步到数据表中
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 对目标数据库的消息队列进行内部激活

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

1
2
3
4
5
6
7
8
9
10
11
12
--对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

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

jimmy0021
关注
  • 0
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
大数据时代，数据实时同步解决方案的思考—最全的数据同步总结
weixin_30677617的博客
08-15 6655
1、 早期关系型数据库之间的数据同步 1）、全量同步 比如从oracle数据库中同步一张表的数据到Mysql中，通常的做法就是 分页查询源端的表，然后通过 jdbc的batch 方式插入到目标表，这个地方需要注意的是，分页查询时，一定要按照主键id来排序分页，避免重复插入。 2）、基于数据文件导出和导入的全量同步，这种同步方式一般只适用于同种数据库之间的同步，如果是不...
使用SyncNavigator v8.6.2自动同步数据库数据
weixin_46503877的博客
03-12 402
自动同步数据库数据 使用 HKROnline SyncNavigator 自动同步数据库数据。与Windows计划任务操作非常类似，您只需要设置同步任务执行的时间系统便能在指定的时间自动运行。 1. 在 "同步管理" 面板中选择需要设置的项目，点击 "修改" 按钮开始设置。 2. 切换到 "计划调度设置" 选项卡，点击 "新建" 按钮新建一个运行计划。 3. 指定需要运行的时...
数据库同步原理
最新发布
weixin_43803780的博客
04-24 420
**数据校验**：定期或在同步完成后进行数据完整性校验，对比源数据库和目标数据库的数据指纹或特定关键值，确保数据同步的正确性。- **全量同步**：首次同步或长时间断开后，可能需要进行全量数据复制，即将源数据库的所有数据一次性复制到目标数据库。- **直接写入**：对于小规模、低频的数据同步，可以直接将变更数据作为SQL语句执行，更新或插入目标数据库的记录。- **增量同步**：在全量同步后或持续同步过程中，仅复制自上次同步以来发生的数据变更。
Elasticsearch-数据同步
山丘i
11-28 1280
elasticsearch中的酒店数据来自于mysql数据库，因此mysql数据发生改变时，elasticsearch也必须跟着改变，这个就是elasticsearch与mysql之间的数据同步。 3.1.思路分析 常见的数据同步方案有三种： 同步调用 异步通知 监听binlog 3.1.1.同步调用 方案一：同步调用 基本步骤如下： hotel-demo对外提供接口，用来修改elas...
一款国产开源数据同步中间件，支持多种数据源和预警功能
欧菲丽的博客
05-10 1074
DBSyncer是一款开源的数据同步中间件，提供MySQLOracleSqlServerPostgreSQLKafkaFileSQL等同步场景。支持上传插件自定义同步转换业务，提供监控全量和增量数据统计图、应用性能预警等。
SQL Azure(十) SQL Azure Data Sync数据同步功能(上)
weixin_34203426的博客
05-29 151
　　首先举个简单的例子：如果某个大型生产企业包含有2个子系统： -　　有一个《产品生产系统》放在企业内部部署，在企业内部办公的员工(生产员工)都可以通过内网来访问该系统。后台的数据库是局域网内的SQL Server 2008 R2。 -　　另外有一个《产品库存系统》放在Windows Azure平台上运行。在企业外部的员工，比如销售人员可以通过手机、平...
Oracle通过LogMiner实现数据同步迁移
09-08
总之，Oracle的LogMiner提供了一种灵活且成本效益高的方法来实现数据同步迁移，尤其适合中小规模的数据同步需求。虽然它不如GoldenGate等专业工具功能强大，但对于许多组织来说，LogMiner的解决方案已经足够满足他们...
Windows几种线程同步方法介绍
01-09
另外如一个线程在读取某块内存中的数据，而另一个线程又正在修改这块内存的值，这同样不是我们想要的，所以线程之间必须要有一套自己的规则，不然凌乱了。线程之间需要通信，如A线程霸占某个B线程需要的资源X，在A...
mysql-oracle数据同步
09-12
对于“mysql-oracle数据同步”这个主题，我们可以采用以下几种方法： 1. **使用ETL工具**：Extract, Transform, Load（ETL）工具如Informatica、Talend或Kettle（Pentaho Data Integration），它们提供图形化界面，...
java 多线程的同步几种方法
08-29
以下将详细解释Java中实现多线程同步的五种方法。 1. ** synchronized 关键字 ** synchronized关键字是Java中最基础的同步机制，它可以保证同一时间只有一个线程执行特定的代码块。synchronized可以应用于方法或...
如何实现多个系统间的数据同步
08-02
NULL 博文链接：https://goodscript.iteye.com/blog/1963423
详解hbase与hive数据同步
08-29
HBase与Hive数据同步是大数据处理中常见的一种数据集成方式。HBase是一种NoSQL数据库，适合存储大量半结构化和非结构化数据，而Hive是基于Hadoop的数据仓库工具，用于数据分析和处理。两者的数据同步可以实现数据的...
三方数据同步方案
yongyuai的博客
05-18 1750
如何实现一个简单的数据同步组件
glmapper_2018
06-18 159
目前关于数据同步的组件开源社区有很多，如：Flink CDC, DataX, seaTunel，Kattle 等等，大体上可以分为两种：基于日志的和基于 JDBC 的。这些同步组件在进行整库同步或者 schema 差异性不大的情况下通过可视化界面或者配置文件映射的方式可以直接达到我们库-库的同步诉求，但是对于一些定制化较大的场景处理起来还比较麻烦。为了满足这样的一个场景，笔者写了一个小的同步组件。...
不同数据源之间数据同步的几种实现方式
weixin_49034832的博客
06-30 2315
SyncNavigator是一款功能强大的数据库同步软件，适用于SQLSERVER, MySQL，具有自动/定时同步数据、无人值守、故障自动恢复、同构/异构数据库同步、断点续传和增量同步等功能，支持Windows xp以上所有操作系统，适用于大容量数据库快速同步。 安装包下载地址：https://www.syncnavigator.cn/Setup.zip 帮助文档地址：https://www.syncnavigator.cn/Help_zh-CN.chm Web文档地址：https://www...
功能设计:设计一个数据同步的方案(p6spy+注解+AOP)
liuhao192的博客
07-02 649
这个方案只完成了一个简单的demo，估计完成了80%后，由于方案的修改，这个部分功能也就作废了，打算写篇文章记录一下，大致的思路 用到的两个知识点： P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。 通过P6Spy我们可以对SQL语句进行拦截，相当于一个SQL语句的记录器，这样我们可以用它来作相关的分析 Java 注解（Annotation）又称 Java 标注，是 JDK5.0 引入的一种注释机制。 Java 语言中的类、方法、变量、参数和包等都可以被标注。和 Javadoc
如何实现实时同步备份数据？
weixin_46934725的博客
04-22 743
分析： 1.备份服务器（rsync），nfs存储服务器 2.监控实时变化的数据信息（inotify） 3.将变化的数据进行备份（nfs+inotify+rsync） 前面已经将备份服务器和nfs配置完成，这里讲一下配置inotify的流程。 先下载epel源，然后下载inotify yum install epel-release -y yum install inotify-tools rp...
数据的实时同步
weixin_33795743的博客
05-20 1326
一、两主机间数据的实时同步 1.同步原理 利用监控服务（inotify），监控同步数据服务器目录中信息的变化发现目录中数据产生变化，就利用rsync服务推送到备份服务器上 2.实现方式 inotify+rsync方式实现数据同步sersync 在inotify 软件基础上进行开发的，功能更加强大 3.inotify 异步的文件系统时间监控机制，利用事件驱动机制，而无须通过诸如cron等的轮询机制来...
实现同步机制的几种方式？
09-19
### 回答1： 一些实现同步机制的方式包括：使用锁、信号量、条件变量、屏障等。锁可以用于保护共享资源，信号量可以用于限制并发访问，条件变量可以用于线程之间的通信，屏障可以用于同步多个线程的执行。具体实现方式取决于具体的应用场景和需求。 ### 回答2： 实现同步机制的几种方式包括：锁机制、信号量机制、条件变量机制和管程机制。 锁机制是最常见的同步机制，通过对关键资源加锁来实现同一时间只能有一个线程访问该资源。常见的锁包括互斥锁和读写锁。互斥锁用于实现互斥访问，即同一时间只能有一个线程访问该资源；读写锁则允许多个线程同时读取资源，但只允许一个线程写入资源。 信号量机制使用信号量来实现对资源的访问控制。它通过一个计数器来控制同一时间内允许访问资源的线程数量，并提供了P操作（申请访问资源）和V操作（释放资源）来实现对资源的加锁和解锁。 条件变量机制用于实现线程之间的通信和协作。它通过一个条件变量和一个互斥锁来实现。线程可以在某个条件成立时等待条件变量，直到条件满足后被唤醒；同时，其他线程也可以通过发送信号来通知条件变量的等待线程条件已经满足。 管程机制是一种高级的同步机制，它将共享资源和对资源的操作封装在一个对象中，通过对象的方法来实现资源的访问控制。管程提供了条件变量和互斥锁来实现线程之间的通信和同步操作。 这些同步机制都可以用来解决多线程并发访问共享资源时可能引发的问题，例如数据竞争和死锁。不同的机制适用于不同的场景和需求，开发者需要根据实际情况选择合适的同步机制来确保程序的正确性和性能。 ### 回答3： 实现同步机制的几种方式有： 1. 互斥量（Mutex）：使用互斥量来保证同一时间只有一个线程执行临界区代码，通过对互斥量的加锁和解锁来控制线程的访问顺序。 2. 信号量（Semaphore）：使用信号量来控制同时可访问某一资源的线程数量，通过信号量的P（wait）和V（signal）操作来进行进程间的同步。 3. 条件变量（Condition）：条件变量用于在某个线程等待特定条件的发生，当条件满足时，其他线程可以唤醒等待的线程继续执行。 4. 临界区（Critical Section）：临界区是指一段代码，一次只允许一个线程进入执行，通过使用互斥量或信号量来保证临界区的互斥性。 5. 事件（Event）：事件用于线程间的通信和同步，一个线程等待某个事件的发生，另一个线程发生该事件后，等待的线程被唤醒继续执行。 6. 互斥量+条件变量（Mutex+Condition）：通过互斥量的加锁和解锁保证临界区的互斥性，通过条件变量的等待和唤醒来控制线程的执行顺序和同步。 以上是实现同步机制的几种常见方式，不同的应用场景可以选择适合的方式来实现线程间的同步和互斥操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值