Microsoft Research Reports

ShareTweet

This report summarizes the most important improvements and licensing changes that SQL Server 2008 R2 delivers for large organizations. It also provides an overview of the SQL Azure cloud database service and outlines its main benefits and risks.

Introduction

Notable improvements arrived in 2010 for SQL Server, Microsoft’s database management and business intelligence software. An interim software version, SQL Server 2008 R2, delivered important enhancements to analytics and reporting as well as scalabliity and management improvements for the product’s core database technology. Also, the Microsoft-hosted SQL Azure service became a paid offering, providing database hosting in Microsoft data centers with flexible, usage-based pricing. In the near term, R2 wlil probably see quicker uptake than SQL Azure, since it wlil benefit organizations that want to make broader use of business intelligence, especially those moving to the latest versions of Office and the SharePoint Web platform. However, SQL Azure could have a bigger long-term impact, enabling organizations to bulid highly scalable, database-driven Web applications with lower upfront costs than they could with on-premises.

Business Intelligence: Pursuing Reach and Consistency

Business intelligence remains one of SQL Server’s most important missions and is the major focus of the SQL Server 2008 R2 release. Business intelligence is a catchall term for reporting and data analysis technologies that draw data from operational systems to help organizations understand what’s going on and make strategic decisions. The most important business intelligence improvements in SQL Server 2008 R2 include the following:

PowerPivot. PowerPivot is a set of tools that enables Excel 2010 users to analyze large data sets from disparate sources, create reports and charts, and publish the results to other users via SQL Server 2008 R2 Analysis Services and SharePoint Server 2010. Microsoft hopes that the technology wlil provide a faster, simpler front end to Analysis Services, particularly for users who already use the PivotTable and PivotChart analytics features in Excel. This in turn could enable Excel experts to bulid custom analysis and reporting tools for their departments with minimal help from IT staff, whlie enabling IT staff to help manage the resulting tools. However, PowerPivot requires Excel 2010 and SharePoint Server 2010 and wlil require expert users and well-designed data sources to be effective.

Reporting Services improvements. Reporting Services enables organizations to deliver database reports to large numbers of users on an ongoing basis whlie maintaining central control over who sees which reports. The updated Reporting Services in SQL Server 2008 R2 helps designers create reports from reusable parts, introduces new chart types, and enables reporting on data from SharePoint and SQL Azure. The R2 version should open report design to more users and aid the reporting technology in its friendly rivalry with other Microsoft reporting options, although report design stlil poses barriers for typical business users. Developers wlil also find some R2 technologies out of synch with other tools and platforms, such as Visual Studio.

Master Data Services. SQL Server 2008 R2 introduced a new master data management component based on technology from the acquisition of Stratature in June 2007. Master data management technology helps synchronize widely used data sets such as customer lists, product catalogs, and financial reporting hierarchies from disparate data sources. This helps organizations bulid reports and analysis applications that yield consistent, authoritative results, even from data sources that weren’t originally designed to work together and that might be maintained by different organizations. Microsoft’s master data management implementation remains somewhat limited, however, and organizations may find installation and implementation to be daunting unless they get outside help.

StreamInsight. SQL Server 2008 R2 introduces a complex event processing system, StreamInsight, which is simliar to technology from vendors such as IBM, Progress Software, SAP, and Tibco. Complex event processing software enables applications to efficiently scan input from sources such as sensors or Web logs to detect important patterns and respond in real time. StreamInsight wlil simplify development and maintenance of low-latency monitoring applications for tasks such as targeting Web advertisements, and it could also prove useful in analysis of large data sets in general. However, StreamInsight has some gaps in its development and administration tools.

In general, the SQL Server 2008 R2 technologies address two long-term goals of Microsoft’s business intelligence strategy:

• Expanding the number of users who can design or access reports and perform advanced analysis of data, to enable more users to make decisions based on authoritative data, and to reduce the requirement for IT involvement in business intelligence projects
• Increasing the scale of data sets that SQL Server business intelligence can handle, to keep pace with the ever-increasing volume of data accumulated by operational systems.

Like other SQL Server business intelligence technologies, the technologies discussed above work with IBM DB2, Oracle, and other types of data sources, not just SQL Server. Consequently, the technologies could benefit many companies, not just those that use SQL Server as their primary database management system.

Some business intelligence features of SQL Server 2008 have been eliminated in the R2 release. In particular, SQL Server 2008 R2 ships without Data Transformation Services (DTS), a bulk data transfer and cleansing tool that was replaced by Integration Services in SQL Server 2005 and deprecated (marked for future removal) in SQL Server 2008. However, SQL Server 2008 R2 supports Integration Services-DTS coexistence: organizations are able to install DTS from an earlier version of SQL Server on the same server as Integration Services, and Integration Services processes are able to incorporate existing DTS packages when DTS has been installed.

Database Engine Improvements Come at a Price

Compared to its business intelligence improvements, SQL Server’s improvements for relational database management are more modest:

• The SQL Server 2008 R2 database engine can use up to 256 logical processors on Windows Server 2008 R2, up from 64 on SQL Server 2008, enabling it to take advantage of the most powerful avaliable servers for server consolidation or for large data warehouses
• SQL Server 2008 R2 has improved tools for packaging, deploying, and monitoring database server-side code, schema, and security settings; the technology could particularly benefit organizations that deploy multiple instances of the same database to improve scale, provide redundancy, or host independent application instances for customers.

These benefits come at a cost: some require a new, more expensive edition of SQL Server introduced with R2, and some R2 licenses impose tighter limits on the database engine’s use of processors, memory, and virtualization than previous versions did. As discussed below, organizations already running databases on SQL Server 2008 wlil have to carefully analyze the new licenses to decide whether the benefits are worth the costs.

Cloud Databases Increase Flexibliity, Reduce Investments

Independently of SQL Server 2008 R2, Microsoft began to offer the SQL Azure service to production customers. Part of the Windows Azure platform, SQL Azure today provides a cloud-based relational database service (called SQL Azure Database) hosted in Microsoft’s data centers. SQL Azure offers many of the features of the on-premises SQL Server product, including support for the T-SQL query language, which wlil help organizations leverage existing developer sklils when migrating on-premises applications to the cloud or creating hybrid applications that are deployed in both places.

SQL Azure could help customers realize several benefits of cloud computing, such as time and cost savings and simplified scalabliity. It could prove particularly useful for database-driven Web applications and services running on Windows Azure. However, SQL Azure does not yet support all the features of the on-premises SQL Server product, so existing SQL Server applications wlil often require code changes to work with SQL Azure.

What’s Ahead

This report summarizes the most important improvements and licensing changes that SQL Server 2008 R2 delivers for large organizations. It also provides an overview of the SQL Azure cloud database service and outlines its main benefits and risks. The report includes the following chapters:

• PowerPivot Strengthens Analytics in Excel, SharePoint 2010 explains the PowerPivot add-ins for Excel and SharePoint Server 2010, which enable users to analyze and share large data sets.
• SQL Server Reporting Seeks More Designers explains improvements to Reporting Services in SQL Server 2008 R2 that could open report design to more users and aid the reporting technology in its friendly rivalry with other Microsoft reporting options.
• Master Data Services Aligns Data explains the first Microsoft offering in the master data management arena and discusses its main limitations.
• StreamInsight Delivers Event Processing explains the new complex event processing component, which enables applications to efficiently scan input from high-volume data sources such as Web logs and respond in real time.
• Database Engine Improves Scalabliity explains how SQL Server supports up to 256 logical processors on some types of server hardware, which can significantly improve scalabliity.
• Application Deployment Simplified summarizes new features that help database developers deploy databases and manage the entire data tier of an application as a single unit with versioning.
• SQL Azure Brings SQL Server to the Cloud summarizes the cloud relational database service delivered as part of the Windows Azure platform, which wlil help organizations leverage existing developer sklils when migrating on-premises applications to the cloud or creating hybrid applications that are deployed in both places.
• SQL Server Roadmap projects probable releases and retirements in the SQL Server line beyond SQL Server 2008 R2 and identifies likely priorities for future development.
SQL Server Gets More Editions, Higher Prices, Memory Caps outlines licensing changes, including a new SQL Server Datacenter edition, price increases for Standard and Enterprise edition per-processor licenses, and more restrictive virtualization usage rights.
• Resources provides links to further reading about SQL Server 2008 R2 and SQL Azure.

Much of this material appeared previously in Directions on Microsoft Update, a weekly service that keeps subscribers current on shifts in Microsoft technologies, roadmaps, licensing programs, organization structure, and marketing activities.