Step-by-step guide to configure Database Mirroring between SQL Server Instances in a Workgroup

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.