- Back to
»
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
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 ***
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 ***
