What's new in SQL Server 2005

New Hardware Support

SQL Server 2005 fully supports both 32-bit and 64-bit hardware platforms for all of its major services, including the SQL Server Engine, Analysis Services, SQL Agent and Reporting Services.

The main Benefit achieved in moving a database to the 64-bit platform is the vastly increased addressable memory. The native 32-bit architecture is limited to a maximum of 4GB addressable memory, which is divided in two pieces, 2GB is reserved for Windows operating system and the remaining 2GB for applications. Using the Advances/Address Windowing Extensions (AWE) support found in the 32-bit version of Windows can address a maximum 32GB of RAM. The native 64-bit implementation eliminates this memory constraint by raising the maximum addressable memory to 32TB.

SQL Server 2005 offers support for the two leading 64-bit architectures for Windows Server systems. By providing this support for the array of 64-bit applications and migration scenarios, organizations will experience true power and ease when implementing SQL Server on 64-bit servers.

NUMA Support

NUMA (Non-uniform) Memeory access allows solutions to be scaled out by grouping together resources such as units and memory into modes or units that can perform together as one server.

Data Availability

SQL Server 2005 allows database administrators to perform a restore operation on a database while database is still �online� and users are able to connect. Online restore improves the availability of SQL Server because only the data being restored is unavailable; the rest of the database remains online and available to users. In previous versions restoring a database required exclusive access to the database.

Online Index Operations

The online index option allows concurrent data modifications (updates, deletes, and inserts) to be made to the underlying table or clustered index data and any associated indexes while indexes are being rebuilt. For example, while a clustered index is being rebuilt, you can continue to make updates to the underlying data and perform queries against the data. This improves performance because indexes can be maintained and managed without the need for a data to be unavailable to users whilst the index rebuild is running.

Database Snapshot

Database Snapshots are a new feature of SQL Server 2005, this feature is only available in the Enterprise Edition of SQL Server 2005. Database Snapshots provide a read-only, "virtual" copy of a database, at a given point in time. The following are possible uses of database snapshots:

Database snapshot can be used to protect your system from user or administrator error. For example if the contents of an entire tables had been deleted, the table had not been updated for a while you could copy the contents of the table back to the live database using the snapshot.

Offloading reporting - The database snapshot can be used as reporting source thus taking the load off the live production database

Maintaining historical data - you can maintain views of historical data using snapshots.

Fast Recovery A new faster recovery option improves availability of SQL Server databases. Administrators can reconnect to a recovering database after the transaction log has been rolled forward.

Mirrored Backups

SQL Server 2005 lets you create mirrored backups. A mirrored backup allows you to create multiple identical copies of the backup files in case one of the sets is damaged. The mirrors have identical contents, so you can mix the files in the event that one becomes corrupt. Let�s say you have two backup sets and when you restore from set 1 the full backup is corrupt you can use the full backup from Set 2 and then continue to use the other backup files from set 1 to complete your restore.

Database Mirroring

Extend log shipping capabilities with the database mirroring solution. You will be able to use database mirroring to enhance availability of your SQL Server systems by setting up automatic failover to a standby server.

Performance

Data partitioning is enhanced with native table and index partitioning that enables efficient manageability of large tables and indexes.

Plan Guides

The Plan Guides feature, new in SQL Server 2005, provides a method for injecting query hints into SQL statements in batches, stored procedures (SP), and so forth without the need to make modifications to the query itself. This is useful when performance tuning code that is non-modifiable.

Dynamic Management Views

Dynamic Management Views (DMV) is new to SQL Server 2005 and is intended to provide an insight and information into what is currently happening on your SQL Server. In previous versions this information was retrieved by querying the system tables and other functions.

Enhancements

Failover clustering has been available since SQL Server 7; in SQL Server 2005 many of the same features of failover clustering have been included. These features are reliable and stable and have been included in the last two release of the product.

SQL Server 2005 does have some Failover Clustering enhancements compared to previous versions. Some of the abilities of failover clustering have been extended to SQL Server Analysis Services, Notification Services, and SQL Server replication. In SQL Server 2000, SQL Server Agent and other job management and processing capabilities were not covered by failover clustering. In SQL Server 2005 these technologies are cluster aware.

Replication

For distributed databases, SQL Server 2005 provides comprehensive schema change (DDL) replication, next-generation monitoring capabilities, built in replication from Oracle to SQL Server, merge replication over https, and significant merge replication scalability and performance improvements.

Full-Text Searching

SQL Server 2005 supports rich, full-text search applications. Cataloguing capabilities provide greater flexibility over what is catalogued. Query performance and scalability have been improved dramatically, and new management tools provide greater insight into the full-text implementation.

Tools and Utilities

SQL Server 2005 includes SQL Server Management Studio, a new integrated suite of management tools with the functionality to develop, deploy, and troubleshoot SQL Server databases, as well as enhancements to previous functionality in Enterprise Manager.

SQL Configuration Manager

SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers.

Conclusion