SQL Server 2008 "Katmai" CTP 5 New Features

Well, the long wait for Katmai CTP5 (The "November CTP") is finally over. Microsoft has released CTP5 and has posted the bits to download.microsoft.com  (Don't look for the bits on Connect just yet as they are not posted there)

New Features

There are a bunch of long-awaited features in CTP5, but here are some of my favorites:

Reporting Services Scale Engine and Robust Server Fit and Finish

A reengineered memory management and scalability infrastructure lays a solid foundation of scalability capability to enterprise customers.  

Transparent Data Encryption

SQL Server 2008 enables encryption of an entire database, 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 both range and fuzzy searches, searching secure data from unauthorized users, and data encryption. These can all be enabled without changing existing applications.  

Resource Governor - Limit Specification

Resource Governor allows organizations to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users. This enhancement specifically delivers the resource limit functionality.

Backup Compression

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

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.

Fully Parallel Plans Scale on Partitioned Tables

Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.

Data Collection and Performance Warehouse for Relational Engine

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 tools for reporting and monitoring

Service Broker Enhancements

Getting the data to the right place at the right time is important.  Service Broker Conversation Priority in SQL Server 2008 gives you greater control over the system by making it easy to configure priority rules so that the most important data is sent first and processed first.

Registered Servers Enhancements

Enhancements to the Registered Servers tool window in Management Studio include running T-SQL queries and policies against groups of servers and the ability to share a common, centrally stored, server topology (Database Engine only).

Synchronous net-changes change tracking for SQL Server

SQL Change Tracking feature provides the functionality to synchronously track changes to data in user table without the need to create triggers or modify schema of the table. Applications will be able to reliably determine what data has changed since a watermark/baseline and will be able to obtain the latest data. The feature is geared towards providing the functionality with least DML overhead.

T-SQL IntelliSense

Transact-SQL IntelliSense provides intelligent aids for Transact SQL scripting that make language references easily accessible for database developers. When coding, you do not need to leave the Database Query Editor to perform searches on T-SQL language elements or your database metadata.  You can keep your context, find the information you need, insert T-SQL language elements directly into your code, and even have IntelliSense complete your typing for you. This can speed up software development by reducing the amount of keyboard input required and minimize references to external documentation.

Declarative Management Framework (DMF) Enhancements

Enhancements to DMF include more expressive conditions (including support for common functions, T-SQL, and WMI queries), more robust target set filtering, custom messages for policies, and the inclusion of best practice policies.

Geo-spatial Support

SQL Server 2008 delivers comprehensive geo-spatial support. The new GEOGRAPHY and GEOMETRY data types provide spatial data support for location-aware applications.  These types can be used to store locations, as well as paths and regions in space, and provide a rich set of functionality for comparing and manipulating these objects.  Use the GEOGRAPHY type when working with latitude and longitude coordinates in a true round-earth model; use GEOMETRY when working in projected planar surfaces, as well as naturally planar systems such as interior spaces.

These types are supported by new spatial indexes, which provide for fast execution of queries involving spatial data.  The query optimizer has been enhanced to build-in knowledge of spatial indexes and types, so that appropriate cost-based plan decisions can be made.

Analysis Services Query and Writeback Performance (FITS)

New MOLAP-enabled write-back capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.

Robust Report Server Platform

Reports can easily be delivered throughout the organization with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.

Integration Services - Lookup Enhancements

The need to perform lookups is one of the most common extraction, transformation, and loading (ETL) operations. This is especially prevalent in data warehousing where fact records must use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.

Analysis Services MDX Query Optimizer - Block Computation

Block computations provide a significant improvement in processing performance, enabling users to increase the depth of their hierarchies and complexity of the computations.

Analysis Services Aggregation Design

SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. The AS Aggregation Design improvement exposes Aggregation Design objects in SQL Server BI Dev Studio and SQL Server Management Studio and provides tools for users to better work with these aggregation designs. In addition, an advanced view in the new Aggregation Design tab of the cube editor provides the ability for an advanced user to view and manually edit individual aggregations within an aggregation design.

Analysis Services Cube Design

New cube design tools help users streamline the development of the analysis infrastructure, enabling them to build solutions for optimized performance. The AS Cube Design improvement introduces a new Cube Wizard which helps users create better cubes in fewer steps.  The new wizard focuses on having the user answer a few questions to create leaner cubes that better targets their needs.  It also unblocks the previously difficult scenarios of creating a cube a cube based on a single, de-normalized table and creating a cube containing only linked dimensions.