SQLServer2000同步复制技术实现步骤

一、通过发布/订阅的方式实现同步

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               队列/服务：存储发送者和接收消息。

推荐阅读：http://blog.csdn.net/beirut/article/details/7693490

                       http://blog.csdn.net/litao2/article/details/77715233

                      【SQL Server学习笔记】Service Broker创建异步的、数据驱动的消息应用程序 (推荐)

Service Broker完成实例之间的会话详细解读 http://www.cnblogs.com/shengdimaya/p/5403938.html

数据加密 http://blog.csdn.net/litao2/article/details/49590843

示例：通过SQL Server Service Broker 消息队列的方式实现数据同步

第一步：为数据库启动Service Broker活动

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

第二步 创建数据库主密匙

Use DBFromgocreate master keyencryption by password='pass@word1'goUse DBTogocreate master keyencryption by password='pass@word1'go

第三步：创建消息类型、协定

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

Use DBFromgo--数据同步—消息类型create message type [http://oa.founder.com/Data/Sync]validation=well_formed_xmlgo--数据同步--错误反馈消息类型create message type [http://oa.founder.com/Data/Sync/Error]validation=well_formed_xmlgo--数据同步协议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)goUse DBTogo--数据同步—消息类型create message type [http://oa.founder.com/Data/Sync]validation=well_formed_xmlgo--数据同步--错误反馈消息类型create message type [http://oa.founder.com/Data/Sync/Error]validation=well_formed_xmlgo--数据同步协议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 DBFromgocreate queue [DBFrom_DataSyncQueue]with status=ongouse DBTogocreate queue [DBFrom_DataSyncQueue]with status=ongo

第五步：创建同步服务

利用上边创建的消息协定和消息队列来创建数据同步的服务。

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

第六步：DBFrom数据库上创建服务配置列表（存储接收服务列表）
创建表的操作之后又插入了一条数据，也就是上边我们创建的服务名，如果有多个服务的话，依次插入该表即可。

use DBFromgo--同步数据--目标服务配置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 DBFromgo--发送同步数据消息(消息内容)--创建存储过程Create procedure UP_SyncDataSendMsg(@PrimaryKeyField nvarchar(128),--主键@TableName nvarchar(128),--表名@DMLType char(1),--操作类型@XMLData xml--正文内容)asbegin   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;endgo

第八步：创建数据同步异常信息记录表
记录在数据同步过程中出现的异常信息。

use DBFromgocreate 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.SyncExceptionaddPrimaryKeyField nvarchar(128),TableName nvarchar(128),DMLType char(1),DBName nvarchar(128)Go

第九步：数据同步反馈
源数据库中接收队列中的消息，将同时出错的信息，解析一下，然后插入到异常信息记录表里边。

use DBFromgocreate procedure UP_SyncDataFeedbackasbeginset 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;endendcommit;go

第十步：对Service Broker队列使用内部激活，并指定将调用的存储过程
激活源数据库的消息队列，并为其指定调用的存储过程，即第九步创建的存储过程

--对Service Broker队列使用内部激活，并指定将调用的存储过程use DBFromgoalter 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 DBFromGo--用户信息同步Create Trigger UT_DataSync_Userson dbo.Org_Users  --为Org_Users添加触发器after insert,update,deleteasset 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';  endelse if(@InsertCount<>0 and @DeleteCount=0) ----Insert  begin  select @XMLData=(select * from inserted for xml raw ,Binary base64,ELEMENTS XSINIL)  set @DMLType='A';  endelse----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 DBTogo--转换用‘,'分割的字符串@strcreate Function dbo.uf_SplitString(@str nvarchar(max),@Separator nchar(1)=',')returns nvarchar(2000)asbegin   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);endgo

第十三步：将解析过的消息信息，根据操作类型的不同同步到数据表中
--将XML数据源中的数据同步到数据表中(包括增删改)

Use DBTogocreate function [dbo].[UF_XMLDataSourceToSQL](   @DataSource XML,--数据源   @TableName varchar(128),--同步数据表名称   @PrimaryKeyField varchar(128),--需要同步的表的主键，主键为多个时用‘,'隔开   @DMLType char(1) --A:新建；U:编辑；D：删除)returns nvarchar(4000)asbegin    --变量声明及数据初始化    --声明数据表@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]asbeginset 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       endend

第十五步：对目标数据库的消息队列进行内部激活
激活目标数据库的消息队列，主要用来实现数据的同步以及同步出错的错误信息的反馈。

--对Service Broker队列使用内部激活,并指定将要调用的存储过程use DBTogo--alter Queue dbo.[DBTo_DataSyncQueue] with activationalter 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