Posted by : Abdul Munaf Wednesday, July 18, 2012


SERVER MIRRORING

 

Introduction:

 

            There are two servers installed. Both the servers are connected to the LAN for automated backup. The following procedure/Script would enable to do automated mirroring of data between two servers (SQL RDBMS) installed.

The server which is being used for day-to-day operation is called as Production Server. The stand-by/Backup Server which is connected to the LAN for any emergencies is called as Mirroring Server.

One should follow the following steps to enable automated mirroring between SERVER1 and SERVER2.

Note: Production Server = SERVER1
         Mirroring Server = SERVER2
Name of the Database = DBNAME

Step 1 & 2 on SERVER1 


Step 1:

Take Backup of Database from the SERVER1. Please ensure that the database is in full recovery model and auto close is set to false. To check the above points you may please follow the following steps.

                        

                           database-->properites-->options-->Recovery model is full and 

                                                       Auto close is false  

Example:  

The following commands should be typed in query analyzer of SQL RDBMS.

                        BACKUP  DATABASE  DBNAME
                        TO DISK = N' D:\ DBNAME.bak' 
                        WITH NAME = N'Full Database Backup', 
                        INIT, STATS = 10 

Step 2:

Please take the backup of Transaction Log file as per the commands given below using query analyzer of SQL RDBMS.

 

                        BACKUP LOG DBNAME
                        TO DISK = N' D:\ DBNAME.trn' 
                        WITH NAME = N'Transaction Log Backup', 
                        STATS = 10 

Step 3 is on SERVER2

Step 3 :

Please Restore the above Database and Transaction Log files into the SERVER2 and also ensure to keep the mirroring server Recovery mode as “  No Recovery Mode” as shown in the following screen shot.


                          

IN ADDITION TO THE ABOVE Step restore the transaction log also using following query on mirroring server.

Restore log DBNAME   

From disk =N’D:\DBNAME.trn’

WITH NORECOVERY

Add the following entries in c:\windows\system32\drivers\etc\hosts files of both the servers and save it.

 

IP Address      TestServer1    (SERVER1)

IP Address      TestServer2    (SERVER2)

 

eg.,  of  My Server

192.168.1.100             TestServer1

192.168.1.200             TestServer2

 

 

Perform the following action on SQL Server 2005 of both the servers and restart ms sql server

 

Start  -> programmes -> microsoft sql server2005c --> configuration tools --->SQL Server configuration manager -> Click on Sql server 2005 services->  Right click on SQL server(MSSQLSERVER) and go to properties-> go to  Advanced tab  -> Startup Parameters->here add   ;-T1400  at the end ->  Apply->ok   then Restart the MSSQL Service.

Step 4 & 5 on SERVER1

            

Step 4:             

We need to create a Master Key, Certificate and End-point for the Master Database of SERVER1 to ensure proper security during change over between two servers due to any emergency situation.  The procedure for doing the above operation is given below. Please use the following commands in the query analyzer of SQL RDBMS.

            USE master 

 

      DBCC TRACEON(1400)      

 

                CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mirror123'

CREATE CERTIFICATE testServer1_cert WITH SUBJECT = 'testServer1 certificate for database mirroring’, start_date='04/23/2010'

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
                AS TCP(LISTENER_PORT = 9999, LISTENER_IP = ALL)
                FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer1_cert,
                ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)


 

In the above statement the red colored strings are to be filled by you. We advice you to follow the same without changing it,  why because in the following steps also we used the same name for easy operation.  If you are confident to change the above variables please ensure to change every step without mismatch. The start date of the above statement is the start on which mirroring started in the format of mm/dd/yyyy.

Step 5:

Please take the backup of the above certificate by following the following command in the query analyzer of SQL RDBMS.

BACKUP CERTIFICATE testServer1_cert  TO FILE = 'C:\testServer1_cert.cer'

Step 6 on SERVER2

Step 6:

 

Please copy the above certificate (step 5) copied from the SERVER1 to the SERVER2 of C:\. (It should be copied to C:/ only) using regular copy command of windows OS.  

Note:

After completing the step 4 and 5 on SERVER1 it is compulsory to do the same set of procedure to create “ Master key, Certificate & End-point” of the SERVER2 and copy to the SERVER1 as mentioned in Step 6. The Procedure is given in Step 7. 

 

Step 7 & 8 on SERVER2

 

Step 7:

 

            USE master
                CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mirror123'


CREATE CERTIFICATE testServer2_cert WITH SUBJECT = 'testServer2 certificate for database mirroring’, start_date='04/23/2010'

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
                AS TCP(LISTENER_PORT = , LISTENER_IP = ALL)
                FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer2_cert,
                ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)


 Step 8:             

            BACKUP CERTIFICATE testServer2_cert  TO FILE = 'C:\testServer2_cert.cer'

Step 9 on SERVER1:

Step 9:

 

Please copy the above certificate (step 8) copied from the SERVER2 to the SERVER1 of C:\. (It should be copied to C:/ only) using regular copy command of windows OS.  

 

Step 10 & 13 on SERVER1:

 

We need to create Login, User and Certificate and enabling permission for the efficient and effective usage of the above mentioned script.

First we need to create Login in the Master database of SQL RDBMS and then User for the Login of the above Database then finally crate Certificate and enable Permission as given below.

Step 10:

            USE master 
            CREATE LOGIN login_mirroring WITH PASSWORD = 'Mirror123'

Step 11:

USE master
            CREATE USER login_mirroring 
            FOR LOGIN login_mirroring

Step 12:

CREATE CERTIFICATE testServer2_cert 
            AUTHORIZATION login_mirroring 
            FROM FILE = 'C:\testServer2_cert.cer' 

Step 13:

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring] 

Note :

After completing the step 10 to 13 on SERVER1 it is compulsory to do the same set of procedure to create “Login, User & Certificate” and enable the Permission of the SERVER2 .The Procedure is given below from Step 14 to 17. 

Step 14 & 18 on SERVER2:

 

Step 14:

            USE master 
            CREATE LOGIN login_mirroring WITH PASSWORD = 'Mirror123'

Step 15:

USE master
            CREATE USER login_mirroring 
            FOR LOGIN login_mirroring

Step 16:

CREATE CERTIFICATE testServer1_cert 
            AUTHORIZATION login_mirroring 
            FROM FILE = 'C:\testServer1_cert.cer' 

Step 17:

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring] 

Step 18:

After completing all the above steps it is necessary to enable the partner server for mirroring purpose. Now for  the SERVER2 the SERVER1 is the partner which can be done as below using SQL query analyzer. The same way for the SERVER1, the SERVER2 should be enabled as Partner server as shown in the next step 19.

ALTER DATABASE DBNAME SET PARTNER = 'TCP://testServer1:9999'

 

Step 19 on SERVER1:

Step 19:

As mentioned in the previous step please enable the SERVER1 as the partner Server as given below.

ALTER DATABASE DBNAME SET PARTNER = TCP://testServer2:1111'

For any further details/doubts please  refer the following article

SQLSERVER MIRRORING ACCROSS DOMAIN

*** END ***

Tags: , , ,

Leave a Reply

Like us on FB

Popular Post

Contact Form

Name

Email *

Message *

Advertisement

Rs. 150 .Com at GoDaddy.com! Rs. 109 .Com at GoDaddy.com!

Advertisement

Powered by Blogger.

Administrator

Visitors

Copyright © TechBuzz18. All Rights Reserved, The content, posted after January 1st 2014 is copyrighted to TechBuzz18 and may not reproduced on other websites. | Powered by TechBuzz18