非域環境下SQL Server搭建Mirror（鏡像）的詳細步驟

1.測試驗證環境

2.創建前環境檢查

（1）網路是否能聯通，並且埠可用。

（2）SQL Server版本、補丁是否滿足鏡像要求。

（3）SQL Server資料庫的恢復模式、相容級別。

（4）SQL Server上是否有常規的備份作業，特別是日誌備份。

（5）主體伺服器和鏡像伺服器的SQL Server能否互通。

3.使用證書配置鏡像，並備份還原資料庫

在這一步中，我們將做兩件事，第一件是使用證書來配置鏡像，第二件是備份還原資料庫。在非域環境下，必須使用證書來搭建鏡像，所以把搭建證書放在第一步。有些資料上會把備份還原操作放在證書搭建之前，但是根據個人經驗，當磁碟IO、網路性能不佳的時候，備份、傳輸、還原都會浪費大量的時間（個人操作過2個小時），並且期間伺服器幾乎不能操作。這種時候，我會選擇先搭建好，再還原，然後馬上進行同步，減少主從差異，需要同步更多的數據。

第一部分  創建證書：

【如果伺服器使用Local System作為SQL Server服務賬號，就需要使用證書授權。】

使用證書搭建鏡像的步驟如下：

（1）創建資料庫主密鑰（如果主密鑰不存在）。

（2）在Master資料庫中創建證書並用主密鑰加密。

（3）使用證書授權創建端點（endpoint）。

（4）備份證書成為證書文件。

（5）在伺服器上創建登錄賬號，用於提供其他實例訪問。

（6）在master庫中創建用戶，並映射到上一步的登錄賬號中。

（7）把證書授權給這些用戶。

（8）在端點上授權。

（9）設置鏡像伺服器的主體伙伴。

（10）設置主體伺服器的鏡像伙伴。

（11）配置見證伺服器。

Step 1：創建資料庫主密鑰

主密鑰的用處在這裡是用於加密證書，當然主密鑰不僅僅只有這個作用。對資料庫主密鑰的密碼及存儲保護要小心，這是實例級別的對象，影響面非常廣。可以使用下麵語句來創建：

USE master   
GO   
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

通過系統表查看，確認。

使用相同方式在鏡像伺服器創建資料庫主密鑰。

Step 2：創建證書，並用主密鑰加密

創建證書時，預設在創建日期開始一年後過期，所以針對證書的創建，要註意其過期時間。下麵是在“主體伺服器”上創建HOST_P_cert證書的創建

USE master   
GO   
CREATE CERTIFICATE Host_A_Cert    
WITH Subject = 'Host_P Certificate',   
Expiry_Date = '2050-1-1'; --過期日期 

使用相同的方法在鏡像伺服器上實現對HOST_S_cert證書的創建。

Step 3：創建端點

可以使用下麵的代碼在主體伺服器中創建端點，並且指定使用5022,埠，埠在鏡像配置過程中不強制使用特定埠（被占用或者特定埠如1433除外）。

--使用Host_A_Cert證書創建端點   
IF NOT EXISTS ( SELECT  1   
                FROM    sys.database_mirroring_endpoints )   
    BEGIN   
        CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,   
            LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =   
            CERTIFICATE Host_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =   
            ALL );   
    END  

 在鏡像伺服器對證書名稍作修改，創建鏡像伺服器的端點。

Step 4：備份證書

備份證書的目的是發送到別的伺服器並導入證書，以便別的伺服器能通過證書訪問這台伺服器（主體伺服器）。

BACKUP CERTIFICATE Host_A_Cert   
TO FILE = 'D:\ShareFoldersMirror\Host_A_Cert.cer';  

同理，在鏡像伺服器上重覆一次，註意證書名和路徑。備份之後可以在目標文件夾上看到有一個cer文件：

備份證書文件互相Copy至對方文件中。

Step 5：創建登錄賬號

針對每個伺服器單獨創建一個伺服器登錄賬號，這裡只需要創建一個登錄給鏡像伺服器即可：

CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa$$w0rd';

同理，在鏡像伺服器上創建Host_A_Login給主體伺服器。

Step 6：創建用戶，並映射到Step 5中創建的登錄賬號中

在主體伺服器上運行：

CREATE USER Host_B_User For Login Host_B_Login;

同理在鏡像伺服器也創建。

Step 7：使用證書授權用戶

創建一個新的證書，並使用從伙伴伺服器中複製過來的證書導入，然後映射step 6中的賬號到這個新證書上。

CREATE CERTIFICATE Host_B_Cert   
AUTHORIZATION Host_B_User   
FROM FILE = 'D:\ShareFoldersMirror\Host_B_Cert.cer';  

註意鏡像伺服器上也同樣。

Step 8：把Step 5中的登錄賬號授權訪問埠

GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];  

鏡像伺服器也一樣。

到此為止，配置鏡像的步驟已經完畢，後續會給出儘可能自動化的配置腳本。

第二部分  備份還原資料庫：

這一部分沒有什麼特別強調的，在此次試驗過程中，使用了界面配置。

註意:本次還原是為Mirror做準備，所以，點擊   【選項】  按鈕 。

所以需要選擇【不對資料庫執行任何操作，不回滾未提交的事務….】

 還原成功

第三步：啟動鏡像

依次分別在鏡像Server和主Server上運行以下命令就可以了【最好在Master DB上執行以下命令】

在鏡像Server上線運行

ALTER DATABASE [Test_Mirror] 
    SET PARTNER = 'TCP://172.83.XXX.XXX:5022';
GO

在主Server上運行

ALTER DATABASE [Test_Mirror] 
    SET PARTNER = 'TCP://172.73.XXX.XXX:5022';
GO

 配置成功，此時顯示如下：

主體伺服器上顯示

鏡像伺服器上DB顯示

4. 補充說明

以下內容用來學習

問題1  查詢判斷資料庫是否已添加主密鑰

---sys.databases的is_master_key_encrypted_by_server得到是否有加密
select top 100 is_master_key_encrypted_by_server,* from sys.databases

----如果沒有就看不到數據【需定義到資料庫】 
----解釋說明：##MS_ServiceMasterKey##----是說的整個服務，而##MS_DatabaseMasterKey## 是說的Master資料庫，需留意。
----我們  使用證書搭建鏡像 是需要在master資料庫上創建資料庫主密鑰（如果主密鑰不存在）。
SELECT * FROM sys.symmetric_keys

以下截圖查詢的數據顯示Master資料庫尚未創建主密鑰。

以下截圖的數據顯示Master資料庫已有主密鑰

問題2 由日誌傳送更改為鏡像。

希望直接更改，即不再需要備份和還原。

Step 1 【註意：此時先手動執行一下此DB的Log 備份的Job，然後停掉此Job，接下來再執行Copy Log 文件的Job（如果有此Job的話），再停掉此Job，最後執行Restore 此Log 文件的Job，接著停掉此Job】

Step 2 選擇指定DB，取消【將此資料庫啟用為日誌傳送配置中的主資料庫…】,就是把 勾 去掉。

點擊確定後，會要求我們再次連接一下。

Step 3 開始建立伙伴關係

先在備份Server的DB上去做

ALTER DATABASE [YYYY_Mob] 
    SET PARTNER = 'TCP://172.87.XXX.XX2:10001';
GO

 然後再在主DB上運行

ALTER DATABASE [YYYY_Mob] 
    SET PARTNER = 'TCP://172.89.XXX.XX4:10002';
GO

問題3 刪除主密鑰

解決方案：

DROP CERTIFICATE 證書名

但此時 還有報錯了

Step 1 刪除映射的登錄賬號和用戶名

查看登入名 

刪除標識的登入名 ，此時執行還會報同樣的錯誤。

註意登入名和用戶名是2個概念，

DROP LOGIN   For_HOST_B_user

（有時還要查詢  select top 100* from sys.sysusers是否還有這個用戶，有的話，還要執行 DROP User For_HOST_B_user）

Step 2 刪除埠 

SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring'

存在則刪除埠

Step 3  刪除

此時，就OK了。去刪除證書和主密鑰

問題4 在建立伙伴關係時，需註意設置伙伴的順序

如果按照網址上介紹的步驟 ，現在主伺服器上執行，設置伙伴。

則可能報錯，提示的錯誤信息如下：

我們先在Mirror伺服器上執行

然後再在主伺服器中執行，則不報錯

參考文獻

http://blog.csdn.net/dba_huangzj/article/details/27652857