SQL Server 2005 – Mirroring using Witness Server

Principal Database: SQL Server 2005 Standard edition

Mirror Database: SQL Server 2005 Standard edition

Witness Server: SQL Server 2005 Express edition

Setup the Principal and Mirror databases as explained in my previous blog post: http://masoodhashim.blogspot.com/2011/07/sql-server-mirroring-step-by-step.html

Not to include a witness server make sure that the port through which you want the endpoint of witness server is open and has connectivity.

Follow these steps to setup the witness:

Step 1: Create encryption key, certificate and end-points on Witness Instance

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'

GO

CREATE CERTIFICATE HOST_WITT_cert

WITH SUBJECT = 'HOST_WITT certificate',

START_DATE = '07/06/2011'

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = CERTIFICATE HOST_WITT_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4,

ROLE = Witness

)

GO

BACKUP CERTIFICATE HOST_WITT_cert

TO FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

Step 2: Create login, user and associate certificate with user on Principal Instance

/*

* Execute this against the Principal Instance. The HOST_WITT_cert.cer

* needs to be copied on the Principal Server.

*/

USE MASTER

GO

/*

* We are creating a SQL Login here. For Windows logins,

* use the Grant Login instead of Create Login

*/

CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login

GO

CREATE CERTIFICATE HOST_WITT_cert

AUTHORIZATION HOST_WITT_user

FROM FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]

GO

Step 3: 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_WITT_login WITH PASSWORD = 'Password!'

GO

CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login

GO

CREATE CERTIFICATE HOST_WITT_cert

AUTHORIZATION HOST_WITT_user

FROM FILE = 'E:\certificate\HOST_WITT_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]

GO

Step 4: Create login, user and associate certificate with user on Witness Instance

/*

* Execute this against the Witness Instance. The HOST_PRIN_cert.cer

* and HOST_MIRR_cert.cer needs to be copied on the Witness Server.

*/

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]

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

Step 5: Setup Mirroring

On Principal Instance:

ALTER DATABASE AdventureWorks

SET WITNESS = 'TCP://192.168.1.3:5024'

Problems faced: The automatic failover was not happening when I was switching off the Principal database server. Then I changed IP address to Fully Qualified Domain Name while setting up the witness server:

ALTER DATABASE AdventureWorks

SET WITNESS = 'TCP://Masood-PC:5024'

Make sure that the Host name is mapped to the IP address in the host file.

Different Scenarios Experimented

1.) Mirror is off. Principal made a change and went offline before Mirror came online. Mirrored DB not able to become principal.

In such cases, do the applications which use alternate connection string can point to alternate string if main connection string is not reachable??

2.) If Witness gets disconnected, and then Principal goes offline. Then Mirror does not become a principal. If you try to switch off mirroring, the mirror goes to restoring state but never online. Then go to step 4 solutions to get back mirroring.

3.) If Witness gets disconnected, and then Mirror goes offline. Then principal goes into recovery state. If you try to switch off mirroring, the Principal gets online. Then goto step 4 solution to get back mirroring.

4.) Principal goes offline, and then mirror becomes Principal. The new principal goes offline and when the old principal comes online it goes to restoring state. In such case set mirroring off

Error: The database 'AdventureWorks' is marked RESTORING and is in a state that does not allow recovery to be run.

And restore DB in recovery mode:

RESTORE database AdventureWorks WITH RECOVERY

Then to get back the Mirroring, Restore DB in Mirror and set partners in both principal and Mirror. Don’t forget to set witness in principal instance.

How to Enable Mirroring on any Server

Add the command –T1400 in the start-up parameters of the SQL Server. Do it as follows:

Go to Surface area configuration then to Services:

Add “;-T1400” at the end of start-up parameters and restart the server. In “;-T1400” is case sensitive as far as I have read. Use Capital ‘T’. This sets up the Trace Mode on for mirroring and it gets enabled.