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