實驗二：SQLserver2005高可用性之—-資料庫映象

如轉載，請註明出處：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. 及時刪除不用的快照，減輕映象伺服器的負載。