New Features of SQL Server "Denali"

1. SQL Server AlwaysOn

Denali's AlwaysOn includes features to help simplify high-availability environments and maximize hardware investments. One of these features is support for multi-site clustering, also referred to as multi-subnet clustering. Windows Server Core support is also included. It attempts to reduce SQL Server downtime by eliminating 50 to 60% of the reboots required by OS patching.

2. High Availability and Disaster Recovery

HADR, High Availability and Disaster Recovery, is also introduced with this latest version. With it comes the concept of Availability Groups. Availability Groups enable a set of one or more related SQL Server databases to define one or more secondary copies. The source set is referred to as the primary replica and the failovers as secondary replica. One of the secondary replicas can be active, allowing for read-only connections. Viola, instant reporting database! For more information on HADR, see http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx.

3. Juneau

Juneau is the code name for the new SQL Server Development Tools. Its purpose is to provide a single development environment for all database related project types including bringing BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio) into the same IDE.

4. Apollo

Apollo is the codename for Column-based Query Accelerator. I have to admit that this is the feature I'm most excited about. I watched some impressive demos showing how much performance gain is realized on very large result set queries. Microsoft claims an approximate 10 times increase in query performance.

Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. The whitepaper on the new Columnstore Index points out the following benefits:
• only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
• it's easier to compress the data due to the redundancy of data within a column, and
• buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.

5. Crescent

This cool new tool aimed at the business user is actually still yet to come. Project "Crescent" furthers Business Intelligence for everyone by providing a powerful and speedy data visualization tool in the browser. It has a feature called storyboarding which allows the user to create multiple snapshots of the data in order to tell a story about that data. Once the snapshots are created, the storyboard can be exported to PowerPoint where the data is displayed live inside the PowerPoint slides. The demos of this product are a must-see. Below are two links. The last is the keynote from Day 1 of the PASS Summit (by Ted Kummert, Microsoft Sr. Vice President, Business Platform Division) and contains a lot more than just Project Crescent.

Data Visualization Done Right: Project Crescent

PASS Summit 2010 Day One Live Streaming Keynote

6. FileTable

The new FileTable feature takes storing blob data to a whole new level. With Denali, you can now define a whole table as a FileTable and point it to a folder on a file system. When files are written to that folder, they are available within that FileTable. They can be queried, backed up, and restored right along with the rest of your application data.

7. Integration Improvements

Several improvements are coming in Denali to make creating SSIS packages more efficient. One of these changes is the new Undo and Redo buttons in the control and flow designers. There are also source and destination assistants available to help set up sources and destinations.

More importantly, though, new knowledge-driven data cleansing capabilities are being delivered as Data Quality Services. Additionally, Impact Analysis and Lineage can be used to predict changes and troubleshoot packages.

8. Contained Databases

http://www.youtube.com/watch?v=CKrH5x7KxfQ&feature=player_embedded#at=488

Contained Databases is a concept where databases are not tied to the instance they reside on. Everything about a database is contained in that database without today's coupling to the database engine (SQL Server instance) it's running under.

Specifically, users are no longer tied to logins on the instance. I'm actually pretty excited about this. I can't tell you how much time this will save, no longer needing to fix orphaned users in development, test, and stage when we pull down production databases to our non-production environments.

9. Spatial Features

SQL Server spatial features have been greatly expanded with this new release. Highlights include new CircularArc subtypes, support for objects bigger than a logical hemisphere (including a new FULLGLOBE data type), new methods for the geography type, spatial index improvements, Nearest Neighbor Query Plan, and support for persisted computes columns.

To get more detail on these features and more, the New Spatial Features Whitepaper can be downloaded from Microsoft.

Conclusion

Intrigued by the new features of SQL Server 11? You can download the CTP at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9 to try it for yourself.