Setting Up SQL Server 2008 R2 Database Mirroring in a Workgroup

  • Article

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 "SQLServer" on Mirroring Server

create a login for local account "SQLServer"

 

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

(1)如果SQL Server啟動帳戶都已經是先建立好Login並授予對端點的CONNECT權限，此範例則在設定的"服務帳戶"的步驟，無須輸入任何帳戶，保留空白。

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

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

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

Mirror

(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 "SQLServer"

  GRANT CONNECT ON ENDPOINT::[DBM_Endpoint] TO [SQL2K8R2M2\SQLServer]
 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.