High-Availability Features

Minimizing the maintenance window, avoiding unnecessary or unscheduled downtimes, and maximizing server availability are top priorities of the administrators. SQL Server 2000 supported three high-availability solutions: failover clustering, transactional replication, and log shipping. SQL Server 2005 builds on these proven technologies and introduces a few new features to assist DBAs in increasing server availability.

Failover Clustering Changes

A SQL Server 2000 cluster allowed a maximum of four nodes. SQL Server 2005 on Windows Server 2003 Enterprise or Datacenter Edition allows up to eight nodes in a cluster. In addition, SQL Server 2005 Analysis Services and Full-Text Search can now be clustered. The cluster setup now allows you to choose which components you want to install. Dynamic AWE memory is another big improvement in SQL Server 2005 clustering. In an active-active cluster scenario, SQL Server 2005 on Windows Server 2003 dynamically adjusts AWE memory usage after failover. Other clustering-related changes include mount point support, script-based setup, and other setup changes for clustering. These changes are discussed in Chapter 8.

Database Mirroring and Snapshot

Unlike failover clustering, which is a high-availability solution at the server level, log shipping is the database-level high-availability solution available in SQL Server 2000. Database mirroring is the successor to log shipping. Mirroring differs from log shipping in two ways: With database mirroring, changes are reflected in real-time on the standby server and the failover is automatic.

A database mirror is made of three instances of SQL Server 2005: the principal database resides on the production server; the mirror database resides on the second hot-standby server, and the witness server is one that monitors the state of the principal and mirror servers.

All the SQL Server 2005 components such as Service Broker and Database Mail support database mirroring. The data access API is updated to support transparent client redirection in case the mirror server takes the role of the principal server.

Database snapshot, a new feature introduced in SQL Server 2005, allows you to create a new database that is a read-only, static view of some other (source) database. The most common application of the database snapshot feature is in conjunction with database mirroring. The mirror database is always in a recovering state and never available for client connections. However, SQL Server 2005 allows creating database snapshots of the mirror database. These snapshots can then be used as the reporting databases, to separate OLTP from reporting.

Database mirroring and snapshot are discussed in great detail in Chapter 8.

Online Index Operations

Index fragmentation is one of the main reasons for performance degradation in SQL Server query execution. To fix the fragmented indexes in SQL Server 2000, there were three options: drop and re-create the indexes manually, rebuild the indexes by using DBCC DBREINDEX, or defragment the indexes by using DBCC INDEXDEFRAG. Most DBAs chose to use DBCC DBREINDEX or INDEXDEFRAG. The problems with DBREINDEX are that it is an atomic operation, so if you cancel it, the rollback might take a long time and that while the index is being built, table data cannot be updated. INDEXDEFRAG is an online index operation and can be cancelled; however, it does not utilize parallelism and can take a very long time to complete if an index is highly fragmented.

SQL Server 2005 supports the same three options as SQL Server 2000 for fixing index fragmentation. It introduces three new options as well: ALTER INDEX...REORGANIZE to defragment the index, ALTER INDEX...REBUILD to rebuild the index, and ALTER INDEX...REBUILD WITH (ONLINE = ON) for online index rebuilding.

Online index rebuilding allows concurrent modifications to the underlying table or index. SQL Server 2005 Profiler has a new event class that can be used to track the progress of online index operation.

Online index operation and other features related to indexes are discussed in more detail in Chapter 8.

Instant File Initialization

Let's say you have to restore a 500GB database from a backup. You begin by creating a new blank database that is 500GB or larger in size. With SQL Server 2000, this step itself could take few hours because SQL Server reserved the space and initialized the data file by filling it with zeros. SQL Server 2005 introduces a new feature called instant file initialization, which skips the second step of zeroing out the file bytes; therefore, even large databases are created instantly. Note that instant file initialization works for data files only, and not for log files.

Online, Piecemeal, and Page Restore

SQL Server 2005 introduces new features so that in case of recovery, a database can be made available as soon as possible. If a database contains multiple filegroups, as soon as the primary filegroup is restored, you can bring the database online and in the background restore other filegroups. The page restore is designed to restore torn pages or pages that a checksum shows to be corrupt.

More discussion on high-availability features can be found in Chapter 8.

Replication

SQL Server Replication is a commonly used technology for copying and distributing data and database objects from one database to another, possibly on a different server, and then synchronizing between databases to maintain consistency. SQL Server 2000 supported 3 replication topologies or models: Snapshot, Transactional, and Merge. SQL Server 2005 builds on this solid foundation and introduces about 30 new features that extend the replication platform to provide new capabilities, simplify configuration and monitoring, and give better control to administrators. The following sections briefly discuss some new features, which are then discussed in more detail, along with other replication enhancements, in Chapter 8.

Replication of Schema Changes

With SQL Server 2000, administrators thought that replication was best suited for static environments, where there were no schema changes on the publisher. This was because SQL Server 2000 provided very limited support for making schema changes once an object is published. In some cases, administrators cannot afford to resynchronize the subscription to apply schema changes on the subscribers. The only two stored procedures available to accommodate schema changes on published objects are sp_repladdcolumn and sp_repldropcolumn. A combination of these two store procedure can be used to implement some other column schema changes (such as changing the data type), in addition to adding or dropping the column in a published object.

SQL Server 2005 contains broader support for allowing schema changes to be made on the published object, without any need for running a specialized stored procedure such as sp_repladdcolumn. SQL Server 2005 makes use of DDL triggers mechanism to capture events such as ALTER TABLE, ALTER VIEW, and so on and then applies the publisher schema changes on the subscribers.

Oracle Publishing

In snapshot and transactional replication topologies, SQL Server 2005 now supports bringing data from Oracle version 8.0.5 and above that resides on any operating system. It does not require anything to be installed on the Oracle server. You can configure and administer everything from SQL Server tools, and you don't necessarily need to have Oracle knowledge to configure Oracle publisher.

Peer-to-Peer Transactional Replication

Peer-to-peer transactional replication is essentially an efficient way to implement scalable and highly available SQL Server solutions. Each node in the topology, a peer, can update the data, and data is synchronized to other peers, making sure all peers have the same copy of the data. Workload can therefore be distributed among peers or fail over to another peer. Unlike merge replication, peer-to-peer transactional replication does not provide the ability for conflict detection and resolution. It is designed for environments where peers update different parts of data, but same databases have to be available on all the nodes.

Merge Replication over HTTPS

Merge replication subscribers getting data over the Internet by using IIS on the server is not new. The SQL Server CE subscribers could always do that. However, if a subscriber was not a SQL Server CE Edition, then it was not possible for the subscriber to connect to the publisher over HTTPS. SQL Server 2005 fixes this and allows both Mobile and Server Editions to connect to the publisher over HTTPS.

There are many other replication improvements in SQL Server 2005 related to functionality, security, performance, monitoring, programming (RMO), and administration. Refer to Chapter 8 for more details.