Introduction to Database Mirroring in Microsoft SQL Server 2008 High Availability

Exclusive offer: get 50% off this eBook here
Microsoft SQL Server 2008 High Availability

Microsoft SQL Server 2008 High Availability — Save 50%

Minimize downtime, speed up recovery, and achieve the top level of high availability and reliability for Microsoft SQL Server applications with this book and eBook

$29.99    $15.00
by Hemantgiri S. Goswami | January 2011 | Enterprise Articles Microsoft

In this article, by Hemantgiri Goswami, author of Microsoft SQL Server 2008 High Availability, we will cover:

  • What is Database Mirroring
  • Different components of Database Mirroring
  • How Database Mirroring works
  • What are the prerequisites of Database Mirroring
  • Configuring Database Mirroring using T-SQL

 

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.
Microsoft SQL Server 2008 High Availability Minimize downtime, speed up recovery, and achieve the top level of high availability and reliability for Microsoft SQL Server applications with this book and eBook
Published: January 2011
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:
        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

Microsoft SQL Server 2008 High Availability Minimize downtime, speed up recovery, and achieve the top level of high availability and reliability for Microsoft SQL Server applications with this book and eBook
Published: January 2011
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:
        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:


About the Author :


Hemantgiri S. Goswami

Hemantgiri S. Goswami is a SQL Server consultant based in Surat, India. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of http://www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com

He has more than 11 years of experience in the IT industry, of which nine years have been as a hard core DBA focusing on High Availability area. During his career, he has worked with the top five IT giants of India. In addition to SQL Server, Hemantgiri also possesses excellent knowledge of Windows Server OS(s) and Networking.

In his free time, he prefers to watch cartoons, read and sometimes sketches.

You can reach him via his blog – http://www.sql-server-citation.com , twitter – http://www.twitter.com/ghemant or by writing to him at Hemantgiri [at] sql-server-citation.com

Books From Packt


Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Microsoft Dynamics NAV 2009 Application Design
Microsoft Dynamics NAV 2009 Application Design

Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 Business Application   Development: Beginner’s Guide
Microsoft Silverlight 4 Business Application Development: Beginner’s Guide

Microsoft Azure: Enterprise Application   Development
Microsoft Azure: Enterprise Application Development

Applied Architecture Patterns on the Microsoft   Platform
Applied Architecture Patterns on the Microsoft Platform

Microsoft Enterprise Library 5.0
Microsoft Enterprise Library 5.0

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
x
u
u
Z
z
U
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software