Microsoft SQL Server 2008 High Availability: Installing Database Mirroring

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 learn how to install database mirroring.

This article covers:

  • Installing Database Mirroring
  • Starting Database Mirroring
  • Manual or forced failover
  • Adding the Witness Server

 

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

First let's briefly see 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.

Now let's move on to the topic of this article – installation of Database Mirroring.

Preparing for Database Mirroring

Before we move forward, we shall prepare the database for the Database Mirroring. Here are the steps:

  1. The first step is to ensure that the database is in Full Recovery mode. You can set the mode to “Full Recovery” using the following code:

  2. Execute the backup command, followed by the transaction log backup command, and move the backups to the server we wish to have as a mirror.
  3. I have run the RESTORE VERIFYONLY command after backup completes. This command ensures the validity of a backup file. It is recommended to always verify the backup.

  4. As we have a full database and log backup file, move them over to the Mirror server that we have identified.
  5. We will now perform the database restoration, followed by the restore log command with NORECOVERY.

It is necessary to use the NORECOVERY option so that additional log backups or transactions can be applied.

Installing Database Mirroring

As the database that we want to participate in the Database Mirroring is now ready, we can move on with the actual installation process.

  1. Right-click on the database we want to mirror and select Tasks | Mirror.....

  2. It will open the following screen. To start with the actual setup, click on the Configure Security... button.
  3. In this dialog box, select the No option as we are not including the Witness Server at this stage and will be performing this task later.
  4. In the next dialog box, connect to the Principal Server. Specify the Listener Port and Endpoint name, and click Next.
  5. We are now asked to configure the property for the Mirror Server, Listener port, and Endpoint name.
  6. In this step, the installation wizard asks us to specify the service account that will be used by the Database Mirroring operation.
  7. If a person is using local system account as a service account, he/she must use Certificates for authentication. Generally, these certificates are used by the websites to assure their users that the information is secured. Certificates are the digital documents that store digital signature or identity information of the holder for authenticity purpose. They ensure that every byte of information being sent over the internet/intranet/vpn, and stored at the server, is safe. Certificates are installed at the servers, either by obtaining them from the providers such as http://www.thwate.com or can be self-issued by Database Administrator or Chief Information Officer of the company using the httpcfg.exe utility. The same is true for SQL Server. SQL Server uses certificates to ensure that the information is secured and these certificates can be issued by self, using httpcfg.exe, or can be obtained from issuing authority.

  8. In the next dialog box, make sure that the configuration details we have furnished are valid. Ensure that the name of the Principal and Mirror Server, Endpoints, and port number are correct. Click Finish.

  9. Ensure that the setup wizard returns a success report at the end.

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

Starting Database Mirroring

Once we are done preparing for and installing Database Mirroring, we can start with the actual Database Mirroring. Please follow the steps, as mentioned here:

  1. As soon as the setup wizard finishes, we will be presented with the option to start mirroring. Please note that the Operating mode is set as High safety without automatic failover (synchronous).
  2. The transactions are written in the synchronous mode of operating, and they commit on Principal and Mirror at the same time.

  3. In the next step, we can change the operating mode of the Database Mirroring we have configured and/or we can pause or remove the mirroring, if we wish to do so for any reason.
  4. As we are done with the Mirroring installation, let's check the status of the databases on the Principal and Mirror server.
    In the following snapshot, the left-hand side shows the Principal Server; notice that the database status shows Principal, Synchronized. The right-hand side shows the Mirror server. Notice again that the database status is Restoring…., which means there are transactions that could be applied on the mirrored database, running in the single user mode.

Manual or forced failover

Until now, we have learned how to install Database Mirroring. Now let's see how to failover manually just to ensure that the installation went fine and is working properly.

  1. Right-click the database name we have configured for mirroring, and from the options, select Tasks | Mirror. This will bring the main configuration dialog box for Database Mirroring. Once there, click on the Failover button. It will then ask us to confirm whether or not we wish to proceed with failover; click Yes.
  2. At this stage, [local\SQL2008R2] is Principal server and [local\SQL2008LS] is the Mirror server.

  3. In the next screenshot, notice that the status of the database on the instance [local\SQL2008R2] has been changed to Mirror, Synchronized / Restoring…, whereas database status on the instance [local\SQL2K8LS] has been changed to Principal, Synchronized.

Adding the Witness Server

We have not included the Witness Server while we installed Database Mirroring, as we want it to be added later, once we finish with the installation. The reason was, what if we don't have a server that we can configure as a Witness Server while installing Database Mirroring? Can we add a Witness Server later? Well, the answer is Yes, and here is how we go about adding a Witness Server:

  1. Right-click on the database we have configured for Database Mirroring and select Tasks | Mirror. From the setup dialog box, click on configuration. It will ask us if we want to include the Witness Server; this time, select the Yes option and then click Next.

  2. In the next dialog box, choose the server to configure. Here, select Witness Server instance and click Next.

  3. As we have failed over, the Principal server instance is now [local\SQL2K8LS]. Click Next.

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

On the Mirror server, we have named the endpoint as Mirror when we installed Database Mirroring. So, do not get confused by these names as these names have been chosen for better understanding.

  • In the Witness Server Instance dialog box, specify the name of the Witness Server instance. As can be seen from the following screenshot, I have named the instance on my server as [local\Witness], which has the Endpoint name as WITNESS and it listens on port 5034.

  • In the Service Accounts dialog box, enter the service account names for the Principal, Mirror, and Witness servers. Ensure that this service account is a domain account and has the necessary permission, that is, the CONNECT permission for the endpoint database role. Click Next.

  • In the next dialog box, ensure that the information furnished while including the Witness Server is correct and click Finish to complete the wizard.

  • Summary

    After reading through this article, I am sure a reader will be very much clear about the installation procedure of Database Mirroring.


    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.
    5
    D
    8
    V
    T
    E
    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