SQL Server2008數據庫鏡像實施筆記

作者：深山老林 2009-04-10 15:37:48

這是一篇實施筆記，講述了本文作者在公司的一個項目中，如何實際操作SQL Server2008數據庫鏡像，以及虛擬機環境下部署的情況。

最初在為公司設計SQL Server數據庫鏡像的時候，首先考慮的是高可用性（三臺計算機，一臺見證服務器，一臺做主數據庫，一臺做鏡像）

在虛擬機環境下部署成功，一切都是那么的完美。故障轉移3秒之內就可以順利完成。

1.高可用性的實施代碼：

主體數據庫

/********************************************************
此腳本在主體服務器執行
********************************************************/
--鏡像只支持完全恢復模式，在備份數據庫之前檢查恢復的模式
--對要鏡像的數據庫進行完整備份后，復制到鏡像數據庫以NORECOVERNY選項進行恢復
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--為此服務器實例制作一個證書。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
   WITH SUBJECT = 'HOST_A certificate',START_DATE = '01/01/2009';
GO
--使用該證書為服務器實例創建一個鏡像端點。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_A_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = PARTNER
   );
GO

--備份 HOST_A 證書，并將其復制到其他機器，將 C:\HOST_A_cert.cer 復制到 HOST_B\HOST_C。
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'e:\HOST_A_cert.cer';
GO
--為入站連接配置 Host_A
--在 HOST_A 上為 HOST_B 創建一個登錄名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
GO

--創建一個使用該登錄名的用戶。
--DROP USER HOST_B_user
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
--使證書與該用戶關聯。
--DROP CERTIFICATE HOST_B_cert
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'e:\HOST_B_cert.cer'
GO

--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

--在 HOST_A 上為 HOST_C 創建一個登錄名。
USE master;
--DROP LOGIN HOST_C_login
CREATE LOGIN HOST_C_login WITH PASSWORD = 'password';
GO

--創建一個使用該登錄名的用戶。
--DROP USER HOST_C_user
CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
GO
--使證書與該用戶關聯。
--DROP CERTIFICATE HOST_C_cert
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'e:\HOST_C_cert.cer'
GO

--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

USE master;
--DROP LOGIN HOST_A_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
GO
--創建一個使用該登錄名的用戶。
--DROP USER HOST_A_user
CREATE USER HOST_A_user FOR CERTIFICATE HOST_A_cert;
GO
--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--必須要在鏡像數據庫中先設置好伙伴后，才能在主體服務器執行
--在 HOST_A 的主體服務器實例上，將 HOST_B 上的服務器實例設置為伙伴（使其成為初始鏡像服務器實例）。
ALTER DATABASE crm
SET PARTNER = 'TCP://192.168.1.205:5022';
GO

--設置見證服務器
ALTER DATABASE crm SET WITNESS = N'TCP://192.168.1.204:5022';
GO

鏡像數據庫

/***********************************************
在鏡像服務器執行此腳本
***********************************************/
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--為 HOST_B 服務器實例制作一個證書。
--DROP CERTIFICATE HOST_B_cert
CREATE CERTIFICATE HOST_B_cert
   WITH SUBJECT = 'HOST_B certificate for database mirroring',START_DATE = '01/01/2009';
GO
--在 HOST_B 中為服務器實例創建一個鏡像端點。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_B_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = PARTNER
   );
GO
--備份 HOST_B 證書，將 C:\HOST_B_cert.cer 復制到 HOST_A\HOST_C。
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'e:\HOST_B_cert.cer';
GO

--為入站連接配置 Host_B
--在 HOST_B 上為 HOST_A 創建一個登錄名。
USE master;
--DROP LOGIN HOST_A_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
GO
--創建一個使用該登錄名的用戶。
--DROP USER HOST_A_user
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
--使證書與該用戶關聯。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'e:\HOST_A_cert.cer'
GO

--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--在 HOST_B 上為 HOST_C 創建一個登錄名。
USE master;
--DROP LOGIN HOST_C_login
CREATE LOGIN HOST_C_login WITH PASSWORD = 'password';
GO

--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

--在 HOST_B 上為 HOST_B 創建一個登錄名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
GO
--創建一個使用該登錄名的用戶。
--DROP USER HOST_B_user
CREATE USER HOST_B_user FOR CERTIFICATE HOST_B_cert;
GO
--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO
--在 HOST_B 的鏡像服務器實例上，將 HOST_A 上的服務器實例設置為伙伴（使其成為初始主體服務器實例）。
ALTER DATABASE crm
SET PARTNER = 'TCP://192.168.1.203:5022';
GO

見證服務器

/****************************

見證服務器執行

*****************************/

--ALTER DATABASE MirrorDB SET PARTNER OFF
USE master;
--DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

--為此服務器實例制作一個證書。
--DROP CERTIFICATE HOST_C_cert
CREATE CERTIFICATE HOST_C_cert
WITH SUBJECT = 'HOST_C certificate',START_DATE = '01/01/2009';
GO

--使用該證書為服務器實例創建一個鏡像端點。
--DROP ENDPOINT Endpoint_Mirroring
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE HOST_C_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = WITNESS
   );
GO

--備份 HOST_C 證書，并將其復制到其他系統，即 HOST_B\HOST_A。
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'e:\HOST_C_cert.cer';
GO

--為入站連接配置 Host_C
--在 HOST_C 上為 HOST_B 創建一個登錄名。
USE master;
--DROP LOGIN HOST_B_login
CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';
GO

--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

--在 HOST_C 上為 HOST_A 創建一個登錄名。
USE master;
--DROP LOGIN HOST_A_login
CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';
GO
--創建一個使用該登錄名的用戶。
--DROP USER HOST_A_user
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
--使證書與該用戶關聯。
--DROP CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'e:\HOST_A_cert.cer'
GO

--授予對遠程鏡像端點的登錄名的 CONNECT 權限。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--在 HOST_C 上為 HOST_C 創建一個登錄名。
USE master;
--DROP LOGIN HOST_C_login
CREATE LOGIN HOST_C_login WITH PASSWORD = 'password';
GO
--創建一個使用該登錄名的用戶。
--DROP USER HOST_C_user
CREATE USER HOST_C_user FOR CERTIFICATE HOST_C_cert;
GO

可能有朋友們會比較有疑惑，你一下搞兩個數據庫出來，他們的ip地址都不一樣，到時候數據庫切換過去了，我的數據庫的連接字符串可如何是好？難道還得在代碼中去控制是連接哪個數據庫嗎？

其實這個問題是這樣的，使用ADO.NET或者SQL Native Client能夠自動連接到故障轉移后的伙伴，連接字符串如下所示：

ConnectionString="DataSource= A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=true;" DataSource= A;

2.高級別保護模式

在昨天晚上加班做實施的時候，才發現我的設計已經被修改了，由于以前的項目有java寫的也有c#寫的，全自動的故障轉移不能夠實現。換句話說，由于老項目中的歷史遺留問題，以及特殊模塊的耦合性過高，無法解耦，只能在高級別保護模式或高性能模式中選擇一種了。那么這兩者有什么區別呢？

簡單一點來說，區別就在與事務安全模式上跟應用場景上。

高級別保護模式采用的是同步鏡像， SAFETY FULL。應用場景：通常在局域網中或對數據要求比較高的場景中。

高性能保護模式采用的是異步鏡像， SAFETY OFF。應用場景：通常在廣域網或對數據要求不太高，丟失幾條數據是允許的，但是必須保證它不中斷服務。

在微軟的SQL Server2005的課程上是這么說的。如果是高級別保護模式的話，主、從數據庫只要有一臺不能正常保證服務，數據庫就不能夠對外進行服務了，我在開始的時候就沒有打算采用這種模式，因為部門經理說了，丟失一兩條數據是可以接受的，況且我們公司是做運營的，按照起先微軟的課程的理論，高級別保護模式是不太適合我們公司的應用場景的，萬一有一臺數據庫出問題了，整個服務就被中斷，這是不能讓人接受的。再說了，公司對數據要求不太苛刻，兩臺服務器都有內網線連接，由于內網傳輸速度非常的快，即使采用高性能模式，一般來說也是不會丟失數據的。于是我打算采用高性能模式來做數據庫的鏡像。由于公司服務器沒有域環境，所以我就采用了證書驗證來做SQL Server鏡像。

意外收獲：

兩臺服務器全部都安裝了SQL Server2008，在設置事務安全模式的時候，才發現SQL Server2008不支持異步模式。提示大概如下：此SQL Server版本不支持修改事務安全模式，alter database失敗。我當時汗都出來了，忙活了一晚上，到最后居然是這個結果。

由于是服務器維護時間，我大膽的把鏡像服務器停止了，結果卻讓我大吃一驚，主數據庫依舊可以正常工作，正常對外提供服務。也就是說，起先微軟的課程講的知識是錯誤的，兩臺數據庫做鏡像，不管是哪臺數據庫出了問題，另外的一臺數據庫都可以保證正常對外提供服務。于是我反復試驗反復切換了一下，結果依然是這樣。

由于高級別保護模式與高性能模式代碼差不太多，只是在事務安全模式的設置上有些小區別，前面已經提到，這里就不再多解釋了。實施的代碼如下：

主體服務器

USE  master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '01/01/2009';

CREATE  ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

BACKUP  CERTIFICATE HOST_A_cert TO  FILE  =  'e:\HOST_A_cert.cer';

CREATE  LOGIN HOST_B_login WITH  PASSWORD  =  'password';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'e:\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

ALTER  DATABASE crm SET  PARTNER  =  'TCP://10.10.10.8:5022';

鏡像數據庫

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '01/01/2009';
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );


BACKUP  CERTIFICATE HOST_B_cert TO  FILE  =  'e:\HOST_B_cert.cer';

CREATE  LOGIN HOST_A_login WITH  PASSWORD  = 'password';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'e:\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

ALTER  DATABASE crm SET  PARTNER  =  'TCP://10.10.10.6:5022';

可能有朋友會比較奇怪，你這里也沒有使用ALTER DATABASE crm SET SAFETY FULL; 按理應該是高性能模式才對呀？

其實這個問題是這樣的，我的這個SQL Server2008默認已經是將事務安全模式設置為full了，即使是手動設置也一樣，并且我實施的時候SQL Server2008不支持將事務安全模式設置為OFF。

OK,一切都設置好了,那么就可以模擬服務器真的down機時候的操作了，后續的工作我也把代碼做了總結，具體代碼如下：

手動故障轉移代碼

--主備互換

--主機執行：

ALTER DATABASE crm SET PARTNER FAILOVER

--主服務器Down掉,備機緊急啟動并且開始服務
ALTER DATABASE crm SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

原來的主服務器恢復,可以繼續工作,需要重新設定鏡像
--備機執行：
USE master
ALTER DATABASE crm SET PARTNER RESUME --恢復鏡像

ALTER DATABASE crm SET PARTNER FAILOVER; --切換主備

3.監視數據庫鏡像

SQL Server提供了一些視圖，可以供查詢鏡像的各種狀態，到時候可以根據這個做一個監視，一旦發生故障轉移群集，發郵件給系統管理員，好讓系統管理員及時的知道數據庫服務器發生了什么問題，即使的做故障分析、排查。有關這方面資料，MSDN上已經提供太多資料了。感興趣的朋友可以去查這方面的資料。

在文章的最后提出一個有爭議的問題：SQL Server（2008）高級別保護模式，只要有一臺數據庫能夠保證正常運行，就可以正常對外提供服務。我的實驗結果是這樣的，這的確跟以往的理論知識有些出入。

還等什么，趕快搭環境動手實驗一下吧，體驗一下SQL Server鏡像帶來的快感。希望有興趣的朋友們一起學習探討。

【編輯推薦】

淺談SQL Server數據庫并發測試方法
微軟發布SQL Server 2008 SP1（附下載鏈接）
淺談如何優化SQL Server服務器

責任編輯：彭凡來源：博客園

SQL Server2008 鏡像實施