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
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
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.
How Database Mirroring Works
The server of the Principal database is called the Principal server instance. The Principal server instance allows users to connect and update the database. The server of Mirror database is called the Mirror server instance. All changes occurring on Principal database are immediately applied to the Mirror database. Thus, the mirror server instance residing on a different computer becomes a hot standby server.
Failover Event
In case of failure event of the Principal database, the Mirror database will take over the role of Principal database. Once the Principal database is up, it will behave as a Mirrored instance.
Overview of Automatic Database Level Failover
Automatic failover requires a third instance of SQL Server 2005, known as the Witness Instance. Typically, this witness instance resides on a third computer. The Witness instance does not serve any of the databases. It is a separate server instance that monitors the status of Principal and Mirror server and can initiate the automatic failover if the Principal instance fails.
Each server instance may participate in multiple database mirroring sessions. A given server instance can act concurrently as the Principal server instance for some databases, and the Mirror server instance for other databases. A Witness can participate in concurrent sessions with the same or different pairs of partners.