Saturday, June 25, 2011

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
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
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.

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

19 comments:

  1. You forgot about the name of the certificate in step two:

    CREATE CERTIFICATE MirrorServerCertificate

    ReplyDelete
  2. Anyway good tutorial, not knowing anything about
    SQL Server 2008 after reading this and spending two days on research I managed to do the mirroring. Thanks man

    ReplyDelete
  3. awesome article!!

    Helped me to setup mirroring between the servers present in workgroups :):)

    ReplyDelete
  4. One of the best article out there on web. Very clear and worked perfectly for non domain accounts. Thank you very much. You saved me.

    ReplyDelete
  5. Thank you very much man...u are the best...your article help me a lot...
    Thanks again :)

    ReplyDelete
  6. I never post online except in very special circumstances. This happens to be one of those. I can't even begin to thank you enough for posting this *thorough* and detailed step-by-step. From start to finish it nailed exactly what I needed to get my mirroring task completed across the web on different domains.

    THANK YOU SO MUCH!! Seriously Thank you. Thank you thank you thank you!!!!!!!!!!!!!! If you had a 'DONATE' button on your site, I'd be buying your beer for a year.

    Thank you again!

    ReplyDelete
  7. It is great to see that this post helps people.
    Thank you guys for the comments

    ReplyDelete
  8. DanP,
    How we set up the database mirroring between two servers outside of domain (over internet)

    Please let me know what else to look at it.

    Thanks,

    ReplyDelete
  9. Fantastic post! There are so many posts out there that make allusions to the difficulty of mirroring in a non-domain joined context, but none that tell you how to actually do it! Thanks so much!

    ReplyDelete
  10. Hi, really good posts...
    I need help on this:
    "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."
    I changed the host

    like xxx.xxx.xxx.xxx MYSERVER\INSTANCE

    but it doesnt work (monitor) Any help?

    ReplyDelete
  11. A Very Very Good Article...
    Great..

    ReplyDelete
  12. Hi. Thank you so much for this post as it saved me endless hours trying to figure out why it wouldn't work. Just would like to know how could you include a witness server into this configuration? Problem is that I'm running server 2008 r2 STD which does not support automatic failover if you do not use a witness server. Once again thanks

    ReplyDelete
  13. Is is possible to create mirror Database snapshot from mirror server.Mirroring between a Server in Domain and Server outside domain (workgroup)

    ReplyDelete
    Replies
    1. You can create DB snapshot from the Mirrored DB
      (and you can access it, because the mirrored DB is in Restoring state)

      Delete
  14. I need query for creation of db snapshot from the mirror DB.please send me.

    Thanks in advance...

    Shani pandey

    ReplyDelete
  15. sql database endpoint mirroring is working fine.I want to create mirror database snapshot.please help me
    Principal server version
    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
    Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition
    (64-bit) on Windows NT 6.1 (Build 7600: )
    -------------------------------
    Mirror server version
    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
    Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition
    on Windows NT 6.0 (Build 6002: Service Pack 2)
    QUERY
    -------------------------------------------------
    CREATE DATABASE TEST ON
    (
    NAME = N'ABC',
    FILENAME = N'C:\andrew_ test_snapshot_2502061800.snap'
    )
    AS SNAPSHOT OF ABC;
    -------------------------------------------
    ERROR
    Msg 946, Level 14, State 1, Line 1
    Cannot open database 'TEST1' version 655. Upgrade the database to the latest version.
    Msg 1823, Level 16, State 2, Line 1
    A database snapshot cannot be created because it failed to start.

    Shani Pandey

    ReplyDelete
  16. THANK YOU BRO!!!!!!
    YOUR TUTORIAL WAS SO AMAZING!!!!!

    ReplyDelete
  17. Hi All

    If you keep hitting problems to try add your witness instance make sure you specify not just the FQDN in your hosts file but also the machine name like below

    192.168.0.1 servername
    192.168.0.1 servername.domainname.com

    This did the trick for me

    ReplyDelete