Smoking SQL Mirror

The Rant

I had someone vehemently insist on a conference call that you could not mirror more than one user database.  Although that alone is worthy of a blog post to clear up the misconception (I’ll explain the confusion in a minute), what I really wanted to talk about is making sure you know the facts before you speak about something you don’t know that much about.  The team I work on is not a SQL DBA team, but more of a development team that supports Active Directory Architecture, Identity Management (FIM), and a custom application to fill in the management blanks that Microsoft left in Active Directory.  I am the only DBA on the team and I have no problem with people asking me SQL questions, in fact I encourage it.  However, would you insist on being correct about something for which someone else on your team is responsible?  Now I’m not saying I am the ultimate “SQLinator”, but I’m certainly no Doofenschmirtz.

There is nothing wrong with being wrong, that’s how we learn.  On the flip side, if you’re not 100% sure then either ask, research it, or respond in a meeting by posing it as a question rather than a statement.  It’s better to say, “I thought you couldn’t mirror two databases on the same server, is that not the case?” than it is to say “You cannot mirror two databases, SQL does not allow it.”  Remember to be professional if someone contradicts you and simply suggest that some research is necessary to verify the correct method.  We are all learning all the time so if you’re not sure then do not insist.  Err on the side of caution and make sure you have the facts before you look like a Doofenschmirtz.

The Technical

Okay I know some of you just skipped to this paragraph after reading the second sentence, but you should really back up for an important lesson in professionalism.  I digress and on to the good stuff.  This individual had stated that you could not mirror more than one user database on an instance.  The truth is that you can mirror more than one user database on an instance.  What you cannot do is have two mirroring sessions for a single DB.  This means that you can mirror every database on an instance to any other instance you want, but you cannot mirror a single database to two different instances.  An example is that you cannot mirror ServerADB1 to both ServerB and ServerC.  If you have a need to mirror a DB to two separate servers or instances you can setup a mirroring session to one instance and log shipping to the other.