Inside SQL Server 2008 - Exploring High Availability and Scalability Enhancements

Expert Reference Series of White Papers
Inside SQL
Server 2008: Exploring
High Availability and
Scalability
Enhancements
1-800-COURSES www.globalknowledge.comInside SQL Server 2008: Exploring High
Availability and Scalability Enhancements
Michael Manning, Global Knowledge Instructor
IntroductionDatabases are at the heart of every organization. They support line-of-business applications, internet applica-tions, human resource systems, financial applications, and more. Application downtime and database errors mean lost customers in both the short- and long-term. According to a 2007 University of Warwick study, hourly downtime costs were pegged at $682,000 per hour in the retail sector and $192,000 in the financial sector. Those costs are probably conservative and could be higher or lower, depending on the size and nature of your business. What are the causes? Causes include such factors as natural disasters, hardware failure, upgrades, hu-man error, and others. Achieving high availability is a critical component of our job as database administrators (DBAs). Your final high availability (HA solution) will depend on what you are trying to protect against, what is your acceptable downtime tolerance, and what will operate along side your Disaster Recovery strategies.
SQL 2005 introduced high availability and scalability enhancements, such as database mirroring and improved failover clustering from earlier versions. SQL Server 2008 has built on the earlier SQL 2005 architecture and added some interesting new features. Microsoft is targeting close to zero downtime with SQL 2008 with avail-ability and recovery features that only used to be available via third-party solutions. These features include prioritization management, hot-swap replication, and the ability to recover off of a mirrored server. This white paper discusses the evolution of High-availability from SQL 2005 to SQL 2008, and the tools available to mini-mize downtime in the Enterprise.
High Availability Options SQL Server 2008 offers a variety of high-availability options to help minimize downtime. These features include: . Database Mirroring. Log Shipping. Failover Clustering. Peer to Peer Replication. Database Snapshots. Dynamic Configuration
Let's take a look at these options and how they can positively affect high-availability and disaster recovery needs.
Copyright ©2009 Global Knowledge Training LLC. All rights reserved. 2Database MirroringDatabase mirroring offers increased database availability by maintaining a hot standby database on another instance of SQL Server 2008. The mirror database is an exact copy of the principal database, and, as changes are made to the principal database, they are automatically synchronized to the mirror. Database mirroring works by transferring and applying a stream of database log records from a principal to a mirror database. In case of a failure on the principal server, client applications are automatically redirected to the mirror server without any changes to the application. The standby database becomes the active database, and clients are redirected with-out any data loss or downtime to the organization. Database mirroring can be configured using the Database Mirroring Wizard (see Figure 1).
Figure 1: Database Mirroring Wizard
Database mirroring fills two roles, firstly as an HA solution for a local site and secondly as a vehicle to meet disaster recovery requirements. As part of an organizational disaster recovery plan, a hot or warm standby database could be placed in a separate geographical location from the primary active database. As an example, the principal database could reside in the Seattle data center, and the mirror database in the San Jose disaster recovery site (see Figure 1). The principal database in the Seattle data center handles all client operations while the mirror database receives a steady stream of transaction log changes through a dedicated TCP endpoint. This ongoing process keeps the mirror database synchronized with the active database and ready to take on client activities in case of a failure.
Figure 2: Seattle Principal and San Jose Mirror Servers
Copyright ©2009 Global Knowledge Training LLC. All rights reserved. 3Database mirroring can be configured with one of two operating modes: high-safety and high-performance.
High-safety mode supports synch... [download for more]