如轉載，請注明出處： http://blog.csdn.net/robinson_0612/archive/2009/11/04/4769060.aspx

?

??? SQL server 2005高可用性之數據庫鏡像，是SQL server 2005的新技術之一，是一種基于軟件的高可用性解決方案，可以對不同服務器或同一服務器不同實例之間的數據庫實驗無數據延遲，自動故障轉移的熱備份。數據庫鏡像是基于數據庫級別的，只適用于使用完整恢復模式的數據庫。

?

??? 一、實驗目的：掌握SQL server 2005數據庫鏡像原理并配置數據庫鏡像、監控鏡像狀態及實現故障轉移。

?

??? 二、數據庫鏡像的組成

????????? 數據庫鏡像由二個數據庫必須的數據庫角色組成，一個是主體服務器角色，一個是鏡像服務器角色。還有一個可選的服務器角色為見證服務器角色。

???????? 1. 主體服務器(Principal Role)之主體數據庫，主體數據庫提供客戶端應用程序的連接，查詢，更新，執行相關事務等，主體數據庫要求使用完全恢復模式。

???????? 2. 鏡像服務器(Mirror Role)之鏡像數據庫，鏡像數據庫持續同步來自主體數據庫的事務，使得鏡像數據庫的數據與主體數據庫保持一致。鏡像數據庫不允許任何的連接存在，但可以對其創建數據庫快照來作為只讀數據庫，實現用戶的相關查詢操作。

??????? ?3. 見證服務器(Witness Server)，可選的配置，用于高可用性操作模式，通過見證服務器自動偵測故障，實現角色切換和故障轉移。一個見證服務器可以為多組鏡像提供服務。

???????? 4. 角色的轉換。主體數據庫與鏡像數據庫互為伙伴，當見證服務器偵測到主體服務器故障時，在高可用性模式下，實現故障自動轉移后，會自動將主體服務器切換為鏡像服務器角色，即角色發生了互換。

?

?? 三、數據庫鏡像的工作過程

???????? 1. 主體數據庫提供服務，當有來自客戶端對主體數據庫的更新時，主體數據庫將數據寫入主體數據庫的同時也將事務傳送給鏡像數據庫。

???????? 2. 鏡像數據庫Redo來自主體數據庫的事務，Redo完畢后，并發送消息通知主體服務器。

???????? 3. 主體服務器收到來自鏡像服務器中鏡像數據寫入完畢的消息后，將完成結果反饋給客戶端。

?

??? 四、端點的作用

????????? SQL server 2005提供了多層次多級別的安全模式，連接端點便是安全中第一個層次級別，為實例級別，它控制著能否連接到實例。數據庫鏡像是三個實例級別的會話，故必須通過創建端點來實現互相通信。

???????? SQL server 2005可以創建兩種類型的端點，一個是HTTP端點，一個是TCP端點。我們可以創建TSQL, SERVICE_BROKER, 或 DATABASE_MIRRORING類型的TCP端點。

???????? 端點上安全分為三個層次，一是需要創建所需類型的端點，但該端點并不能提供服務。二是在創建的端點上指定端口號，并指定IP地址，數據庫缺省的端口號為5022。三是對已創建并指定IP及端口號采用基于Windows身份認證或數字證書的加密功能加強安全。四是端點的狀態必須為啟動狀態，才能夠提供服務，如果端點在停止狀態，對任意的連接，將給出錯誤提示。五是對于已建立的會話必須擁有端點的connect連接權限。

?

??? 五、數據庫鏡像的操作模式

????????? 數據庫鏡像可以使用三種不同的操作模式，高可用性、高級別保護、高性能模式。在鏡像會話期間，故障發生時，不同的操作模式對應著不同的事務轉換方式。

???????? 1. 高可用性:在鏡像正常會話期間，主體服務器和鏡像服務器之間能夠持續，同步的傳送事物。主體服務器中主體數據庫發送日志后等待鏡像服務器中的鏡像數據庫確認，確認完畢后再反饋給應用程序。高可用性模式需要使用見證服務器，參與會話的主體和鏡像實例之間不停的發送ping命令來偵測對方的狀態，見證服務器則偵測主體和鏡像兩者的狀態。一旦偵測到故障發生，則主體或鏡像提交請求到見證服務器，由見證服務器來仲裁角色的轉換。高可用性的使用場景為要求提供高服務質量、能夠自動實現故障轉移、保證數據完整的場合。

???????? 2. 高級別保護: 此模式沒有見證服務器，主體服務器和鏡像服務器之間同樣能夠持續，同步的傳送事物。但由于少了見證服務器進行仲裁，則主體和鏡像數據庫之間不能夠實現故障的自動轉移，需要手動來實現角色之間的切換。高級別保護模式的使用場景多為高數據完整性要求、無須實現故障自動轉移、對服務可用性要求相對較低的場合。

??????? 3. 高性能: 此模式沒有見證服務器，主體服務器和鏡像服務器之間采用異步傳送模式。主體服務器上的事務直接提交后通知應用程序，無須等待鏡像服務器的確認，所主體數據庫和鏡像數據庫之間有延遲的現象存在。沒有了見證服務器進行仲裁，主體和鏡像數據庫之間不能夠實現故障的自動轉移，需要手動來實現角色之間的切換。高性能模式多使用于對性能要求高、主體鏡像服務器相對較遠、允許有延遲現象的場合。

???????? 4. 事務安全性的說明:數據庫鏡像會話中數據庫的安全性可以設定為Full或Off。Full模式的特性為主體和鏡像數據庫實現同步傳輸，主體發送日志后需要等待鏡像數據庫的確認，主體數據庫和鏡像數據庫的日志完全一致。Off模式則表現為主體和鏡像使用的異步傳輸模式，主體發送日志后無須等待鏡像數據庫的確認，主體數據庫失敗時，鏡像服務器上可能會丟失部分日志，使得兩者不能實時同步。

???????? 5. 仲裁: 仲裁用于設定了見證服務器的鏡像會話，用于高可用性模式。仲裁要求必須有兩個或兩個以上的服務器實例，且任一時間內必須要有一個伙伴為數據庫提供服務，當故障發生時，仲裁決定故障的轉移。

???????? 6. 幾種數據庫鏡像模式的比較，如下:???????????????????

?	操作模式	傳輸機制	事務安全	見證服務器	是否要仲裁	故障轉移類型
?	高可用性	同步	Full	Y	Y	自動或手動
?	高級別保護	同步	Full	N	Y	僅手動
?	高性能	異步	Off	N/A	N	僅強制

?

??? 六、數據庫鏡像所需的環境

???????? 1. 支持數據庫鏡像所需的版本，確保主體服務器和鏡像服務器使用相同的版本，如兩個伙伴運行SQL server 2005標準版或SQL server 2005運行企業版，安裝sp2以上補丁，否則需要使用跟蹤標記1400來實現。

???????? 2. 一個主體服務器，一個鏡像服務器，一個可選的見證服務器，見證服務器可以使用任意版本的SQL server 2005。

???????? 3. 主體服務器的主體數據庫設置為 FULL恢復模式。?????

?

??? 七、本次實驗的環境

???????? 1. windows xp pro (英文版) + sp2

???????? 2. SQL server 2005 Developer + sp3

???????? 3. 同一主機的三個實例： ROBINSON ， ROBINSON/MIRROR，ROBINSON/WITNESS

???????? 4. 用于實現鏡像的數據庫為Performance，此Performance數據庫為SQL server 2005技術內幕：T-SQL查詢中的腳本生成，現轉其腳本如下，此數據生成后大小為1GB左右，主要是日志文件較大，可以修改@max和@numorders的值來縮小數據庫，也可以停止MSSQLSERVER服務后刪除日志文件，使用sp_attach_single_file_db來重新生成較小日志文件。

SET NOCOUNT ON;
USE master;
GO
IF DB_ID('Performance') IS NULL
? CREATE DATABASE Performance;
GO
USE Performance;
GO

-- Creating and Populating the Nums Auxiliary Table
IF OBJECT_ID('dbo.Nums') IS NOT NULL
? DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
? INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
? SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
? SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

-- Drop Data Tables if Exist
IF OBJECT_ID('dbo.Orders') IS NOT NULL
? DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers') IS NOT NULL
? DROP TABLE dbo.Customers;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
? DROP TABLE dbo.Employees;
GO
IF OBJECT_ID('dbo.Shippers') IS NOT NULL
? DROP TABLE dbo.Shippers;
GO

-- Data Distribution Settings
DECLARE
? @numorders?? AS INT,
? @numcusts??? AS INT,
? @numemps???? AS INT,
? @numshippers AS INT,
? @numyears??? AS INT,
? @startdate?? AS DATETIME;

SELECT
? @numorders?? =?? 1000000,
? @numcusts??? =???? 20000,
? @numemps???? =?????? 500,
? @numshippers =???????? 5,
? @numyears??? =???????? 4,
? @startdate?? = '20030101';

-- Creating and Populating the Customers Table
CREATE TABLE dbo.Customers
(
? custid?? CHAR(11)???? NOT NULL,
? custname NVARCHAR(50) NOT NULL
);

INSERT INTO dbo.Customers(custid, custname)
? SELECT
??? 'C' + RIGHT('000000000' + CAST(n AS VARCHAR(10)), 10) AS custid,
??? N'Cust_' + CAST(n AS VARCHAR(10)) AS custname
? FROM dbo.Nums
? WHERE n <= @numcusts;

ALTER TABLE dbo.Customers ADD
? CONSTRAINT PK_Customers PRIMARY KEY(custid);

-- Creating and Populating the Employees Table
CREATE TABLE dbo.Employees
(
? empid???? INT????????? NOT NULL,
? firstname NVARCHAR(25) NOT NULL,
? lastname? NVARCHAR(25) NOT NULL
);

INSERT INTO dbo.Employees(empid, firstname, lastname)
? SELECT n AS empid,
??? N'Fname_' + CAST(n AS NVARCHAR(10)) AS firstname,
??? N'Lname_' + CAST(n AS NVARCHAR(10)) AS lastname
? FROM dbo.Nums
? WHERE n <= @numemps;

ALTER TABLE dbo.Employees ADD
? CONSTRAINT PK_Employees PRIMARY KEY(empid);

-- Creating and Populating the Shippers Table
CREATE TABLE dbo.Shippers
(
? shipperid?? VARCHAR(5)?? NOT NULL,
? shippername NVARCHAR(50) NOT NULL
);
INSERT INTO dbo.Shippers(shipperid, shippername)
? SELECT shipperid, N'Shipper_' + shipperid AS shippername
? FROM (SELECT CHAR(ASCII('A') - 2 + 2 * n) AS shipperid
??????? FROM dbo.Nums
??????? WHERE n <= @numshippers) AS D;

ALTER TABLE dbo.Shippers ADD
? CONSTRAINT PK_Shippers PRIMARY KEY(shipperid);

-- Creating and Populating the Orders Table
CREATE TABLE dbo.Orders
(
? orderid?? INT??????? NOT NULL,
? custid??? CHAR(11)?? NOT NULL,
? empid???? INT??????? NOT NULL,
? shipperid VARCHAR(5) NOT NULL,
? orderdate DATETIME?? NOT NULL,
? filler??? CHAR(155)? NOT NULL DEFAULT('a')
);

INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
? SELECT n AS orderid,
??? 'C' + RIGHT('000000000'
??????????? + CAST(
??????????????? 1 + ABS(CHECKSUM(NEWID())) % @numcusts
??????????????? AS VARCHAR(10)), 10) AS custid,
??? 1 + ABS(CHECKSUM(NEWID())) % @numemps AS empid,
??? CHAR(ASCII('A') - 2
?????????? + 2 * (1 + ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid,
????? DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate)
??????? -- late arrival with earlier date
??????? - CASE WHEN n % 10 = 0
??????????? THEN 1 + ABS(CHECKSUM(NEWID())) % 30
??????????? ELSE 0
????????? END AS orderdate
? FROM dbo.Nums
? WHERE n <= @numorders
? ORDER BY CHECKSUM(NEWID());

CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate);

CREATE NONCLUSTERED INDEX idx_nc_sid_od_cid
? ON dbo.Orders(shipperid, orderdate, custid);

CREATE UNIQUE INDEX idx_unc_od_oid_i_cid_eid
? ON dbo.Orders(orderdate, orderid)
? INCLUDE(custid, empid);

ALTER TABLE dbo.Orders ADD
? CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(orderid),
? CONSTRAINT FK_Orders_Customers
??? FOREIGN KEY(custid)??? REFERENCES dbo.Customers(custid),
? CONSTRAINT FK_Orders_Employees
??? FOREIGN KEY(empid)???? REFERENCES dbo.Employees(empid),
? CONSTRAINT FK_Orders_Shippers
??? FOREIGN KEY(shipperid) REFERENCES dbo.Shippers(shipperid);

?? 八、實驗步驟

???????? 1. 檢查Performance數據庫的還原類型是否為FULL，否則請修改Performance的恢復模式為FULL。

???????? 2. 從主服務器備份主數據庫后恢復到鏡像服務器中，并確保兩者數據庫處于一致狀態，在恢復時指定norecovery選項，此處也可以使用日志傳送來初始化數據庫鏡像，恢復其他的如增量備份和日志備份文件，同樣需使用norecovery選項。

???????? 3. 復制其他需要的對象到鏡像服務器，如logins，SSIS，Jobs等。

???????? 4. 創建端點。端點的創建需要在每個實例上創建，且必須是sysadmin角色的成員，創建時需指定端點角色，并對端點激活。

??????????? --ROBINSON ：

??????????? CREATE ENDPOINT [DB_mirroring]?
?????????????? STATE=STARTED
?????????????? AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
?????????????? FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
?????????????? ENCRYPTION = SUPPORTED ALGORITHM RC4);

???????????? --ROBINSON/MIRROR：

???????????? CREATE ENDPOINT [DB_mirroring]?
?????????????? STATE=STARTED
?????????????? AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
?????????????? FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
?????????????? ENCRYPTION = SUPPORTED ALGORITHM RC4);

?

???????????? --ROBINSON/WITNESS：

????????????? CREATE ENDPOINT [DB_mirroring]?
?????????????? AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
?????????????? FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE,
?????????????? ENCRYPTION = SUPPORTED ALGORITHM RC4);

?????????????? ALTER ENDPOINT [DB_MIRRORING] STATE = STARTED;

?

????????? 5. 分別在各個實例上查看端點的配置情況及端點的狀態。

??????????? SELECT * FROM SYS.DTABASE_MIRRORING_ENDPOINTS;

??????????? GO

???????? 6. 分別在各個實例上配置數據庫鏡像的安全性，本實驗使用的同一帳戶，故配置數據庫鏡像的安全性語句相同，如下。如要設定不同的帳戶，請在各實例上增加Login帳戶，映射到Windows。

???????????? USE MASTER

???????????? GO

???????????? GRANT CONNECT ON ENDPOINT::”db_mirroring” TO ”robinson/SQL_mirror”;

???????????? GO

???????? 7. 單擊各個實例的Security，Logins下的SQL_mirror帳戶，查看其Properities，在Securables可以看到SQL_mirror被授予了connect權限。

???????? 8. 啟動數據庫鏡像

???????????? 在鏡像服務器上執行以下語句，用已指明主服務器的伙伴。注意應先在鏡像服務器上指明主服務器伙伴，然后才在主服務器上指明鏡像伙伴。

???????????? ALTER DATABASE Performance SET PARTNER = N ‘TCP://Robinson:5022’;? ----在鏡像服務器上執行

???????????? GO

???????????? ALTER DATABASE Performance SET PARTNER = N ‘TCP://Robinson:5023’;? ----在主服務器上執行

???????????? GO

???????????? ALTER DATABASE Performance SET WITNESS = N ‘TCP://Robinson:5024’;? ----在主服務器上執行

???????????? GO

???????? 9. 配置數據庫事務鏡像安全級別

???????????? ALTER DATABASE Performance SET SAFETY FULL;

???????????? GO

???????? 10. 查看數據庫鏡像的狀態

?????????????? 可以在主服務器上選擇主體數據庫，再單擊屬性，單擊鏡像，可以查看當前鏡像數據庫所使用的狀態，端口及鏡像模式等，也可以通過以下視圖來查看當前鏡像的狀態。

?????????????? 使用數據庫鏡像監視器。展開主服務器的主體數據庫，右單擊主體數據庫，單擊任務， 單擊啟動數據庫鏡像。在“數據庫鏡像監視器”對話框中，單擊“注冊鏡像數據庫”以注冊一個或多個鏡像數據庫。

?????????????? 使用動態管理視圖監控鏡像數據的轉態。

??????????????? SYS.DATABASE_MIRRORING:此視圖顯示一個服務器實例中每個鏡像數據庫的數據庫鏡像元數據。

??????????????? SYS.DATABASE_MIRRORING_ENDPOINTS:顯示有關服務器實例的數據庫鏡像的端點信息。

??????????????? SYS.DATABASE_MIRRORING_WITNESSES:顯示服務器實例為見證服務器的每個會話的數據庫鏡像元數據。

??????????????? SYS.DM_DB_MIRRORING_ CONNECTIONS:為每個數據庫鏡像網絡連接返回一行。

???????? 11. 鏡像數據庫故障時角色轉換的幾種方式

?????????????? 自動故障轉移: 僅適用于高可用性，設置事務鏡像安全級別為FULL。

?????????????? 手動故障轉移: 適用于高可用性和高級別保護模式，設置事務鏡像安全級別為FULL。

?????????????? 強制故障轉移: 僅適用于高性能模式，設置事務鏡像安全級別為OFF。

???????? 12. 演示幾種轉移過程

?????????????? 自動故障轉移:在使用高可用性的配置環境中，手動停止主體服務器，并刪除主體數據庫日志文件后，再啟動主體服務器，觀察主體和鏡像服務器中數據庫名后所顯示的字樣發生了變化，主體數據庫變成了鏡像數據庫，鏡像修復后成了主體數據庫。??

?????????????? 手動故障轉移:可以在無故障的情況下實現手動故障轉移。在主體數據庫中執行 ALTER DATABASE? Performance SET PARTNER FAILOVER;

?????????????? 強制故障轉移: 通常應用于高性能模式中，高可用性鏡像和見證服務器均不可用時，可以使用此方法快速修復，但此方法容易以導致數據的丟失。強制故障轉移語句：ALTER DATABASE? Performance SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;我們對上述采用了高可用性模式的實驗切換到高性能模式并實行強制故障轉移，執行下述語句：

?????????????? ALTER DATABASE Performance SET WITNESS OFF;????? ----在主服務器上執行，停用Witness

?????????????? GO

?????????????? ALTER DATABASE Performance SET SAFETY OFF;???????? ----在主服務器上執行，關閉事務安全?

?????????????? GO

?????????????? ----然后停止主服務器的SQL server服務

?????????????? ALTER DATABASE Performance SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;? ----在鏡像服務器上執行

?????????????? GO??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ----用于強制轉移故障

?????????????? ALTER DATABASE Performance SET SAFETY OFF;???????? ----在鏡像服務器上執行后，鏡像服務器開始提供服務，此句可以不用執行。

?????????????? GO

?????????????? 執行上述操作后，鏡像服務器開始提供服務，原主體服務器處于掛起狀態，此時可以使用以下SQL語句來恢復掛起的數據庫。

?????????????? ALTER DATABASE? Performance SET PARTNER RESUME;?????????? ----在新的主體服務器上執行

?????????????? GO

???????? 13. 實現客戶端重定向

?????????????? 自動重定向連接，使用ADO.NET或者SQL Native Client能夠自動連接到故障轉移后的伙伴，連接字符串中必須指定故障轉移伙伴。

??????????????? ConnectionString=”Data Source=computerA;Failover Partner=computerB;

??????????????? Initial Catalog=Profermance;Integrated Security=True;”

?

???????? 14. 對鏡像數據庫創建快照用作報表服務器等，減輕主數據的負載

???????????????? 鏡像數據庫的不可直接訪問的特性，使得創建數據庫的快照用作報表服務器的特性得以體現。用戶可以通過快照來訪問鏡像實例上的數據。當發生故障轉移后，快照仍保留在原實例上，以下我們對Performance的鏡像數據庫創建快照。

??????????????? CREATE DATABASE Performance_snap ON?

??????????????? (NAME = N’Performance_data’,FILENAME = N‘D:/SQL_Data/Performance_mirror/Performance.ss’)

??????????????? AS SNAPSHOT OF Performance;

??????????????? GO

??????? 15. 及時刪除不用的快照，減輕鏡像服務器的負載。

?

?

?

?

實驗二：SQL server 2005高可用性之----數據庫鏡像