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.


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 (27)

  1. Gurmit Singh says:

    This is great a step by step tutorial. However there are few corrections/comments.

    1. make sure the START_DATE is as current as possible

    2. File name for paths should be a UNC (local drive letter did not work for me).

    3. Major road block I hit was at Step 10.

    Beforebackup do this:

    ALTER DATABASE MirrorDB SET RECOVERY FULL

    GO

    and BACKUP statement must include WITH INIT other you most likely will end up with error "Error: This log cannot be restored because a gap in the log chain was created" while restoring on Mirror server.

  2. Dananjaya says:

    I'm using SQL 2005 Workgroup edition with SP 3. But when I tried to execute the Step 1, it Says "This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this edition of SQL Server."

    I'm very new to this subject and please help on this matter.

    Dananjaya

  3. Suhas De says:

    Hi Dananjaya

    The "Database Mirroring" feature is not availble in SQL Server 2005 Workgroup Edition. This feature is available in Enterprise and Standard Editions only.

    Please refer: http://www.microsoft.com/…/compare-features.aspx

    Hope this helps!!!!

    Thanks

    Suhas

  4. manoj says:

    Article is very interesting and informative. Is it possible to do it with management studio

  5. Suhas De says:

    Do you mean from the GUI – through the wizard? No…

  6. nilupa says:

    i done all steps but in Step 10: Setup Mirroring i cannot run

    *  Execute this against the Principal Instance.

    */

    ALTER DATABASE MirrorDB

       SET PARTNER = 'TCP://<<your mirror server name here>>:5023'

    GO

    it gives me a error :

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://<<your mirror server name here>>:5023" 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.

    how can i resolved this issue.

  7. Suhas De says:

    Hi Nilupa

    I hope you have replace the <<your mirror server name here>> by the actual Mirror Server Name.

    If you have, please check the following:

    1. Make sure that the TCP EndPoint is Started on the Mirror Server. (SELECT * FROM sys.tcp_endpoints)

    2. Make sure that SQL Server is listening on the EndPoints. You might want to look at the SQL Server Errorlog to ensure that SQL Server is actually listening on this EndPoint.

    3. The SQL Server Browser Service is running on the Mirror Server.

    4. The UDP Port 1434 and TCP Port 5023 is opened on the Firewall.

    Let me know if this helps.

    Thanks

    Suhas.

  8. nilupa says:

    thx shhas for the reply.

    i done all things that u mentioned. but no luck still i get the same error. is this ipv6/ipv4 issue because when i ping the mirror server name it reply in ipv6.

    here some result….

    –  endpoint is started

    –  Server is listening on [ 'any' <ipv6> 1433].

      Server is listening on [ 'any' <ipv4> 1433].

      Server is listening on [ ::1 <ipv6> 1434].

      Server is listening on [ 127.0.0.1 <ipv4> 1434].

    how can i resolve this mess.

    nilupa.

  9. Suhas De says:

    Hi Nilupa

    I can see that the SQL Server is listening on Ports 1433 and 1434. However, it does not seem to be listening on the Mirroring EndPoint Port (5023). Can you confirm that SQL Server is listening on the Mirroring EndPoint Port (5023).

    It is not an IPv4/IPv6 issue.

    Suhas

  10. nilupa says:

    or sorry i forgot

    here the errorlog results..

         Server is listening on [ 'any' <ipv6> 5023].

         Server is listening on [ 'any' <ipv4> 5023].

    nilupa.

  11. Suhas De says:

    Nilupa, I do not think I will be able to troubleshoot the problem any further this way. If you can open a Support Incident with Microsoft Support, one of our guys will help you out.

    Suhas

  12. nilupa says:

    thanks for the info suhas.

  13. Azhar says:

    it gives me a error :

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://<<your mirror server name here>>:5023" 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.

    how can i resolved this issue.

  14. Vinoth says:

    thanks for the info…If you monitor the Synchronization status.Mirror server shows Limited permission.

    How to rectify it.

  15. James Beniopa says:

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

  16. ananda says:

    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?

  17. Mahmood A. Sheikh says:

    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.

  18. Mahmood A. Sheikh says:

    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.

  19. Gary Magadzire says:

    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!

  20. Kumar says:

    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

  21. Marc says:

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

  22. Camelia says:

    Hi! I am following this tutorial but at step 3 query:

    GRANT LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'

    GO

    I am getting the following error message:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '='.

    Can you help me to understand what is that I am doing wrong please!!! It sounds like a great tutorial and I was hoping to manage my db mirroring finally, after wasting a lot of time trying different things…

    Thanks a million!

  23. Camelia says:

    CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'  

    worked with no errors but when I run the following query:

    CREATE CERTIFICATE HOST_MIRR_cert

    AUTHORIZATION HOST_MIRR_user

    FROM FILE = 'C:certificateHOST_MIRR_cert.cer'

    I get the following error:

    Msg 15208, Level 16, State 1, Line 1

    The certificate, asymmetric key, or private key file does not exist or has invalid format.

    Searching on forums I gather it has something to do with permissions. Now how do I fix that? Can you help me please? Thank you

  24. Tanveer says:

    Nice post.

    Does this mirror setup lead to synchronous or asynchronous ?

  25. John Walker says:

    Database mirroring provides almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance. To know more benefits of database mirroring you can also take the help of this link: http://www.sqlserverlogexplorer.com/database-mirroring-in-sql-server  

  26. Puneet Pawaia says:

    Do the Passwords used for creating the Master key and the login that uses the certificate need to be the same or can they be different. I don’t have the password for Master key encryption for one of the servers.

    1. Suhas De says:

      They need not be the same.