程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 同步兩個SQLServer數據庫的內容解決方法

同步兩個SQLServer數據庫的內容解決方法

編輯:關於SqlServer

1.發布服務器,訂閱服務器都創建一個同名的Windows用戶,並設置相同的密碼,做為發布快照文件夾的有效訪問用戶
  我的電腦
  控制面板
  管理工具
  計算機管理
  用戶和組
  右鍵用戶
  新建用戶
  建立一個隸屬於administrator組的登陸Windows的用戶

  2.在發布服務器上,新建一個共享目錄,做為發布的快照文件的存放目錄,操作:

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


  3.設置SQL代理(SQLSERVERAGENT)服務的啟動用戶(發布/訂閱服務器均做此設置)

  開始  程序  管理工具  服務
  右鍵SQLSERVERAGENT
  屬性  登陸  選擇"此賬戶"
  輸入或者選擇第一步中創建的Windows登錄用戶名
  "密碼"中輸入該用戶的密碼

  4.設置SQL Server身份驗證模式,解決連接時的權限問題(發布/訂閱服務器均做此設置)

  企業管理器
  右鍵SQL實例  屬性
  安全性  身份驗證
  選擇"SQL Server 和 Windows"
  確定

  5.在發布服務器和訂閱服務器上互相注冊
  企業管理器
  右鍵SQL Server組
  新建SQL Server注冊...
  下一步  可用的服務器中,輸入你要注冊的遠程服務器名  添加
  下一步  連接使用,選擇第二個"SQL Server身份驗證"
  下一步  輸入用戶名和密碼
  下一步  選擇SQL Server組,也可以創建一個新組
  下一步  完成

  6.對於只能用IP,不能用計算機名的,為其注冊服務器別名
  (在連接端配置,比如,在訂閱服務器上配置的話,服務器名稱中輸入的是發布服務器的IP)
  開始  程序  Microsoft SQL Server  客戶端網絡實用工具
  別名  添加
  網絡庫選擇"tcp/ip"  服務器別名輸入SQL服務器名
  連接參數  服務器名稱中輸入SQL服務器ip地址
  如果你修改了SQL的端口,取消選擇"動態決定端口",並輸入對應的端口號


  SQL數據庫教程同步復制

  一、建立發布和分發服務器

[歡迎使用配置發布和分發向導]->[選擇分發服務器]

->[使"@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日志讀取器在事務復制的時候是一直處於運行狀態。(在合並復制的時候可以根據調度的時間表來運行)

建立一個數據庫復制訂閱的過程:

[復制] -> [訂閱] -> 右鍵選擇 -> [新建請求訂閱]

-> [下一步] -> [查找發布] -> [查看已注冊服務器所做的發布]

-> [下一步] -> [選擇發布] -> [選中已經建立發布服務器上的數據庫發布名]

-> [下一步] -> [指定同步代理程序登錄] -> [當代理程序連接到代理服務器時:使用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 代理程序立即執行作業。

示例
下例啟動名為 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服務器上的數據庫備份到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服務的啟動帳戶與共享目錄的有效訪問同名且密碼相同,這樣才能通過驗證(所以你也可以用其他有效的用戶來代替,只需要滿足用戶名和密碼相同,並且擁有足夠的權限)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved