同步兩個SQLServer數據庫

　　如何同步兩個SQLServer數據庫的內容?程序代碼可以有版本管理CVS進行同步管理可是數據庫同步就非常麻煩只能自己改了一個後再去改另一個如果忘記了更改另一個經常造成兩個數據庫的結構或內容上不一致各位有什麼好的方法嗎?

　　一分發與復制

　　用強制訂閱實現數據庫同步操作 大量和批量的數據可以用數據庫的同步機制處理:
//
說明
為方便操作所有操作均在發布服務器(分發服務器)上操作並使用推模式
在客戶機器使用強制訂閱方式

　　有疑問聯系作者zlp@hotmailcom

　　二測試通過

　　:環境

　　服務器環境:
　　機器名稱 ZehuaDb
　　操作系統Windows Server
　　數據庫版本SQL Server 個人版

　　客戶端
　　機器名稱Zlp
　　操作系統Windows Server
　　數據庫版本SQL Server 個人版

　　:建用戶帳號

　　在服務器端建立域用戶帳號
　　我的電腦管理>本地用戶和組>用戶>建立
UserName:zlp
UserPwd:zlp

　　:重新啟動服務器MSSQLServer

　　我的電腦>控制面版>管理工具>服務>MSSQLServer 服務
　　(更改為域用戶帳號我們新建的zlp用戶 \zlp密碼:zlp)

　　:安裝分發服務器

　　A:配置分發服務器
工具>復制>配置發布訂閱服務器和分發>下一步>下一步(所有的均采用默認配置)

　　B:配置發布服務器
工具>復制>創建和管理發布>選擇要發布的數據庫(SZ)>下一步>快照發布>下一步>選擇要發布的內容>下一步>下一步>下一步>完成

　　C:強制配置訂閱服務器(推模式拉模式與此雷同)
工具>復制>配置發布訂閱服務器和分發>訂閱服務器>新建>SQL Server數據庫>輸入客戶端服務器名稱(ZLP)>使用SQL Server 身份驗證(sa空密碼)>確定>應用>確定

　　D:初始化訂閱
復制監視器>發布服務器(ZEHUADB)>雙擊訂閱>強制新建>下一步>選擇啟用的訂閱服務器>ZLP>下一步>下一步>下一步>下一步>完成

　　:測試配置是否成功

　　復制監視器>發布服務器(ZEHUADB)>雙擊SZ:SZ>點狀態>點立即運行代理程序

　　查看

　　復制監視器>發布服務器(ZEHUADB)>SZ:SZ>選擇ZLP:SZ(類型強制)>鼠標右鍵>啟動同步處理

　　如果沒有錯誤標志(紅色叉)恭喜您配置成功

　　:測試數據

　　在服務器執行:

　　選擇一個表執行如下SQL
insert into WQ_NEWSGROUP_S select 測試成功

　　復制監視器>發布服務器(ZEHUADB)>SZ:SZ>快照>啟動代理程序 >ZLP:SZ(強制)>啟動同步處理

　　去查看同步的 WQ_NEWSGROUP_S 是否插入了一條新的記錄

　　測試完畢通過

　　:修改數據庫的同步時間一般選擇夜晚執行數據庫同步處理
(具體操作略) :D

/*
注意說明
服務器一端不能以(local)進行數據的發布與分發需要先刪除注冊然後新建注冊本地計算機名稱

卸載方式工具>復制>禁止發布>是在ZehuaDb上靜止發布卸載所有的數據庫同步配置服務器

注意發布服務器分發服務器中的SQLServerAgent服務必須啟動
      采用推模式: D:\Microsoft SQL Server\MSSQL\REPLDATA\unc 目錄文件可以不設置共享
      拉模式:則需要共享~!

*/

　　少量數據庫同步可以采用觸發器實現同步單表即可

　　三配置過程中可能出現的問題

　　在SQL Server 裡設置和使用數據庫復制之前應先檢查相關的幾台SQL Server服務器下面幾點是否滿足
   
　　MSSQLserver和Sqlserveragent服務是否是以域用戶身份啟動並運行的（\administrator用戶也是可以的）
   
        如果登錄用的是本地系統帳戶local將不具備網絡功能會產生以下錯誤:
       
        進程未能連接到Distributor @Server name
       
      (如果您的服務器已經用了SQL Server全文檢索服務 請不要修改MSSQLserver和Sqlserveragent服務的local啟動
       會照成全文檢索服務不能用請換另外一台機器來做SQL Server 裡復制中的分發服務器)   
       
      修改服務啟動的登錄用戶需要重新啟動MSSQLserver和Sqlserveragent服務才能生效    
       
　　檢查相關的幾台SQL Server服務器是否改過名稱(需要srvid=的本地機器上srvname和datasource一樣)
   
　　在查詢分析器裡執行:
         use master
         select srvidsrvnamedatasource from sysservers    
        
　　如果沒有srvid=或者srvid=（也就是本機器）但srvname和datasource不一樣 需要按如下方法修改:
        
         USE master
        GO
        設置兩個變量
        DECLARE @serverproperty_servername  varchar()
                @servername    varchar()
        取得Windows NT 服務器和與指定的 SQL Server 實例關聯的實例信息
        SELECT @serverproperty_servername = CONVERT(varchar() SERVERPROPERTY(ServerName))
        返回運行 Microsoft SQL Server 的本地服務器名稱
        SELECT @servername = CONVERT(varchar() @@SERVERNAME)
        顯示獲取的這兩個參數
        select @serverproperty_servername@servername
        如果@serverproperty_servername和@servername不同(因為你改過計算機名字)再運行下面的
        刪除錯誤的服務器名
        EXEC sp_dropserver @server=@servername
        添加正確的服務器名
        EXEC sp_addserver @server=@serverproperty_servername @local=local
 
       
　　修改這項參數需要重新啟動MSSQLserver和Sqlserveragent服務才能生效    
      
　　這樣一來就不會在創建復制的過程中出現錯誤了

　　檢查SQL Server企業管理器裡面相關的幾台SQL Server注冊名是否和上面第二點裡介紹的srvname一樣
   
　　不能用IP地址的注冊名
       
　　（我們可以刪掉IP地址的注冊新建以SQL Server管理員級別的用戶注冊的服務器名）
        
　　 這樣一來就不會在創建復制的過程中出現錯誤了    
       
　　檢查相關的幾台SQL Server服務器網絡是否能夠正常訪問
   
　　如果ping主機IP地址可以但ping主機名不通的時候需要在   
       
            winnt\system\drivers\etc\hosts   (WIN)
            windows\system\drivers\etc\hosts (WIN)
　　
　　文件裡寫入數據庫服務器IP地址和主機名的對應關系
              
           例如   
              
                  localhost
               oracledb    oracledb
               fengyu    fengyu
              bj_db       bj_db    

              
　　或者在SQL Server客戶端網絡實用工具裡建立別名例如:

　　系統需要的擴展存儲過程是否存在(如果不存在需要恢復):
     
          sp_addextendedproc xp_regenumvalues@dllname =xpstardll
         go
         sp_addextendedproc xp_regdeletevalue@dllname =xpstardll
         go
         sp_addextendedproc xp_regdeletekey@dllname =xpstardll
         go             
         sp_addextendedproc xp_cmdshell @dllname =xplogdll  
 
 
　　接下來就可以用SQL Server企業管理器裡[復制]> 右鍵選擇 >[配置發布訂閱服務器和分發]的圖形界面來配置數據庫復制了
   
　　下面是按順序列出配置復制的步驟:

　　建立發布和分發服務器
   
        [歡迎使用配置發布和分發向導]>[選擇分發服務器]>[使@servername成為它自己的分發服務器SQL Server將創建分發數據庫和日志]
 >[制定快照文件夾]> [自定義配置] > [否使用下列的默認配置] > [完成]
      
　　上述步驟完成後 會在當前@servername SQL Server數據庫裡建立了一個distribion庫和 一個distributor_admin管理員級別的用戶(我們可以任意修改密碼)
            
　　服務器上新增加了四個作業:

                [ 代理程序歷史記錄清除: distribution ]
                [ 分發清除: distribution ]
                [ 復制代理程序檢查 ]
                [ 重新初始化存在數據驗證失敗的訂閱 ]
   
　　SQL Server企業管理器裡多了一個復制監視器 當前的這台機器就可以發布分發訂閱了
   
　　我們再次在SQL Server企業管理器裡[復制]> 右鍵選擇 >[配置發布訂閱服務器和分發]
    
 　　我們可以在 [發布服務器和分發服務器的屬性] 窗口> [發布服務器] > [新增]   > [確定] > [發布數據庫] > [事務]/[合並] > [確定]  > [訂閱服務器] > [新增]  > [確定]  
     
　　把網絡上的其它SQL Server服務器添加成為發布或者訂閱服務器

　　新增一台發布服務器的選項    
        
　　我這裡新建立的JIN發布服務器是用管理員級別的數據庫用戶test連接的      

　　到發布服務器的管理鏈接要輸入密碼的可選框 默認的是選中的

　　在新建的JIN發布服務器上建立和分發服務器FENGYU/FENGYU的鏈接的時需要輸入distributor_admin用戶的密碼到發布服務器的管理鏈接要輸入密碼的可選框也可以不選也就是不需要密碼來建立發布到分發服務器的鏈接(這當然欠缺安全在測試環境下可以使用)
         

　　新建立的網絡上另一台發布服務器(例如JIN)選擇分發服務器
   
　　[歡迎使用配置發布和分發向導]>[選擇分發服務器]
   
     > 使用下列服務器(選定的服務器必須已配置為分發服務器) > [選定服務器](例如FENGYU/FENGYU)
    
     > [下一步] > [輸入分發服務器（例如FENGYU/FENGYU）的distributor_admin用戶的密碼兩次]
    
     > [下一步] > [自定義配置] > [否使用下列的默認配置]
    
     > [下一步] > [完成] > [確定]
    
　　建立一個數據庫復制發布的過程:
    
     [復制] > [發布內容] > 右鍵選擇 > [新建發布]
    
     > [下一步] > [選擇發布數據庫] > [選中一個待發布的數據庫]
           
     > [下一步] > [選擇發布類型] > [事務發布]/[合並發布]

     > [下一步] > [指定訂閱服務器的類型] > [運行SQL Server 的服務器]
       
     > [下一步] > [指定項目] > [在事務發布中只可以發布帶主鍵的表] > [選中一個有主鍵的待發布的表]
       
     >[在合並發布中會給表增加唯一性索引和 ROWGUIDCOL 屬性的唯一標識符字段[rowguid]默認值是newid()]
                
               (添加新列將: 導致不帶列列表的 INSERT 語句失敗增加表的大小增加生成第一個快照所要求的時間)
               
     >[選中一個待發布的表]
    
     > [下一步] > [選擇發布名稱和描述] >
    
     > [下一步] > [自定義發布的屬性] > [否根據指定方式創建發布]
    
     > [下一步] > [完成] > [關閉]
    
　　發布屬性裡有很多有用的選項設定訂閱到期(例如小時)
     
　　設定發布表的項目屬性:
    
　　常規窗口可以指定發布目的表的名稱可以跟原來的表名稱不一樣
    
　　下圖是命令和快照窗口的欄目   
 
　　( SQL Server 數據庫復制技術實際上是用insertupdatedelete操作在訂閱服務器上重做發布服務器上的事務操作
    
      看文檔資料需要把發布數據庫設成完全恢復模式事務才不會丟失
     
      但我自己在測試中發現發布數據庫是簡單恢復模式下每秒生成一些大事務分鐘後再收縮數據庫日志
          這期間發布和訂閱服務器上的作業都暫停暫停恢復後並沒有丟失任何事務更改 )
    
　　發布表可以做數據篩選例如只選擇表裡面的部分列:
    
　　例如只選擇表裡某些符合條件的記錄 我們可以手工編寫篩選的SQL語句:
    
　　發布表的訂閱選項並可以建立強制訂閱:
        
　　成功建立了發布以後發布服務器上新增加了一個作業: [ 失效訂閱清除 ]
   
　　分發服務器上新增加了兩個作業:
                [ JINdackdack ] 類型[ REPL快照 ]
                [ JINdack ]      類型[ REPL日志讀取器 ]

　　上面藍色字的名稱會根據發布服務器名發布名及第幾次發布而使用不同的編號
           
　　REPL快照作業是SQL Server復制的前提條件它會先把發布的表結構數據索引約束等生成到發布服務器的OS目錄下文件
    (當有訂閱的時候才會生成 當訂閱請求初始化或者按照某個時間表調度生成)

　　REPL日志讀取器在事務復制的時候是一直處於運行狀態(在合並復制的時候可以根據調度的時間表來運行)

　　建立一個數據庫復制訂閱的過程: 
     
     [復制] > [訂閱] > 右鍵選擇 > [新建請求訂閱]
    
     > [下一步] > [查找發布] > [查看已注冊服務器所做的發布]
           
     > [下一步] > [選擇發布] > [選中已經建立發布服務器上的數據庫發布名]     

     > [下一步] > [指定同步代理程序登錄] > [當代理程序連接到代理服務器時:使用SQL Server身份驗證]
                                               (輸入發布服務器上distributor_admin用戶名和密碼)     
                                              
     > [下一步] > [選擇目的數據庫] > [選擇在其中創建訂閱的數據庫名]/[也可以新建一個庫名]  
    
     > [下一步] > [允許匿名訂閱] > [是生成匿名訂閱]     
   
     > [下一步] > [初始化訂閱] > [是初始化架構和數據]     

     > [下一步] > [快照傳送] > [使用該發布的默認快照文件夾中的快照文件]
                    (訂閱服務器要能訪問發布服務器的REPLDATA文件夾如果有問題可以手工設置網絡共享及共享權限)     

     > [下一步] > [快照傳送] > [使用該發布的默認快照文件夾中的快照文件]     

     > [下一步] > [設置分發代理程序調度] > [使用下列調度] > [更改] > [例如每五分鐘調度一次]     

     > [下一步] > [啟動要求的服務] > [該訂閱要求在發布服務器上運行SQLServerAgent服務]     

     > [下一步] > [完成] > [確定]      
    
　　成功建立了訂閱後訂閱服務器上新增加了一個類別是[REPL分發]作業(合並復制的時候類別是[REPL合並])

　　它會按照我們給的時間調度表運行數據庫同步復制的作業

　　SQL Server復制配置好後 可能出現異常情況的實驗日志:

　　發布服務器斷網sql server服務關閉重啟動關機的時候對已經設置好的復制沒有多大影響

        中斷期間分發和訂閱都接收到沒有復制的事務信息

　　分發服務器斷網sql server服務關閉重啟動關機的時候對已經設置好的復制有一些影響

        中斷期間發布服務器的事務排隊堆積起來
           (如果設置了較長時間才刪除過期訂閱的選項 繁忙發布數據庫的事務日志可能會較快速膨脹)
       
        訂閱服務器會因為訪問不到發布服務器反復重試
            我們可以設置重試次數和重試的時間間隔(最大的重試次數是 如果每分鐘重試一次可以支持約天不出錯)

        分發服務器sql server服務啟動網絡接通以後發布服務器上的堆積作業將按時間順序作用到訂閱機器上:

        會需要一個比較長的時間(實際上是生成所有事務的insertupdatedelete語句在訂閱服務器上去執行)
        我們在普通的PC機上實驗的個事務個命令執行花了分秒

　　訂閱服務器斷網sql server服務關閉重啟動關機的時候對已經設置好的復制影響比較大可能需要重新初試化

        我們實驗環境(訂閱服務器)從:分意外停機以 第二天:分重啟動後 已經設好的復制在:分以後又開始正常運行了 發布服務器上的堆積作業將按時間順序作用到訂閱機器上 但復制管理器裡出現快照的錯誤提示 快照可能需要重新初試化復制可能需要重新啟動(我們實驗環境的機器並沒有進行快照初試化復制仍然是成功運行的)
   

　　刪除已經建好的發布和定閱可以直接用delete刪除按鈕
   
　　我們最好總是按先刪定閱再刪發布最後禁用發布的順序來操作
   
　　如果要徹底刪去SQL Server上面的復制設置 可以這樣操作:

    [復制] > 右鍵選擇 [禁用發布] > [歡迎使用禁用發布和分發向導]
 
     > [下一步] > [禁用發布] > [要在@servername上禁用發布]
           
     > [下一步] > [完成禁用發布和分發向導] > [完成]
    
   我們也可以用TSQL命令來完成復制中發布及訂閱的創建和刪除 選中已經設好的發布和訂閱 按屬標右鍵可以[生成SQL腳本](這裡就不詳細講了 後面推薦的網站內有比較詳細的內容)
  
   當你試圖刪除或者變更一個table時出現以下錯誤
   Server: Msg Level State Line
   Cannot drop the table object_name because it is being used for replication

   比較典型的情況是該table曾經用於復制但是後來又刪除了復制

   處理辦法
   select * from sysobjects where replinfo >
   sp_configure allow updates
   go
   reconfigure with override
   go
   begin transaction
   update sysobjects set replinfo = where replinfo >
   commit transaction
   go
   rollback transaction
   go
   sp_configure allow updates
   go
   reconfigure with override
   go
 

　　疑問:

　　在合並復制配置完全後如果同步代理停止了我要在程序中去重新啟動合並復制的同步代理請問使用什麼命令或存儲過程呢？
 
　　解決辦法:

sp_start_job
指示 SQL Server 代理程序立即執行作業

　　示例

下例啟動名為 Nightly Backup 的作業

USE msdb
EXEC sp_start_job @job_name = Nightly Backup

　　日志還原功能

/*說明
下面的代碼演示了如何利用日志還原功能將主數據庫中的數據變化及時反饋到備用數據庫中
備用數據庫的數據可以隨時用於查詢但不能被更新（備用數據庫只讀）
*/

　　首先創建一個演示用的數據庫(主數據庫)

CREATE DATABASE Db_test
ON
( NAME = Db_test_DATA
      FILENAME = c:\Db_testmdf )
LOG ON
( NAME = Db_test_LOG
   FILENAME = c:\Db_testldf)
GO

　　對數據庫進行備份

BACKUP DATABASE Db_test TO DISK=c:\test_databak WITH FORMAT
GO

　　把數據庫還原成備用數據庫(演示主數據庫與這個備用數據庫之間的同步)

RESTORE DATABASE Db_test_bak FROM DISK=c:\test_databak
WITH REPLACESTANDBY=c:\db_test_bakldf
MOVE Db_test_DATA TO c:\Db_test_datamdf
MOVE Db_test_LOG TO c:\Db_test_logldf
GO

　　啟動 SQL Agent 服務

EXEC masterxp_cmdshell net start sqlserveragentno_output
GO

　　創建主服務器數據訓與備用服務器數據庫之間同步的作業

DECLARE  @jogid uniqueidentifier
EXEC msdbsp_add_job
@job_id = @jogid OUTPUT
@job_name = N數據同步處理

　　創建同步處理步驟

EXEC msdbsp_add_jobstep
@job_id = @jogid
@step_name = N數據同步
@subsystem = TSQL
@command = N

　　主數據庫中進行日志備份

BACKUP LOG Db_test TO DISK=c:\test_logbak WITH FORMAT

　　備用數據庫中還原主數據庫的日志備份(應用主數據庫中的最新變化

　　實際應該時主數據庫備份與備用數據庫的還原作業應該分別在主服務器和備用服務器上建立並且備份文件應該放在主服務器和備用都能訪問的共享目錄中

RESTORE LOG Db_test_bak FROM DISK=c:\test_logbak WITH STANDBY=c:\test_logldf
@retry_attempts =
@retry_interval =

　　創建調度(每分鐘執行一次)

EXEC msdbsp_add_jobschedule
@job_id = @jogid
@name = N時間安排
@freq_type=
@freq_interval=
@freq_subday_type=x
@freq_subday_interval=
@freq_recurrence_factor=

　　添加目標服務器

EXEC msdbdbosp_add_jobserver
@job_id = @jogid
@server_name = N(local)
GO

　　通過上述處理主數據庫與備用數據庫之間的同步關系已經設置完成

　　下面開始測試是否能實現同步

　　在主數據庫中創建一個測試用的表

CREATE TABLE Db_testdboTB_test(ID int)
GO

　　等待分鐘秒（由於同步的時間間隔設置為分鐘所以要延時才能看到效果）

WAITFOR DELAY ::
GO

　　查詢一下備用數據庫看看同步是否成功

SELECT * FROM Db_test_bakdboTB_test

/*結果:
ID         

（所影響的行數為 行）
*/

　　測試成功

GO

　　最後刪除所有的測試

DROP DATABASE Db_testDb_test_bak
EXEC msdbsp_delete_job @job_name=N數據同步處理
GO

/*===========================================================*/

/*服務器檔機處理說明
使用這種方式建立的數據庫同步當主數據庫不可用時（例如主數據庫損壞或者停機檢修）
可以使用以下兩種方法使備用數據庫可用
*/
 

　　 如果主數據庫損壞無法備份出最新的日志可以直接使用下面的語句使備用數據庫可讀寫（丟失最近一次日志還原後的所有數據）
RESTORE LOG Db_test_bak WITH RECOVERY

　　 如果主數據庫可以備份出最新日志則可以使用下面的語句
先備份主數據庫的最新的事務日志
BACKUP LOG Db_test TO DISK=c:\test_logbak WITH FORMAT
再在備用數據庫中恢復最新的事務日志並且使備用數據庫可讀寫（升級為主數據庫）
RESTORE LOG Db_test_bak FROM DISK=c:\test_logbak

　　簡單地說:

　　 你的sql服務要使用指定的windows用戶登陸 而不能使用本地系統帳戶
　　 用於登陸sql服務的用戶要求對共享目錄具有所有權限
　　 如果你的電腦沒有加入到域 還必須保證源和目標服務器的sql服務設置的登陸用戶是一樣的(用戶名和密碼都一樣)

　　網絡備份主要是權限設置問題 參考下面的備份文件共享目錄權限設置方法去解決目錄的共享權限就可以了

　　下面假設是假設A服務器上的數據庫備份到B服務器上的共享目錄權限設置(兩台服務器應該在局域網內允許目錄共享訪問)::

　　機器AB創建一個同名的windows用戶用戶組設置為administrators並設置相同的密碼做為備份文件夾文件夾的有效訪問用戶操作:

　　我的電腦控制面板管理工具計算機管理用戶和組右鍵用戶新建用戶建立一個隸屬於administrator組的登陸windows的用戶

　　在B機器器上新建一個共享目錄做為備份文件的存放目錄操作:

　　我的電腦D:\ 新建一個目錄名為: BAK右鍵這個新建的目錄屬性共享選擇共享該文件夾通過權限按紐來設置具體的用戶權限保證第一步中創建的用戶具有對該文件夾的所有權限確定

　　設置 MSSQLSERVER 及 SQLSERVERAGENT 服務的啟動用戶
　　
　　開始程序管理工具服務右鍵 MSSQLSERVER屬性登陸選擇此賬戶輸入或者選擇第一步中創建的windows登錄用戶名密碼中輸入該用戶的密碼確定同樣的方法設置 SQLSERVERAGENT

　　在A機器上完成對B機器BAK目錄的映射

　　查詢分析器中執行下面的語句檢驗是否成功:

exec masterxp_cmdshell dir 映射的盤符

　　A服務器上做備份計劃

　　備注:創建一個新的用戶只是為了讓MSSQLSERVER服務的啟動帳戶與共享目錄的有效訪問同名且密碼相同這樣才能通過驗證(所以你也可以用其他有效的用戶來代替只需要滿足用戶名和密碼相同並且擁有足夠的權限)

From:http://tw.wingwit.com/Article/program/SQL/201311/16428.html