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

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

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.

1. As we have a full database and log backup file, move them over to the Mirror server that we have identified.
2. 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. 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.

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

1. 1. In the next dialog box, connect to the Principal Server. Specify the Listener Port and Endpoint name, and click Next.

1. 1. We are now asked to configure the property for the Mirror Server, Listener port, and Endpoint name.

1. 1. In this step, the installation wizard asks us to specify the service account that will be used by the Database Mirroring operation.

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.

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

   

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