This blog is intended to give you a short introduction to Database mirroring. Database mirroring is a high availability functionality, that are easy to setup, reliable and has two features that I like:
- It makes it easy for developers to create software against a mirrored database (I will explain that later).
- It has automatic failover.
Basically the question is: What is database mirroring?
Imagine that you have a database that are critical for you. It could be a paymentDB, and it is used by a website to track customers payments. If the database is down, then your customers can’t pay and therefore you loose money!!!!
Today a lot of important software and websites have a setup like this:
You have a website and a single point database that are running on a server. Problem is that if your paymentDB is offline for some reason, then your website does not work (and yes, I have seen that a lot of times, and when I talk to customers about that, then they always says that “our database is never offline”… Well!! I can guarantee you that, their database will be down sometimes).
So, what we want is a setup, that could look a little bit like this, where the webapplication is communicating with the RunningDB, but if that fails, the the database that are “Ready to take over”, becomes the runningDB.
That is the basics of Database mirroring, so lets see what to do.
First of all, you must at least have two instances of Microsoft SQL Server installed. They can be on the same server or on two different servers. There is a lot of benefits on having it on two servers, and basically, you are more protected and have a better chance for have a good uptime.
In this demo, I just have some instances on the same machine.
So to get started, then you must:
- Have at least two instances. If you want to have automatic failover from the one database to the other (from one instance to the other one, then you must also have an instance that becomes the witness).
- Have a database that meets the requirements for database mirroring.
So, lets try to see how that works in.
It is quite easy, but unfortunately there are some important things that you need to know, and the two most important are:
- What did I configure when I clicked Next –> Next –> Next… We talk about the endpoints.
- Operational modes. What does the three modes mean.
I have attached the video below, so that you also can get an understanding about that.
In survival kit #4, we talk a little bit more about security, because when you have all your instances running on the same server, then it is very easy. It becomes complicated when you want a more complex setup.