Setup SQL Server 2008 R2 Database Mirroring with Automatic Failover Across the Internet

By:   |   Updated: 2016-06-09   |   Comments   |   Related: > Database Mirroring


Problem

Have you ever had challenges when trying to setup a Witness for a SQL Server Database Mirroring scenario? It's very simple when we are on the same network.  It is more of a challenge when you have to setup a Witness with two locally separated servers over the Internet. I'm always asking myself why?  Let's see if there is an easy way to accomplish this mission.

Solution

I have been able to setup a Witness to support SQL Server Database Mirroring over the Internet with a VPN and Windows hosts file. Perhaps there may be other alternatives, but this method I'm sharing worked for me.  In this tip let's walk through the steps to complete the configuration.  At a high level we need to:

  • Setup a VPN connection between the servers, establishing a channel over the internet to be able to communicate like over an intranet
  • Specify mappings of IP Address to Host Names for each server in the Windows Hosts file, used by Microsoft TCP/IP for Windows. The Windows Hosts file can be found at "%windir%\system32\drivers\etc\hosts".

Understanding the VPN Connection

The Virtual Private Network (VPN) is an encrypted connection between two or more devices over a public network. There are different types of VPNs available, from simple to complex configurations.  Based on my needs, I was able to move forward with IPSec or SSL/TLS-based VPN.  Keep in mind that the key is deciding when to use IPSec and when to use SSL.  Both options can provide enterprise-level secure remote access.  Generally speaking, IPSec connects hosts to entire private networks and SSL connects users to services and applications inside networks.  The with this tip is not to teach how to setup a VPN connection, but how to make progress with the benefits and advantage offered by it.  You will probably need to work the Network Engineers at your company in order to get the VPN configured.

Setting up Database Mirroring with Witness Including Automatic Failover

Once we have the VPN connection established between the Principal, Mirror and Witness Servers, we need to take the following steps to setup SQL Server Database Mirroring with a Witness:

Master key creation

Execute on each Server

You will need to enter your master key password as noted.

USE MASTER 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some password here'
GO

Certificate creation

Execute on Principal Server 

USE MASTER 
GO 
CREATE CERTIFICATE CERT_Principal WITH SUBJECT = 'Principal Certificate', 
 START_DATE = '01/01/2010', -- For Example 
 EXPIRY_DATE = '12/31/2020' -- For Example
GO

Execute on Mirror Server

USE MASTER 
GO 
CREATE CERTIFICATE CERT_Mirror WITH SUBJECT = 'Mirror Certificate', 
 START_DATE = '01/01/2010', -- For Example 
 EXPIRY_DATE = '12/31/2020' -- For Example
GO

Execute on Witness Server

USE MASTER 
GO 
CREATE CERTIFICATE CERT_Witness WITH SUBJECT = 'Witness Certificate', 
 START_DATE = '01/01/2010', -- For Example 
 EXPIRY_DATE = '12/31/2020' -- For Example
GO

Endpoint creation

It's possible to use the same port number on each server. Here the port number is 5140. The name chosen for the Endpoint is Endpoint_Mirroring.

Execute on Principal Server

USE MASTER 
GO 
-- We have to put here the name of the Principal Certificate "CERT_Principal"
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP 
(
  LISTENER_PORT = 5140, LISTENER_IP = ALL
) 
FOR DATABASE_MIRRORING 
( 
  AUTHENTICATION = CERTIFICATE CERT_Principal, 
  ENCRYPTION = REQUIRED ALGORITHM RC4, 
  ROLE = ALL
) 
GO

Execute on Mirror Server

USE MASTER 
GO 
-- We have to put here the name of the Mirror Certificate "CERT_Mirror"
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP 
(
  LISTENER_PORT = 5140, LISTENER_IP = ALL
) 
FOR DATABASE_MIRRORING 
( 
  AUTHENTICATION = CERTIFICATE CERT_Mirror, 
  ENCRYPTION = REQUIRED ALGORITHM RC4, 
  ROLE = ALL
) 
GO

Execute on Witness Server

USE MASTER 
GO 
-- We have to put here the name of the Witness Certificate "CERT_Witness"
-- Pay attention, the ROLE here is Witness
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP 
(
  LISTENER_PORT = 5140, LISTENER_IP = ALL
) 
FOR DATABASE_MIRRORING 
( 
  AUTHENTICATION = CERTIFICATE CERT_Witness, 
  ENCRYPTION = REQUIRED ALGORITHM RC4, 
  ROLE = Witness
) 
GO

NOTE - Remember to enable inbound and outbound firewall rules for the endpoint port number.

Certificate backups

Create the backup of the certificates and copy each file to each server.  I am placing my files in the C:\Temp folder.

Execute on Principal Server

USE MASTER 
GO 
BACKUP CERTIFICATE CERT_Principal TO FILE = 'C:\Temp\CERT_Principal.cer' 
GO

Execute on Mirror Server

USE MASTER 
GO 
BACKUP CERTIFICATE CERT_Mirror TO FILE = 'C:\Temp\CERT_Mirror.cer' 
GO

Execute on Witness Server

USE MASTER 
GO 
BACKUP CERTIFICATE CERT_Witness TO FILE = 'C:\Temp\CERT_Witness.cer' 
GO

Login and User creation

Create the login, user and associated certificate with the user.  The Principal Server will use the CERT_Mirror certificate.  The Mirror Server will use the CERT_Principal certificate.  The Witness Server will use both Principal and Mirror certificates.  You can use the same password as the Master Key here. You will need to update the key below where noted.

Execute on Principal Server

USE MASTER 
GO 

-- Create a login and user for Mirror Server
CREATE LOGIN LOGIN_Mirror WITH PASSWORD = 'Master key password here' 
GO
CREATE USER USER_Mirror FOR LOGIN LOGIN_Mirror
GO
-- Create a login and user for Witness Server
CREATE LOGIN LOGIN_Witness WITH PASSWORD = 'Master key password here' 
GO
CREATE USER USER_Witness FOR LOGIN LOGIN_Witness
GO

-- Create a certificate for Mirror
CREATE CERTIFICATE CERT_Mirror AUTHORIZATION USER_Mirror
 FROM FILE = 'C:\Temp\CERT_Mirror.cer' 
GO
-- Create a certificate for Witness
CREATE CERTIFICATE CERT_Witness AUTHORIZATION USER_Witness
 FROM FILE = 'C:\Temp\CERT_Witness.cer' 
GO

-- Set CONNECT permission on Endpoint to Mirror Login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO LOGIN_Mirror
GO
-- Set CONNECT permission on Endpoint to Witness Login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO LOGIN_Witness
GO

Execute on Mirror Server

USE MASTER 
GO 

-- Create a login and user for Principal Server
CREATE LOGIN LOGIN_Principal WITH PASSWORD = 'Master key password here' 
GO
CREATE USER USER_Principal FOR LOGIN LOGIN_Principal
GO
-- Create a login and user for Witness Server
CREATE LOGIN LOGIN_Witness WITH PASSWORD = 'Master key password here' 
GO
CREATE USER USER_Witness FOR LOGIN LOGIN_Witness
GO

-- Create a certificate for Principal
CREATE CERTIFICATE CERT_Principal AUTHORIZATION USER_Principal
 FROM FILE = 'C:\Temp\CERT_Principal.cer' 
GO
-- Create a certificate for Witness
CREATE CERTIFICATE CERT_Witness AUTHORIZATION USER_Witness
 FROM FILE = 'C:\Temp\CERT_Witness.cer' 
GO

-- Set CONNECT permission on Endpoint to Principal Login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO LOGIN_Principal
GO
-- Set CONNECT permission on Endpoint to Witness Login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO LOGIN_Witness
GO

Execute on Witness Server

USE MASTER 
GO 

-- Create a login and user for Principal Server
CREATE LOGIN LOGIN_Principal WITH PASSWORD = 'Master key password here' 
GO
CREATE USER USER_Principal FOR LOGIN LOGIN_Principal
GO
-- Create a login and user for Mirror Server
CREATE LOGIN LOGIN_Mirror WITH PASSWORD = 'Master key password here' 
GO
CREATE USER USER_Mirror FOR LOGIN LOGIN_Mirror
GO

-- Create a certificate for Principal
CREATE CERTIFICATE CERT_Principal AUTHORIZATION USER_Principal
 FROM FILE = 'C:\Temp\CERT_Principal.cer' 
GO
-- Create a certificate for Mirror
CREATE CERTIFICATE CERT_Mirror AUTHORIZATION USER_Mirror
 FROM FILE = 'C:\Temp\CERT_Mirror.cer' 
GO

-- Set CONNECT permission on Endpoint to Principal Login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO LOGIN_Principal
GO
-- Set CONNECT permission on Endpoint to Mirror Login
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO LOGIN_Mirror
GO

Database Backup and Restore Process

In this step we will create a backup of the database on the Principal Server and restore it on the Mirror Server.

Execute on Principal Server

USE MASTER 
GO 

BACKUP DATABASE database_name
TO DISK = N'C:\Backup\database_name.bak' WITH NOFORMAT, 
NOINIT, NAME = N'database_name-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

BACKUP LOG database_name
TO DISK = N'C:\Backup\database_name.trn' WITH NOFORMAT, 
NOINIT, NAME = N'database_name-Transaction Log  Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Execute on Mirror Server

USE MASTER 
GO 

RESTORE DATABASE database_name FILE = N'database_name' 
FROM DISK = N'C:\Backup\database_name.bak' WITH FILE = 1,  
MOVE N'database_name' TO N'C:\DB\MDF\database_name.mdf',  
MOVE N'database_name_log' TO N'C:\DB\LDF\database_name_0.ldf',  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG database_name FROM DISK = N'C:\DB\Backup\database_name.trn' 
WITH FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Windows Hosts File Configuration

Open the Windows Hosts File at "%windir%\system32\drivers\etc\hosts" and insert lines such as these to map the IP Addresses to Host Names.  You will need to use the correct IP address for each server.  Also, note the names of the servers I am using PRINCIPAL, MIRROR and WITNESS.

192.168.0.2 PRINCIPAL # Principal Server IP Address and Host Name
192.168.0.3 MIRROR # Mirror Server IP Address and Host Name
192.168.0.4 WITNESS # Witness Server IP Address and Host Name

Finishing the Database Mirroring with Automatic Failover

The last steps are here:

  • Set Partner on Mirror
  • Set Partner on Principal
  • Set Witness on Principal

Execute on Mirror Server

USE MASTER 
GO 
ALTER DATABASE database_name SET PARTNER = 'TCP://PRINCIPAL:5140'
GO

Execute on Principal Server

USE MASTER 
GO 
ALTER DATABASE database_name SET PARTNER = 'TCP://MIRROR:5140'
GO
ALTER DATABASE database_name SET WITNESS = 'TCP://WITNESS:5140'
GO
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Douglas P. Castilho Douglas Castilho has been a SQL Server DBA over 6 years, focuses on tuning, backup, disaster recovery, mirroring, T-SQL, PL-SQL and .NET

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-06-09

Comments For This Article

















get free sql tips
agree to terms