Microsoft SQL Server 2008 High Availability: Installing Database Mirroring


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


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

        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.

        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:

    You've been reading an excerpt of:

    Microsoft SQL Server 2008 High Availability

    Explore Title