This post is again in response to requests from customers who want to know how to configure Database Mirroring between instances of SQL Server 2005 in a WORKGROUP.

As you might have noticed, while configuring Database Mirroring using the GUI, it requires us to enter FQDNs of the servers; hence using the GUI, we can configure Database Mirroring between instances of SQL Server in a domain environment.


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

 /* Execute this against the 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 = '01/07/2009'
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 = 'D:\certificate\HOST_PRIN_cert.cer'
GO

Step 2: Create encryption key, certificate and end-points on Mirror Instance

 /* Execute this against the 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 = '01/07/2009'
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 = 'D:\certificate\HOST_MIRR_cert.cer';
GO

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

 

/*
* Execute this against the Principal Instance. The HOST_MIRR_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_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 = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO


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

 /* 
* Execute this against the Mirror Instance. The HOST_PRIN_cert.cer
* needs to be copied on
the Mirror 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 = 'D:\certificate\HOST_PRIN_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO

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

 /* Execute this against the 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 = '01/07/2009'
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 = 'D:\certificate\HOST_WITT_cert.cer'
GO

Step 6: 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 = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 7: Create login, user and associate certificate with user on Mirror Instance

 /*
* Execute this against the Mirror Instance. The HOST_WITT_cert.cer
* needs to be copied on the Mirror 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 = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 8: 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 = 'D:\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 = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

Step 9: Create the Mirrored Database on the Mirror Server using backups from the Principal Server

 /*
* Execute this against the Principal Instance.
*/
USE MASTER
GO
BACKUP DATABASE MirrorDB
TO DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
GO
BACKUP LOG MirrorDB
TO DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
GO
/*
* Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the
* Mirror Server.
* Execute this against the Mirror Instance.
*/
USE MASTER
GO
RESTORE DATABASE MirrorDB
FROM DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
WITH NORECOVERY
GO
RESTORE LOG MirrorDB
FROM DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
WITH NORECOVERY
GO

Step 10: Setup Mirroring

 /*
* Execute this against the Mirror Instance.
*/
ALTER DATABASE MirrorDB
SET PARTNER = 'TCP://<<your principal server name here>>:5022'
GO
/*
* Execute this against the Principal Instance.
*/
ALTER DATABASE MirrorDB
SET PARTNER = 'TCP://<<your mirror server name here>>:5023'
GO
ALTER DATABASE MirrorDB
SET WITNESS = 'TCP://<<your witness server name here>>:5024'
GO

At this point your Database Mirroring should be up and running. You can use the Database Mirroring Monitor to verify the setup as well as to monitor the Synchronization status.

DBMirroring