Recently I needed to setup MS SQL mirroring. As usual there are plenty of guides attempting to describe the process, but inevitably none of them really cut it when you actually start implementing. I’ll try to clear up the process and hopefully provide some help to anyone out there wanting to implement MS SQL mirroring.
Why?
Do you have a database or databases? What do you do if they go down? Probably restore from your latest backup (which may or may not be very recent and may take hours to restore). Let’s say you have a scheduled backup that runs every night at 11pm; what if your database/server fails at 10:59pm? What now? SQL mirroring will keep an updated copy of your databases on another server that can easily be “turned on” in case your primary server/databases fail.
How?
The primary server is called the “principal” and the secondary server is called the “mirror.” The principal is your live database, and the mirror is your standby copy of the database. In addition to the principal and the mirror, you can introduce another optional server called the “witness.” The witness is a third instance of SQL Server that determines when to failover. This option is only used when you want to do an automatic failover. We chose to implement manual failover to avoid a situation of an unwanted failover.