The below is an extract from my presentation at SQL Saturday 252 on high availability and disaster recovery. This post covers the basics of SQL Server Mirroring.
Like log shipping, mirroring is something that you are going to set up at the database level. That means that if you change anything outside the database on the SQL instance, you are going to need to make the change on both sides of the equation. So if you add a login on the principal then you need to manually make the same change on the mirror. Incidentally you’ll notice there’s a fresh set of jargon with each new technology. Logshipping has it’s primaries and secondaries. Clustering has it’s active and passive nodes. Mirroring has it’s principals and mirrors and replication has it’s publishers and subscribers. Availability groups continue the pattern with primary and secondary replicas.
But anyway – we’re talking about mirroring. So what mirroring is doing is redo-ing every insert, update or delete that happens on the principal server on another server. So you are increasing your availability by effectively having two copies of the data which are pretty close to exactly the same. I say pretty close because there’s a couple of ways you can set this up.
Firstly you can set it up as a synchronous mirror. With a synchronous mirror every transaction that happens on the principal is written into the log on the principal, then moved over to the mirror, written into the log and committed on the mirror, then it sends an acknowledgement back to the principal to say the transaction is committed and only then does it actually commit the transaction and notify the application the transaction is successful. It’s also known as High-Safety because your mirror is always going to hold all the data that the principal has….sometimes more infact.
Now if you are mirroring in high safety mode you can also add another server into the mix here – a witness server. When you have a witness and your mirroring is configured in high safety mode you have a situation where automatic failover is available. So, if something goes wrong on the principal, it’s automatically going to fail over to the mirror. The principal now becomes the mirror and the mirror the principal.
That’s cool from the database point of view, and it’s nice to see that happen….when it’s meant to…and being able to think what a clever chap you are for keeping the data available, but it’s not like clustering, you aren’t pointing to a virtual server name that just knows where the actual data is sitting. You need to make sure the applications connecting to the database have failover connection strings, so they know that if the principal server isn’t there, then they can try to get the data from a different server. You also need to be pretty sure that all your logins and any agent jobs, maintenance plans and backups can all handle the state of the database. So does your backup check to see if the database is online before it kicks off, or does it just fail?
Now, all that is available in the standard edition of SQL Server, and gives you a nice high availability option. But if you spend your pennies on the Enterprise version….you can also have Asynchronous mirroring. What does that do? Well basically the same thing, it’s just that instead of committing on the mirror before it will commit on the principal, SQL just goes ahead and writes your transaction locally then fires off a copy of it to the mirror. It doesn’t care if you have committed to a mirror, it just wants to get on with writing things locally and returning responses to the application. You don’t have a witness involved and there is no automatic failover. So….it seems like this is worse right? You are paying Enterprise licensing for something that doesn’t give you an equal level of availability? That seems like a pretty poor deal, but what you do buy is….performance. Because there is no latency on the write across the network your application is going to perform much much faster. And that’s why the other name for this is High-Performance mode.
There’s also some nice bonuses to mirroring. In 2008 and higher….and we are all on 2008 or higher right?…..you have Automatic page repair. So if there’s an attempt to read a page on the principal and it finds that page is bad it will go over to the mirror and try and get a clean copy of the page and fix the principal then send that info back to the application.
Also introduced in 2008 the data stream was compressed which lowers the amount of data being sent both ways, and as a result lowers the latency of the transaction.
And you can report off the mirrored databases. CAN being the important word here. You do this using snapshots on the mirror side and snapshots of course require enterprise edition. It’s got some serious drawbacks around the point in time nature of the reporting, the database needing to be synchronised before you can do the snapshot. I’m not going to talk about it too much more because I’ve never seen an implementation of it I’d be happy to call a reporting solution.
On the minus side, there’s implications to doing this in the SQL log and you need to do some planning around log sizes and physical structure. And there’s network issues, particularly around High Safety. You are sending a lot of information across the network and you want it to be pretty close to realtime. If you are using High-Safety then you aren’t going to get a response to your application until it’s committed on the mirror as well as the principal, so if there’s lag, be it from limited hardware or just purely from volume of data being transferred, there’s a relatively high chance of a time out. This can be mitigated by having good hardware, good network speeds and applications having good code. If your lucky you may even be able to influence one of those things.
And an important point – Mirroring is deprecated. That’s different from discontinued – it’s still there, it’s just on the chopping block. In a future version it will be discontinued and we will be using Availability groups instead.