数据库同步

1.数据同步定义： 目前应用系统处理核心，不但应用系统需要向数据库进行增/删/改/查操作，同样数据仓库也需要从众多的数据库中获取不同交易数据来完善自身的数据集。

2.安全性： 从业务主机数据库到同步查询数据库必须只支持单项复制，防止对业务主机的任何反向攻击；数据库同步服务器单独设置，不占用主机任何资源，采用读取数据库日志方式获取相关表数据，对不需要同步的数据进行过滤。

3.数据同步方法： 关于数据同步主要有两个层面的同步。 一 是通过后台程序编码实现数据同步；二是直接作用于数据库，在数据库层面实现数据的同步。数据库层面的数据库同步主要有三种方式：通过发布/订阅的方式实现同步，通过SQL JOB方式实现数据同步，通过Service Broker 消息队列的方式实现数据同步。

4.发布/订阅实现同步： 发布/订阅是Sql Server自带的一种数据库备份的机制，通过该机制可以快速的实现数据的备份同步，不用编写任何的代码。总的来说，这种数据备份同步的方式，在表结构一致、数据量不是特别大的情况下还是非常高效的一种同步方式。

发布订阅两个步骤：1、发布。2、订阅。首先在数据源数据库服务器上对需要同步的数据进行发布，然后在目标数据库服务器上对上述发布进行订阅。发布可以发布一张表的部分数据，也可以对整张表进行发布。下面分别介绍发布、订阅的过程。

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

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

具体过程： http://kb.cnblogs.com/page/103975/

5.定时作业实现同步

通过Sql Job定时作业的方式实现同步其基本原理就是通过目标服务器和源服务器的连接，然后通过编写Sql语句，从源服务器中读取数据，再更新到目标服务器。这种数据同步的方式比较灵活。创建过sql定时作业之后，主要需要执行以下关键的两步。

5.1 创建数据库连接

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

• 添加一个连接；
• 系统存储过程sp_addlinkedserver 参数。

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'

5.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数据库上获取源数据，并同步到目标数据库中

SELECT [DeptID],[DeptStatus],[DeptTel],[DeptBrief],[DeptFunctions] FROM [WIN-S1PO3UA6J7I].[DBFrom].[dbo].[Org_DepartmentsExt];

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

6.消息队列实现同步

6.1SQL 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 中所包含的功能有助于防止未经授权的网络访问，并可以对通过网络发送的消息进行加密。

6.2具体的实现步骤

6.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

6.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

6.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

6.2.4 创建消息队列

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

use DBFrom

go

create queue [DBFrom_DataSyncQueue]

with status=on

go

use DBTo

go

create queue [DBFrom_DataSyncQueue]

with status=on

go

6.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

6.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

6.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

6.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

6.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

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

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

use DBFrom

go

alter queue dbo.DBFrom_DataSyncQueue with activation

(

status=on,

max_queue_Readers=1,

procedure_name=UP_SyncDataFeedback,

execute as owner

);

Go

6.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

6.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

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

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

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

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

这里主要用来读取队列中的消息，并将消息进行处理，最终处理成一定的格式，并调用6.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

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

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

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

7、冗余表

      互联网很多业务场景的数据量很大，此时数据库架构要进行水平切分，水平切分会有一个patition key，通过patition key的查询能够直接定位到库，但是非patition key上的查询可能就需要扫描多个库了。     

     例如订单表，业务上对用户和商家都有订单查询需求：

Order(oid, info_detail)，

T(buyer_id, seller_id, oid)。

如果用buyer_id来分库，seller_id的查询就需要扫描多库；如果用seller_id来分库，buyer_id的查询就需要扫描多库。这类需求，为了做到高吞吐量低延时的查询，往往使用“数据冗余”的方式来实现，就是我们所说的的“冗余表”：

T1(buyer_id, seller_id, oid)，

T2(seller_id, buyer_id, oid)

同一个数据，冗余两份，一份以buyer_id来分库，满足买家的查询需求；一份以seller_id来分库，满足卖家的查询需求。当然，元数据还是放在Order(oid, info_detail)里。

如何实现冗余表

• 方法一：服务同步写

   

顾名思义，由服务层同步写冗余数据，如上图流程：

• 业务方调用服务，新增数据
• 服务先插入T1数据
• 服务再插入T2数据
• 服务返回业务方新增数据成功

优点：

• 不复杂，服务层由单次写，变两次写 
• 数据一致性相对较高（因为双写成功才返回） 

缺点：

• 请求的处理时间增加（要插入次，时间加倍） 
• 数据仍可能不一致，例如第二步写入T1完成后服务重启，则数据不会写入T2

• 方法二：服务异步写

数据的双写并不再由服务来完成，服务层异步发出一个消息，通过消息总线发送给一个专门的数据复制服务来写入冗余数据，如上图1-6流程：

• 业务方调用服务，新增数据
• 服务先插入T1数据
• 服务向消息总线发送一个异步消息（发出即可，不用等返回，通常很快就能完成）
• 服务返回业务方新增数据成功
• 消息总线将消息投递给数据同步中心
• 数据同步中心插入T2数据

        优点：

• 请求处理时间短（只插入1次） 

缺点：

• 系统的复杂性增加了，多引入了一个组件（消息总线）和一个服务（专用的数据复制服务）
• 因为返回业务线数据插入成功时，数据还不一定插入到T2中，因此数据有一个不一致时间窗口（这个窗口很短，最终是一致的）
• 在消息总线丢失消息时，冗余表数据会不一致

• 方法三：线下异步写

数据的双写不再由服务层来完成，而是由线下的一个服务或者任务来完成，如上图1-6流程：

• 业务方调用服务，新增数据 
• 服务先插入T1数据 
• 服务返回业务方新增数据成功 
• 数据会被写入到数据库的log中 
• 线下服务或者任务读取数据库的log 
• 线下服务或者任务插入T2数据

优点：

• 数据双写与业务完全解耦 
• 请求处理时间短（只插入1次） 

缺点：

• 返回业务线数据插入成功时，数据还不一定插入到T2中，因此数据有一个不一致时间窗口（这个窗口很短，最终是一致的） 
• 数据的一致性依赖于线下服务或者任务的可靠性

冗余表如何保证数据的一致性

• 方法一：线下扫描正反冗余表全部数据

如上图所示，线下启动一个离线的扫描工具，不停的比对正表T1和反表T2，如果发现数据不一致，就进行补偿修复。

优点：

• 比较简单，开发代价小 
• 线上服务无需修改，修复工具与线上服务解耦 

缺点：

• 扫描效率低，会扫描大量的“已经能够保证一致”的数据 
• 由于扫描的数据量大，扫描一轮的时间比较长，即数据如果不一致，不一致的时间窗口比较长 

很容易想到这个方案的优化方向。有没有可能只扫描“可能存在不一致可能性”的数据，而不是每次扫描全部数据，以提高效率呢？这就引出了第二种方案。

• 方法二：线下扫描增量数据

每次只扫描增量的日志数据，就能够极大提高效率，缩短数据不一致的时间窗口，如上图1-4流程所示：

• 写入正表T1 
• 第一步成功后，写入日志log1 
• 写入反表T2 
• 第二步成功后，写入日志log2 

当然，我们还是需要一个离线的扫描工具，不停的比对日志log1和日志log2，如果发现数据不一致，就进行补偿修复。互联网大部分业务是读多写少的场景。其实对于新增的数据量，是很小的，所有折中方案只需要扫描很少的数据，保证一致的数据也不会被重复扫描。

优点：

• 虽比方法一复杂，但仍然是比较简单的 
• 数据扫描效率高，只扫描增量数据 

缺点：

• 线上服务略有修改（代价不高，多写了2条日志） 
• 虽然比方法一更实时，但时效性还是不高，不一致窗口取决于扫描的周期 

我们之前的im好友表与反向好友表，修复周期是1天。当然这个周期也是由业务场景决定的。无论如何，修复还是不实时，有没有更为实时的修复方法呢？这就引出了方案三。

• 方法三：实时线上“消息对”检测

这次不是写日志了，而是向消息总线发送消息，如上图1-4流程所示：

• 写入正表T1
• 第一步成功后，发送消息msg1
• 写入反表T2
• 第二步成功后，发送消息msg2

这次不是需要一个周期扫描的离线工具了，而是一个实时订阅消息的服务不停的收消息。假设正常情况下，msg1和msg2的接收时间应该在3s以内，如果检测服务在收到msg1后没有收到msg2，就尝试检测数据的一致性，不一致时进行补偿修复。

优点：

• 效率高，每个数据只扫一次
• 实时性高，消息的通知很实时

缺点：

• 方案相对比较复杂，引入了消息总线这个组件
• 线下多了一个订阅总线的检测服务