Denali - It’s the Future

In line with recent SQL Server releases, Microsoft on Nov. 9 announced at the PASS Summit that the first Community Technology Preview (CTP) version of SQL Server 11 - codenamed Denali (after the Alaska mountain also known as Mt. McKinley, the highest peak in North America) - is available for download and evaluation. This is our first opportunity to look at some of the features that will form the basis for the next major release of SQL Server, expected to be fully available in the second half of 2011.

The CTP program is an effective way to allow the community to preview the new functionality in SQL Server releases since the release criteria for CTP features are the same as those for a final release. Microsoft works with partners to develop features, and then releases them to the community for widespread testing and feedback. MVPs get earlier access, which helps facilitate dissemination of information out to the community at the time of release. SQL Server 2008 had six CTP releases, so, right now we are probably in the early stages for SQL Server 11. Microsoft refers to a CTP as a release candidate rather than a beta, so the quality is expected to be high but still allow for changes and fixes based on feedback.

Some of the cool features included in CTP1 include:

HADR - Built on database mirroring functionality from SQL Server 2005, HADR allows you to group databases together into availability groups, and configure automatic failover to up to 4 failover partners. Each failover group enables the constituent user databases to failover as a single unit. In addition, you can use the replicas for read-only access to take reporting load away from the production databases, and you can even use them for production backups, making maintenance window management significantly easier.

Contained Databases - SQL Server has long struggled with portability of databases. While it has always been easy to move data and data structures, other objects, like logins, related SQL Agent jobs and linked servers have had to be moved independently. This was because these objects were tied to the instance rather than the individual databases. A contained database will move all these objects, along with information such as system settings and error messages - and the database - to a new instance. This can greatly assist application development, deployment and ongoing administration.

Management Studio Enhancements - It is possible to undock windows in SQL Server Management Studio (SSMS). This provides SQL Server users much more flexibility within the environment, and brings the user experience closer to that of Visual Studio, which is desirable for programmers and DBAs who need to port their skills.

One of the exciting features expected in CTP2 is Columnstore Indexes. This functionality will enable significantly faster query execution targeted for large scale data warehouses utilizing OLAP queries. Hundreds, possibly thousands, of times faster queries are possible, and storage compression can be much more efficient. Inserts, updates and deletes are not possible without rebuilding the columnstore index, so a regular load cycle would be required to update the data as necessary.

You should also be equally aware of the features that are being depreciated so you can prepare for migrations when the release becomes necessary for your environment. Early testing of your applications with a new database platform will help with planning for the future. Probably the major change to note here is that support for Data Transformation Services (DTS from SQL 2000) is now removed entirely and replaced with SSIS. This was a significant headache when moving to SQL 2005, but, you could still use the old DTS functionality. This is no longer possible. The documentation also says support for database compatibility mode 80 (SQL 2000) will be removed; however, it is still shown in CTP1.

Have a look at the CTPs and provide Microsoft with as much feedback as possible, as this will help ensure the high quality of future SQL Server releases for all of us.

All content in this column was correct at the time of this writing; feature names and functionality are subject to change in future CTP releases.