Step 1:
Backup and Restore:
Principal Server:
Backup the principal server. Full backup and Transaction log backup of principal server should be taken and restored at the mirror server with NORECOVERY.
Mirror Server:
RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AW.bak'
WITH FILE = 1, MOVE N'AdventureWorks_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Data.mdf',
MOVE N'AdventureWorks_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Log.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AW1.trn'
WITH FILE = 1, MOVE N'AdventureWorks_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Data.mdf',
MOVE N'AdventureWorks_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mir\AdventureWorks_Log.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO
Problems Faced:
If recovering is not happening due to permission issues then the backup folder should be given NETWORK SERVICE account permission. I tried but I did not success so I changed the logon of Mirror Server to LOCAL SYSTEM under SQL SERVER CONFIGURATION MANAGER and I succeeded.
After the restore is done change the log on back to NETWORK SERVICE as it was before.
Step 2:
Make sure TCP\IP connection for both principal and mirror SQL SERVER is on from SQL SERVER CONFIGURATION MANAGER:
Check if the Port number for Clients is set to 1433(Default):
After Restoring, the Database in Mirror server should be in restoring state as it was restored in NORECOVERY mode:
Steps to Start Mirroring:
I used the following link to setup the mirror:
Step 1: Create encryption key, certificate and end-points on Principal Instance
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_PRIN_cert
WITH SUBJECT = 'HOST_PRIN certificate',
START_DATE = '07/05/2011'
GO
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE HOST_PRIN_cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
BACKUP CERTIFICATE HOST_PRIN_cert
TO FILE = 'E:\certificate\HOST_PRIN_cert.cer'
GO
--Drop Endpoint End_Mirroring
--Drop Certificate HOST_PRIN_cert
--Drop Master Key
The Certificate gets created:
Step 2: Create encryption key, certificate and end-points on Mirror Instance
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_MIRR_cert
WITH SUBJECT = 'HOST_MIRR certificate',
START_DATE = '07/05/2011'
GO
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE HOST_MIRR_cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
BACKUP CERTIFICATE HOST_MIRR_cert
TO FILE = 'E:\certificate\HOST_MIRR_cert.cer';
GO
The certificate gets created:
Step 3: Create login, user and associate certificate with user on Principal Instance
USE MASTER
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
AUTHORIZATION HOST_MIRR_user
FROM FILE = 'E:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO
--drop login HOST_MIRR_login
--drop user HOST_MIRR_user
--Select * from sys.certificates
--DROP CERTIFICATE HOST_MIRR_cert
Step 4: Create login, user and associate certificate with user on Mirror Instance
USE MASTER
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
AUTHORIZATION HOST_PRIN_user
FROM FILE = 'E:\certificate\HOST_PRIN_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
--GRANT CONNECT ON ENDPOINT::End_Mirroring TO [NT SERVICE\MSSQL$MACHA]
GO
--DROP CERTIFICATE HOST_PRIN_cert
--drop login COMMONT
--drop user HOST_PRIN_user
Problems Faced in Step 3 and 4:
I was getting permission errors. Make sure that both the certificates that we had created have the permissions to the SQL server instance logins..
Step 5: Setup Mirroring
/*
* Execute this against the Mirror Instance.
*/
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://192.168.1.3:5022'
GO
/*
* Execute this against the Principal Instance.
*/
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://192.168.1.3:5023'
GO
Problems Faced in Step 5:
It was saying the principal log is not big enough and some cycling errors.
Resolution:
Take log backup from principal and apply it at the mirror:
On the principal, I did:
BACKUP LOG ADVENTUREWORKS TO DISK = 'E:\DB_LOG.BAK'
On the mirror, I did:
RESTORE TRANSACTION ADVENTUREWORKS FROM DISK = 'E:\DB_LOG.BAK'
WITH LOG
Now both the databases are mirrored.
Mirroring Monitor:
You can check if both the databases are synchronized from Mirroring Monitor and also check the data flow rate from this:
Setting up The witness Server:
I had SQL server 2008 for both Principal and Mirror Databases. I tried installing SQL Server 2005 express edition to be used as a Witness server. But i failed miserably. I tries out all the Google answers to solve it. But finally i arrived at an article which said that the witness server should not be any lower versions than those of principal or mirror. So i tried Mirroring using Witness server between the SQL Server 2005 standard editions for both Principal and Mirror and SQL Server express edition for Witness server and i succeeded. Check this out in my next post :
http://masoodhashim.blogspot.com/2011/07/sql-server-2005-mirroring-using-witness.html
No comments:
Post a Comment