How to Configure Database Mirroring for SQL Server

1. What is SQL Server Database Mirroring?

Accessibility of data at one’s fingertips is what every business urges for. Faster access to data is only possible if businesses build a high-performing and advanced platform that fetches the data faster and accelerates the overall process. For all data fetched by using SQL, there are continuous upgrades seen in the field. SQL mirroring is one such advancement that involves two SQL servers that need to be accessed either on the same machines or different. Out of the two machines, one of them is a primary machine and the other one has a mirrored SQL server instance of the primary one. Sometimes, there is one and sometimes there is more than one SQL Server that is working as a witness server.

The main objective of SQL Server Database Mirroring is to make SQL Server available for the user, in a case when the main database server is down or in any other emergency where Software developers have to make the Database Server down. It can be implemented on the Database level but remember the database must be in a fully-recovery model.

Implementation Diagram

The above image shows SQL Mirroring implementations performed on two different servers. When DB-1 on Server-1 is Principal and DB-2 on Server-2 are mirrored and Server -1 gets down- which is the principal server goes down then it makes DB-2 of Server -2 is made mirrored of Principal Database.

In the above Image SQL Mirroring implementation is done on a single server. The only limitation of this implementation is if Server -1 (primary server) gets down then both databases are not accessible.

2. Different Operating Modes of SQL Mirroring

Below are some of the various operating modes of SQL mirroring.

2.1 High Safety

Principal and Mirror databases are in sync with each other. If you apply to commit to any one of the databases then its applications including all its activities will continue as it is, there won’t be any change in operations or functioning. It reduces the processing speed of the transaction as it chooses the nearest server to fetch data and that can be anyone- either primary or mirrored. It is highly secure as one needs to complete any given task on both servers so there cannot be conflicts.

2.2 High Safety with Automatic Failover

It works the same as above but the main objective of this is whenever the principal database gets failover then the mirrored database becomes a principal database. It reduces processing speed because the transactions must be completed on both servers.

2.3 High Performance

In the above two modes, synchronous operations are occurring while in this mode we are having an asynchronous operation. In this mode, data is initially written/committed on the Principal database and then it is transferred to the mirror database. You will get this mode on SQL Server Enterprise Edition.

3. How to Set up Database Mirroring?

Let’s understand this clearly with an example. To start with let’s create two servers. As we know, one will be primary and another one will be the mirror server. For more understanding, we will consider the primary server name as pca189\SQL2014dev and the mirrored server name as pct91\SQL2014. The SQL server database mirroring setup needs to be initiated from the main primary server using the SQL server management studio wizard.

1. Connect to the Primary Server and create a database named TestMirror. If you are creating a mirror from the existing database, then it is advisable to take a backup of the whole system initially. We need to use that backup on the mirror server. Now, restore that on the mirror server with No Recovery option. Once you are done with this, then your database will look something as shown in the image.
   

Now that you are done with the mirroring configuration. You will now notice that the principal Database is showing Principal, Synchronized data in the bracket besides database name and on the mirror server Mirror, Synchronized / Restoring. This is nothing but it provides you information on the type of database stating a clear difference between the Principal database and the Mirrored.

You can also manually change the principal database to mirror by simply following the below steps.

1. Right-click on the Principal Database, and select Task > Mirror. You can also open this popup by Properties and then the mirroring page.
2. On this page, you can see there is a button named Failover. Click on that button.
3. It will Failover the existing database and make the Mirror database a Primary database.

4. What are the Benefits of SQL Mirroring?

1. Database Availability
   In case of any emergency or any unexpected situation, the primary aim is to secure data and the important database. There shouldn’t be any impact on the overall operations and thus for such situations, the mirrored database is used with a fundamental aim to prevent failover using the set of two databases.
2. Data Protection
   Using this, data redundancy can be easily addressed and higher safety and higher performance can be expected depending on this operating model. Mirroring your managed database will automatically resolve and prevent the capturing of data from pages.
3. Database Availability when Upgrading
   Aiding to this, users can reduce downtime of the mirrored database. Just upgrade an instance of the SQL server in a sequence of hosting your failover partner. It will reduce the possibility of downtime or failure.

5. Conclusion

Here in this blog, we learned how SQL Mirroring works and how it is one of the best techniques to avoid database failure and increase database availability. With this technique, we now know that whenever an emergency or unfortunate incident in database happens,  mirroring helps you in this cases when the main server is down or any situation that makes the database server down. As a fast-growing software development company, the data-related environment should be robust to stay ahead in the competition of the market. Also, it’s a good choice to keep the website up at any time with a focus on managing essential data meticulously.