Microsoft Research Reports

This report is designed for IT planners and developers who are considering migration to SQL Server 2012. Based on the Nov. 2011 release candidate of the SQL Server 2012 software, the report discusses the new features and benefits available in SQL Server 2012 for businesses. It also discusses improved tools and APIs that SQL Server 2012 delivers for developers and changes that could affect migration of applications to the new version.

INTRODUCTION

The next version of SQL Server, SQL Server 2012 (formerly code-named Denali) will be generally available in the first half of 2012 and is designed to enable greater uptime with more capable disaster recovery capability, easier business intelligence (BI) and data analysis, and more rapid development, deployment, and management of database applications. With these and other updates available in SQL Server 2012, the product should provide a compelling upgrade from earlier versions, especially for customers who have not upgraded since SQL Server 2005.

Delivering Enterprise Availability and Reliability

SQL Server 2012 provides a new infrastructure for high availability and disaster recovery of database servers, called AlwaysOn. Organizations can take advantage of these improvements for their own solutions in order to meet uptime and compliance objectives.

Single System for High Availability and Disaster Recovery

AlwaysOn provides replication of data between primary to secondary servers and failover between servers to aid both high availability and disaster recovery.

High availability. High-availability features aim to keep databases working when their primary servers become unavailable. High availability is provided by a secondary server, which is ready should the primary server become temporarily unavailable due to server load, hardware failure, or a scheduled update. The former primary server remains generally available at some time in the future to resume the primary role or assume the secondary role. A key aspect of high availability is providing the switchover (failover) to the secondary server with as little downtime as possible.

Disaster recovery. Disaster recovery features aim to bring databases back up after some catastrophic event (such as a fire) takes down servers. Disaster recovery is different from high availability in that a secondary server assumes the role of the primary server for the foreseeable future because some event (usually at the data center or network level) has precluded the availability of the original server. Disaster recovery generally involves a server at a second location from the first server, in the event of an entire site failing due to a natural disaster or similar large-scale problem.

Compared with earlier versions of similar functionality in SQL Server, AlwaysOn enables a larger number of secondary servers, allows real-time read access to secondary servers for purposes such as reporting and backup, and simplifies deployment and management for both high availability and disaster recovery. AlwaysOn also does not require identical server hardware or expensive networked storage systems. The result should be less downtime with lower hardware cost and simpler configuration than in earlier versions of SQL Server.

Enterprise Edition, Other Resources Required

AlwaysOn is only available in the Enterprise edition of SQL Server 2012. AlwaysOn relies on replication of databases over the network rather than shared disk storage, so organizations will have to ensure they have networks whose bandwidth and latency can handle their database workloads. AlwaysOn also requires Windows Server Failover Clustering, a feature that is exclusive to Windows Server Enterprise and Datacenter editions.

Providing Self-Service Business Intelligence

SQL Server 2012 delivers new components for BI that are designed to make it easier to use and more powerful. BI is a catchall term for the process of deriving business trends and decisions from operational databases (such as sales orders or customer support events) by summarizing multiple data points. BI technology helps organizations optimize core business processes (for example, estimating demand and managing their supply chains), track key performance metrics, and generally understand what is going on within the business and identify opportunities for improvements.

Business Units Build Their Own Business Intelligence

With SQL Server 2012, Microsoft hopes to enable individual business units to build BI tools for their own purposes, without relying on an organization's central IT arm or requiring the significant skillset required for previous versions of SQL Server BI tools. The strategy: Enable expert Excel users to design databases for analysis, load those databases from an organization's key data sources, and then hand them off to other users in their business units who can build analysis reports and charts with Web-based tools. SQL Server 2012 delivers the following new or improved components for this model:

• Enhanced PowerPivot plug-ins for Excel and SharePoint Server, which enable Excel experts to build analytic databases and distribute spreadsheets that analyze them
• A new Power View report designer for SharePoint Server, which enables SharePoint users to build interactive, animated reports that can be displayed in any Web browser or in PowerPoint 2010 presentations
• Enhancements to the SQL Server database engine to speed performance in data warehouses, databases designed to act as BI data sources.

Compared with earlier versions of SQL Server, the new PowerPivot technology enables BI for a broader category of business analysts and enables BI tools to be shared across the organization through SharePoint Server.

Software Investment, Collaboration Needed

The new BI features included in SQL Server 2012 are only available in the new Business Intelligence edition and the Enterprise edition of SQL Server 2012. Organizations that have not adopted or migrated to SharePoint Server 2010 or Office 2010 will not be able to take advantage of the new BI features until they update. Deploying and maintaining the BI features also requires close collaboration between SharePoint administrators and SQL Server administrators.

Unified Development, Even to the Cloud

SQL Server 2012 delivers an enhanced environment for software developers to speed developer workflow, enhance productivity, and improve code reliability, while minimizing the number of tools necessary. The tools will also help teams develop and update SQL Server—based applications rapidly, with fewer errors when deploying or updating databases. Database applications can be deployed and updated using a single framework, regardless of the version of SQL Server being deployed to and whether the database is on-premises or in SQL Azure, Microsoft's cloud-based database technology.

One Environment, Enhanced Deployment

SQL Server 2012 includes two key improvements intended to simplify the development and deployment of database and BI applications.

Unified development environment. The SQL Server Data Tools (previously code-named Juneau) provide a single development environment for all SQL Server 2012 development tasks, including BI development. Previous versions of SQL Server required distinct tools for each component being developed, and software developers often had to resort to database administration tools to do tasks not supported by the development environment. The Data Tools also deliver many improvements over previous versions; for example, they provide IntelliSense (a feature of Visual Studio that provides pop-up suggestions for methods, parameters, and other information as code is typed) for the Transact-SQL query language used by SQL Server.

Database deployment. The Data-tier Application (DAC) framework initially provided in SQL Server 2008 R2 has been expanded to provide a more agile and unified deployment process for database applications, both on-premises and in SQL Azure. Used with the Data Tools, DAC could speed updates to corporate applications and internally hosted databases by minimizing the steps needed to deploy a database and reducing the work that requires a database administrator (DBA). The Data Tools also deliver many improvements for testing and deploying databases. For example, they enable development of updates for a database even while disconnected from the database using LocalDB, a locally installed version of SQL Server, allowing developers to work with a current version without requiring the assistance of a DBA. The Data Tools also allow developers to visually compare two versions of a database (at a schema level) before deployment, to understand any potential breaking changes or dependencies, and either update the current version immediately or save the changes as a script to be run later.

Software developers will benefit from a number of other improvements in SQL Server 2012, depending on the type of applications they are developing. For example, SQL Server 2012 delivers a better-performing and more manageable database engine for PC applications, and numerous improvements for applications that store documents and other types of files in databases.

SQL Server 2012 Availability

SQL Server 2012 Release Candidate 0 (RC0) was released in Nov. 2011 and is feature complete. RC0 is provided for customers to evaluate the product and provide feedback on compatibility or other issues experienced with it ahead of the product's final release during the first half of 2012. While major changes are unlikely, some features could change between RC0 and the first production release of SQL Server 2012.

The production release is expected during the first half of 2012. SQL Server 2012 will be available in three editions, with important changes to its licensing model. (See the illustration "SQL Server 2012 Editions".)

What's Ahead

This report is designed for IT planners and others who are considering migration to SQL Server 2012. It discusses the value, benefits, and new features available in SQL Server 2012 for businesses, and the benefits to developers in migrating to SQL Server 2012.

The report is organized into the following chapters:

SQL Server Improves Replication, Availability explains the features of SQL Server's new AlwaysOn infrastructure for replication, high availability, and disaster recovery.

New Local Database Mode Arriving in SQL Server describes the new LocalDB version of SQL Express and how it benefits businesses and developers.

SQL Server Includes a New Business Intelligence Layer discusses the changes made to BI development in SQL Server 2012 with the new BI Semantic Model.

PowerPivot Strengthens Analytics in Excel, SharePoint 2010 explains the role PowerPivot plays in SQL Server 2012 BI.

Power View Delivers Self-Service Data Visualizations describes the new Power View feature designed to enable self-service analysis from data stored in PowerPivot workbooks.

SQL Server Accelerates Complex Queries explains the new columnstore index designed to speed large data warehouse queries in SQL Server 2012.

SQL Server Development Enhancements overviews the key value of SQL Server 2012 for BI and database application developers.

SQL Server Improves Tools for Developers describes the new unified developer experience provided by SQL Server Data Tools and the features provided by the Data-tier Application framework for deploying and managing database applications.

FileTable to Blend SQL Server, Files explains the new FileTable functionality designed to provide easier access from within the Windows file system to files stored in SQL Server databases.

Resources contains links to Microsoft information about SQL Server 2012.