DELL POWER SOLUTIONS Reprinted from Dell Power Solutions, February 2006. Copyright © 2006 Dell Inc. All rights reserved. February 2006 26 DATABASES: SQL SERVER 2005 Enterprise data centers that host business-critical appli- cations and databases can be at risk from failures and disasters. Availability and recoverability become crucial for business operations. Current database availability technolo- gies vary in their complexity, cost, level of automation, incurred downtime, and supported distance. A major chal- lenge for an enterprise is determining the most cost-effective and least complex solution to help ensure database avail- ability and meet business needs. Microsoft SQL Server 2005 introduces several high-availability features and feature enhancements for the SQL Server database platform. Failover clustering Failover clustering with Microsoft Cluster Service (MSCS) is a popular way to achieve high availability for SQL Server. With MSCS, multiple servers or nodes are linked to function as a single system and provide an automatic failover solution. Enterprises are connected to virtual serv- ers and not physical servers; each active physical server may host one or more virtual servers. If one of the physical servers fails because of either a hardware or software problem, MSCS detects the failure and moves resources that reside on the failing server— including the virtual server(s)—to one or more remaining physical servers. End users connected to the failed server observe only a momentary delay in accessing its resources while MSCS restarts SQL Server services on the remaining server(s) and remaps the virtual server connections. Installing a failover cluster requires the following: • Microsoft Windows® 2000 Server (Advanced Server or Datacenter Server) or Microsoft Windows Server™ 2003 (Enterprise Edition, Datacenter Edition, Enterprise x64 Edition, or Datacenter x64 Edition) • Microsoft SQL Server 2005 (Standard Edition or Enterprise Edition) • Shared storage based on SCSI, Fibre Channel, or Serial Attached SCSI • All components (server, host bus adapter, and stor- age system) and the cluster solution listed in the Microsoft Windows Server Catalog Figure 1 shows two typical two-node failover cluster configurations for SQL Server 2005, one in active/passive BY ANANDA SANKARAN, DAT NGUYEN, AND NAM NGUYEN Exploring High-Availability Featuresin Microsoft SQL Server 2005 Microsoft® SQL Server™ 2005 offers failover clustering, database mirroring, log shipping, replication, and database snapshot features that can help safeguard an enterprise’s critical data and operations. This article discusses these features and their associated trade-offs. Related Categories: Clustering Database High availability (HA) Microsoft SQL Server 2005 Visit www.dell.com/powersolutions for the complete category index. DATABASES: SQL SERVER 2005 www.dell.com/powersolutions Reprinted from Dell Power Solutions, February 2006. Copyright © 2006 Dell Inc. All rights reserved. DELL POWER SOLUTIONS 27 mode and one in active/active mode. In a failover cluster envi- ronment, SQL Server 2005 offers several additional features and improvements compared to SQL Server 2000: • Support for multinode clustering: Standard Edition allows up to two-node clustering and Enterprise Edition up to eight- node clustering. • Support for SQL services: Analysis Services feature offers multi-instance support and full-text indexing. • Easier installation: System Configuration Check automati- cally detects and installs SQL Server as virtual servers across the nodes in the cluster. Database mirroring Database mirroring is designed to augment the availability of SQL Server 2005. With this feature, transactions to a database hosted on a SQL Server instance, referred to as the principal, can be con- tinuously duplicated in real time onto a copy hosted on another instance, referred to as the mirror. The principal and mirror servers are considered partners in a database mirroring session. Transac- tions (such as insertions, updates, and deletions) on the principal database are duplicated by continuously sending transaction log records to the mirror over the network (see Figure 2). The mirror receives the log records continuously and restores them. If the principal or mirror server incurs a failure, the mirror- ing session is disconnected. Before starting the mirroring session, administrators must initialize the mirror database from a principal database full-restore with the NORECOVERY option. Besides the primary and mirror, a mirroring session may include an optional third server, referred to as the witness. The witness server enables automatic failover after a principal server failure by promoting the mirror to function as the principal. Using a witness server helps achieve a quorum and prevents accidental promotion of the mirror database that results from communication failures between the principal and mirror. Database mirroring requires the following configuration: • The primary and mirror databases must be hosted on sepa- rate SQL Server 2005 instances. • The principal database must be set to the Full Recovery model, not the Bulk-Logged Recovery or Simple Recovery model. Database mirroring can be configured in any of the following three operating modes based on transaction safety level and the presence of a witness server: • High-availability mode: In this mode, log records are trans- ferred synchronously from the principal database to the mirror. The principal waits for an acknowledgment from the mirror before “hardening” log records to its disk. In addition to providing synchronous transfer, this mode uses a witness server to enable automatic failover. • High-protection mode: This mode is similar to the high- availability mode except that it does not support a witness server, and thus automatic failover is not possible. However, failover can be performed manually to promote the mirror database. Because the log records are transferred synchro- nously in this mode, the mirror database is synchronous and consistent with the principal. • High-performance mode: In this mode, log records are transferred asynchronously from the principal to the mirror database. The principal does not wait for an acknowledg- ment from the mirror before “hardening” its logs to the disk, Figure 1. Failover cluster configurations for SQL Server 2005 SQL Server 2005 db1 db2 SQL Server 2005 Heartbeat LAN Active Passive SQL Server 2005 db1 SQL Server 2005 Heartbeat LAN Active Active db2 Figure 2. Database mirroring for database db1 in high-availability mode LAN Principal Mirror db1 db2 db3 SQL Server 2005 db5 db6 SQL Server 2005 SQL Server 2005 db1 Transaction log records Log recovery Witness DATABASES: SQL SERVER 2005 DELL POWER SOLUTIONS Reprinted from Dell Power Solutions, February 2006. Copyright © 2006 Dell Inc. All rights reserved. February 2006 28 and the mirror is not guaranteed to be synchronous with the principal at any point. This mode does not support a wit- ness server, and thus neither automatic nor manual failover is possible. However, a forced failover can be performed to recover the mirror, with some data loss occurring because mirroring is asynchronous. A key advantage of database mirroring is that it provides quick failover and high availability with minimal hardware cost and complexity. Formerly, such SQL Server mirroring solutions were often feasible only with expensive, proprietary hardware- based mechanisms. With acceptable latencies and bandwidth, mirroring can be deployed over a long- distance wide area network (WAN) as part of a disaster recovery solution. During the mirroring session, the mirror data- base is not available for client access because it is in a recovery state, but a database snapshot can be created on the mirror for limited reporting. Only one mirror database can be established for a princi- pal database. However, the participating SQL Server instances can assume differ- ent roles for different mirror- ing sessions. For example, a SQL Server instance can serve as a principal for one database, as a mirror for another database, and as a witness for yet another mirroring session. The notion of a virtual server, as is used in failover clustering, does not exist. However, Microsoft ADO.NET provides an application programming interface (API) for clients to automatically reconnect to the mirror server during failures. Log shipping Log shipping is similar to database mirroring. It allows a database hosted on a primary server to automatically send transaction logs to a secondary database for duplication. The log records are not transferred continuously as in database mirroring, but rather in intervals. Log shipping comprises backing up the transaction logs on the primary server instance, copying the backed-up logs to the secondary server instance, and restoring the copied logs on the secondary server instance periodically. The primary database should be configured for the Full Recovery or Bulk-Logged Recovery model. The secondary database should be initialized by a full recovery of the primary database with the NORECOVERY or STANDBY option. Log shipping can optionally include a third server named “mon- itor” to record the history of log-shipping operations and to raise alerts during failures. Log-shipping operations are carried out by four Microsoft SQL Server agent jobs: backup job, copy job, restore job, and alert job. During log shipping, the secondary database is not completely synchronized with the primary database. Also, log shipping does not provide automatic failover to the secondary server during a failure. Log shipping supports multiple secondary servers for a primary server. In addition, a SQL Server instance can function as a secondary or monitor server for multiple primary servers. The secondary server can be used directly for limited query processing. Log shipping can be used in scenarios where multiple destina- tion servers are needed and where delays in restoring logs on the duplicate are acceptable. Replication Replication is a set of technologies for distributing data and data- base objects across Microsoft SQL Server databases over a net- work. Replication involves a publisher database instance that makes data available for copying, a distributor instance for copying data and maintaining metadata, and subscriber instances that can receive replicated data. SQL Server 2005 provides three types of replication tech- nologies suitable for different needs: transactional, merge, and snapshot. SQL Server 2005 adds enhancements to these repli- cation methods to help improve scalability, performance, and monitoring capabilities. Some of the improvements include additional replication data types, support for partitioned tables and full-text index, direct Data Definition Language (DDL) replication for schema changes, and replication from Oracle® databases. In transactional replication, SQL Server 2005 adds an impor- tant feature called peer-to-peer replication, which enhances the existing bidirectional replication option. Advantages of replicated databases include data load balancing and disaster recovery. With the peer-to-peer replication feature, administrators can set up multiple peer-to-peer transactional replication pairs among different data centers. That way, if one data center is down, it can be failed over to its peer center(s). Although there is no limit to the number of nodes in a peer-to-peer topology, man- ageability of the peer-to-peer relationships between nodes can become a constraint. SQL Server 2005 provides three types of replication technologies suitable for different needs: transactional, merge, and snapshot. SQL Server 2005 adds enhancements to these replication methods to help improve scalability, performance, and monitoring capabilities. DATABASES: SQL SERVER 2005 www.dell.com/powersolutions Reprinted from Dell Power Solutions, February 2006. Copyright © 2006 Dell Inc. All rights reserved. DELL POWER SOLUTIONS 29 Database snapshots Administrative and application errors can be a major cause of database downtime. SQL Server 2005 introduces a feature called Data- base Snapshots to help protect a database from erroneous user operations and to augment database backup and restore operations. The Database Snapshots feature allows recovery from user errors by allowing the database state to revert to a point in time before the error(s) occurred. It works in stand-alone, failover clus- tering, and database mirroring environments. However, it does not work with a log-shipping secondary database. A database snapshot is a snapshot of an entire database at a given time. The snapshot is created instantly and can be used for read-only tasks such as reporting. A snapshot must have been cre- ated before the error occurred for an administrator to restore the database to a consistent state. Snapshot creation does not impose restrictions on the base database operations. Administrators can create multiple snapshots from a database as well. The SQL Server 2005 Database Snapshot feature employs a copy-on-write mechanism. Only changes to the base database—not the entire base database—are recorded after the snapshot has been taken. The snapshot is a pointer image to the base database. It shares the unchanged pages of the base database and requires only extra storage for changed pages. The Database Snapshot mechanism is illustrated in Figure 3. The Database Snapshot feature is designed to be extremely space-efficient. Any modification such as a write (step 1 in Figure 3) to the base database after a snapshot is taken will be recorded (step 2 in Figure 3) as part of the target snapshot. If I/O read requests (step 3 in Figure 3) are to blocks that have not changed since the snapshot was created, then the request will read from the base database. If I/O requests are to blocks that have changed since snapshot creation, then the request will be read from the snapshot. Figure 4 summarizes the high-availability features and options available in Microsoft SQL Server 2005. A high-availability database platform Microsoft SQL Server 2005 offers several features that are designed to help augment availability of data. Dell™ PowerEdge™ servers, Dell PowerVault™ storage, and Dell/EMC storage are also designed with a broad array of redundant hardware and software features to maximize hardware availability. The features in Dell hardware and SQL Server 2005 provide an integrated, industry-standard approach to high database availability. A combination of these components can greatly benefit enterprises with high-availability database requirements. Ananda Sankaran is a systems engineer in the High-Availability Cluster Development Group at Dell. His current interests related to high-availability clustering include storage systems, application performance, business conti- nuity, and cluster management. Ananda has a master’s degree in Computer Science from Texas A&M University. Dat Nguyen is a systems engineer in the High-Availability Cluster Develop- ment Group at Dell. His responsibilities include developing storage area network (SAN)–based high-availability clustering products. His current interests are in enterprise storage products and technologies. Dat has a B.S. in Electrical Engineering from the University of Houston. Nam Nguyen is a senior consultant in the High-Availability Cluster Develop- ment Group at Dell, and the lead engineer for Dell Fibre Channel PowerEdge Cluster products. His current interests include business continuity, cluster- ing, and storage technologies. He has a B.S. and an M.S. in Electrical Engineering from The University of Texas at Austin. FOR MORE INFORMATION Microsoft SQL Server 2005: www.dell.com/sql www.microsoft.com/sql Figure 3. Database Snapshot mechanism in SQL Server 2005 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 Snapshots Page Base database Figure 4. High-availability options and features in SQL Server 2005 Solution-specific Automatic certification Granularity Data loss failover Downtime requirements Failover clustering System and No Yes Approximately Cluster solution listed database 20 seconds plus in Microsoft Windows database recovery time Server Catalog Database mirroring Database Depends on Yes Less than 3 seconds No operating mode Peer-to-peer replication Table or view Some Optional Variable (little to none) No Log shipping Database Some No Variable No Database snapshots Table or view Some No Variable No