Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Implementing Database Mirroring in SQL Server 2005 across domains

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (17)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Mirroring

Problem
I want to implement database mirroring on my SQL Server 2005 databases, but both the principal and mirror servers are not members of the domain or are members of different Active Directory domains that do not have trust relationships. How do I go about it?

Solution
Database mirroring has become a very popular choice for a high availability solution in SQL Server 2005. And while Microsoft recommends using Windows Authentication for anything accessing SQL Server, there are cases where this is a limitation. A typical example would be a non-Microsoft application running on a non-Microsoft platform accessing a SQL Server database. Another would be SQL Servers spread across different geographical locations with no Active Directory to authenticate user access or member servers on different domains with no trust relationships. SQL Server 2005 enables us to configure database mirroring under mixed mode authentication using SQL Server logins with the added security of using certificates.

We will use the Northwind database to demonstrate the process. Remember to change Northwind's database recovery model to FULL before working through the steps below.

Let's call the principal server testServer1, the mirror server testServer2 and witness server testServer3. You can also do this without the witness although this tip will highlight using one.

1) Backup the principal database and restore it on the mirror server

Perform a FULL and LOG backup for the Northwind database on the principal server.

--Run this on testServer1/principal server 
USE master  
GO  
BACKUP DATABASE Northwind   
   
TO DISK = N'D:\DBBackup\NorthwindBackup.bak'  
   
WITH NAME N'Full Database Backup',  
    
INITSTATS 10  
GO  

BACKUP LOG Northwind   
   
TO DISK = N'D:\DBBackup\NorthwindBackupLog.trn'  
   
WITH NAME N'Transaction Log Backup',    
       
STATS 10  
GO  

After the backup has completed, restore it on the mirror server using the WITH NORECOVERY option.

--Run this on testServer2/mirror server 
RESTORE DATABASE Northwind  
FROM DISK = N'D:\DBBackup\NorthwindBackup.bak'  
WITH NORECOVERY
GO  

RESTORE LOG Northwind  
FROM DISK = N'D:\DBBackup\NorthwindBackupLog.trn'  
WITH NORECOVERY
GO 

2) Validate your DNS entries

Chances are that you may have your DNS entries for the SQL Server instances properly configured. To validate, do a PING test on both hostnames of the servers running your SQL Server 2005 instances using it's fully qualified domain name (FQDN) in the form hostname.primary DNS suffix. You can retrieve the value of the primary DNS suffix by running an IPCONFIG /ALL in your command line. For servers in workgroups, this could be a bit of a challenge as you might not have a DNS server that would resolve the hostname to their appropriate IP addresses. You would have to modify your hosts file to manually add the IP address-hostname mappings. To do this, open the hosts file on your %systemroot%\system32\drivers\etc\ using any text editor. Enter the IP addresses and hostnames of your principal, mirror and witness servers as shown below

3) Create a database master key on the principal server

--Run this on the principal server/testServer1 
USE master 

CREATE MASTER KEY ENCRYPTION BY PASSWORD 'y0ur$ecUr3PAssw0rd'
GO

4) Create the server-based certificate which will be used to encrypt the database mirroring endpoint

--Run this on the principal server/testServer1 
CREATE CERTIFICATE testServer1_cert WITH SUBJECT 'testServer1 certificate for database mirroring' 
GO 

5) Create the database mirroring endpoint for the principal server using the certificate for authentication

--Run this on the principal server/testServer1 
CREATE ENDPOINT Endpoint_Mirroring STATE STARTED
AS TCP(LISTENER_PORT 9999LISTENER_IP ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION CERTIFICATE testServer1_cert
ENCRYPTION REQUIRED ALGORITHM AESROLE ALL); 
GO 

This script creates a database mirroring endpoint named Endpoint_Mirroring using port number 9999 and the certificate named testServer1_cert. You can use any encryption algorithm available in SQL Server 2005 a described in this Microsoft TechNet article. I just opted to use AES. You can validate that the endpoint has been created by querying the sys.endpoints catalog view

6) Export the certificate to a file

--Run this on the principal server/testServer1 
BACKUP CERTIFICATE testServer1_cert TO FILE = 'C:\testServer1_cert.cer'
GO 

7) Copy the certificate file to the mirror and witness server

You need to copy the certificate file to both the mirror and the witness server as we will use this certificate to associate a login for authentication. For this example, all certificates will be copied to the C:\ drive on all servers

Steps #3 to #7 should be repeated on both mirror and witness server, modifying the certificate name while keeping the other configurations.

A complete script for the mirror server is shown below

--Run this on testServer2 

--STEP #3: 
USE master 

CREATE MASTER KEY ENCRYPTION BY PASSWORD 'y0ur$ecUr3PAssw0rd'
GO

--STEP #4: 
CREATE CERTIFICATE testServer2_cert WITH SUBJECT 'testServer2 certificate for database mirroring' 
GO 

--STEP #5: 
CREATE ENDPOINT Endpoint_Mirroring STATE STARTED 
AS TCP(LISTENER_PORT 9999LISTENER_IP ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION CERTIFICATE testServer2_cert
ENCRYPTION REQUIRED ALGORITHM AESROLE ALL); 
GO 

--STEP #6: 
BACKUP CERTIFICATE testServer2_cert TO FILE = 'C:\testServer2_cert.cer'
GO

--STEP #7: Copy the certificate to the principal and witness servers 

A complete script for the witness server is shown below

--Run this on testServer3 

--STEP #3: 
USE master 

CREATE MASTER KEY ENCRYPTION BY PASSWORD 'y0ur$ecUr3PAssw0rd'
GO

--STEP #4: 
CREATE CERTIFICATE testServer3_cert WITH SUBJECT 'testServer3 certificate for database mirroring' 
GO 

--STEP #5: 
CREATE ENDPOINT Endpoint_Mirroring STATE STARTED 
AS TCP(LISTENER_PORT 9999LISTENER_IP ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION CERTIFICATE testServer3_cert
ENCRYPTION REQUIRED ALGORITHM AESROLE ALL); 
GO 

--STEP #6: 
BACKUP CERTIFICATE testServer3_cert TO FILE = 'C:\testServer3_cert.cer'
GO

--STEP #7: Copy the certificate to the principal and mirror servers 

8) Create a login on the principal for the mirror

For simplicity's sake we shall use the same name for the login on all servers to identify that it is used primarily for the database mirroring sessions

--Run this on testServer1

USE master
CREATE LOGIN login_mirroring WITH PASSWORD 'y0ur$ecUr3PAssw0rd'
GO 

9) Create a user for the login

--Run this on testServer1

CREATE USER login_mirroring 
FOR LOGIN login_mirroring 
GO 

10) Associate the certificate we created in Step #4 with the user

This script will associate the certificate we created for the mirror server - testServer2_cert.cer - to the user login_mirroring

--Run this on testServer1

CREATE CERTIFICATE testServer2_cert 
AUTHORIZATION login_mirroring 
FROM FILE = 'C:\testServer2_cert.cer' 
GO 

We'll do the same thing for the certificate we created for the witness server - testServer3_cert.cer - to the user login_mirroring

--Run this on testServer1

CREATE CERTIFICATE testServer3_cert 
AUTHORIZATION login_mirroring 
FROM FILE = 'C:\testServer3_cert.cer' 
GO 

11) Grant the CONNECT permission on the login

This script will grant the CONNECT permission to the login login_mirroring for the database mirroring endpoint

--Run this on testServer1

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring]
GO 

Since we used the same name for the logins on all servers, you only need to do this once. If you are using a different name for the logins, you will have to do this for each login. The key thing to remember here is that the logins you use to connect to any of the SQL Server 2005 instances are granted the CONNECT permissions with the appropriate certificates.

We will do the exact same thing on both the mirror and witness servers.

A complete script for the mirror server is shown below

--Run this on testServer2

--STEP #8: 
USE master
CREATE LOGIN login_mirroring WITH PASSWORD 'y0ur$ecUr3PAssw0rd'
GO

--STEP #9:
CREATE USER login_mirroring 
FOR LOGIN login_mirroring 
GO

--STEP #10:
CREATE CERTIFICATE testServer1_cert 
AUTHORIZATION login_mirroring 
FROM FILE = 'C:\testServer1_cert.cer' 
GO

CREATE CERTIFICATE testServer3_cert 
AUTHORIZATION login_mirroring 
FROM FILE = 'C:\testServer3_cert.cer' 
GO

--STEP #11:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring]
GO

A complete script for the witness server is shown below

--Run this on testServer3

--STEP #8: 
USE master
CREATE LOGIN login_mirroring WITH PASSWORD 'y0ur$ecUr3PAssw0rd'
GO

--STEP #9:
CREATE USER login_mirroring 
FOR LOGIN login_mirroring 
GO

--STEP #10:
CREATE CERTIFICATE testServer1_cert 
AUTHORIZATION login_mirroring 
FROM FILE = 'C:\testServer1_cert.cer' 
GO

CREATE CERTIFICATE testServer2_cert 
AUTHORIZATION login_mirroring 
FROM FILE = 'C:\testServer2_cert.cer' 
GO

--STEP #11:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring]
GO

12) Prepare the mirror server for the database mirroring session

This script prepares the mirror database for the database mirroring session, specifying testServer1 as the partner. It is important to run this first on the mirror server before running the equivalent script on the principal server. The hostname specified here should be resolved using the approach you used in Step #2

--Run this on testServer2 
USE master 
GO 
ALTER DATABASE Northwind SET PARTNER 'TCP://testServer1:9999'
GO 

13) Prepare the principal server for the database mirroring session

Now that the mirror database has been prepared for the database mirroring session, we will do the same for the principal database specifying testServer2 as the partner.

--Run this on testServer1 
USE master 
GO 
ALTER DATABASE Northwind SET PARTNER 'TCP://testServer2:9999'
GO 

We will also specify testServer3 as the witness.

--Run this on testServer1 
USE master 
GO 
ALTER DATABASE Northwind SET WITNESS 'TCP://testServer3:9999'
GO 

14) Validate the database mirroring configuration using Database Mirroring Monitor

Run the Database Mirroring Monitor to check the status of the database mirroring sessions. This MSDN article on SQL Server Books Online describes how to launch the Database Mirroring Monitor.

Next Steps

  • Try configuring database mirroring using certificates by following the procedures outlined above
  • Check out this MSDN article on how to setup database mirroring
  • You can get the Northwind database used in the sample here.

Related Tips



Last Update: 3/11/2009


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, August 07, 2014 - 12:52:48 PM - bass_player Read The Tip

Great to hear that it worked. In most cases, cleaning everything up and restarting from scratch works


Thursday, August 07, 2014 - 11:39:09 AM - Daisy Read The Tip

I cleaned up everything and restarted the process from the start.  It finally works.  Not sure what made it work.  But I have to specify a static port for the named instances and make sure that port is opened.  Also the port for the mirroring endpoint is opened.  Thanks for the clear and step by step instructions. 


Wednesday, August 06, 2014 - 3:45:56 PM - Daisy Read The Tip

Tried recreating the endpoints, also tried using a different port.  Still having error 1418. 

 


Tuesday, August 05, 2014 - 6:21:40 PM - bass_player Read The Tip

Hi Daisy,

 

Have you tried dropping and recreating the endpoints?


Tuesday, August 05, 2014 - 4:48:38 PM - Daisy Read The Tip

 

Followed the guide step by step, but still getting error 1418.  Ping to each other, and telnet to each others' endpoint port (9999) also worked.  Any other suggestions?


Friday, November 16, 2012 - 5:22:29 AM - Arunkumar Read The Tip

Working like a charm.. Configured successfully WORKGROUP servers mirroring along with witness server.

Only problem i faced while creating the certificates. We need to specify the expiry dates. 

Thanks alot. It saved my time.


Monday, July 16, 2012 - 2:21:50 PM - andrismg Read The Tip

Hi Edwin, I would do like what is the difference between ROLE = ALL and ROLE = PARTNER in the Principal Server and Mirror Server. This can slow my connection between the two server?


Thursday, May 17, 2012 - 8:09:57 AM - Shamas Read The Tip

Nice artilce. I have issues while start configuration of database mirroring accross domain but this artilce helps alot.


Friday, April 27, 2012 - 12:59:56 PM - Adam Read The Tip

Very helpful....thanks!


Friday, January 14, 2011 - 4:56:45 PM - bass_player Read The Tip

I am beginning to suspect that this is more of a networking issue than SQL Server. Try the following

1) Run a PING test vice versa. PING the IP address of the mirror from the principal, then, PING the IP address of the principal from the mirror. If this works, make sure that you have entries on your hosts file or DNS server for these servers and that you can PING via hostname/FQDN

2) Run a TELNET test to check if the port number that you assigned to the instance is open. The port numbers are the ones you assigned using the CREATE ENDPOINT command on Step #5 but for the mirror server. Do the TELNET test vice versa - from the principal to the mirror and from the mirror to the principal. You can also use the netstat command to see if the port is indeed open and listening


Wednesday, January 12, 2011 - 6:08:09 PM - Jag Read The Tip

Thanks for the reply bass_player. That helped.

 

I am mirroring the server on seperate domain first time and running into issues. Here is the situation:

I can register the SQL servers sucessfully from both servers as follows:

From Principal to Mirror Server: Public IP:3947

 

 

From Mirror To Principal Server:  Public IP\Local IP,1443

However when I try to run the following SQL from Principal Server I get the following error:

 

 

ALTER 

 

DATABASE SET PARTNER = 'TCP://Public IP:3947';

 

The server network address "TCP://Public IP:3947" 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 have the TCP/IP enabled on SQL Server Configuration on both servers.

One more thing, I can connect to Mirror server from Principal Server "Mirror" wizard using the following connection:

TCP://Public IP\SQLInstance,3947:3947

When I can connect to Mirror server through SQL Server Registration or through "Mirror" wizard, why can't I connect from alte database.

Can you please tell me what else setting I need or what am I missing?

 

 

 

 


Tuesday, January 11, 2011 - 11:32:46 PM - bass_player Read The Tip

Hi Jag,

It seems that the database has been configured for database mirroring. If you've managed to go thru the process until the part where you successfully imported a certificate, simply restore the database backup (FULL plus LOG) on the mirror server and do steps #12 and #13. To be really sure, remove the mirroring session (if it does exist) prior to restoring the backups on the mirror

ALTER DATABASE <databaseName> SET PARTNER OFF


Thursday, January 06, 2011 - 2:08:56 PM - Jag Read The Tip

I found this artical hitting right on the nail.

I could run all the steps sucessfully till 5, however when I run the 6 step on Mirror Server (2nd server):

USE master
GO
ALTER  DATABASE SET PARTNER = 'TCP://SQLServer.IPAddress:1433'

it throws me the following error:

The database "" is already enabled for database mirroring.

 

I restored the database and log on Mirror server WITH NORECOVERY mode prior to start implemeting the setting from this artical.

Please advise me if I am doing any thing wrong and missing anything.

Thanks

Jag

 

 


Sunday, March 07, 2010 - 8:04:27 PM - ScottSpec Read The Tip

Will this work on a Windows 2000 server?  The reason I ask is that when I try to run the script, I get and error message saying:

 Msg 28060, Level 16, State 1, Line 5

The AES encryption algorithm is only supported on Windows XP, Windows Server 2003 or later versions.

Scott


Wednesday, April 22, 2009 - 6:47:14 AM - sfrvn Read The Tip

Good morning, bass_player

I have deleted the certificate for serverInfo. I reran the CREATE CERTIFICATE script without USE serverInfo. When I then try to create the endpoint, I receive the following error message:

" Cannot find the object 'cslgsrvg_cert' because it does not exist or..."

[snip]

Added:

I ran query to display all certificates. The 'cslgsrvg_cert' was NOT listed.

 I selected master database in SSMS and executed the CREATE CERT script. If I look under Security/Certificates, I can now see the cert listed.

I reran the CREATE ENDPOINT script and... it executed successfully !  So I am going to the next steps in your list.

The only thing I did differently (between unsuccessful and successful create endpoint scripts) was to run create cert script while master database was selected in SSMS GUI. 

Will report back later.

 gary b

 


Tuesday, April 21, 2009 - 2:53:16 PM - bass_player Read The Tip

Hi,

The certificate has to be configured in the master database as you would first have to be authenticated on the server-instance level before the database level.  This is the reason why the TSQL query only uses a single USE master command from steps #3 to #11. Step #5 asks you to create an endpoint, which is a server-instance securable.  The reason the for the "certificate does not exist" error is because you are trying to grant permission to a server-instance level object using a database-level certificate. You can drop the certificate you've created on the serverInfo database and start all over again

I normally recommend to test this out using a test environment - a virtual machine or a physical lab - so as not to mess around with your production environment setup


Tuesday, April 21, 2009 - 11:05:32 AM - sfrvn Read The Tip

This tip seems to be exactly what I need.  Additionally, I applaud the style in which it is presented!  However, when I attempt to follow the steps outlined in the tip, I encountered two issues. The second issue stopped my progress.  Let me elaborate:

A)  I started on Step 3. All previous steps were already done.  The script executed without issue.

B)  For Step 4, I copied the SQL and pasted it into a New Query. The script would not 'run' as listed in the tip. I discovered that if I preceded the listed script with  USE <database_name>    the script executed. I noted that the Step 3 script cited USE master. But I want to mirror a database named serverInfo, so I created:

USE serverInfo CREATE CERTIFICATE cslgsrvg_cert WITH SUBJECT = 'cslgsrvg certificate for database mirroring'

This script executed without error and I can 'see' the cert under serverInfo database [ SSMS:  serverInfo/Security/Certificates ]

C)  However (!) -- when I tried to execute Step 5, the script fails. I get following error:

Cannot find the object "cslgsrvg_cert" because it does not exist or you do not have permission

I am logged in as User with 'sa' permissions.

I cannot get past this point. How can I get back 'on track'? Your guidance would be appreciated.

 gary b

 Addendum: Please note that I had SQL database mirroring fully operational when both Principal *and* Mirror servers were in the same domain. But recent network changes required use of a different SQL server as the mirror -- and that server is in a different domain. Hence, my interest in mirroring across domains.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.