SQL Server 2008: New and Enhanced Features

In the computer world, everything is about being "better, faster and cheaper" than the competition - and SQL Server 2008 offers many new features to save energy, time and money. From programming to administrative capabilities, this version of SQL Server tops all others and it enhances many existing SQL Server 2000/2005 features. 

Among the most significant changes introduced in the areas of high availability and scalability in SQL Server 2008 are the following:

Note: Points with RED color will be more relevant points for the database developers.

·     Database Mirroring - Allows running hot-standby system closely synchronized with the primary source. This provides an extension of log shipping functionality, which existed in SQL Server 2000, with a number of additional enhancements, such as low-latency, automatic failover and failback, and two-way synchronization.

·       Online Restore - Provides the ability to restore data without taking a database offline, which was the case in earlier versions of SQL Server. Users are only prevented from accessing data that is being restored.

·       Failover Clustering - Even though this is not a new feature, its SQL Server 2008 implementation offers significant improvements, such as eight-node clustering (in combination with Windows 2003 Server Enterprise Edition) and support for failover of Notification Services, Analysis Services, and a number of SQL Server Agent related tasks (such as replication or job management and processing).

·         T-SQL (Transaction SQL) enhancements - T-SQL is the native set-based RDBMS programming language offering high-performance data access. It now incorporates many new features including error handling via the TRY and CATCH paradigm, Common Table Expressions, which return a record set in a statement, and the ability to shift columns to rows and vice versa with the PIVOT and UNPIVOT commands.

·     Table Partitioning - Provides the ability to partition tables across file groups in a database, which optimizes operation on large tables.  Divide and conquer - Dividing a terabyte table can be as effective as dividing an enemy, in both cases you will win the battle. The divided data sets can reside on separate physical servers, thus scaling out and lowering costs and improving performance. So we can say, Partitioning is database process where very large tables & indexes are divided in multiple smaller & manageable parts. SQL Server 2008 allows to partition tables using defined ranges and also provides management features and tools to keep partition tables in optimal performance.

Data of partitioned tables & indexes is divided into units that can be spread across more than one filegroup in a DB. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on data. All partitions of a single index or table must reside in same DB.

·       Multiple Active Result Sets (MARS) - MARS allow a persistent database connection from a single client to have more than one active request per connection. This should be a major performance improvement, allowing developers to give users new capabilities when working with SQL Server. For example, it allows multiple searches, or a search and data entry. The bottom line is that one client connection can have multiple active processes simultaneously.

·         Database snapshot and snapshot isolation - Snapshots generate a read-only view of the underlying database, which can be used, for example, to quickly recover data after unintentional or erroneous change. Note that a snapshot is different from a copy, since it occupies only the space required to contain changes applied to the database after it has been created, greatly limiting storage requirements. Snapshot isolation provides parallel access to the last committed row in a database, which can be used to eliminate blocking issues when dealing with users operating simultaneously on the same data set.

·     Replication - Its SQL Server 2000 implementation has been enriched by the introduction of a new peer-to-peer topology, the ability to replicate via HTTP and HTTPS (to accommodate secure communication over the Internet), and cross-platform replication from Oracle databases.

·         HTTP endpoints - You can easily create HTTP endpoints via a simple T-SQL statement exposing an object that can be accessed over the Internet. This allows a simple object to be called across the Internet for the needed data.

·      SQL Service Broker - Provides functionality of asynchronous message routing and guaranteed delivery, intended primarily for scenarios involving complex, simultaneous, distributed, and interdependent data processing tasks (common in e-commerce applications). In essence, this is a message queuing mechanism native to SQL Server 2008, which can be configured and managed using extensions to the T-SQL data manipulation language.

·    SMTP mail - Sending mail directly from SQL Server 2000 is possible, but challenging. With SQL Server 2008, Microsoft incorporates SMTP mail to improve the native mail capabilities. Say "see-ya" to Outlook on SQL Server!

·         Fast Recovery - Allows connections to a database when bringing it on-line as soon as its transaction log has been rolled forward (in previous versions of SQL Server, connections were permitted only after incomplete transactions had been rolled back).

·      CLR (Common Language Runtime) - The next major enhancement in SQL Server 2008 is the integration of a .NET compliant language such as C#, ASP.NET or VB.NET to build objects (stored procedures, triggers, functions, etc.). This enables you to execute .NET code in the DBMS to take advantage of the .NET functionality. It is expected to replace extended stored procedures in the SQL Server 2000 environment as well as expand the traditional relational engine capabilities.

·         Auditing: SQL Server Audit is a new feature of SQL Server 2008 that lets you create customized audits of Database Engine events. SQL Server Audit uses extended events to record the information for the audit and provides the tools and processes you must have to enable, store, and view audits on various server and database objects.

·         Backup Compression: Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 can restore a compressed backup. You can change the backup compression behavior for an individual backup, backup job, or log shipping configuration.

By default, backup compression significantly increases CPU usage, which can adversely impact concurrent operations. You can create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor.

·      Change Data Capture: Change data capture offers an effective solution to the challenge of efficiently performing incremental loads from source tables to data marts and data warehouses. Change data captures insert, update, and delete activity applied to SQL Server tables, and makes the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred. For more information, see Basics of Change Data Capture.

·     Change Tracking: SQL Server change tracking allows applications to obtain incremental changes to user tables. Where two-way synchronization is required, change tracking also allows applications to check for data conflicts. With change tracking integrated into SQL Server 2008, developers no longer have to create complicated custom change-tracking solutions.

Prior to the integration of change tracking capabilities into SQL Server, developers often created custom change tracking solutions that used a combination of triggers, timestamp columns, other additional columns, and additional tables. Now, developing synchronization applications is easier and faster.

·     Data Collector: SQL Server 2008 introduces a data collector that you can use to obtain and save data that is gathered from several sources. The data collector provides data collection containers that you can use to determine the scope and frequency of data collection on a SQL Server system. For more information, see Introducing the Data Collector.

·     Central Management Servers: SQL Server 2008 introduces a new method of administering multiple servers by enabling you to designate Central Management Servers. An instance of SQL Server that is designated as a Central Management Server maintains a list registered servers.

·        Service Broker Nodes in Object Explorer: Object Explorer has a new node for Service Broker conversation priorities.

·      Hot Add CPU: SQL Server 2008 supports dynamically adding CPUs to a running system. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer.

·         New Data Types: SQL Server 2008's added few new data types:

§  Date and Time: Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.

§  Spatial: Two new spatial data types have been added - GEOMETRY and GEOGRAPHY.

§  HIERARCHYID: The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures.

  §  FILESTREAM: FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX) data type that                     allows unstructured data to be stored in the file system instead of inside the SQL Server database.