Setting Up SQL Server 2008 R2 Database Mirroring step by step

Artikel
08/31/2013

1.Prepare a Mirror Database for Mirroring

(1)在主體伺服器，對要設定鏡像的資料庫執行完整備份。(Create either a full database backup or a differential database backup of the principal database.)

(2)執行交易紀錄檔備份(Take a log backup) ，為何需要Log Backup可以參考這篇文件

Typically, you need to take at least one log backup on the principal database.

However, a log backup might be unnecessary, if the database has just been created and no log backup has been taken yet, or if the recovery model has just been changed from SIMPLE to FULL.

一般來說，您必須至少取得主體資料庫上的一個記錄備份。不過，如果資料庫剛剛建立，而且尚未建立任何記錄備份，或是如果復原模式剛剛從 SIMPLE 變更為 FULL，可能就不需要有記錄備份。

(3)將備份檔複製到鏡像伺服器。

copy backup file to Mirroring Server

(4)在鏡像伺服器，以WITH NORECOVERY參數還原完整備份與交易紀錄檔備份。

Using RESTORE WITH NORECOVERY, create the mirror database by restoring the full database backup and, optionally, the most recent differential database backup, onto the mirror server instance.

還原後資料庫狀態為(正在還原中)(Restoring)

2.開始設定Database Mirroring

(1)在主體伺服器設定主體資料庫屬性

After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and select the database to be mirrored.
Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

(2)在選取頁面點選[鏡像]，右邊點選[設定安全性]

To begin configuring mirroring, click the Configure Security button to launch the Configure Database Mirroring Security Wizard.

(3)選擇是否要一併設定見證伺服器。

(4)若上一個步驟選擇[否]，則不會出現此步驟，此步驟可以取消見證伺服器，等稍後再加入見證伺服器。

(5)設定主體伺服器的端點。

端點名稱可自行命名。(例如DBM_Endpoint)

The Configure Database Mirroring Security Wizard automatically creates the database mirroring endpoint (if none exists) on each server instance, and enters the server network addresses in the field corresponding to the role of the server instance (Principal, Mirror, or Witness).

(6)設定鏡像伺服器的端點。

端點名稱可自行命名。(例如DBM_Endpoint)

(7)設定見證伺服器的端點。

端點名稱可自行命名。(例如DBM_Endpoint)

(8)設定服務帳戶。

此步驟需要輸入SQL Server的啟動帳戶，設定精靈會是需要建立Login並在端點授予CONNECT權限。

When creating an endpoint, the Configure Database Mirroring Security Wizard always uses Windows Authentication. Before you can use the wizard with certificate-based authentication, the mirroring endpoint must already have been configured to use certificates on each of the server instances. Also, all the fields of the wizard's Service Accounts dialog box must remain empty. For information about creating a database mirroring endpoint to use certificates, see CREATE ENDPOINT (Transact-SQL).