数据同步更新---复制

复制的概念  
  复制是将一组数据从一个数据源拷贝到多个数据源的技术，是将一份数据发布到多个存储站点上的有效方式。使用复制技术，用户可以将一份数据发布到多台服务器上，从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据。复制技术可以确保分布在不同地点的数据自动同步更新，从而保证数据的一致性。   
 
SQL复制的基本元素包括  
  出版服务器、订阅服务器、分发服务器、出版物、文章  
   
  SQL复制的工作原理  
  SQL   SERVER   主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器，负责发表数据。出版服务器把要发表的数据的所有改变情况的拷贝复制到分发服务器，分发服务器包含有一个分发数据库，可接收数据的所有改变，并保存这些改变，再把这些改变分发给订阅服务器  
   
  SQL   SERVER复制技术类型,三种复制技术，分别是：  
  1、快照复制（呆会我们就使用这个）  
  2、事务复制  
  3、合并复制  
   
  只要把上面这些概念弄清楚了那么对复制也就有了一定的理解。接下来我们就一步一步来实现复制的步骤。  
  第一先来配置出版服务器  
  (1)选中指定[服务器]节点  
  (2)从[工具]下拉菜单的[复制]子菜单中选择[发布、订阅服务器和分发]命令  
  (3)系统弹出一个对话框点[下一步]然后看着提示一直操作到完成。  
  (4)当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加一个复制监视器。同时也生成一个分发数据库(distribution)  
   
  第二创建出版物  
  (1)选中指定的服务器  
  (2)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令。此时系统会弹出一个对话框  
  (3)选择要创建出版物的数据库，然后单击[创建发布]  
  (4)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)    
  (5)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型，SQLSERVER允许在不同的数据库如   ORACLE或ACCESS之间进行数据复制。但是在这里我们选择运行"SQL   SERVER   2000"的数据库服务器  
  (6)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表  
  (7)然后[下一步]直到操作完成。当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。  
   
  第三设计订阅  
  (1)选中指定的订阅服务器  
  (2)从[工具]下拉菜单中选择[复制]子菜单的[请求订阅]  
  (3)按照单击[下一步]操作直到系统会提示检查SQL   SERVER代理服务的运行状态，执行复制操作的前提条件是SQL   SERVER代理服务必须已经启动。  
 (4)单击[完成]。完成订阅操作。  
  完成上面的步骤其实复制也就是成功了。但是如何来知道复制是否成功了呢？  
  这里可以通过这种方法来快速看是否成功。  
  展开出版服务器下面的复制——发布内容——右键发布内容——属性——击活——状态然后点立即运行代理程序接着点代理程序属性击活调度  
  把调度设置为每一天发生，每一分钟，在0：00：00和23：59：59之间。  
  接下来就是判断复制是否成功了打  
  开C:\Program   Files\Microsoft   SQL   Server\MSSQL\REPLDATA\unc\XIAOWANGZI_database_database下面  
  看是不是有一些以时间做为文件名的文件夹差不多一分中就产生一个。  
  要是你还不信的话就打开你的数据库看在订阅的服务器的指定订阅数据库下看是不是看到了你刚才所发布的表  
   
   
  /*****************   常见问题：权限问题      
   
  启动代理的域用户账号必须具有读取发布-分发服务器目录的权限  
  你用的账号没有读取\\ServerA\D$\Program   Files\Microsoft   SQL   Server\MSSQL\repldata\unc\这个目录下的文件的权限。  
   
   
            启动代理的域用户账号必须具有读取发布-分发服务器目录的权限  
  ----^^^^^^^^^^^^^^^在控制面板-->管理-->服务-->sqlagent-->属性里的那个启动用户  
--如果你对你的库和SQL很熟可以自己写代码同步,下面是示例  
   
  --定时同步服务器上的数据(服务器上的数据修改同步到本地)  
   
  --例子:  
  --测试环境,SQL   Server2000,远程服务器名:rserver,用户名为:ruser,密码：rpwd,  
  测试数据库:test  
   
  --服务器上的表(查询分析器连接到服务器上创建)  
  --state字段为辅助更新而设置的附加字段,字段值说明:null   表示新增记录,1   表示修改过的记录,0   表示无变化的记录  
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[luser]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [luser]  
  GO  
  create   table   [luser](id   int   identity(1,1)   primary   key,[user]   varchar(4),pwd   varchar(10),address   varchar(50),state   bit)  
  go  
   
  --创建触发器,维护state字段的值  
  create   trigger   t_state   on   [luser]  
  after   update  
  as  
  update   [luser]   set   state=1  
  from   [luser]   a   join   inserted   b   on   a.id=b.id  
  where   a.state   is   not   null  
  go  
   
  ----以下在局域网(本机操作)  
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[luser]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [luser]  
  create   table   [luser](id   int   primary   key,[user]   varchar(4),pwd   varchar(10),address   varchar(50))  
  go  
   
  --为了方便同步处理,创建链接服务器到要同步的服务器  
  --这里的远程服务器名:rserver,用户名为:ruser,密码：rpwd  
  if   exists(select   1   from   master..sysservers   where   srvname='srv_lnk')  
  exec   sp_dropserver   'srv_lnk','droplogins'  
  go  
  exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','rserver'  
  exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'ruser','rpwd'  
go  
   
  --创建同步处理的存储过程  
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_synchro]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_synchro]  
  GO  
  create   proc   p_synchro  
  as  
  /*--因为MSDTC服务(分布式事务处理需要)工作不稳定,所以下面部分为选用内容  
  --set     XACT_ABORT   on  
  --启动远程服务器的MSDTC服务  
  --exec   master..xp_cmdshell   'isql   /S"rserver"   /U"ruser"   /P"rpwd"   /q"exec   master..xp_cmdshell   ''net   start   msdtc'',no_output"',no_output  
   
  --启动本机的MSDTC服务  
  --exec   master..xp_cmdshell   'net   start   msdtc',no_output  
   
  --进行分布事务处理,如果表用标识列做主键,用下面的方法  
  --BEGIN   DISTRIBUTED   TRANSACTION  
  --*/  
  --同步删除的数据  
  delete   from   [luser]  
  where   id   not   in(select   id   from   srv_lnk.test.dbo.[luser])  
   
  --同步新增的数据  
  insert   into   [luser]  
  select   id,[user],pwd,address   from   srv_lnk.test.dbo.[luser]  
  where   state   is   null  
   
  --同步修改的数据  
  update   [luser]   set  
  [user]=b.[user],pwd=b.pwd,address=b.address  
  from   [luser]   a  
  join   srv_lnk.test.dbo.[user]   b   on   a.id=b.id  
  where   b.state=1  
   
  --同步后更新服务器上的标志  
  update   srv_lnk.test.dbo.[luser]   set   state=0   where   isnull(state,1)=1  
  --COMMIT   TRAN --如果启用分布式事务处理,加上此句  
  go  
   
  --创建作业,定时执行数据同步的存储过程  
  if   exists(SELECT   1   from   msdb..sysjobs   where   name='数据处理')  
  EXECUTE   msdb.dbo.sp_delete_job   @job_name='数据处理'  
  exec   msdb..sp_add_job   @job_name='数据处理'  
   
  --创建作业步骤  
  declare   @sql   varchar(800),@dbname   varchar(250)  
  select   @sql='exec   p_synchro'     --数据处理的命令  
  ,@dbname=db_name() --执行数据处理的数据库名  
   
  exec   msdb..sp_add_jobstep   @job_name='数据处理',  
  @step_name   =   '数据同步',  
  @subsystem   =   'TSQL',  
  @database_name=@dbname,  
        @command   =   @sql,  
  @retry_attempts   =   5,   --重试次数  
  @retry_interval   =   5     --重试间隔  
   
  --创建调度  
  EXEC   msdb..sp_add_jobschedule   @job_name   =   '数据处理',    
  @name   =   '时间安排',  
  @freq_type=4,   --4   每天,8   每周,16   每月  
  @freq_interval=1, --作业执行的天数  
  @freq_subday_type=0, --是否重复执行,0x1   在指定的时间,   0x4   分钟,   0x8   小时    
  @freq_subday_interval=1,   --重复周期  
  @freq_recurrence_factor=0, --重复执行,则设置为1,否则设置为0  
  @active_start_time   =   00000 --0点开始执行  
   
  --添加目标服务器  
  EXEC   msdb.dbo.sp_add_jobserver    
  @job_name   =   @jobname   ,  
  @server_name   =   N'(local)'    
  go  
--上面是定时的,下面是即时的.  
   
  --用触发器即时同步两个表的实例:  
   
  --测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test  
   
  --创建测试表,不能用标识列做主键,因为不能进行正常更新  
  --在远程主机上建表  
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[test]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [test]  
   
  create   table   test(id   int   not   null   constraint   PK_test   primary   key  
  ,name   varchar(10))  
  go  
   
  --以下操作在本机进行  
  --在本机上建表及做同步处理的工作  
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[test]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1)  
  drop   table   [test]  
   
  create   table   test(id   int   identity(1,1)   primary   key  
  ,name   varchar(10))  
  go  
   
  --创建同步的触发器  
  create   trigger   t_test   on   test  
  for   insert,update,delete  
  as  
  set     XACT_ABORT   on  
  --启动远程服务器的MSDTC服务  
  exec   master..xp_cmdshell   'isql   /S"xz"   /U"sa"   /P""   /q"exec   master..xp_cmdshell   ''net   start   msdtc'',no_output"',no_output  
   
  --启动本机的MSDTC服务  
  exec   master..xp_cmdshell   'net   start   msdtc',no_output  
   
  --进行分布事务处理,如果表用标识列做主键,用下面的方法  
  BEGIN   DISTRIBUTED   TRANSACTION  
  delete   from   openrowset('sqloledb','xz';'sa';'',test.dbo.test)  
  where   id   in(select   id   from   deleted)  
  insert   into   openrowset('sqloledb','xz';'sa';'',test.dbo.test)  
  select   *   from   inserted  
  commit   tran  
  go  
   
  --插入数据测试  
  insert   into   test  
  select   1,'aa'  
  union   all   select   2,'bb'  
  union   all   select   3,'c'  
  union   all   select   4,'dd'  
  union   all   select   5,'ab'  
  union   all   select   6,'bc'  
  union   all   select   7,'ddd'  
   
  --删除数据测试  
  delete   from   test   where   id   in(1,4,6)  
   
  --更新数据测试  
  update   test   set   name=name+'_123'   where   id   in(3,5)  
   
  --显示测试的结果  
  select   *   from   test   a   full   join  
  openrowset('sqloledb','xz';'sa';'',test.dbo.test)   b   on   a.id=b.id