Database Engine Availability Enhancements
The availability of Microsoft SQL Server 2005 databases is improved by online indexing operations. Database mirroring enables the creation of hot standby servers that provide rapid failover support with no loss of data from committed transactions.
Availability Enhancements
Instance Availability: Failover Clustering
Instances of the SQL Server 2005 Database Engine support failover clusters on 32-bit and 64-bit Enterprise, Developer, and Enterprise Evaluation editions with as many nodes as the operating system supports. SQL Server 2005 Standard Edition supports 2-node failover clusters. Earlier versions of SQL Server supported 4-node clusters for 32-bit SQL Server instances and 8-node clusters for SQL Server 2000 (64-bit).
For more information, see Failover Clustering.
Instance Availability: Multi-Instance Support
SQL Server 2005 Enterprise, Developer, and Evaluation editions support up to 50 instances of the Database Engine on one computer. The other editions of SQL Server 2005 support up to 16 instances of the Database Engine on one computer. All editions of SQL Server 2000 are limited to 16 instances per computer.
Instance Availability: Dedicated Administrator Connection
In some error states, an instance of the Database Engine may not be able to accept new connections, preventing a database administrator from diagnosing the problem. The SQL Server 2005 Database Engine introduces a dedicated administrator connection (DAC); a member of the sysadmin fixed server role can use the new sqlcmd utility and the DAC to access and diagnose an instance of the Database Engine.
For more information, see Using a Dedicated Administrator Connection.
Instance Availability: Dynamic AWE Memory Management
When using AWE memory to support large amounts of memory, instances of the SQL Server 2005 Database Engine dynamically adjust the amount of memory they use based on current workloads. In earlier versions of SQL Server, when AWE memory was enabled, instances acquired a static amount of memory when the instance was started and could not adjust memory use to varying workloads.
For more information, see Managing Memory for Large Databases.
Instance Availability: Hot-Add Memory
Hot-Add Memory allows the SQL Server 2005 Database Engine to use new memory added to a computer while it is running. While earlier versions of SQL Server dynamically adjusted memory usage to match current workloads, they could not make use of memory added to the computer after it had started.
For more information, see Hot Add Memory.
Database Availability: Database Mirroring
Database mirroring creates a hot standby server for a database. Database mirroring is an alternative to failover clusters for improving the availability of a database and is easier to manage than failover clusters. In database mirroring, all updates to a database (the principal database) are immediately copied to a separate full copy of the database (the mirror database). The principal and mirror databases reside on two instances of the SQL Server Database Engine, which should reside on different computers. The server instance that is currently serving the principal database is called the principal server. The server instance that is currently maintaining the mirror database is called the mirror server. If the principal server fails, the mirror server can rapidly switch the mirror database to the role of principal database.
For more information, see Database Mirroring.
Database Availability: Database Snapshots
Database snapshots offer an efficient way to revert a database to a point of known logical consistency. A database snapshot records the current state of the data in a database as if all active transactions were rolled back. The snapshot then records all data changes from that point forward. If a mistake is made, such as dropping a large table, the database can be reverted to the state it was in at the time the snapshot was taken.
For more information, see Database Snapshots.
Database Availability: Checksum I/O Validation and Read-Retry
SQL Server 2005 increases data stability with the introduction of checksum and read-retry logic for database pages. The concepts of checksum and read-retry have provided rich benefits to the Microsoft Exchange Server to help detect I/O path problems that can lead to physical data problems. In SQL Server 2005, checksum and read-retry are built into the Database Engine.
The SET PAGE_VERIFY clause of the ALTER DATABASE statement now includes a CHECKSUM option. When CHECKSUM is specified, a checksum is calculated over the contents of the whole page and stored in the database page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the database page header. If the values do not match, it indicates the page has been physically damaged while written to, stored on, or read from the I/O path or media. For this failure, the Database Engine returns an error to the application, the Windows event log, and the Database Engine error log. Database page checksums can also be validated during backup and restore operations. A checksum failure is an indication of an I/O path problem and requires investigation of your hardware, firmware drivers, BIOS, filter drivers (for example, virus software), and other I/O path components to determine the root cause.
The Database Engine will retry any failed I/O operation up to four times to account for any possible transient problems with the I/O path. Successful retries are still indications of possible I/O path problems, but read-retry can help provide data availability while allowing you to fully investigate the I/O problem.
For more information, see the PAGE_VERIFY option in ALTER DATABASE (Transact-SQL).
Database Availability: Mirrored Backup Media
Performing backups to mirrored sets of backup media reduces the impact of the possible loss of one of the backups. If one backup device fails, one of the mirrors can be used to restore the database.
For more information, see Using Mirrored Backup Media Sets.
Database Availability: Backup and Restore Media Checks
If the TORN_PAGE_DETECTION or new CHECKSUM database options are set, new options for the BACKUP and RESTORE statements allow the integrity of data pages to be validated during backup and restore operations. RESTORE VERIFYONLY can be used with these options to perform a more complete validation of a backup before using it to restore a database.
For more information, see Detecting and Coping with Media Errors.
Database Availability: Fast Recovery During Both Crash Recovery and Database Mirroring Failover
The Database Engine in SQL Server 2005 Enterprise Edition allows fast recovery during both crash recovery and database mirroring failover. Fast recovery makes the database available during the undo phase, with partial availability during restore operations, database page checksums, and backup media mirroring. The other editions of SQL Server 2005 do not let users access the database until recovery completes. Earlier versions of SQL Server never allowed access to the database until completion of the undo phase. For more information about recovery in general, see Understanding How Restore and Recovery of Backups Work in SQL Server. For more information about recovery after a failover, see Estimating the Interruption of Service During Role Switching.
Database Availability: Backup and Restore Error Reporting
The BACKUP and RESTORE statements support a CONTINUE_AFTER_ERROR option that instructs the Database Engine to keep processing after it receives an error. If multiple problems exist, this option lets the database administrator assess the scope of the problem.
For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups.
Database Availability: Online Restores
Users can access databases when a partial database restore is being performed of a database file or page. Users cannot access the part of the database until it has been recovered, but have access to all other data. Earlier versions of SQL Server required that no users access a database during any restore operation.
For more information, see Performing Online Restores.
Database Availability: EMERGENCY option
If a database has been marked suspect during recovery, it can now be placed into EMERGENCY mode to allow members of the sysadmin fixed server role read-only access. This allows them to diagnose the problem or retrieve any available data.
For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups.
Database Availability: Online Index Operations
Index operations can now be performed online; users can still access the table data and use other indexes on the table while one index is being created, altered, or dropped.
For more information, see Performing Index Operations Online.
Database Availability: Parallel Index Operations
The MAXDOP clause can now be specified on index data definition language (DDL) statements, controlling the number of parallel operations used by that specific statement. In earlier versions of SQL Server, MAXDOP could not be specified on index DDL statements, which sometimes allowed large index operations to adversely affect the performance of the instance of the Database Engine.
For more information, see Configuring Parallel Index Operations.