一、通过发布/订阅的方式实现同步
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队列使用内部激活,并指定将调用的存储过程
激活源数据库的消息队列,并为其指定调用的存储过程,即第九步创建的存储过程
--对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