The last three releases of SQL Server had unofficial marks, each based on the type of user that the most new features were geared headed for. For example, SQL Server 2005 was considered a developer relief, while SQL Server 2008 was viewed as a relief 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) relief.
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 relief 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 initiation evaluating and testing it now, so you’ll be ready to upgrade once the commercial relief becomes available later this year or next year.
First, there’s Scheme Apollo, which harnesses the same column-store indexes that potential 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 hard to know how much quicker data retrieval will be in real-world applications, but Microsoft says it could run from several times to several hundred times quicker.
From a business standpoint, this feature could change the way users work with your data warehouse. Traditionally, data warehouse tables are too huge 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 intelligence need to be generated and cached overnight in peacefulness to provide users with timely responses.
Let’s say you have a $1,000 in your checking tab 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 tab balances because with generous 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 thrash 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 choice 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 scheme, exceeding the effort vital 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 amalgamation 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 make possible cross-database dependency in a mirroring environment, AlwaysOn introduces something called an availability assemble. You can define a assemble 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-buying databases need to be available 24/7, and even a small outage could cost you revenue as well as customers. Therefore, a high-availability choice that provides seamless failover with minimal downtime and makes it simpler to result in up primary databases on secondary servers can provide significant return on investment.
Data Visualization, aka Scheme 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 simple-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 simpler than by using traditional cubes. And that’s made it simpler for less-technical users to initiation BI applications. BISM uses the same VertiPaq technology that’s behind column-store indexes, allowing quick response times when querying generous sets of data. When SQL Server Denali was previewed at the PASS Summit, it was Crescent that caused the largest 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.
Scheme Crescent will provide value to companies that want to make possible 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 intelligence look a total lot cooler, it will also increase perception of the value of the intelligence and the work that went into them. Reckon 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 Scheme Crescent, you‘ll be able to place together flashier intelligence. And if all works as promised, AlwaysOn will reduce the downtime of your SQL Server installations.
Some words of caution, even if: The initial previews of Denali are impressive, but we still haven’t seen the “small print” on features — things like restrictions, limitations and licensing supplies. In early 2011, column-store indexes and Scheme 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 surpass picture of how they can best use them.