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

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

Rate This
  • Comments 11

One of the Best practice for database mirroring(DBM) is to setup DBM on dedicated NIC card\Network adapter, as mentioned in http://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.

 

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: http://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.

 

 

Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
  • You mentioned one of the option is to Register the DNS of NIC2 for both servers.  Could you please give the command/step?

    Thanks

  • Hi John,

    This needs to be done at DNS server. Incase you require the steps to be carried out at DNS server, here you go:

    1>Go to START-->RUN-->and type in dnsmgmt.msc to open up dnsconsole OR you can also to go All Programs --> Adminitratitive tools -->DNS.

    2>Right click on Domain name for which you want to register the NIC and go to New Host(a) from the popup menu.

    3>Enter the secondary NIC Ip adress and the domain name you want to give.

    4>you can CheckBox "Create associated Pointer (PTR) Record" too so as to have reverse .

    5>Incase you receive the below Warning Message safely Ignore that.

    "warning: the associated pointer (PTR) record cannot be created,probably because the referenced reverse lookup zone cannot be found."

    Hope that helps!!

  • 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?

  • 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?

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

  • 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

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

  • 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

  • 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

  • Nice article I was looking for solution of this type

  • Clearly explained and easily understood..

    Thanks for the great article Sanket!!!

Page 1 of 1 (11 items)