Blogs from Suhas

Dirtying my hands in SQL Server

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

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

Rate This
  • Comments 21

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.

All credit goes to Varun for creating this step-by-step guide. I take the liberty to post it in my blog link.

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

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Comments
  • hi,

    For the setting up Database mirroring. with no wittness server. maunal failover

    1. Mirror server database is restoring stage with sequence log backup were applied.

    In this case, how many steps should followed?

  • Most of the information provided on the Web in this regard is quite misleading but this is very well tailored tutorial. The only thing to care about is the Start_Date statement which should be current and you should also not be misled by the "Use Master" statement since it does not mean that you are mirroring the "Master" database. In fact, System databases can't be mirrored. You will have to use the "Master" database to create and implement certificate for mirroring. Moreover you can also specify an Expiry_Date as by default certificate is leased for one year. By using Expiry_date statement by putting a coma after the Start_Date, you can create certificate that has been leased for less than or more than one year. Go ahead and enjoy the outcome at the end.

    Cheers & Thanks for sharing such useful information

    Mahmood A. Sheikh.

  • Hi Ananda, you should leave all the statements regarding witness server and things will go fine as they did in my case. Later on, you can also add a witness server and Automatic Failover will be ready to go.

    regards,

    Mahmood.

  • Thanks for the insightful guide. I have been struggling to get this working for a week and on first trial your guide got me on target. Keep up the good work!

  • I am trying to setup from last 1 day but no luck....

    I have three different windows 2008 R2 Datacenter server in WORKGROUP and SQL 2012 is running. I followed each steps suggested in this blog...but getting error:-

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://10.0.13.144:5022" can not be reached or does not exist.

    Check the network address name and that the ports for the local and remote endpoints are operational.

    I tried each and every points but still no luck... Please help me out...

    Thanks

  • This blog helps a lot and save our time. Thanks!!!

Page 2 of 2 (21 items) 12
Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post