SQL Server Database Mirroring – A Perfect Solution for Disaster Recovery

May 11, 2015 / SQL

Disaster recovery! Makes you restless, right? What if it turns into reality with your business? Made your blank. No this isn’t said to scare you. In fact, this article is going to offer you a perfect solution to it. So, first, let’s start from the base which is database mirroring.

What is Database Mirroring?

Mirroring itself gives you an idea that it is related to database cloning or duplication. It is actually creating and maintaining redundant copies of a database. This is to confirm continuous data availability and avoid or minimize downtime. That might result in a fault in network operation. When partially compromised or data corruption and loss.

When there is redundancy, it ensured that there is at least one viable copy of a database available and accessible during system upgrades.

Why is it called SQL Server Database Mirroring?

Microsoft SQL Server, a relational database management system (RDBMS) uses database mirroring for maintaining the enterprise environment. The process involves two copies of a single database on the same or different computers termed server instances, usually located physically at a particular distance.

One SQL server acts as a primary server instance called the principal. That offers a database to clients and the mirror server instance called the mirror works as standby so that gets replaced with the primary one some problem. There can be a third SQL Server instance that acts as a witness, in special cases.

How is it implemented?

SQL Server database mirroring configured in two different ways –

One common mirroring configuration environment involves two SQL Servers, two instances, and one mirrored database named SQLDB-1.

SQL Server Mirroring_2 Servers

Other common configuration environment involves one SQL Server, two instances, and one mirrored database named SQLDB-1. This solution contains a major flaw since if SQLServer-1 goes down both instances will be unavailable.

SQL Server Mirroring_1 Servers

Now let’s focus on the benefits of SQL Server Database Mirroring –

A built-in SQL server feature.

Relatively easy to set up.

Can provide automatic failover in high safety mode.

Also, one significant advantage of database mirroring is that it can work with other disaster recovery options including log shipping, clustering, and replication.

According to the American proverb – “you have to take the good with the bad”, SQL server mirroring also has some disadvantages coming up with its benefits.

There is a possibility of the removal of database mirroring from SQL Server in future versions due to the rise of Always On Availability Groups. Also, there would be a requirement for the manual creation of logins and jobs from the principal SQL Server as database mirroring is per database-only solution.

At last, what said businesses need to be on their toes for disaster recovery solutions as disasters may result due to natural calamities, or technical issues or can done manually. No doubt, several other solutions for disaster recovery but SQL Server Mirroring can’t missed.