Step by Step Guide to Setup a Dedicated SQL Database Mirroring(DBM on dedicated Nic card)

One of the Best practice for database mirroring(DBM) is to setup DBM on dedicated NIC card\Network adapter, as mentioned in https://msdn.microsoft.com/en-us/library/cc917681.aspx

The idea is to transport the mirroring traffic via a dedicated network line and hence any network load on server will not impact Database Mirroring and Vice versa. This can be achieved by having 2 Nic card, which will have 2 IP address, and DBM Logs can be mirrored via one IP while other NIC card will handle the server network communication. This activity can’t be achieved via User Interface and require modification of normal Database mirroring scripts. This blog outlines the steps that’s needs to be performed for setting up dedicated database mirroring on separate NIC.

Principle Configuration:

=================

Server Name: DBM1

SQL Server Name : DBM1\PRINCIPLE

NIC 1: 172.23.96.83

NIC 2: 172.23.96.88

Mirror Configuration:

==============

Server Name: DBM2

SQL Server Name : DBM2\MIRROR

NIC 1: 172.23.96.82

NIC 2: 172.23.96.37

It would be a good practice to have an isolated Network connection between NIC 2: of Principle and NIC 2: of MIRROR.

Below are the steps for configuring dedicated database mirroring:

==================================================

STEP1> Configure Endpoint at Listener IP of NIC 2. Below is the syntax for the ENDPOINT Configuration.

CREATE ENDPOINT <EndpointName>

STATE=STARTED

AS TCP (LISTENER_PORT=<TCP Port>,LISTENER_IP=<IP of NIC 2>)

FOR DATABASE_MIRRORING (ROLE=PARTNER)

For eg:

On Mirror below is my create Endpoint syntax

CREATE ENDPOINT Endpoint_Mirroring

STATE=STARTED

AS TCP (LISTENER_PORT=5025,LISTENER_IP=(172.23.96.88))

FOR DATABASE_MIRRORING (ROLE=PARTNER)

On Principle below is my create Endpoint syntax

CREATE ENDPOINT Endpoint_Mirroring

STATE=STARTED

AS TCP (LISTENER_PORT=5024,LISTENER_IP=(172.23.96.37))

FOR DATABASE_MIRRORING (ROLE=PARTNER)

STEP2> Take a Backup of SQL Database and log, which needs to be mirrored, from Principle.

Backup database <db_name> to disk ='c:\db_name.bak'

go

Backup log <db_name> to disk ='c:\db_name.trn'

For Eg:

Backup database AdventureWorks to disk ='c:\AdventureWorks.bak'

go

Backup log AdventureWorks to disk ='c:\AdventureWorks.trn'

STEP3> Copy the Backup files taken in above step to the C:\ drive of Mirror Server and Restore the database on Mirror with Exact Database Name

Restore database <db_name> from disk='c:\<db_name>.bak' with norecovery

For eg:

restore database AdventureWorks from disk='c:\AdventureWorks.bak' with norecovery

go

restore log AdventureWorks from disk='c:\AdventureWorks.trn' with norecovery

STEP4> To enable Database Mirroring between 2 Dedicated NIC Card i.e between NIC2 of principle and NIC2 of Mirror, we need to have a Full Qualified Domain Name(FQDN) for each of those.

To perform this step there are 2 options

1. Register the DNS of NIC2 for both servers OR

2. Add the corresponding FQDN of NIC2 on Hosts file of each server. The hosts file is located in C:\Windows\System32\drivers\etc

You can use any of the above options to get the FQDN, through I would recommend to use option 1 from stability prospective. The Option 2 is very simple to use but just incase the FQDN mentioned in step 2 is registered at domain server for any other machine then you may land into trouble.

For demonstration, lets use option 2 i.e. Assigning FQDN for each of IP of NIC 2 in hosts file:

For eg below is the IP and FQDN I appended at the end of My Host file:

172.23.96.37 DBM-MIR.mydomain.corp.microsoft.COM

172.23.96.88 DBM-PRI.mydomain.corp.microsoft.COM

NOTE: Its highly recommended to restart the box after following the step4 or else you might face network issue going forward.

STEP5> Setting the partner for Mirror using below script:

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://<FQDN of Principle NIC2>:<TCP port>'

For eg:

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://DBM-PRI.mydomain.corp.microsoft.COM:5024'

STEP6> Setting the Partner for Principle using below script:

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://<FQDN of Mirror NIC2>:7025'

For eg:

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://DBM-MIR.mydomain.corp.microsoft.COM:5025'

STEP7> Verifying the Database Mirroring Configuration using the below query\dmv's

select name,type_desc,state_desc,port,is_dynamic_port,ip_address from sys.tcp_endpoints

go

select database_id,mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance from sys.database_mirroring

Sample O/p on Principle:

name type_desc state_desc port is_dynamic_port ip_address

-------------------------- ------------------- ----------- ----------- --------------- -------------

Dedicated Admin Connection TSQL STARTED 0 1 NULL

TSQL Default TCP TSQL STARTED 0 1 NULL

Endpoint_Mirroring DATABASE_MIRRORING STARTED 5024 0 172.23.96.88

(3 row(s) affected)

database_id mirroring_state_desc mirroring_role_desc mirroring_partner_name mirroring_partner_instance

----------- -------------------- ------------------- --------------------------------------------------- ---------------------------

1 NULL NULL NULL NULL

2 NULL NULL NULL NULL

3 NULL NULL NULL NULL

4 NULL NULL NULL NULL

5 NULL NULL NULL NULL

6 SYNCHRONIZED PRINCIPAL TCP://DBM-MIR.mydomain.corp.microsoft.COM:5025 DBM2\MIRROR

Sample O/p on Mirror:

name type_desc state_desc port is_dynamic_port ip_address

-------------------------- ------------------- ----------- ----------- --------------- -------------

Dedicated Admin Connection TSQL STARTED 0 1 NULL

TSQL Default TCP TSQL STARTED 0 1 NULL

Endpoint_Mirroring DATABASE_MIRRORING STARTED 5024 0 172.23.96.88

(3 row(s) affected)

database_id mirroring_state_desc mirroring_role_desc mirroring_partner_name mirroring_partner_instance

----------- -------------------- ------------------- --------------------------------------------------- ---------------------------

1 NULL NULL NULL NULL

2 NULL NULL NULL NULL

3 NULL NULL NULL NULL

4 NULL NULL NULL NULL

5 NULL NULL NULL NULL

6 SYNCHRONIZED MIRROR TCP://DBM-PRI.mydomain.corp.microsoft.COM:5024 DBM1\MIRROR

Error You might face while configuring the Database Mirroring: On principle\Mirror

Msg 1418, Level 16, State 1, Line 2

The server network address "TCP://DBM-MIR.mydomain.corp.microsoft.COM:5025" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

The above Error is a general network error which means the “DBM-MIR.mydomain.corp.microsoft.COM:5025” is not able to be reached.

v Troubleshooting Steps for above error:

1. you can try a ping test via command prompt: “ Ping DBM-MIR.mydomain.corp.microsoft.COM” . Repeat the same test from Mirror to principle using the FQDN of Principle. If you didn’t get any reply then verify the Network Setup(Step 4) once again OR check out the general Connectivity between 2 servers.

2. If the above Ping test succeed and still you are facing the error then you need to verify the connectivity via telnet as SQL Mirroring uses this protocol. For Telnet test you can run the following command : “telnet DBM-MIR.mydomain.corp.microsoft.COM 5025”. If the command prompt comes out to be blank then it means that telnet test succeeded. Repeat the same test from Mirror to principle using the FQDN of Principle. If the test failed you need to troubleshoot the telnet problems. Following link can be very useful: https://technet.microsoft.com/en-us/library/cc771162(WS.10).aspx.

Ideally if the telnet test succeed then there shouldn’t be any network issue and any SQL errors can be considered as a result of improperly configuring the steps mirroring

3. Verify if Endpoint is listening. Run the command on principle and mirror to verify if Endpoint are started and listening:

select name,state_desc,port,is_dynamic_port,ip_address from sys.tcp_endpoints

You should see the State_Desc as “STARTED” which means the Endpoint is listening.

4. Disable any firewall or add the TCP ports as exceptions in the firewall configuration so that connectivity between the servers is not blocked by firewall.

Sanket Sao

SE, Microsoft SQL Server.

Reviewed by
Ouseph Devis T & Nickson Dicson
Technical Lead, Microsoft SQL Server.

Comments

• Anonymous
  March 31, 2010
  The comment has been removed

• Anonymous
  March 31, 2010
  The comment has been removed

• Anonymous
  April 01, 2010
  Does this overwrite the Binding order of NIC cards? And what if there is teaming between NIC cards does that will work in the scenario?

• Anonymous
  April 06, 2010
  Hi krishnamurthy, The Binding order comes into picture only when request is placed through both the NIC cards, for eg: If we use Listener_Ip=ALL, the network load will be shared as per Binding order. Network Teaming, ideally, shouldn't have any impact, but why do you want to go for teaming if you want to set 1NIC card for database mirroring?

• Anonymous
  December 07, 2010
  if two server be members of a domain then the above direction is same or it a litle diffrint for doamin?

• Anonymous
  April 09, 2011
  estimate, can further detail? using screenshots? I would appreciate it heaps, and I need a lot. thanks and greetings. ¡Nuevo! Haz clic en las palabras que aparecen arriba para ver traducciones alternativas. Descartar

• Anonymous
  April 10, 2011
  Hello Hamed, Yes, this is applicable to systems in Domain only. To add further detail, If the systems are in workgroup then you need to provide authentication using Certificates, for which I haven't tried setting Dedicated Database mirroring.

• Anonymous
  May 09, 2011
  Hi, is it correct to configure the partner-ip in the endpoint? The Mirror has NIC 2: 172.23.96.37 but in the example you configured ...LISTENER_IP=(172.23.96.88)... Thanks

• Anonymous
  May 09, 2011
  Hi Joseph, Thanks for pointing out that flaw. The Mirror server should have IP of NIC2 of Mirror server, and Principle should have ip of NIC2 of Principle server. Will rectify that. Cheers Sanket Sao

• Anonymous
  October 10, 2013
  Nice article I was looking for solution of this type

• Anonymous
  June 26, 2014
  Clearly explained and easily understood.. Thanks for the great article Sanket!!!

• Anonymous
  September 03, 2016
  How do you verify the Database Mirroring is configure correctly or not?

• Anonymous
  September 03, 2016
  How does the RPO, RTO and SLA play a vital role in decision making to select a HA solution?