Published: 12 May 2011
The last three releases of SQL Server had unofficial labels, each based on the type of user that the most new features were geared toward. For example, SQL Server 2005 was considered a developer release, while SQL Server 2008 was viewed as a release for database administrators. SQL Server 2008 R2, with Microsoft’s PowerPivot data analysis tool and enhancements to both SQL Server Analysis Services and Reporting Services, was widely known as a business intelligence (BI) release.
For More on SQL Server Denali
Announced with SQL Server Denali was Project Atlanta; learn about the release candidate
Microsoft released the first community technology preview (CTP) of the next version of SQL Server, code-named Denali, at November’s Professional Association for SQL Server (PASS) Summit 2010 in Seattle. It appears that this release won’t continue the labeling tradition; instead, SQL Server Denali has something for everybody. And several of the new features make Denali compelling enough to start evaluating and testing it now, so you’ll be ready to upgrade once the commercial release becomes available later this year or next year.
First, there’s Project Apollo, which harnesses the same column-store indexes that power PowerPivot in the database engine. Fueled by the database architecture called VertiPaq, column-store indexes are designed to provide significant performance gains over regular indexes.
In a regular index, indexed data from each row is kept together on a single page, and data in each column is spread across all of the pages. A column-store index packs data from each column together, so each page contains data from a single column. Plus, the index data for each column is compressed, and since columns often contain highly repetitive values, you’re looking at a high compression ratio. All this means fewer pages in the index and fewer pages that need to be scanned if you’re selecting a small number of columns.
Since the nature of the data stored in SQL Server varies from instance to instance, it’s difficult to know how much faster data retrieval will be in real-world applications, but Microsoft says it could run from several times to several hundred times faster.
From a business standpoint, this feature could change the way users work with your data warehouse. Traditionally, data warehouse tables are too big to query in real time, so they aren’t suitable for ad hoc queries: The data in those tables needs to be aggregated, cubes need to be built or reports need to be generated and cached overnight in order to provide users with timely responses.
Let’s say you have a $1,000 in your checking account and write $500 in checks. But look online soon afterward, and your balance still shows that $1,000. Banks sometimes take a while to refresh account balances because with large datasets, it’s either impractical or nearly impossible to do real-time updates. Similarly, data warehouses often have to run extract, transform and load (ETL) processes at night to update and aggregate data.
But a column-store index can potentially run a multi-hour query in minutes and a multi-minute query in seconds, so your business could switch to real-time queries. That means no more waiting for days for the data to be collected and aggregated. The bottom line? Your business users and executive decision makers will have the data they need much more readily.
Each version of SQL Server has either added new high-availability features or improved existing ones. But while there are several viable options, such as database mirroring, log shipping or SQL Server clustering, each has its own disadvantages; you need to be ready for compromise when choosing one technology over the other.
SQL Server Denali introduces a new technology called AlwaysOn, or High Availability Disaster Recovery (HADR). According to Microsoft, developing it was a significant engineering project, exceeding the effort required to rewrite the SQL Server engine for version 7.0 in the late 1990s, after Microsoft and original development partner Sybase Inc. went their own ways on the database. Look at AlwaysOn as a combination of what worked best in database mirroring and clustering, with new capabilities added based on user feedback.
For example, database mirroring works on a single database, but often a database accesses objects in other databases on the same server. So, let’s say Database A accesses objects in Database B using views or synonyms; those queries work only if both databases are on the same server. But if Database A fails over to the mirror server, the references to the objects in Database B become invalid, and your application starts generating errors.
To enable cross-database dependency in a mirroring environment, AlwaysOn introduces something called an availability group. You can define a group of databases as an availability unit; then they all fail over as a unit, and your cross-database references will remain functional. AlwaysOn also lets you configure several replicas of the primary database. These replicas can be made read-only for reporting purposes. If your primary database fails, one of the replicas can take over in the primary role.
For many businesses, these improved HADR features will be the most attractive reason to upgrade to SQL Server Denali. These days, more e-commerce databases need to be available 24/7, and even a short outage could cost you revenue as well as customers. Therefore, a high-availability option that provides seamless failover with minimal downtime and makes it easier to bring up primary databases on secondary servers can provide significant return on investment.
Data Visualization, aka Project Crescent
This feature is probably the most exciting new BI offering in SQL Server Denali, and more specifically, in the Microsoft self-service BI mix. Crescent continues data visualization enhancements delivered in SQL Server Reporting Services 2008 R2, and later in PowerPivot. It is a Web-based report designer that utilizes Microsoft’s Silverlight application framework and provides ad hoc reporting to end users with an easy-to-use presentation tool and powerful visualization components.
Behind the scenes, Crescent utilizes Microsoft’s Business Intelligence Semantic Model (BISM), a new technology that makes building BI models easier than by using traditional cubes. And that’s made it easier for less-technical users to create BI applications. BISM uses the same VertiPaq technology that’s behind column-store indexes, allowing fast response times when querying large sets of data. When SQL Server Denali was previewed at the PASS Summit, it was Crescent that caused the biggest stir. Audiences were impressed with the demonstrations of the new tool, especially its visual presentation of data, which goes way beyond traditional charts or key performance indicators.
Project Crescent will provide value to companies that want to enable less-technical staff -- managers, executives, marketing departments, researchers -- to browse data, run ad hoc reporting and present data in a visually striking format. It will also be very useful to companies that specialize in collecting, processing and selling data in report formats.
So not only will Crescent make reports look a whole lot cooler, it will also increase perception of the value of the reports and the work that went into them. Think of it as visiting a fancy French restaurant -- the creative presentation of food on your platter not only enhances your enjoyment of the meal, it increases your perception of its value.
Upgrading to SQL Server Denali when it’s released is bound to have a significant impact. Its column-store index technology will give business users quicker, timelier access to the data stored in data warehouses. With Project Crescent, you‘ll be able to put together flashier reports. And if all works as promised, AlwaysOn will reduce the downtime of your SQL Server installations.
Some words of caution, though: The initial previews of Denali are impressive, but we still haven’t seen the “small print” on features -- things like restrictions, limitations and licensing requirements. In early 2011, column-store indexes and Project Crescent weren’t even available for evaluation, since they weren’t included in the first CTP. AlwaysOn was, but in a limited feature set. Once all of these features are fully available in a public CTP, SQL Server users will get a better picture of how they can best use them.
ABOUT THE AUTHOR
Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He contributes to Visual Studio Magazine,SQL Server Magazine and other publications.