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.




About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer, Windows and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s