SQL: Database Mirroring over different domains

Soooooooo I’m back. I’ve never went away, actually. But time to write is always more rare.

Today I wanted to share a small post about Database Mirroring. It’s one of the simplest ways to have “High Availability” for your databases, but not exactly “Always On” – that’s a subject for a longer discussion.

The thing is: mirroring requires a lot less resources and lets you choose between automatic or manual failover. For smaller systems where humam intervention is required to do the fail-over, this is actually great. All you need is a second database server on the same network, and you are good-to-go.

Usually corporations will have one large single Domain (Active Directory) structure, but sometimes they don’t. In those cases, Mirroring comes to the rescue because it works with SQL Server Logins by using self-signed certificates to authenticate. By doing this you remove any requirement about the services, hostnames, access restrictions, etc – you just have two SQL servers talking directly to each other.

TechNet has a great article about this: http://technet.microsoft.com/en-us/library/ms191140.aspx .The summary is:

1. have your firewall open the required ports (read more about endpoints here);
2. make your database recovery model FULL and do a backup of it;
3. restore it with NORECOVERY at the secondary server (Mirror);
4. create the certificates at Principal and Mirror instances;
5. create the logins using those certificates;
6. set-up the partners and turn-on mirroring;

And that’s it. If you have a network with lots of restrictions, use the IP addresses of both servers to set-up the partners. If you don’t, you may receive weird messages when activating any of the partners, like this one:

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://Your_Server.Domain.com:7022" can not be reached or does not exist.

Wich would point you to this article. To read more about mirroring, go to this link. If you want to know more about High-Availability in general, read this one.

And why would I recommend doing Mirroring, instead of Clusters or Always-On availability groups? Because Clustering/Always-On has a LOT of requirements, and is way more complicated to implement. Log-Shipping is ancient, so Mirroring is the chosen one: stretching Occam’s Razor concept a bit, the simplest solution must be used.

Here is a simple breakdown of High-Availability options for SQL Server and the best use-case for each (in my humble opinion, of course).

1. Log shipping: use with SQL2000, also good if you want a copy of a database with a few minutes of delay compared to the principal. Manual fail-over only, different host/instance name, needs a shared network folder.
2. Mirroring: use with SQL2005+, when you want almost-instant updates to secondary, or when servers are not on the same domain. Both manual and automatic fail-over supported, different host/instance name. Only uses SQL connections.
3. Clustering: use with any SQL version, when servers have access to the same storage/SAN and you need to connect using always the same instance name. Server physical location is vulnerable. Needs Windows Clustering.
4. Always-On: use with SQL2012+, it’s a mix of Clustering+Mirroring when you need the highest availability and protection from disasters at the main server location. Needs a lot of stuff.

The key aspect here is how the servers talk to each other. The only method that uses only SQL connections is mirroring, so it is my favourite. Note that none of these options have any interference with virtualization: we are talking about OS/SQL levels only. You can have some virtualization technologies with high-availability, but that’s not in our scope today.

And this article ended up a lot bigger than my intention. Hope it helps someone out there.

cya!