兩個sql server資料庫同步資料-按順序列出配置複製的步驟

一、建立發布和散發者
   
        [歡迎使用配置發布和分發嚮導]->[選擇散發者]
   
       ->[使"@servername"成為它自己的散發者,SQL Server將建立散發資料庫和日誌]
      
       ->[制定快照檔案夾]-> [自訂配置] -> [否,使用下列的預設配置] -> [完成]
      
    上述步驟完成後, 會在當前"@servername" SQL Server資料庫裡建立了一個distribion庫和
   
            一個distributor_admin管理員層級的使用者(我們可以任意修改密碼)
           
    伺服器上新增加了四個作業:
                [ 代理程式記錄清除: distribution ]
                [ 分發清除: distribution ]
                [ 複製代理程式檢查 ]
                [ 重新初始化存在資料驗證失敗的訂閱 ]
   
    SQL Server企業管理器裡多了一個複製監視器, 當前的這台機器就發行就緒、分發、訂閱了。
   
    我們再次在SQL Server企業管理器裡[複製]-> 右鍵選擇
        ->[配置發布、訂閱伺服器和分發],可以看到類似:
   
 
   
    我們可以在 [發行伺服器和散發者的屬性] 視窗
        -> [發行伺服器] -> [新增]        -> [確定]
        -> [發行集資料庫] -> [事務]/[合并] -> [確定]
        -> [訂閱伺服器] -> [新增]        -> [確定]       
       把網路上的其它SQL Server伺服器添加成為發布或者訂閱伺服器.
       新增一台發行伺服器的選項：   
        
    我這裡建立立的JIN001發行伺服器是用管理員層級的資料庫使用者test串連的，      
    到發行伺服器的管理連結要輸入密碼的可選框, 預設的是選中的，
    在建立的JIN001發行伺服器上建立和散發者FENGYU/FENGYU的連結的時需要輸入distributor_admin使用者的密碼
         到發行伺服器的管理連結要輸入密碼的可選框，也可以不選，
         也就是不要求輸入密碼來建立發布到散發者的連結(這當然欠缺安全，在測試環境下可以使用)
        
    二、建立立的網路上另一台發行伺服器(例如JIN001)選擇散發者
   
    [歡迎使用配置發布和分發嚮導]->[選擇散發者]
   
     -> 使用下列伺服器(選定的伺服器必須已配置為散發者) -> [選定伺服器](例如FENGYU/FENGYU)
    
     -> [下一步] -> [輸入散發者（例如FENGYU/FENGYU）的distributor_admin使用者的密碼兩次]
    
     -> [下一步] -> [自訂配置] -> [否，使用下列的預設配置]
    
     -> [下一步] -> [完成] -> [確定]
    
     建立一個資料庫複寫發布的過程:
    
     [複製] -> [發布內容] -> 右鍵選擇 -> [建立發布]
    
     -> [下一步] -> [選擇發行集資料庫] -> [選中一個待發布的資料庫]
           
     -> [下一步] -> [選擇發布類型] -> [事務發布]/[合并發布]

     -> [下一步] -> [指定訂閱伺服器的類型] -> [運行SQL Server 2000的伺服器]
       
     -> [下一步] -> [指定項目] -> [在事務發布中只發行就緒帶主鍵的表] -> [選中一個有主鍵的待發布的表]
      
         ->[在合并發布中會給表增加唯一性索引和 ROWGUIDCOL 屬性的唯一識別碼欄位[rowguid],預設值是newid()]
                
               (添加新列將: 導致不帶列列表的 INSERT 語句失敗,增加表的大小,增加產生第一個快照所要求的時間)
              
         ->[選中一個待發布的表]
    
     -> [下一步] -> [選擇發布名稱和描述] ->
    
     -> [下一步] -> [自訂發布的屬性] -> [否，根據指定方式建立發布]
    
     -> [下一步] -> [完成] -> [關閉]
    
     發布屬性裡有很多有用的選項：設定訂閱到期(例如24小時)
     
         設定發行資料表的項目屬性:
    
     常規視窗可以指定發布目的表的名稱，可以跟原來的表名稱不一樣。
    
     是命令和快照集視窗的欄目  
 
    ( SQL Server 資料庫複寫技術實際上是用insert,update,delete操作在訂閱伺服器上重做發行伺服器上的事務操作
    
      看文檔資料需要把發行集資料庫設成完全復原模式，事務才不會丟失
     
      但我自己在測試中發現發行集資料庫是簡單復原模式下，每10秒產生一些大事務，10分鐘後再收縮資料庫日誌，
          這期間發布和訂閱伺服器上的作業都暫停，暫停恢複後並沒有丟失任何事務更改 )
    
     發行資料表可以做資料篩選，例如只選擇表裡面的部分列:
    
    例如只選擇表裡某些合格記錄, 我們可以手工編寫篩選的SQL語句:
    
    發行資料表的訂閱選項，並可以建立強制訂閱:
       
       
    成功建立了發布以後,發行伺服器上新增加了一個作業: [ 失效訂閱清除 ]
   
    散發者上新增加了兩個作業:
                [ JIN001-dack-dack-5 ] 類型[ REPL快照 ]
                [ JIN001-dack-3 ]      類型[ REPL日誌讀取器 ]

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

REPL日誌讀取器在事務複製的時候是一直處於運行狀態。(在合併式複寫的時候可以根據調度的時間表來運行)

      建立一個資料庫複寫訂閱的過程: 
     
     [複製] -> [訂閱] -> 右鍵選擇 -> [建立提取訂閱]
    
     -> [下一步] -> [尋找發布] -> [查看登入伺服器所做的發布]
           
     -> [下一步] -> [選擇發布] -> [選中已經建立發行伺服器上的資料庫發布名]     

     -> [下一步] -> [指定同步代理程式登入] -> [當代理程式串連到Proxy 伺服器時:使用SQL Server身分識別驗證]
                                               (輸入發行伺服器上distributor_admin使用者名稱和密碼)     
                                              
     -> [下一步] -> [選擇目的資料庫] -> [選擇在其中建立訂閱的資料庫名]/[也可以建立一個庫名]  
    
     -> [下一步] -> [允許匿名訂閱] -> [是，產生匿名訂閱]     
   
     -> [下一步] -> [初始化訂閱] -> [是，初始化架構和資料]     

     -> [下一步] -> [快照傳送] -> [使用該發布的預設快照檔案夾中的快照檔案]
                    (訂閱伺服器要能訪問發行伺服器的REPLDATA檔案夾，如果有問題，可以手工設定網際網路共用及共用許可權)     

     -> [下一步] -> [快照傳送] -> [使用該發布的預設快照檔案夾中的快照檔案]     

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

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

     -> [下一步] -> [完成] -> [確定]     
    
    成功建立了訂閱後，訂閱伺服器上新增加了一個類別是[REPL-分發]作業(合併式複寫的時候類別是[REPL-合并])
    它會按照我們給的時間調度表運行資料庫複製的作業
   
    三、SQL Server複製配置好後, 可能出現異常情況的實驗日誌:

    1.發行伺服器斷網,sql server服務關閉,重啟動,關機的時候,對已經設定好的複製沒有多大影響

        中斷期間,分發和訂閱都接收到沒有複製的事務資訊

    2.散發者斷網,sql server服務關閉,重啟動,關機的時候,對已經設定好的複製有一些影響

        中斷期間,發行伺服器的事務排隊堆積起來
           (如果設定了較長時間才刪除到期訂閱的選項, 繁忙發行集資料庫的交易記錄可能會較快速膨脹),
       
        訂閱伺服器會因為訪問不到發行伺服器,反覆重試
            我們可以設定重試次數和重試的時間間隔(最大的重試次數是9999, 如果每分鐘重試一次,可以支援約6.9天不出錯)

        散發者sql server服務啟動,網路接通以後,發行伺服器上的堆積作業將按時間順序作用到訂閱機器上:

        會需要一個比較長的時間(實際上是產生所有事務的insert,update,delete語句,在訂閱伺服器上去執行)
        我們在普通的PC機上實驗的58個事務100228個命令執行花了7分28秒.

    3.訂閱伺服器斷網,sql server服務關閉,重啟動,關機的時候,對已經設定好的複製影響比較大,可能需要重新初試化

        我們實驗環境(訂閱伺服器)從18:46分意外停機以, 第二天8:40分重啟動後,
            已經設好的複製在8:40分以後又開始正常運行了, 發行伺服器上的堆積作業將按時間順序作用到訂閱機器上
            但複製管理器裡出現快照的錯誤提示, 快照可能需要重新初試化,複製可能需要重新啟動.
            (我們實驗環境的機器並沒有進行快照初試化,複製仍然是成功啟動並執行)
   
    四、刪除已經建好的發布和定閱可以直接用delete刪除按鈕
   
    我們最好總是按先刪定閱，再刪發布，最後禁用發布的順序來操作。
   
    如果要徹底刪去SQL Server上面的複製設定, 可以這樣操作:

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

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

   處理辦法：

select * from sysobjects where replinfo >'0'    sp_configure 'allow updates', 1    go    reconfigure with override    go    begin transaction    update sysobjects set replinfo = '0' where replinfo >'0'    commit transaction    go    rollback transaction    go    sp_configure 'allow updates', 0    go    reconfigure with override    go

   疑問:
    在合併式複寫配置完全後，如果同步代理停止了。我要在程式中去重新啟動合併式複寫的同步代理。請問使用什麼命令或預存程序呢？
    解決辦法:
sp_start_job
指示 SQL Server Agent程式立即執行作業。

樣本
下例啟動名為 Nightly Backup 的作業。

USE msdb
EXEC sp_start_job @job_name = 'Nightly Backup'

2、日誌還原功能、、、

/*--說明： 下面的代碼示範了如何利用日誌還原功能，將主要資料庫中的資料變化及時反饋到備用資料庫中 備用資料庫的資料可以隨時用於查詢，但不能被更新（備用資料庫唯讀）。 --*/ --首先,建立一個示範用的資料庫(主要資料庫) CREATE DATABASE Db_test ON ( NAME = Db_test_DATA,       FILENAME = 'c:\Db_test.mdf' ) LOG ON ( NAME = Db_test_LOG,    FILENAME = 'c:\Db_test.ldf') GO --對資料庫進行備份 BACKUP DATABASE Db_test TO DISK='c:\test_data.bak' WITH FORMAT GO --把資料庫還原成備用資料庫(示範主要資料庫與這個備用資料庫之間的同步) RESTORE DATABASE Db_test_bak FROM DISK='c:\test_data.bak' WITH REPLACE,STANDBY='c:\db_test_bak.ldf' ,MOVE 'Db_test_DATA' TO 'c:\Db_test_data.mdf' ,MOVE 'Db_test_LOG' TO 'c:\Db_test_log.ldf' GO --啟動 SQL Agent 服務 EXEC master..xp_cmdshell 'net start sqlserveragent',no_output GO --建立主伺服器資料訓與待命伺服器資料庫之間同步的作業 DECLARE  @jogid uniqueidentifier EXEC msdb..sp_add_job @job_id = @jogid OUTPUT, @job_name = N'資料同步處理' --建立同步處理步驟 EXEC msdb..sp_add_jobstep @job_id = @jogid, @step_name = N'資料同步', @subsystem = 'TSQL', @command = N' --主要資料庫中進行記錄備份 BACKUP LOG Db_test TO DISK=''c:\test_log.bak'' WITH FORMAT --備用資料庫中還原主要資料庫的記錄備份(應用主要資料庫中的最新變化 --實際應該時主要資料庫備份與備用資料庫的還原作業應該分別在主伺服器和待命伺服器上建立，並且備份檔案應該放在主伺服器和備用都能訪問的共用目錄中 RESTORE LOG Db_test_bak FROM DISK=''c:\test_log.bak'' WITH STANDBY=''c:\test_log.ldf''', @retry_attempts = 5, @retry_interval = 5 --建立調度(每分鐘執行一次) EXEC msdb..sp_add_jobschedule @job_id = @jogid, @name = N'時間安排', @freq_type=4, @freq_interval=1, @freq_subday_type=0x4, @freq_subday_interval=1, @freq_recurrence_factor=1 -- 添加目標伺服器 EXEC msdb.dbo.sp_add_jobserver @job_id = @jogid, @server_name = N'(local)' GO --通過上述處理,主要資料庫與備用資料庫之間的同步關係已經設定完成 --下面開始測試是否能實現同步 --在主要資料庫中建立一個測試用的表 CREATE TABLE Db_test.dbo.TB_test(ID int) GO --等待1分鐘30秒（由於同步的時間間隔設定為1分鐘,所以要延時才能看到效果） WAITFOR DELAY '00:01:30' GO --查詢一下備用資料庫,看看同步是否成功 SELECT * FROM Db_test_bak.dbo.TB_test /*--結果: ID          ----------- （所影響的行數為 0 行） --*/ --測試成功 GO --最後刪除所有的測試 DROP DATABASE Db_test,Db_test_bak EXEC msdb..sp_delete_job @job_name=N'資料同步處理' GO /*===========================================================*/ /*--伺服器檔機處理說明 使用這種方式建立的資料庫同步，當主要資料庫不可用時（例如，主要資料庫損壞或者停機檢修） 可以使用以下兩種方法使備用資料庫可用。 --*/

--1. 如果主要資料庫損壞，無法備份出最新的日誌，可以直接使用下面的語句使備用資料庫可讀寫（丟失最近一次日誌還原後的所有資料）。
--RESTORE LOG Db_test_bak WITH RECOVERY

--2. 如果主要資料庫可以備份出最新日誌，則可以使用下面的語句。
--先備份主要資料庫的最新的交易記錄
--BACKUP LOG Db_test TO DISK=''c:\test_log.bak'' WITH FORMAT
--再在備用資料庫中恢複最新的交易記錄，並且使備用資料庫可讀寫（升級為主要資料庫）
--RESTORE LOG Db_test_bak FROM DISK='c:\test_log.bak'

簡單地說:
1. 你的sql服務要使用指定的windows使用者登陸, 而不能使用"本地系統帳戶"
2. 用於登陸sql服務的使用者要求對共用目錄具有所有許可權
3. 如果你的電腦沒有加入到域, 還必須保證源和目標伺服器的sql服務設定的登陸使用者是一樣的(使用者名稱和密碼都一樣)

網路備份主要是使用權限設定問題, 參考下面的備份檔案共用目錄使用權限設定方法去解決目錄的共用許可權就可以了

下面假設是假設A伺服器上的Database Backup到B伺服器上的共用目錄使用權限設定(兩台伺服器應該在區域網路內,允許目錄共用訪問)::

1.機器A,B建立一個同名的windows使用者,使用者組設定為administrators,並設定相同的密碼,做為備份檔案夾檔案夾的有效訪問使用者,操作:
我的電腦
--控制台
--管理工具
--電腦管理
--使用者和組
--右鍵使用者
--建立使用者
--建立一個隸屬於administrator組的登陸windows的使用者

2.在B機器器上,建立一個共用目錄,做為備份檔案的存放目錄,操作:
我的電腦--D:\ 建立一個目錄,名為: BAK
--右鍵這個建立的目錄
--屬性--共用
--選擇"共用該檔案夾"
--通過"許可權"按紐來設定具體的使用者權限,保證第一步中建立的使用者具有對該檔案夾的所有許可權
--確定

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

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

5.查詢分析器中執行下面的語句,檢驗是否成功:
  exec master..xp_cmdshell 'dir 映射的盤符'

6.A伺服器上做備份計劃

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