Database Mirroring between a Server in Domain and Server outside domain (workgroup)
When you setup a Database Mirroring via the GUI in SSMS, you are
requested to specify the fully qualified domain names (FQDN) of the servers.
What about the cases when you need to configure the Database Mirroring
![]()
Note: The explanation below is dealing with mirroring configuration without
the witness server, please refer to links below if your configuration includes
witness server, in general all the steps for Principal/Mirror server should be
done also for the witness server.
Here is the highlight summary of the steps:
All the steps below should be done in each server instance(Principal/Mirror)
SQL scripts for each step you can find at the end,
and I suppose the you already restored the DB (and transaction logs, if needed)
in Mirror SQL Server with NORECOVEY of course.
1.In the master database, create a database master key.
2.In the master database, create an encrypted certificate.
3.Create a mirroring endpoint using the created certificate.
4.Back up the certificate to a file and copy it to the other server.
After you complete the steps above in both servers, do the following
also in both servers in master database:
5.Create a SQL Server login in each Server
6.Create a database user for created login.
7.Associate the certificate with the database user created in step 6.
8.Grant CONNECT permission on the SQL Server login for created
mirroring endpoint.
Now let`s see the SQL Scripts,
note that the scripts have different numeration then general steps above.
-------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance */
-------------------------------------------------------------------------------------------------------
-- Step 1 :
-- CREATE MASTER KEY,CREATE CERTIFICATE
-- CREATE ENDPOINT,BACKUP CERTIFICATE to file
---------------------------------------------------------------------------------
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE PrincipalServerCertificate
WITH SUBJECT = 'PrincipalServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
--Create the database mirroring endpoint for the principal server using the certificate for authentication
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE PrincipalServerCertificate
,ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE PrincipalServerCertificate
TO FILE = 'c:\PrincipalServerCertificate.cer'
GO
--------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Mirror Instance */
---------------------------------------------------------------------------------------------------------------------------
-- Step 2 :
-- CREATE MASTER KEY,CREATE CERTIFICATE,
-- CREATE ENDPOINT,BACKUP CERTIFICATE to file
----------------------------------------------------------------------------------
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE MirrorServerCertificate
WITH SUBJECT = 'MirrorServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE MirrorServerCertificate TO FILE = 'c:\MirrorServerCertificate.cer';
GO
------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance.
The MirrorServerCertificate.cer needs to be copied on the Principal Server.
*/
-------------------------------------------------------------------------------------------------------------------------
-- Step 3 :
-- CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Mirror server)
-- and grant the created User to it.
------------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN MirrorLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER MirrorUser FOR LOGIN MirrorLogin
GO
CREATE CERTIFICATE MirrorServerCertificate AUTHORIZATION MirrorUser
FROM FILE ='c:\MirrorServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]
GO
/* Execute this against the Mirror Instance.
The PrincipalServerCertificate.cer needs to be copied on the Mirror Server.
*/
----------------------------------------------------------------------------------------------------------------------
-- Step 4 :
-- CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Principal server)
-- and grant the created User to it.
----------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN PrincipalLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER PrincipalUser FOR LOGIN PrincipalLogin
GO
CREATE CERTIFICATE PrincipalServerCertificate AUTHORIZATION PrincipalUser
FROM FILE = 'c:\PrincipalServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [PrincipalLogin]
GO
/* Execute this against the Mirror Instance.*/
------------------------------------------------------------------------------------------------
-- Step 5 :
-- Prepare the mirror server for the database mirroring session
------------------------------------------------------------------------------------------------
ALTER DATABASE TESTDB
SET PARTNER = 'TCP://PrincipalServerIP5022'
GO
/* Execute this against the Principal Instance.*/
-------------------------------------------------------------------------------------------------
-- Step 6 :
-- Prepare the Principal server for the database mirroring session
-------------------------------------------------------------------------------------------------
ALTER DATABASE TestDB
SET PARTNER = 'TCP://MirrorServerIP:5023'
GO
-- Set the Mirroring to Asynchronous mode (if needed)
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
GO
That`s all after Step 6 the mirroring shoul be initialiazed.
Troubleshooting:
1) Ensure the Principal and Mirror Server listening to defined mirroring endpoints.
They should be listening after you define the endpoints and their state is Started.
You can use the following command in Principal Server
netstat -na | find "5022"
and this in Mirror Server
netstat -na | find "5023"
Of course, you can set the port numbers to be different then in this example.
2) Make sure firewall is not blocking the port and the firewall is allowing traffic both directions
3) Check the SQL log for errors
Mirroring Monitoring problems:
In our case, which is, Principal Server in a Domain and the Mirror Server
is not, when launching the "Database Mirroring Monitor" we had the following error:
![]()
If the Monitor was executed in the Principal Server, then there was an error
saying that the Mirror Server not connected. And vice versa when the Monitor
was launched from Mirror Server.
Going to "Manage Server Instance Connections" option
![]()
and choosing SQL Authentication in the connection of Mirror Server not helped,
it gave the following errors
![]()
or
"SQL Server replication requires the actual server name to make a connection to
the server. Connections through a server alias, IP address, or any other alternate
name are not supported."
It seems that SQL Server tries to connect to the Mirror server by using
the Server Name and not IP.
Adding the mapping to HOST file solved the problem.
---------------------------------------------------------------------------------------
To see how to monitor the DB Mirroring from every angle,
please my post here.
---------------------------------------------------------------------------------------
Also, I want to share the Clean up scripts,
they are needed when the mirroring is not working and you want to start
over again or if you are not sure in each step you stopped and etc.
-----------------------------------------------------------------------
-- Clean up / check scrips for the PRINCIPAL/MIRROR SQL Server
-----------------------------------------------------------------------
/*
------------------------------------------------------------------------------------------------------------
Drop master key
----------------------------------------------------------------------------------------------------------
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
-- check if the database master key is encrypted by the service master key.
SELECT is_master_key_encrypted_by_server ,*
FROM sys.databases
WHERE name='master'
select name, principal_id, algorithm_desc, create_date
from master.sys.symmetric_keys
where name like '%databasemaster%'
DROP MASTER KEY
--------------------------------------------------------------------------------------------------------
-- DROP ENDPOINT
--------------------------------------------------------------------------------------------------------
DROP ENDPOINT MirroringEndPoint
SELECT name, role_desc, state_desc
FROM sys.database_mirroring_endpoints
--------------------------------------------------------------------------------------------------------
-- DROP CERTIFICATE
--------------------------------------------------------------------------------------------------------
DROP CERTIFICATE PrincipalServerCertificate
DROP CERTIFICATE MirrorServerCertificate
SELECT *
FROM sys.certificates
-------------------------------------------------------------------------------------------------------
-- DROP USER
-------------------------------------------------------------------------------------------------------
DROP USER MirrorUser
SELECT * FROM sys.sysusers;
--------------------------------------------------------------------------------------------------------
-- DROP LOGIN
-------------------------------------------------------------------------------------------------------
DROP LOGIN MirrorLogin
SELECT * FROM sys.server_principals
*/
For more information see following links
http://technet.microsoft.com/en-us/library/ms191477.aspx
requested to specify the fully qualified domain names (FQDN) of the servers.
What about the cases when you need to configure the Database Mirroring
between two servers that not in a domain at all or the 2 servers on different
domains with no trust relationships or one server in domain and
the second is not. The setup of a DRP site whould be classic example.
You can enter IP addresses in the GUI wizard or
You can enter IP addresses in the GUI wizard or
replace the IPs with some Server names that you put in the HOST file
it will work only if both servers in the same domain, in other cases you will
probably fail to setup the Mirroring, because SQL Server will try to use
Windows Authentication between the servers (By default the Configure
Database Mirroring Security Wizard always uses Windows Authentication)
You will get an error like this:
The server network address“TCP://SQLServer:5023″ cannot be reached or
does not exist.Check the network address name and that the ports for the local
and remote endpoints are operational.(Microsoft SQL Server, Error: 1418)
So how to setup the Database Mirroring when the two Servers not in the same
domain or the two servers are workgroups or one server in a domain and other not?
Well, SQL Server allows configuring database mirroring under mixed mode
authentication using SQL Server logins with the added security of using certificates.
In English, you will have to create SQL Server Logins/Database Users and
the Mirroring EndPoint with authentication that use Certificate and not
Windows Authentication.
the witness server, please refer to links below if your configuration includes
witness server, in general all the steps for Principal/Mirror server should be
done also for the witness server.
Here is the highlight summary of the steps:
All the steps below should be done in each server instance(Principal/Mirror)
SQL scripts for each step you can find at the end,
and I suppose the you already restored the DB (and transaction logs, if needed)
in Mirror SQL Server with NORECOVEY of course.
1.In the master database, create a database master key.
2.In the master database, create an encrypted certificate.
3.Create a mirroring endpoint using the created certificate.
4.Back up the certificate to a file and copy it to the other server.
After you complete the steps above in both servers, do the following
also in both servers in master database:
5.Create a SQL Server login in each Server
6.Create a database user for created login.
7.Associate the certificate with the database user created in step 6.
8.Grant CONNECT permission on the SQL Server login for created
mirroring endpoint.
Now let`s see the SQL Scripts,
note that the scripts have different numeration then general steps above.
-------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance */
-------------------------------------------------------------------------------------------------------
-- Step 1 :
-- CREATE MASTER KEY,CREATE CERTIFICATE
-- CREATE ENDPOINT,BACKUP CERTIFICATE to file
---------------------------------------------------------------------------------
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE PrincipalServerCertificate
WITH SUBJECT = 'PrincipalServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
--Create the database mirroring endpoint for the principal server using the certificate for authentication
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE PrincipalServerCertificate
,ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE PrincipalServerCertificate
TO FILE = 'c:\PrincipalServerCertificate.cer'
GO
--------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Mirror Instance */
---------------------------------------------------------------------------------------------------------------------------
-- Step 2 :
-- CREATE MASTER KEY,CREATE CERTIFICATE,
-- CREATE ENDPOINT,BACKUP CERTIFICATE to file
----------------------------------------------------------------------------------
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE MirrorServerCertificate
WITH SUBJECT = 'MirrorServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE MirrorServerCertificate TO FILE = 'c:\MirrorServerCertificate.cer';
GO
------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance.
The MirrorServerCertificate.cer needs to be copied on the Principal Server.
*/
-------------------------------------------------------------------------------------------------------------------------
-- Step 3 :
-- CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Mirror server)
-- and grant the created User to it.
------------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN MirrorLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER MirrorUser FOR LOGIN MirrorLogin
GO
CREATE CERTIFICATE MirrorServerCertificate AUTHORIZATION MirrorUser
FROM FILE ='c:\MirrorServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]
GO
/* Execute this against the Mirror Instance.
The PrincipalServerCertificate.cer needs to be copied on the Mirror Server.
*/
----------------------------------------------------------------------------------------------------------------------
-- Step 4 :
-- CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Principal server)
-- and grant the created User to it.
----------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN PrincipalLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER PrincipalUser FOR LOGIN PrincipalLogin
GO
CREATE CERTIFICATE PrincipalServerCertificate AUTHORIZATION PrincipalUser
FROM FILE = 'c:\PrincipalServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [PrincipalLogin]
GO
/* Execute this against the Mirror Instance.*/
------------------------------------------------------------------------------------------------
-- Step 5 :
-- Prepare the mirror server for the database mirroring session
------------------------------------------------------------------------------------------------
ALTER DATABASE TESTDB
SET PARTNER = 'TCP://PrincipalServerIP5022'
GO
/* Execute this against the Principal Instance.*/
-------------------------------------------------------------------------------------------------
-- Step 6 :
-- Prepare the Principal server for the database mirroring session
-------------------------------------------------------------------------------------------------
ALTER DATABASE TestDB
SET PARTNER = 'TCP://MirrorServerIP:5023'
GO
-- Set the Mirroring to Asynchronous mode (if needed)
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
GO
That`s all after Step 6 the mirroring shoul be initialiazed.
Troubleshooting:
1) Ensure the Principal and Mirror Server listening to defined mirroring endpoints.
They should be listening after you define the endpoints and their state is Started.
You can use the following command in Principal Server
netstat -na | find "5022"
and this in Mirror Server
netstat -na | find "5023"
Of course, you can set the port numbers to be different then in this example.
2) Make sure firewall is not blocking the port and the firewall is allowing traffic both directions
3) Check the SQL log for errors
Mirroring Monitoring problems:
In our case, which is, Principal Server in a Domain and the Mirror Server
is not, when launching the "Database Mirroring Monitor" we had the following error:
If the Monitor was executed in the Principal Server, then there was an error
saying that the Mirror Server not connected. And vice versa when the Monitor
was launched from Mirror Server.
Going to "Manage Server Instance Connections" option
and choosing SQL Authentication in the connection of Mirror Server not helped,
it gave the following errors
or
"SQL Server replication requires the actual server name to make a connection to
the server. Connections through a server alias, IP address, or any other alternate
name are not supported."
It seems that SQL Server tries to connect to the Mirror server by using
the Server Name and not IP.
Adding the mapping to HOST file solved the problem.
---------------------------------------------------------------------------------------
To see how to monitor the DB Mirroring from every angle,
please my post here.
---------------------------------------------------------------------------------------
Also, I want to share the Clean up scripts,
they are needed when the mirroring is not working and you want to start
over again or if you are not sure in each step you stopped and etc.
-----------------------------------------------------------------------
-- Clean up / check scrips for the PRINCIPAL/MIRROR SQL Server
-----------------------------------------------------------------------
/*
------------------------------------------------------------------------------------------------------------
Drop master key
----------------------------------------------------------------------------------------------------------
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
-- check if the database master key is encrypted by the service master key.
SELECT is_master_key_encrypted_by_server ,*
FROM sys.databases
WHERE name='master'
select name, principal_id, algorithm_desc, create_date
from master.sys.symmetric_keys
where name like '%databasemaster%'
DROP MASTER KEY
--------------------------------------------------------------------------------------------------------
-- DROP ENDPOINT
--------------------------------------------------------------------------------------------------------
DROP ENDPOINT MirroringEndPoint
SELECT name, role_desc, state_desc
FROM sys.database_mirroring_endpoints
--------------------------------------------------------------------------------------------------------
-- DROP CERTIFICATE
--------------------------------------------------------------------------------------------------------
DROP CERTIFICATE PrincipalServerCertificate
DROP CERTIFICATE MirrorServerCertificate
SELECT *
FROM sys.certificates
-------------------------------------------------------------------------------------------------------
-- DROP USER
-------------------------------------------------------------------------------------------------------
DROP USER MirrorUser
SELECT * FROM sys.sysusers;
--------------------------------------------------------------------------------------------------------
-- DROP LOGIN
-------------------------------------------------------------------------------------------------------
DROP LOGIN MirrorLogin
SELECT * FROM sys.server_principals
*/
For more information see following links
http://technet.microsoft.com/en-us/library/ms191477.aspx