SQL Server 2000 & 2005 Clustering
Windows Server 2003 is Now Active Directory-Aware
Windows Server 2003 is now Active Directory-aware.
You can publish a Network Name as a computer object in Active Directory.
You can run Active Directory-aware applications as a clustered application.
Improved Cluster Service Account Maintenance
You do not have to take the cluster nodes offline to change the password for the cluster service account.
Improved Resource Maintenance
You can delete resources in Cluster Administrator or with Cluster.exe without first taking them offline. The cluster service takes them offline automatically and then deletes them.
Chapter 11 – What is New with SQL Server 2005 – Clustering & Availability
SQL Server 2005 database engine supports 8 node failover clusters on 32-bit systems, and 4 node clusters on 64 bit systems. SQL Server 2000 supported only 2 node or 4 node failover clusters.
Instance Support – Improved Scalability
SQL Server 2005 supports 50 SQL Server Instances per cluster. SQL Server 2005 Enterprise Edition can have up to 50 SQL Server instances on a single computer. SQL Server 2000 supported only 16 instances per Window’s box.
Dedicated DBA Connection
Many DBAs may have seen instances where the database engine denies any new connection and no one is able to access it. Thus, it becomes very difficult to diagnose such a critical condition. The SQL Server 2005 database engine provides a smarter way to eliminate this scenario. SQL Server 2005 introduces a dedicated administrator connection. Members of the sysadmin fixed server role can make use of the sqlcmd utility and a dedicated administrator connection pool will allow those users to diagnose an instance.
AWE Memory Management is Now Dynamic
Earlier versions of SQL Server used to manage AWE memory in a static way. Excluding 128 MB RAM for operating system, it used to consume all available memory on the machine provided the max server memory option threshold was not set. SQL Server 2005 has overcome this limitation and now the AWE enabled memory management will do a dynamic management of available memory, depending on the varying workload of the instance.
HOT Add Memory
SQL Server 2005 is intelligent and can detect if there is any hot memory added, after the SQL Server instance is started. It can detect this memory and also it can make it available to the instance if memory management is configured as dynamic. Earlier versions of SQL Server were not smart enough to know if there is any new memory added to the machine.
Improved Disk I/O Error Detection
SQL Server 2005 has a new ability with Alter Database Set Page_Verify Checksum. When CHECKSUM is specified, a checksum is taken over the contents of the entire page and stored in the page header when a page is written to disk. When a page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. In SQL Server 2005 an I/O error detected by the operating system is reported as 823, and an I/O error detected by SQL Server PAGE_VERIFY CHECKSUM option is reported as error 824. Should the values not match, error message 824 is reported in the SQL Server error log as well as in the Event Viewer Log. The checksums can also be validated during backup and restore operations.
Backup and Restore Media Checks
Backup and Restore in SQL Server 2005 allows the integrity validation of data pages during backup and restore sessions. Database options TORN_PAGE_DETECTION or CHECKSUM should be set for this. To do a complete validation of a backup before utilizing it, the statement RESTORE VERIFYONLY provides a complete picture.
Database Availability During Restores
Earlier SQL Server versions did not allow users to access the database until the rollback or undo phase is completed, it required that no user should access a database while it is being restored. SQL Server 2005 Enterprise Edition has a great ability to let users access the database after the roll-forward phase of a database restore operation is completed. User can access the database while a partial database restore session is going on, but they cannot access the part of the database until it has been fully recovered, but still they have access to all other data.
Improved Handling for Damaged Backups
SQL Server 2005 has improved error handling for damaged backups. Backup and Restore has a new option, CONTINUE_AFTER_ERROR, that tells to the database engine to keep processing the operation even if it receives an error. If there are more problems, this option lets the DBA to assess the environment.
EMERGENCY Mode allows access of the database to all members of sysadmin server role. If a database is falling in suspect mode during recovery mode, such databases can be placed into EMERGENCY mode.
Index Operations Online
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.
Database Mirroring is one of the most significant new features of SQL Server 2005 Enterprise edition. Database Mirroring is a high availability solution to produce a hot copy of any given database. It works only if database model is set to full recovery. Database Mirroring reproduces every change to a database onto separate full copy of the database. Source database is called as Principal database and destination database is called as Mirror database. They both reside on two separate instances of SQL Server 2005.