SQL Server 2012 Features

Although most organizations are still getting standardized on SQL Server 2005, Microsoft continues to pump out new SQL Server releases, and the next one, code-named “Denali,” is right around the corner. Microsoft released Community Technology Preview (CTP)1 at the 2010 PASS conference. You can also see a version of SQL Server Denali Books Online. SQL Server Denali’s most important features include these top five:

5. New Development Console

One obvious improvement is the new development environment, code-named “Juneau,” demonstrated at Quentin Clark’s PASS keynote address. Juneau uses the new Windows Presentation Foundation (WPF)-based Visual Studio shell that Visual Studio 2010 is built on. One goal for the new feature is to make the development environment consistent for both SQL Azure and on-premises SQL Server. It’s not in CTP1, but you can preview it at the Live Keynote site at PASS.

4. Columnar Index

One of the more surprising features in the Denali release is the new columnar index, also called the column-based query accelerator. I can’t help but wonder if this feature isn’t a reaction to the NoSQL movement.

The columnar index uses the same high performance/high compression technology that Microsoft uses in PowerPivot and brings it into the database engine. Indexed data is stored column-wise and only the needed columns are returned as query results for columnar indexes. Microsoft says this technology can provide up to a 10X improvement in query performance with reduced I/O—depending on your data.

3. Contained Databases

Contained databases make it easy to move databases between different instances of SQL Server. Users of a contained database don’t need logins for the SQL Server instance. Instead, the contained database handles all authentications.

Contained databases hosted on an instance of SQL Server have no configuration dependencies on that instance and are very similar in concept to the virtual databases that I discussed in “Riding the Virtualization Express: From Desktops to Databases” at InstantDoc 125267. And you can see the MSDN website for more information.

2. Support for Windows Server Core

Windows Server Core is designed for infrastructure applications like SQL Server that provide back-end services but don’t need a graphical UI on the same server. The inability to run previous versions of SQL Server on Windows Server Core always seemed wrong. Denali’s support for Windows Server will enable leaner and more efficient SQL Server installations, while also reducing the potential attack vector and the need for patching.

1. AlwaysOn

Probably the most significant new feature in SQL Server Denali is the SQL Server AlwaysOn feature, which is essentially the name for the next evolution of database mirroring. Database mirroring is very cool but had limitations: It was limited to a single mirrored partner, and the data replica contained on the mirrored site couldn’t be used while mirroring was active.

AlwaysOn, or HADR (High Availability Disaster Recovery) as it’s also called, supports up to four replicas and the data in the mirrored replicas can be queried and backups performed. At this early stage, HADR seems more complicated to set up than database mirroring, but the advantages look to make it worth the extra effort. Read more about HADR at the MSDN site.