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

 


 


Comments (11)

  1. John says:

    You mentioned one of the option is to Register the DNS of NIC2 for both servers.  Could you please give the command/step?

    Thanks

  2. sanket sao says:

    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!!

  3. krishnamurthy says:

    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?

  4. sanket sao says:

    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?

  5. hamed says:

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

  6. claudio says:

    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

  7. Sanket Sao says:

    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.

  8. josefzen says:

    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

  9. sanket sao says:

    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

  10. shanky21smat@hotmail.com says:

    Nice article I was looking for solution of this type

  11. Priya says:

    Clearly explained and easily understood..

    Thanks for the great article Sanket!!!