Setting Up SQL Server 2008 R2 Database Mirroring in a Workgroup 在Workgoup環境設定SQL Server資料庫鏡像

Setting Up SQL Server 2008 R2 Database Mirroring in a Workgroup
在Workgoup環境設定SQL Server資料庫鏡像
from my MSDN blog - September 8, 2013

1.Verify SQL Server service account and configure SQL Server Login for Database Mirroring

(1)SQL Server service account

Principal Server SQL Server service account is local account name "SQLServer"

Mirroring Server SQL Server service account is local account name "SQLService"

(2)create a local account "SQLservice" on Principal Server

create a login for local account "SQLservice"

(3)create a local account "SQLService" on Mirroring Server

create a login for local account "SQLService"

2.Prepare Mirroring Database

3.Setup Database Mirroring

此步驟需要輸入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).

SQL DBM 只支援Windows驗證與憑證驗證，若採用Windows驗證，此步驟需輸入SQL Server的服務帳號，SQL DBM鏡像使用SQL Server服務帳號連線到鏡像夥伴來同步資料

此帳號可以是本地Windows帳號，輸入後精靈會在各個角色的SQL instance建立Login並授予對上一個步驟建立的Endpoint端點有CONNECT權限。

如果沒有事先建立login或沒有事先授予端點的CONNECT權限，則輸入對方的SQL Server服務啟動帳戶(SQL Server Startup Account)

Principal下方輸入Mirror的SQL Server啟動帳戶

Mirror下方輸入Principal的SQL Server啟動帳戶

(2)設定成功後，不要啟動鏡像(Do not start Mirroring)。

(3)Grant Connect permission to Mirroring service account "SQLservice"

GRANT CONNECT ON ENDPOINT::[DBM_Endpoint] TO [SQL2K8R2M1\SQLservice] 
GO

(4)Grant Connect permission to Principal service account "SQLService"

GRANT CONNECT ON ENDPOINT::[DBM_Endpoint] TO [SQL2K8R2M2\SQLservice] 
GO

(5)Start Mirroring

(6)click [Yes]

(7)finish

4.Network Traffic

Configure the Windows Firewall to Allow SQL Server Access
https://msdn.microsoft.com/en-us/library/cc646023.aspx

Scenario	Port	Comments
Database Mirroring	Administrator chosen port. To determine the port, execute the following query:  SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints  WHERE type_desc = 'DATABASE_MIRRORING'	There is no default port for database mirroring however Books Online examples use TCP port 7022. It is very important to avoid interrupting an in-use mirroring endpoint, especially in high-safety mode with automatic failover. Your firewall configuration must avoid breaking quorum. For more information, see Specify a Server Network Address (Database Mirroring).

UDP port 137 and 138, and TCP port 139 possible need for NetBIOS name resolution.