How to Configure Database Mirroring for SQL Server

1. What is SQL Server Database Mirroring?

Accessibility of data at 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 is 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 which 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 is 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

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. 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.

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 do I 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 we have to set up some of the basic things. So, now we will be going to configure Mirroring in the next steps.

In the Primary Server, Perform a right-click action on the database and select Task > Mirror.
Once you click this option, you will see a popup as shown in the image. You can also open this popup by Properties and then cab mirror the page.

 

By clicking on the Configure Security button, you will see a new Configure Database Mirroring Security Wizard.Note: It will show some information related to Mirroring. For not showing up on the starting page repetitively you can check on the checkbox shown on the screen to skip this page. Click on the Next button, if this page shows up again otherwise click on the continue option to go ahead with the further process.

In the next step, it will ask you to configure the witness server which is used when we have asynchronous transfer with automatic failover. As we are implementing basic mirroring, we can skip that step by click on No and then Next as shown in the image.

At the next step. Now it will ask for some additional information related to the Principal server. In this step, we will be able to create the endpoint (Communication medium used by principal and mirror servers) using two SQL server instance that can easily communicate with each other over a single network. The endpoint name is Mirroring and Listener Port is 5022 Information which is auto-populated so that you can cross-check and click on the Next button as shown in the image.

The Next step is to configure this Mirror Server Instance. Follow the steps shown below image.

Click on the Connect button and connect to the Mirror server for instance. It asks for the Mirror server credentials. Provide the credentials and then connect. It will auto-populate information into the Listener Port and Endpoint name. Click on Next after that.

The Next Screen for you is to provide Service Account Details. If this server instance uses the same domain then you can leave this screen blank and click on the Next/Finish.

If everything goes fine, then you will see the screen as shown below which summarizes the details of the Mirroring. Here we will get information on the action performed. One is configuring the endpoint on the principal server and another is configuring the endpoint on the mirror server. If it is showing Success at the top which means you have configured mirroring correctly. Click on the Close button and continue.

Once you click on the Close button from the above screen you will get prompted to ask whether to start mirroring or not. You can click on the Start Mirroring button. You can click Do Not Start Mirroring when you want to choose the different operating modules as mentioned above. You can directly click on the Start Mirroring also. In this popup, you will get information about the Principal network address, Mirror network address and Operating mode.

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.