Setting Up SQL Server 2008 R2 Database Mirroring in a Workgroup


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

(1)在設定的”服務帳戶”的步驟,無須輸入任何帳戶,保留空白。

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

 

Comments (2)

  1. Padiamon says:

    In addition to 5022,UDP 137 is also needed to be open in firewall for network discovery and netbios talking between servers.

    Hope this helps.