SQL SERVER發布與訂閱

1、發布與訂閱    
  用強制訂閱實現資料庫同步操作    
  大量和批量的資料可以用資料庫的同步機制處理:  
  //  
  說明：  
  發行伺服器(分發)操作,訂閱伺服器(訂閱)操作  
  在客戶機器使用強制訂閱者式。  
  測試通過  
  //  
  --1:環境  
  伺服器環境(  發布):  
  機器名稱：   devserver
  作業系統：Windows   2003   Server  
  資料庫版本：SQL   2005  Server   企業版  
  用戶端 (訂閱): 
  機器名稱：pkit-bingxu
  作業系統：Windows   2003   Server  
  資料庫版本：SQL   2005  Server   企業版  
  --2:建使用者帳號  
  在伺服器端建立域使用者帳號  
  我的電腦管理->本機使用者和組->使用者->建立    
  UserName:dbrepluser
  UserPwd:dbrepluser  
   隸屬於：SQLServer2005MSSQLUser$devserver$MSSQLSERVER
                   SQLServer2005MSSQLAgentUser$devserver$MSSQLSERVER

  --3:重新啟動伺服器MSSQLServer  
  我的電腦->控制面版->管理工具->服務->MSSQLServer   服務  
  (更改為：域使用者帳號,我們建立的dbrepluser使用者   .\dbrepluser,密碼:dbrepluser)   

  在發行伺服器和訂閱伺服器上互相註冊 
  企業管理器 
  --右鍵SQL  Server組 
  --建立SQL  Server註冊... 
  --下一步--可用的伺服器中,輸入你要註冊的遠程伺服器名 --添加 
  --下一步--串連使用,選擇第二個"SQL  Server身分識別驗證" 
  --下一步--輸入使用者名稱和密碼 
  --下一步--選擇SQL  Server組,也可以建立一個新組 
  --下一步--完成 

  --4:安裝發行伺服器  
  A:配置發行伺服器  
  複製->本地發布->建立和管理髮布->選擇要發布的資料庫(SZ)->下一步->快照發布->下一步->  
  選擇要發布的內容->下一步->下一步->下一步->完成  
  B:強制配置訂閱伺服器(請求模式,推模式與此雷同)  
  複製->本機訂閱->建立訂閱->訂閱伺服器->建立->SQL   Server資料庫->輸入用戶端伺服器名稱(ZLP)->使用SQL   Server   身分識別驗證(sa,空密碼)->確定->應用->確定  
  C:初始化訂閱  
  複製監視器->發行伺服器(ZEHUADB)->雙擊訂閱->強制建立->下一步->選擇啟用的訂閱伺服器->ZLP->  
  下一步->下一步->下一步->下一步->完成  
  --5:測試組態是否成功  
  複製監視器->發行伺服器(ZEHUADB)->雙擊SZ:SZ->點狀態->點立即運行代理程式  
  查看：  
  複製監視器->發行伺服器(ZEHUADB)->SZ:SZ->選擇ZLP:SZ(類型強制)->滑鼠右鍵->啟動同步處理  
  如果沒有錯誤標誌(紅色叉)，恭喜您配置成功  
  --6:測試資料  
  --在伺服器執行:  
  選擇一個表，執行如下SQL  
  insert   into   WQ_NEWSGROUP_S   select   '測試成功',5  
  複製監視器->發行伺服器(ZEHUADB)->SZ:SZ->快照->啟動代理程式  
                                                                              ->ZLP:SZ(強制)->啟動同步處理  
  去查看同步的   WQ_NEWSGROUP_S   是否插入了一條新的記錄  
  測試完畢，通過。  
  --7修改資料庫的同步時間,一般選擇夜晚執行資料庫同步處理  
  (具體操作略)   :D  
  /*  
  注意說明：  
  伺服器一端不能以(local)進行資料的發布與分發,需要先刪除註冊，然後建立註冊本機電腦名稱  
  特別注意：配製請求定閱的時候必須在訂閱伺服器上註冊發行伺服器
  卸載方式：工具->複製->禁止發布->是在"ZehuaDb"上靜止發布,卸載所有的資料庫同步設定管理員  
  注意：發行伺服器、散發者中的SQLServerAgent服務必須啟動  
              採用推模式:   "D:\Microsoft   SQL   Server\MSSQL\REPLDATA\unc"   目錄檔案可以不設定共用  
              請求模式:則需要共用~!  
  */  
  少量資料庫同步可以採用觸發器實現,同步單表即可：  
  ========================================================  
  ========================================================  
  在SQL   Server   2000裡設定和使用資料庫複寫之前，應先檢查相關的幾台SQL   Server伺服器下面幾點是否滿足：  
          1、MSSQLserver和Sqlserveragent服務是否是以域使用者身份啟動並啟動並執行（.\administrator使用者也是可以的）  
                  如果登入用的是本地系統帳戶local，將不具備網路功能，會產生以下錯誤:  
                  進程未能串連到Distributor   '@Server   name'    
              (如果您的伺服器已經用了SQL   Server全文檢索索引服務,   請不要修改MSSQLserver和Sqlserveragent服務的local啟動。  
                會照成全文檢索索引服務不能用。請換另外一台機器來做SQL   Server   2000裡複製中的散發者。)          
              修改服務啟動的登入使用者，需要重新啟動MSSQLserver和Sqlserveragent服務才會生效。          
          2、檢查相關的幾台SQL   Server伺服器是否改過名稱(需要srvid=0的本地機器上srvname和datasource一樣)  
                在查詢分析器裡執行:  
                    use   master  
                    select   srvid,srvname,datasource   from   sysservers          
                如果沒有srvid=0或者srvid=0（也就是本機器）但srvname和datasource不一樣,   需要按如下方法修改:  
                    USE   master  
                  GO  
                  --   設定兩個變數  
                  DECLARE   @serverproperty_servername     varchar(100),    
                                  @servername         varchar(100)  
                  --   取得Windows   NT   伺服器和與指定的   SQL   Server   執行個體關聯的執行個體資訊  
                  SELECT   @serverproperty_servername   =   CONVERT(varchar(100),   SERVERPROPERTY('ServerName'))  
                  --   返回運行   Microsoft   SQL   Server   的本機伺服器名稱  
                  SELECT   @servername   =   CONVERT(varchar(100),   @@SERVERNAME)  
                  --   顯示擷取的這兩個參數  
                  select   @serverproperty_servername,@servername  
                  --如果@serverproperty_servername和@servername不同(因為你改過電腦名稱字),再運行下面的  
                  --刪除錯誤的伺服器名  
                  EXEC   sp_dropserver   @server=@servername  
                  --添加正確的伺服器名  
                  EXEC   sp_addserver   @server=@serverproperty_servername,   @local='local'  
                修改這項參數，需要重新啟動MSSQLserver和Sqlserveragent服務才會生效。          
                這樣一來就不會在建立複製的過程中出現18482、18483錯誤了。  
          3、檢查SQL   Server企業管理器裡面相關的幾台SQL   Server註冊名是否和上面第二點裡介紹的srvname一樣  
                  不能用IP地址的註冊名。  
              （我們可以刪掉IP地址的註冊，建立以SQL   Server管理員層級的使用者註冊的伺服器名）  
                這樣一來就不會在建立複製的過程中出現14010、20084、18456、18482、18483錯誤了。          
          4、檢查相關的幾台SQL   Server伺服器網路是否能夠正常訪問  
                  如果ping主機IP地址可以，但ping主機名稱不通的時候，需要在          
                          winnt\system32\drivers\etc\hosts       (WIN2000)  
                          windows\system32\drivers\etc\hosts   (WIN2003)  
                                檔案裡寫入資料庫伺服器IP地址和主機名稱的對應關係。  
                        例如：          
                        127.0.0.1               localhost  
                        192.168.0.35         oracledb         oracledb  
                        192.168.0.65         fengyu02         fengyu02  
                        202.84.10.193       bj_db               bj_db          
                          或者在SQL   Server用戶端網路工具 + 生產力裡建立別名，例如:  
              5、系統需要的擴充預存程序是否存在(如果不存在，需要恢複):  
                    sp_addextendedproc   'xp_regenumvalues',@dllname   ='xpstar.dll'  
                    go  
                    sp_addextendedproc   'xp_regdeletevalue',@dllname   ='xpstar.dll'  
                    go  
                    sp_addextendedproc   'xp_regdeletekey',@dllname   ='xpstar.dll'    
                    go                              
                    sp_addextendedproc   xp_cmdshell   ,@dllname   ='xplog70.dll'      
   註：對發行伺服器增加新的表時，只要在發行伺服器上 啟用複製監控器->  右擊訂閱伺服器->從新初始化訂閱   

----------------------------------------------------------------------------------------------------

一個手工同步的方案

--定時同步伺服器上的資料
--例子:
--測試環境,SQL Server2000,遠程伺服器名:xz,使用者名稱為:sa,無密碼,測試資料庫:test
--伺服器上的表(查詢分析器串連到伺服器上建立)
create table [user](id int primary key,number varchar(4),name varchar(10))
go

--以下在區域網路(本機操作)
--原生表,state說明:null 表示新增記錄,1 表示修改過的記錄,0 表示無變化的記錄
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]
GO

create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
go

--建立觸發器,維護state欄位的值
create trigger t_state on [user]
after update
as
update [user] set state=1
from [user] a join inserted b on a.id=b.id
where a.state is not null
go

--為了方便同步處理,建立連結的伺服器到要同步的伺服器
--這裡的遠程伺服器名為:xz,使用者名稱為:sa,無密碼
if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
go

exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','xz'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa'
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
--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 srv_lnk.test.dbo.[user]
where id not in(select id from [user])

--同步新增的資料
insert into srv_lnk.test.dbo.[user]
select id,number,name from [user] where state is null

--同步修改的資料
update srv_lnk.test.dbo.[user] set
number=b.number,name=b.name
from srv_lnk.test.dbo.[user] a
join [user] b on a.id=b.id
where b.state=1

--同步後更新原生標誌
update [user] 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, --每天
@freq_interval = 1, --每天執行一次
@active_start_time = 00000 --0點執行
go

本文來自CSDN部落格，轉載請標明出處：file:///G:/彭華斌/學習/SQL%20SERVER發布與訂閱%20-%20bingtingabc的專欄%20-%20CSDN部落格.mht