Introduction to Database Mirroring in Microsoft SQL Server 2008 High Availability

This is the complete guide to keeping your SQL server applications up and running at all times. You’ll learn how to minimize downtime, speed up recovery, and achieve the highest levels of availability and reliability.

 

Microsoft SQL Server 2008 High Availability

Microsoft SQL Server 2008 High Availability

Minimize downtime, speed up recovery, and achieve the highest level of availability and reliability for SQL server applications by mastering the concepts of database mirroring,log shipping,clustering, and replication

  •  Install various SQL Server High Availability options in a step-by-step manner
  •  A guide to SQL Server High Availability for DBA aspirants, proficient developers and system administrators
  •  Learn the pre and post installation concepts and common issues you come across while working on SQL Server High Availability
  •  Tips to enhance performance with SQL Server High Availability
  •  External references for further study

 

        Read more about this book      

(For more resources on this subject, see here.)

Introduction

Mr. Young, who is the Principal DBA in XY Incorporation, a large manufacturing company, was asked to come up with a solution that could serve his company's needs to make a database server highly available, without a manual or minimal human intervention. He was also asked to keep in mind the limited budget the company has for the financial year.

After careful research, he has come up with an idea to go with Database Mirroring as it provides an option of Automatic Failover—a cost effective solution. He has prepared a technical document for the management and peers, in order to make them understand how it works, based on tests he performed on virtual test servers.

What is Database Mirroring

Database Mirroring is an option that can be used to cater to the business need, in order to increase the availability of SQL Server database as standby, for it to be used as an alternate production server in the case of any emergency. As its name suggests, mirroring stands for making an exact copy of the data. Mirroring can be done onto a disk, website, or somewhere else.

Similarly, Microsoft has introduced Database Mirroring with the launch of SQL Server 2005 post SP1, which performs the same function—making an exact copy of the database between two physically separate database servers. As Mirroring is a database-wide feature, it can be implemented per database instead of implementing it server wide.

Disk Mirroring is a technology wherein data is stored on physically separate but identical hard disks at the same time called hardware array disk 1 or RAID 1.

Different components of the Database Mirroring

To install Database Mirroring, there are three components that are required. They are as follows:

  • Principal Server: This is the database server that will send out the data to the participant server (we'll call it secondary/standby server) in the form of transactions.
  • Secondary Server: This is the database server that receives all the transactions that are sent by the Principal Server in order to keep the database identical.
  • Witness Server (optional): This server will continuously monitor the Principal and Secondary Server and will enable automatic failover. This is an optional server.

To have the automatic failover feature, the Principal Server should be in the synchronous mode.

How Database Mirroring works

In Database Mirroring, every server is a known partner and they complement each other as Principal and Mirror. There will be only one Principal and only one Mirror at any given time.

In reality, DML operations that are performed on the Principal Server are all re-performed at the Mirror server. As we all know, the data is written into the Log Buffer before it is written into data pages. Database Mirroring sends data that is written into Principal Server's Log Buffer simultaneously to the Mirror database. All these transactions are sent in a sequential manner and as quickly as possible.

There are two different operating modes at which Database Mirroring operates—asynchronous and synchronous.

Asynchronous a.k.a. High Performance mode

The transactions are sent to the Secondary Server as soon as they are written into the Log Buffer. In this mode of operation, the data is first committed at the Principal Server before it actually is written into the Log Buffer of the Secondary Server. Hence this mode is called High Performance mode, but at the same time, it introduces a chance of data loss.

Synchronous a.k.a. High Safety mode

The transactions are sent to the Secondary Server as soon as they are written to the Log Buffer. These transactions are then committed simultaneously at both the ends.

Prerequisites

Let's now have a look at the prerequisite to have Database Mirroring in place. Please be cautious with the prerequisites, as a single missed requisite would result in a failure in installation.

  • Recovery Mode: To implement Database Mirroring, the database should be in the Full Recovery mode.
  • Initialization of database: The database on which to install Database Mirroring should be present in the mirror database. To achieve this, we can restore the most recent backup, followed by the transaction log with the NORECOVERY option.
  • Database name: Ensure that the database name is the same for both, the principal as well as the mirror database.
  • Compatibility: Ensure that the partner servers are on the same edition of the SQL Server. Database Mirroring is supported by the Standard and Enterprise edition.
  • Synchronous mode with Automatic failover is an Enterprise-only feature.

  • Disk space: Ensure that we have ample space available for the mirror database.
  • Service accounts: Ensure that the service accounts that we have used are domain accounts and they have the required permission, that is, CONNECT permission to the endpoints.
  • Listener port: These are TCP ports on which a Database Mirroring session is established between the Principal and Mirror server.
  • Endpoints: These are the objects that are dedicated to Database Mirroring and enable the SQL Server to communicate over the network.
  • Authentication: While both the Principal and Mirror servers talk to each other, they should authenticate each other. For this, the accounts that we use—local accounts or domain accounts—should have login and send message permissions. If the accounts we use are using local logins as service accounts, we must use Certificates to authenticate a connection request.
        Read more about this book      

(For more resources on this subject, see here.)

Configuration using T-SQL

We have seen how to install Database Mirroring using the wizard. It's now time to see Database Mirroring installation using T-SQL code.

  1. The first step is to create an endpoint and a listener port on the Principal Server.
  2. The next step is to create the endpoint and listener port on the Mirror instance. To do so, execute the following code on the Mirror server:
  3. We will now create a partner for the database that we want to be part of the Database Mirroring setup. Execute the code shown in the next screenshot into the Mirror server to establish the Mirroring session:
  4. In the next step, we will create a partnership for the Mirror server, by executing the code shown in the following screenshot:
  5. To failover using the T-SQL command, execute the following command, and it will initiate failover:

    Microsoft SQL Server 2008 High Availability

Automatic failover is an enterprise-specific feature and will be available when we have included the Witness Server. The Witness Server does the work of monitoring the availability of the Principal Server, and as soon as it doesn't receive an acknowledgement, it initiates the failover process.

Monitoring the Database Mirroring status using Database Mirroring Monitor

While using Database Mirroring, we can use Database Mirroring Monitor periodically to see how it is performing. Here is how we do it:

Right-click on the database we have mirrored and select Tasks | Launch Database Mirror Monitor.

This brings the mirroring monitor, which gives information on unsent or unrestored logs (if any) along with the oldest unsent transaction information.

I have executed the UPDATE command and they have been recorded here; check the highlighted area.

Configuring the threshold for Database Mirroring

It is always easy to troubleshoot if we get alerts for various errors or warnings, and Database Mirror is no exception. Let's see how to configure the threshold for the Database Mirroring.

  1. On the Database Mirroring Monitor screen, select the Warnings tab and then click on the Set Thresholds... button at the bottom-right side.
  2. On the Set Warning Thresholds screen, check and enable the warning we want to observe and click on the Threshold at listbox to alter the value. Once we are done with the altering of the value, click OK to exit.
  3. If we want to receive an alert using an alternate way, we can use the native Alert feature of SSMS. To configure Alert for database mirroring:
    1. Expand the SQL Server Agent.
    2. Expand the Alerts node.
    3. Right-click on Alerts and select New Alert.
    4. Enter the name of the alert.
    5. Mention the type of alert as SQL Server Performance condition alert.
    6. Select the object in the form Instance Name: Database Mirroring. In our case, it will be MSSQL$SQL2008R2: Database Mirroring.
    7. Select Transaction Delay as the value for Counter; we may define any other object as per requirement.
    8. Select the database name as the value for Instance.
  4. Set the counter value. The default value is 10 (as shown in the screenshot), and we can change this value based on the environment and requirement.
  5. Microsoft SQL Server 2008 High Availability

  6. Click on the Response option in the left-hand corner, which will bring the screen shown in the following screenshot. Here, we have to select the operator to send a notification to, along with a means of notification; we have selected to notify the operator via e-mail. Click OK.
    Alternatively, we may execute same job by checking the Execute job option.

Microsoft SQL Server 2008 High Availability

        Read more about this book      

(For more resources on this subject, see here.)

Summary

In this article, we learned what database mirroring is, how it works, and what are the prerequisites and how to configure it using T-SQL.


Further resources on this subject:


Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free