Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Mirroring Across Domains, Using Certificates.....

Database Mirroring w/o a Domain, Can be a Pain - but it can be done......

Have you ever had to setup mirroring across domains, DR/Colo Sites, and/or over the Internet?  When the Principal and Mirrored servers are in the same network/domain, it always seems to go rather smoothly:

Set the database to be mirrored to FULL Recovery Mode, backup the database from ServerA, restore the database to ServerB with NO Recovery, right-click the database --> Tasks --> Mirror and click on <Confgure Security>, which will launch the, you guessed it - the 'Configure Database Security Wizard', (thanks Microsoft - what a wonderful wizard it is! because, because....never mind), follow the prompts and then click <Start Mirroring>.

That's sort of the steps in a nutshell, and is pretty straightforward.  For an excellent starter on Database Mirroring, please check out this excellent article on 'Database Mirroring Setup in SQL Server 2005' - http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004_mem1,00.html - you may need to sign up to access it.

However, as helpful as the above article is for setting up basic mirroring, you will run into some challenges if the Principal and the Mirror are in disperse geographic locations and if in different domains.

Not too long ago, I had just this requirement at a client, as I promised to implement some DR for their critical business server, and due to all the security entanglements across domains, it wasn't easy to figure out, until I found this awesome tutorial that was written, as stated, 'for people who are really really frustrated.'  That was me, and it is sure to solve the infamous error message:

Error: 1474, Severity: 16, State: 1

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

Another common error, is the diplomatic deed of doom:  'Connection Handshake Failed'.  The detailed description is in the tutorial, but you gotta love it!

It does however point you in the right direction, as it talks about not having 'Connect permission on the ENDPOINT'.  So, we need to properly configure the endpoints, and make sure that our handshake (H1N1 aside) succeeds across our domains.  And the way to do that is....Drum roll, please..........

USE CERTIFICATES!  Yes, that's the solution proposed in the tutorial.  You will need to copy the certificates to the servers manually,  Finally, you will need to abandon the wizard, and use good 'ole reliable TSQL Code applied to both sides of the sql servers.  It is included in the article, and there will be a lot of back and forth, so, go slow and steady.  I personally had some issess when using the default mirroring ports 5022 and 5023 - it didn't work for me, maybe due to a firewall issue, and throw the errors discussed above.  In the tutorial, as I have used successfully, I specify port 7024.  You will also as the usual prerequisites, create the logins and passwords for each node involved in the mirroring (Principal, Mirror and Witness).

Oh, let's not forget the magic link to this great tutorial: http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx

Enjoy and hope this helps you too!

----------------------------------------------------------------------------------------------------------

Want quality Remote DBA Services?  Want enterprise sql server monitoring?

Ask us for a FREE Report on the state of your SQL Server Infrastructure.

Try SQLCentric:http://www.pearlknows.com

Comments

Posted by Anonymous on 26 August 2009

Pingback from  Twitter Trackbacks for                 SQL Server Central, Database Mirroring Across Domains, Using Certificates..... - Pearl Knows         [sqlservercentral.com]        on Topsy.com

Posted by Steve Jones on 26 August 2009

Nice job, hadn't thuught about certificates, but it makes sense if you don't have a domain, or don't want one. However for most companies, I'd think you'd want a DC on the other end to DR your AD.

Posted by petertrast on 26 August 2009

One of the MANY reasons that I push techs to get certified is that this kind of information is in abundance when you go through the Self-Paced Microsoft books. There are so many scenarios that are covered in these books that you will usually have a solution for most situations without even having to scan the web for articles like these. But this IS one of my favorite real-world scenarios (having done it) and it is great that you shared it.

Cheers!

Peter Trast

Posted by Robert Davis on 26 August 2009

Why do you say this: "And, one other tip from me, DO NOT use the default mirroring ports 5022 and 5023 - it will not work, and throw the errors discussed above."?

I have used these port successfully with certificates many times.

Posted by Robert Pearl on 27 August 2009

Thank, Robert D. for your input.  This was my experience with SQL 2005. Since you wrote a book on the subject, I will defer to your expertise :-)  I updated my BLOG. - RP

Posted by steve smith-401573 on 20 August 2012

Robert Pearl, where did the magic link go?

Posted by r.armstrong-finnerty on 5 September 2013

Tutorial link needs checking.

Leave a Comment

Please register or log in to leave a comment.