First Look: SQL Server Denali

Ready or not, here it comes! The next release of SQL Server, code-named Denali, is right around the corner. For most companies, Microsoft is churning out new releases way faster than they can roll them out internally. However, each release of SQL Server definitely brings a fuller feature set to the table than ever before. The new enterprise-oriented feature set in SQL Server Denali definitely testifies as to this database software’s maturity. Let’s take a look at some of the enhancements that are expected in the SQL Server Denali release.

Support for Windows Server Core

Windows Server Core is designed for infrastructure applications that provide back-end services but don’t really need a graphical UI on the same server. Although SQL Server is such an application, you can’t run previous versions of SQL Server on Windows Server Core. SQL Server Denali’s new support for Windows Server Core will enable leaner and more efficient SQL Server installations. Running SQL Server on Windows Server Core will also reduce the potential attack surface and the need for patching.

It’s important to realize that running SQL Server Denali on Windows Server Core means that you must manage the instance locally using a command-line interface (e.g., Windows PowerShell, thesqlcmdutility) or manage the instance remotely. Most SQL Server instances are managed remotely—and managing this instance remotely shouldn’t be much different than managing most of your other instances remotely.

Revamped SSMS

One of the first things you’ll notice about SQL Server Denali is that SQL Server Management Studio (SSMS) has been updated to use the new Windows Presentation Foundation (WPF)–based shell that Microsoft Visual Studio 2010 is built on. SSMS is still the DBA’s best friend. It lets you manage multiple SQL Server systems and create T-SQL scripts and database objects. It also provides several built-in management reports, such as the Server Dashboard report in Figure 1.

Figure 1: Server Dashboard report in Denali

New Development Environment

Another enhancement that’s slated for the Denali release is the new development environment that’s code-named Juneau. Like SSMS, Juneau uses the new WPF-based shell.

Juneau promises to provide a development experience that will be immediately familiar to DBAs who know and love SSMS. However, it goes beyond what SSMS offers. For example, Juneau sports a new graphical table designer with split graphical and T-SQL views that let you make table schema changes in either view and see immediate updates to both views. Juneau is aware of dependencies and can generate a list of errors if you try to drop columns that other database objects use.

Juneau can also analyze a set of changes and generate a script that will update the database and all the objects affected by changes that you make in the development environment. Like Visual Studio, Juneau supports the concept of projects and can be integrated with source control using Team Foundation Server. One of the cooler features is Juneau’s ability to immediately jump to column definitions or show all the references for a given column. One of Juneau’s goals is to make the development environment consistent for both SQL Azure and the on-premises version of SQL Server.

SQL Server AlwaysOn

Probably the most significant new feature in the upcoming Denali release is the SQL Server AlwaysOn feature. AlwaysOn is essentially the next evolution of database mirroring. AlwaysOn doesn’t replace database mirroring or failover clustering. Instead, it adds a new high-availability option to SQL Server.

Database mirroring is a very important high-availability technology, but it has a few significant limitations. First, it’s limited to a single database. Second, it’s limited to a single mirrored partner, and the mirrored data contained in the secondary site can’t be used while mirroring is active. AlwaysOn, which is also called High Availability and Disaster Recovery (HADR), eliminates these problems and provides some other important benefits as well:

AlwaysOn supports multiple-database failover.

AlwaysOn supports up to four active secondary sites, and the data in the mirrored sites can be queried and used for backups.

The New Availability Group Wizard steps you through the process of configuring AlwaysOn. The core construct of AlwaysOn is the availability group, which defines the databases and servers that will participate in the availability solution. All the servers in the HADR availability group must be a part of a Windows failover cluster. An AlwaysOn dashboard provides a visual overview of the availability configuration and the status of the servers in the availability group.

The AlwaysOn technology provides faster client failover and faster client connection redirection. Because of the requirements for failover clustering, HADR is clearly more complicated to set up than database mirroring. But the advantages look they will make it well worth the extra effort. You can read more about HADR here.

Contained Databases

Contained databases are another new feature in SQL Server Denali. 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 instance of SQL Server. Instead, all authentications are handled by the contained database. Contained databases have no configuration dependencies on the instance of SQL Server on which they’re hosted. You can find more information about contained databases here.

Columnar Index

One of the most important new performance features in the upcoming Denali release is the columnar index. The columnar index brings the same high performance/high compression technology used in PowerPivot to the database engine. With a columnar index, the index data is stored column-wise, and only the needed columns are returned as query results for columnar indexes. Microsoft states this technology can provide up to a tenfold improvement in query performance with reduced I/O. Of course, the actual performance gains depend on your own data scheme and storage.

FileTable

SQL Server Denali’s new FileTable feature is an integrated storage solution that essentially combines the FileStream and HierarchyID data types, which were introduced in SQL Server 2008. FileTable is a new type of database object (i.e., a table) that links the SQL Server database engine with directories on the NTFS file system. It enables applications to access the files in the FileTable table. (No changes need to be made to the applications.) It also enables SQL Server to manage those files with full relational integrity.

Using Denali’s FileTable support, you can run queries against files and directories in the NTFS file system. Queries can use full-text searches to find keywords and phrases inside the FileTable files. You can also use T-SQL to update the names and attributes of FileTable files. SQL Server management operations (e.g., backups) can be performed on the contents of the FileTable files.

And There’s More

In addition to these major new features, the Denali release includes numerous other enhancements. On the T-SQL front, there’s support for a new sequence generator and support for new windowing and paging capabilities. In addition, the new THROW operator improves error-handling capabilities.

A new business intelligence (BI) subsystem named Data Quality Services has also been added to the Denali release. Data Quality Services is a tool that enables data analysts to clean and massage data. You can use a UI to directly edit data, or you can integrate the data cleaning engine into SQL Server Integration Services (SSIS) for automated data cleaning.

SQL Server Denali also includes a host of smaller enhancements, including:

A Master Data Services add-on that lets you to manage your data definitions from within Microsoft Excel

Next and Best?

There’s no doubt that the upcoming Denali release will raise the bar for enterprise data platforms. The new development and enterprise features give it several capabilities that aren’t found in any other enterprise database software. Plus, the performance and manageability enhancements push SQL Server up to a higher rung on the enterprise ladder. For more information about this upcoming release, check out the early release copy of Books Online (BOL) for SQL Server Denali.