Setting Up SQL Server 2008 R2 Database Mirroring step by step

Setting Up SQL Server 2008 R2 Database Mirroring step by step

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).

服務帳戶的相關問題,請參考這篇 Service Accounts and Database Mirroring

 

(9)完整精靈。

按一下[完成]

 

按下完成後就會開始設定。

 

(10) 關閉精靈之後,就會跳出一個新視窗,讓你決定是否要立即啟動鏡像。

(11)資料庫屬性的鏡像狀態如果還沒有出現"已同步處理:資料庫已完全同步",可以按一下重新整理。

(12)從SSMS的物件總管的資料庫狀態。

主體資料庫的狀態為(主體,已同步處理)

鏡像資料庫的狀態為(鏡像,已同步處理/正在還原...)

[Reference]

Prepare a Mirror Database for Mirroring (SQL Server)

https://technet.microsoft.com/en-us/library/ms189053.aspx

Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)

https://technet.microsoft.com/en-us/library/ms188712.aspx