Book Image

Microsoft SQL Server 2008 High Availability

By : Hemantgiri S. Goswami
Book Image

Microsoft SQL Server 2008 High Availability

By: Hemantgiri S. Goswami

Overview of this book

<p>Every business has it's mission critical applications and data. Therefore, it is very important to keep database servers up and running all the time – 24 X 7. There is a need to store and process terabytes of data to cater for business needs, and it is vital to make data highly available.<br /><br />High availability is all about the site being accessible all the time. High availability solutions minimize the downtime for these mission critical applications.</p> <p>Microsoft SQL Server is a powerful relational database engine, widely used to store and maintain data in Enterprises of various levels be they small, medium or large.</p> <p>This book will teach you how best to use these readily-available options and save you time in making your websites highly available.<br /><br />This Microsoft SQL Server 2008 High Availability book will take you through pre and post installation concepts and common issues you come across while working with SQL Server HA. It will teach you how these various HA solutions can be installed using GUI and the command line. It will also show you how to troubleshoot common issues you may encounter whilst installing or managing the HA option in your environment. It provides references to external links for more advanced learning on the topic.<br /><br />This book starts with an introductory chapter into the windows domain, domain users and various handshake methods available with Windows server. It also offers information the different authentication methods available with SQL Server - giving you an insight into the importance of security. After you are through with the security aspects, your journey to installing SQL Server HA will start. It will cover the concepts of database mirroring, log shipping, clustering, and replication.<br /><br />By the end of this book you will be confident enough to take up the challenge to install any of the SQL Server HA options.</p>
Table of Contents (18 chapters)
Microsoft SQL Server 2008 High Availability
About the Author
About the Reviewers

Components of clustering

There are some components that we need to carefully consider before we go about designing cluster architecture and installing. Here are the components:

Shared disk array

As the name implies, the disks are shared. The reason behind using shared disk arrays is the fact that they are accessible from remote locations or clients and this is exactly what is required by cluster services.

The cluster architecture forces us to use shared disk resources, as the disk to be used with the cluster environment should be accessible by any of the nodes. The shared disk plays a vital role in the cluster, because it stores not only data files, but logs, FILESTREAM files, and full-text index files. In case of failover, the preferred node will have access and ownership for the disk resource so that clients can access the database system without any interruption.

The shared disk should be reliable and fault tolerant as it stores database files. We can have this shared disk resource in form of Fibre Channel SCSI or SAN.

Internet Small Computer System Interface

In the earlier version of SCSI, we needed to set special cabling, which we need not worry about in the Internet Small Computer System Interface (iSCSI) as it works well with IP and that is the beauty of the iSCSI — it is used to transfer data across intranets and to manage storage for long distances. This feature is really very useful and handy in the case of Multi-site clustering because we may use it as a location-independent storage solution.

Also, administrators can consider using iSCSI from the point of view of storage consolidation, that is, dispersed located data center, or multi-site clustering, among others.

Storage Area Network (SAN)

SAN is network storage, which is meant to be used when we need to consolidate our storage at our corporate network or at our data center as a highly reliable storage solution.

SAN has multiple (sometimes hundreds of) hard disks attached to it using high performance adapters. Also, it has a great caching capacity that increases the performance ultimately. All these hard disk drives are then virtualized or created logically thus labeled as Logical Unit Number (LUN). These LUNs can be shared or dedicated to a particular application, such as, SQL Server.

Every operating system maintains its own file system to the dedicated LUNs, that is, NTFS, and when it comes to shared LUNs among different flavors of operating systems, it may use SAN File systems to cater to the need. In most cases, SAN uses fibre channel to connect to the network, called Fibre Channel over Ethernet (FCoE), though it is not limited to FCoE only.

Redundant Array of Independent Disks

Redundant Array of Independent Disks (RAID) provides better fault-tolerance by making use of redundancy of disk(s). RAIDs are widely used as storage solutions to get the best I/O performance, based on the application, whether the application is write intensive or read intensive. We may have a choice having RAID array with iSCSI or SAN. There are mainly three concepts in RAID:

  • Mirroring: In this type, an entire disk is mirrored on another disk or set of disks using hardware or software-level mirroring. Mirroring creates a replica of what is written on one disk on another disk, or set of disks, and that is why configuring Mirroring requires at least two drives, for example, RAID 1.

  • Striping: Here, there are two or more disks required to configure striping. While writing data into disks or reading from the disks, it is spread across all the disks configured in stripes and thus gives best performance for writing as each disk has its own reader, for example, RAID 5.

  • Parity: In general, parity sets status as even or odd by adding a bit known as the parity bit. For even parity, the parity bit is set to 1 if the number of ones in the set of bits (not including the parity bit) is odd. On the other hand for odd parity, the parity bit is set to 1 if the number of ones is even. So, if there is some corruption in data being written or read, it will not match the pattern and thus reports incorrect status, for example, RAID 5. This is generally known as fault-tolerance.

There are many RAID arrays available such as RAID 0, RAID 1, RAID 3, RAID 4, RAID 5, RAID 6, RAID 10, and RAID 01. Here, we will discuss only those that are widely used with the SQL Server to boost performance level.

  • RAID1: This is the simplest form of RAID array. It requires a minimum of two disks to create a RAID1 array. RAID1 creates a replica of the disk being written, and is extremely helpful to recover data in case of physical damage to the first disk. RAID1 is the perfect choice to store operating systems, binaries, and database log files.

  • RAID5: RAID5, also known as stripe set with parity, requires three or more disks, and it creates and stores parity values internally. So in case of failure or damage of a single disk, data can be recalculated and rebuilt on a newly added drive. Please be aware that this will degrade the performance until the disk that has failed or damaged is rebuilt. This type of RAID is most useful when our application is read intensive as it reads data in a sequential manner.

  • RAID10: RAID10 is also known as mirrored stripe or mirrored then striped. This means it is a combination of RAID1+RAID0. RAID10 is used when we want best performance with a write-intensive application or data files and tempdb, as it writes data in a random manner.