Search This Blog

Wednesday, June 22, 2011

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.

No comments:

Post a Comment