一、通过发布/订阅的方式实现同步
http://www.cnblogs.com/CareySon/archive/2012/06/20/IntroductToSQLServerReplicationPart1.html
注意：通过发布/订阅的方式实现同步（发布类型：事务发布），发布的表，表必须要有主键。
二、数据同步的几种实现（推荐阅读）
1、通过发布/订阅的方式实现同步2、通过SQL计划方式实现数据同步3、通过SQL Server Service Broker消息队列的方式实现数据同步（1）、Service Broker 服务代理 概述SQL Server Service Broker 为 SQL Server 数据库引擎中的消息和队列应用程序提供本机支持。 这使开发人员可以更轻松地创建使用 数据库引擎 组件在完全不同的数据库之间进行通信的复杂应用程序。 开发人员可以使用 Service Broker 轻松生成可靠的分布式应用程序。
使用 Service Broker 的应用程序开发人员无需编写复杂的内部通信和消息，即可跨多个数据库分发数据工作负荷。 因为 Service Broker 会处理会话上下文中的通信路径，所以这就减少了开发和测试工作。 同时还提高了性能。 例如，支持网站的前端数据库可以记录信息，并发送处理密集型任务以便在后端数据库中进行排队。 Service Broker 确保在事务上下文中管理所有任务，以确保可靠性和技术一致性。
结构如下：
服务：消息发送和接收的处理接口。
A. MessageType 消息类型：服务代理对象名称（URI格式）、消息类型。消息结构
B. Constract 约束：确定服务代理对象哪个是发送者、哪个是接收者。消息规则
C. Queue 队列/服务：存储发送者和接收消息。
Service Broker完成实例之间的会话详细解读 http://www.cnblogs.com/shengdimaya/p/5403938.html
示例：通过SQL Server 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
第二步 创建数据库主密匙
Use DBFrom
go
create master key
encryption by password='pass@word1'
go
Use DBTo
go
create master key
encryption by password='pass@word1'
go第三步：创建消息类型、协定
创建消息类型和消息协定，源数据库和目标数据库的消息类型和协定都要一致。
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
第四步：创建消息队列
创建消息队列，源数据库和目标数据库都要创建，队列名字可以自主命名。
use DBFrom
go
create queue [DBFrom_DataSyncQueue]
with status=on
go
use DBTo
go
create queue [DBFrom_DataSyncQueue]
with status=on
go第五步：创建同步服务
利用上边创建的消息协定和消息队列来创建数据同步的服务。
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第六步：DBFrom数据库上创建服务配置列表（存储接收服务列表）
创建表的操作之后又插入了一条数据，也就是上边我们创建的服务名，如果有多个服务的话，依次插入该表即可。
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第七步：发送数据同步消息
向服务配置列表（DBTo服务）发送数据
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第八步：创建数据同步异常信息记录表
记录在数据同步过程中出现的异常信息。
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第九步：数据同步反馈
源数据库中接收队列中的消息，将同时出错的信息，解析一下，然后插入到异常信息记录表里边。
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激活源数据库的消息队列，并为其指定调用的存储过程，即第九步创建的存储过程
--对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第十一步：在源数据库中为需要同步的数据表创建触发器
通过查询系统的Inserted和Deleted临时表来判断执行同步的操作类型是更新（U）、新增（A）还是删除（D），最后调用第七步 中创建的存储过程来对数据进行处理并发送。
use DBFrom
Go
--用户信息同步
Create Trigger UT_DataSync_Users
on dbo.Org_Users --为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该函数主要是用来进行字符分割，用来处理主键有多个字段的情况。
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第十三步：将解析过的消息信息，根据操作类型的不同同步到数据表中
--将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_SplitString(@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第十四步：解析并处理从队列中读取的消息
读取队列中的消息，并将消息进行处理，最终处理成一定的格式，并调用13步中的存储过程，将数据同步到数据库中。
--将数据同步到数据表中
create procedure [dbo].[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
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;
insert into dbo.Table_1 values(@Sql,@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);
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 conversation @ConversationHandle
end
end catch
end
end
commit transaction
end
end第十五步：对目标数据库的消息队列进行内部激活
激活目标数据库的消息队列，主要用来实现数据的同步以及同步出错的错误信息的反馈。
--对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推荐阅读： http://blog.csdn.net/xuemoyao/article/details/14002209
3167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
