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