What's new in SQL Server 2008 - Some More

Filestream Storage

As the name may suggest, Filestream enables the storage of unstructured data such as documents and videos.  This feature integrates with the NTFS files system by using the nvarchar(max) data type to store data on the file system.  This feature is great for when you’re currently storing documents inside of your SQL Server system that are larger than 1 MB and fast read access is important.

Sparse Columns

Sparse columns are very similar to other types of database columns, except that they are specialized and optimized for null values.  If you have a table that holds a lot of NULL values, first consider your table design.  If your design is solid or cannot be altered, then your table may be a candidate to take advantage of sparse columns.  The rule from Microsoft is to consider using Sparse columns when 20-40 percent of the values in a column will be NULL.

New Date & Time Data Types

Prior to SQL Server 2008, there was no way to native data type to store time related data.  Time data was only available as part of the datetime (or smalldatetime) data types.  With SQL Server 2008, TIME is a separate data type, able to hold time values such as ‘23:59:59.9999999′.  Along with the new TIME data type, there are additional data types such as datetime2, which holds additional nanosecond data and the datetimeoffset data type, which can hold datetime data that is timezone aware.

Spatial Data Types

Two new data types are included in SQL Server 2008; geometry and geography.  These data types hold information regarding the physical location and shape of geometric objects, which allow for applications to be built that are geographically aware.

Dependency Reporting

Reporting dependencies has never been consistent or reliable in SQL Server.  The difficultly is that when objects are added, modified or dropped, special actions must be taken by the database engine to ensure that the dependency chain is correct.  In 2008, new dynamic management views are included keep track of these dependencies so that reliable reporting can be done one these objects.

Advanced Auditing Features

SQL Server 2008 includes several new features that facilitate the auditing of data.  These features include a new Auditing feature that allows you to easily create customized audits of database engine events.  Change Data Capture, while not auditing specific, makes data changes typically used for loading a data warehouse easily available in an easily used format.  SQL Server 2008 also includes a Data Collector feature that allows the DBA to gather and compare data that is gathered from several different sources.

Policy Based Administration

This is one of my favorite features of SQL Server 2008.  This feature allows a database administrator (DBA) to easily and uniformly set policies across servers that ensure system rules are met.  These policies can be rules such as simplifying administration tasks, preventing unauthorized system changes, making sure code compliance is met, ensuring best practices are met.

TSQL Intelli-sense

SQL Server Management Studio 2008 includes Intelli-sense which is a familiar feature in other Visual Studio products.  This feature presents a popup box when typing SQL statements to give options to choose columns from tables that are involved in your queries.

Central Management Servers

This feature allows the database administrator (DBA) to administer multiple SQL Servers easily and efficiently.  These servers allow for multi-server queries and for policy based management policies to be executed against multiple servers at the same time.

Compound Operators

This form of syntactic sugar is present in many other programming languages such as C++ or C#.  Consider the following TSQL statement.

SET @x = @x + 1

Using compound operators, the above statement can be rewritten in the following manner:

SET @x += 1

This statement, while not groundbreaking, takes TSQL a step forward in the evolution of programming languages.

Transparent Encryption

As data becomes more and more sensitive, the ability to keep the data secret becomes more important.  SQL Server 2008 includes the ability to encrypt data stored in the database transparently, which keeps you from having to alter applications to take advantage of the feature.  This feature prevents unauthorized users from reviewing the data even if they have the backup files.

What's new in SQL Server 2008

SQL Server 2008 will be released in 2^nd quarter of 2008. SQL Server 2008 will also be part of a joint launch with Windows Server 2008 and Visual Studio 2008 on February 27, 2008 in Los Angeles, CA.

Transparent Data Encryption

SQL Server 2008 enables encryption of entire databases, data files, and log files, without the need for application changes. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. Some of the benefits of transparent data encryption include searching encrypted data using either range or fuzzy searches, more secure data from unauthorized users, and data encryption. These can be enabled without changing existing applications.

External Key Management

SQL Server 2008 provides a comprehensive solution for encryption and key management. To meet the growing need for greater security of information within data centers, organizations have invested in vendors to manage security keys within the enterprise. SQL Server 2008 provides excellent support for this need by supporting third-party key management and hardware security module (HSM) products.

Enhanced Auditing

SQL Server 2008 improves compliance and security by allowing you to audit activity on your data. Auditing can include information about when data has been read, in addition to any data modifications. SQL Server 2008 has features such as enhanced configuration and management of audits in the server, which enable organizations to meet varied compliance needs. SQL Server 2008 can also define audit specifications in each database, so audit configuration can be ported with databases. Filtering of audits to specific objects allows better performance in audit generation and flexibility in configuration.

Enhanced Database Mirroring

• Automatic page repair. SQL Server 2008 enables the principal and mirror computers to transparently recover from 823 and 824 errors on data pages by requesting a fresh copy of the corrupted page from the mirroring partner.
• Improved performance. SQL Server 2008 compresses the outgoing log stream in order to minimize the network bandwidth required by database mirroring.
• Enhanced supportability
• SQL Server 2008 includes additional performance counters to enable more granular accounting of the time spent across the different stages of Database Management System (DBMS) log processing.
• SQL Server 2008 includes new Dynamic Management Views and extensions of existing views to expose additional information about mirroring sessions.

Hot Add CPU

Extending existing support in SQL Server for adding memory resources online, Hot Add CPU allows a database to be scaled on demand. In fact, CPU resources can be added to SQL Server 2008 on supported hardware platforms without requiring application downtime.

Performance data collection

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 delivers more extensive performance data collection, a new centralized data repository for storing performance data, and new reporting and monitoring tools.

Extended Events

SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure is a lightweight mechanism that supports capturing, filtering, and acting upon events generated by the server process. This ability to act upon events allows users to quickly diagnose run time problems by adding contextual data, such as Transact SQL call stacks or query plan handles, to any event.  Events can be captured into several different output types, including Event Tracing for Windows (ETW).  When Extended Events are output to ETW, correlation with operating system and database applications is possible, allowing for more holistic system tracing.

Backup compression

Keeping disk-based backups online is expensive and time consuming. With SQL Server 2008 backup compression, less disk I/O is required, less storage is required to keep backups online, and backups run significantly faster.

Data compression

Improved data compression enables data to be stored more effectively and reduces the storage requirements for your data. Data compression also provides significant performance improvements for large input/output-bound workloads such as data warehousing.

Resource Governor

SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor enables database administrators to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users.

Plan Freezing

SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.