SQL Server 2012 Targets BI, Development, Availability

Posted: 
November 21, 2011
Report by: 

The next version of SQL Server, SQL Server 2012 (formerly code-named SQL Server Denali), will be generally available in the first half of 2012 and is designed to enable more rapid development, easier deployment and management, faster business intelligence (BI) and data analysis, and greater uptime with more capable disaster recovery capability. 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.

Focus on Business Intelligence

A primary focus of SQL Server 2012 is easier and more powerful BI. 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. SQL Server 2012 includes an updated version of PowerPivot, first included in SQL Server 2008 R2, and Power View, a new self-service BI reporting feature that enables data analysis by less technical users, with data from IT managed sources, but without requiring ongoing support from IT to provide data to be analyzed. The BI tools and additional BI changes should also require less development time before users can begin deriving insights from the data.

Technology acquired by Microsoft from Stratature in 2007 and Zoomix in 2008 are also integrated into the product and help to ensure the data available for BI tasks is accurate and available across the organization. BI is also enhanced by a new feature in SQL Server 2012 intended to ensure the product can deliver high performance even with very large data sets, common in data warehouses, which are special-purpose databases designed and organized to store large volumes of historical data to aid decision making in organizations.

Enhanced PowerPivot

SQL Server 2012 will deliver improved versions of PowerPivot for Excel 2010 and PowerPivot for SharePoint Server. (See "PowerPivot Strengthens Analytics in Excel, SharePoint 2010".)

Like its predecessors available since May 2010, PowerPivot enables expert users to create and process large data sets (called models) embedded in Excel workbooks to analyze the data to look for patterns, track business metrics, or perform other BI tasks. This new version of PowerPivot adds functionality to make model creation and sharing easier.

Using PowerPivot for SharePoint included in SQL Server 2012, a user can publish their PowerPivot models to SharePoint Server 2010 SP1 so that users can analyze the workbooks using just a browser, without Excel 2010 or PowerPivot on their desktops. Excel Services and PowerPivot for SharePoint tackle one of the central problems of Excel as a BI tool: multiple, inconsistent workbook versions with out-of-date data. PowerPivot for SharePoint also enables monitoring of workbook usage and identification of gaps in BI systems.

Power View Self-Service Data Visualization

Power View, which is a new feature of SQL Server 2012 Reporting Services, provides a method for users who are not business analysts to explore data stored in PowerPivot for SharePoint, examine it for patterns or insights, and share that information with others.

Power View charts are different from traditional Excel charts, however, in that they can be animated and interactive across an entire area of the Power View display, called a canvas. Changes and filters in one chart on one area of the canvas can affect other charts on the canvas, resulting in interactivity that Excel cannot offer, at least without requiring significant amounts of custom code development for each workbook. Power View is designed to help users easily spot patterns or anomalies in the data, and the canvas can be displayed directly within any Web browser that supports Silverlight, or shared and displayed within PowerPoint 2010, where it retains its animations and interactivity.

A New Model for BI

Microsoft has introduced a new, parallel set of Analysis Services components and BI tools in SQL Server 2012 designed to make BI more approachable for new developers, but taking advantage of it may require retraining for business analysts familiar with its predecessor. In particular, a new data model called the BI Semantic Model (BISM) and a new database query engine and query language, called Data Analysis Expressions (DAX) underpin PowerPivot and Power View. BISM will not replace the previously available Unified Dimensional Model (UDM) components of Analysis Services.

Reliable BI Data

To ensure the highest-quality data for BI tasks and data warehouses, SQL Server 2012 also includes Data Quality Services (DQS). By taking advantage of a constantly updating knowledge base of data patterns from within a business, DQS profiles, cleanses, and matches data to ensure that data which has been manually put into the system to be analyzed is as reliable as possible. Master Data Services (MDS), first provided with SQL Server 2008, provides a single source of master data for use across an organization, and MDS in SQL Server 2012 now enables MDS creation and management using Excel.

Maximum Performance for Data Warehouses

SQL Server 2012 includes functionality designed to improve the performance of data warehouse queries, using a feature called a Columnstore index. The Columnstore index takes advantage of data patterns common in data warehouses in order to compress data in memory and maximize performance even with very large datasets and queries. Although this Columnstore index does take time to initially build and must be updated completely when new data is imported, the feature aligns well with how customers will likely use it, since data warehouses generally use data copied from transactional databases on a regular schedule.

Improved Development Tools

The development tools included with all versions of SQL Server have been completely rewritten, and SQL Server 2012 includes an update of the earlier Data-tier Applications (DAC) framework which can now deploy and update databases both on-premises and in SQL Azure, Microsoft's cloud-based database technology.

Single Unified SQL Development Environment

Database development within Visual Studio (VS) is not new; it has been included for some time. Notably, VS 2008 shipped a Database edition targeted exclusively at database developers. When VS 2010 released, the functionality included in Database edition was subsumed into the Premium and Ultimate editions.

Now called the SQL Server Data Tools (SSDT), the new development environment for SQL Server 2012 is available both as a stand-alone application and as a set of plug-ins for Visual Studio (VS) 2010 serves as the primary development tool for all SQL Server 2012 development tasks, and is now also the SQL Server BI development tool, replacing the BI Development Studio (BIDS) included in previous versions of SQL Server.

Key enhancements focus on improving developer workflow, productivity, and code reliability. The tools will be useful for individuals and teams of SQL Server application developers and will likely help teams develop and update applications rapidly, with fewer errors when deploying or updating databases, by including features such as Transact-SQL language (T-SQL, the primary language used to develop for SQL Server) and IntelliSense (a feature of VS that provides pop-up suggestions for variables, methods, and other information as code is typed). Although IntelliSense was available in the SQL Server Management Studio (SSMS) administration tools in earlier versions of SQL Server, it was not available in the tools used by developers.

With SSDT, developers can explore and modify existing databases, drilling down into each database to the row level and changing an existing table or designing a new table using Visual Table Designer (a table editor based on a graphical user interface similar to that used in SSMS) or a new T-SQL script editor. Both editing tools can be used simultaneously with concurrent editing between them.

The functionality included in SSDT enables comprehensive version control, change management, and deployment. Development projects created in SSDT can also be used with Team Foundation Server, enabling database developers to collaborate on a project.

SSDT also allows developers to begin a project without needing to design a specific version for each major version of SQL Server, including SQL Azure. Previously developers had to decide when beginning their project what version of SQL would be used for the application.

Easier Database Development, Deployment, and Updating

The DAC framework in SQL Server 2008 R2 first began to address deployment and schema management of databases, and SQL Server 2012 builds upon that to provide a more agile development process for database development, beginning with SSDT. SSDT enables development of updates for a database even while disconnected from the database, allowing the developer to work with a current version without requiring any dependence on a database administrator (DBA). SSDT uses the new SQL Server LocalDB feature (a single-user, local-connectivity-only instance of SQL Server that starts and stops as needed for testing and debugging) and uses DAC to deploy and update the database. This process allows a virtual deployment of the resulting database for testing without risking a production version to test it. Used with SSDT, DAC could help dramatically accelerate the speed of changes in corporate application development or internally hosted databases by minimizing the work required by a DBA to update the database.

SSDT lets developers easily see the differences between their current project and a production version or set up other tasks, such as data importing/modification or additional predeployment or postdeployment tasks (for example, unit testing or other acceptance testing) as a part of the project. SSDT also allows developers to visually compare two versions of a database (at a schema level), 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.

Delivering Enterprise Availability, Security, and Manageability

Among the features that SQL Server 2012 delivers are several designed to help customers more easily set up and manage high availability, disaster recovery and security within solutions in order to meet uptime and compliance objectives.

AlwaysOn Improves Replication, Availability

SQL Server 2012 provides a new infrastructure for replicating databases from server to server, called AlwaysOn. This new infrastructure could increase server availability (uptime) and speed disaster recovery (such as recovery from loss of a data center) by simplifying configuration and management and broadening the capabilities of SQL Server database mirroring technology.

AlwaysOn uses replication to maintain secondary copies of databases that can be brought online in the event of a failure or disaster (called Availability Groups), and the replicas can be used for tasks like reporting and backup in the interim. AlwaysOn helps customers to both avoid downtime and ensure the best performance of the primary database. These replicas can be in the same data center or a remote data center as needed to provide the necessary high-availability or disaster recovery infrastructure.

Compared with earlier versions of similar functionality in SQL Server, AlwaysOn enables more secondary replicas, real-time read access to those servers with replicas, and a simplified deployment process for both high availability and disaster recovery.

Windows Server Core Compatibility, LocalDB Maximize Security

Windows Server 2008 introduced a new installation mode, Server Core, which installs only the files needed to support a designated infrastructure role, such as running a Dynamic Host Configuration Protocol server or a Web server. With Windows Server 2008 R2, Server Core installations could include the .NET Framework. Even with the addition of the .NET Framework, a Server Core installation generally requires less maintenance and updating than a full Windows server installation because fewer components are installed and running on the server, but such servers must be managed remotely. This also means fewer components are exposed to attack over the network, making servers running the Server Core installation less vulnerable to viruses, data theft, or other compromises, as well as delivering better uptime by requiring less patching. SQL Server 2012 is the first version of SQL Server that can be deployed on Windows Server 2008 R2 installations running Windows Server Core, minimizing the OS patching necessary to run a SQL Server 2012 system.

Another new feature of SQL Server 2012, LocalDB, is designed to provide an efficient, secure run-time version of SQL Server and is built upon SQL Server Express edition. Applications running on Windows often need to deal with large volumes of data that can't be loaded into RAM, and applications for mobile devices may require replicating data from a server so that the application can be used while disconnected. An example might be an insurance claims application used for adjusters. Intended for use with these types of PC and mobile applications, LocalDB is not always running (unlike SQL Server Express); instead, when an application attempts to connect to a database hosted by LocalDB, the LocalDB service starts when needed and stops automatically. By minimizing when the service is running and sharing the single instance of the SQL Server application, LocalDB could result in significant power savings, as well as a reduced security and servicing footprint.

Availability and Limitations

Release Candidate 0 (RC0) of SQL Server 2012 was released in Nov. 2011 and is feature complete. The final product is expected during the first half of 2012 and will be available in three editions. Not all features mentioned are available in all editions. (See the illustration "SQL Server 2012 Editions".)

Developers looking to make the most of the new tools included in SQL Server 2012 and administrators looking to take advantage of AlwaysOn will likely require training to make the most of those technologies. SQL Server 2012 de-emphasizes the earlier UDM BI model in favor of BISM, and BI analysts looking to make the most of SQL Server 2012, whether familiar with UDM or not, will likely require training in BISM, PowerPivot, and Power View.

The new self-service BI features included in SQL Server 2012 will work well for organizations that have invested in SharePoint 2010 and Office 2010 and want to centralize and broaden BI use across their organization. Organizations that have not adopted or migrated to SharePoint 2010 or use earlier versions of 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.

SQL Server 2012 will require SharePoint 2010 SP1 in order to use PowerPivot for SharePoint or Power View and Excel 2010 SP1 in order to use PowerPivot for Excel. Power View will also require a browser capable of running Microsoft Silverlight. PowerPivot for SharePoint and Power View will continue to require SQL Server 2012 Enterprise edition or the new Business Intelligence edition for each SharePoint Server hosting them. They will also require SharePoint Server 2010 SP1, and all client users or devices will require a SharePoint Server Enterprise Client Access License (CAL) for that version. All PowerPivot users will require licenses for any data sources they employ; for example, users will require SQL Server CALs when using a SQL Server data source that has been licensed in server-CAL mode.

SQL Server 2012 will require the .NET Framework 3.5 SP1, and .NET Framework 4.0 as well as Windows PowerShell, and an x86 or x64 edition of Windows Vista SP2, Windows Server 2008 SP2, or newer version of Windows. SQL Server 2012 requires 512MB of RAM for 32-bit installations and 1GB of RAM for 64-bit installations. SQL Server 2008 R2 deprecated support for the Intel Itanium processor architecture, and SQL Server 2012 will not run on Itanium. Installations of SQL Server 2012 on Windows Server Core require Windows Server 2008 R2 SP1 (only available in x64 editions).

Resources

The roadmap for SQL Server 2012 is described in the "Application Platform" chapter on page 44 of the Dec. 2011 Enterprise Software Roadmap.

SQL Server 2012 Release Candidate 0 (RC0) is available at www.microsoft.com/download/en/details.aspx?id=28145.

SQL Server 2012 Release Candidate 0 (RC0) preview documentation is at msdn.microsoft.com/library/ms130214(SQL.110).aspx.

AlwaysOn Availability Groups were described in "Next SQL Server Improves Replication, Availability" on page 24 of the May 2011 Update.

The SQL Server 2012 Columnstore index (formerly code-named Apollo) is described in "Next SQL Server Accelerates Complex Queries" on page 10 of the Oct. 2011 Update.

The LocalDB feature of SQL Server 2012 is described in "New Local Database Mode Arriving in Next SQL Server" on page 7 of the Sept. 2011 Update.

DAX, MDX, and the new Business Intelligence Semantic Model were discussed in "Next SQL Server Includes a New Business Intelligence Layer" on page 12 of the Apr. 2011 Update.

Power View was discussed in "Next SQL Server Features Self-Service Data Visualizations" on page 19 of the Nov. 2011 Update.

SQL Server 2012 support for BI tools from mobile devices is described in "SQL Server 2012 Mobile BI Strategy Outlined" on page 24 of the Nov. 2011 Update.

Analysis Services, PowerPivot, and other SQL Server BI technologies are explained in more detail in the Dec. 2010 Research Report, "Evaluating SQL Server 2008 R2 and SQL Azure."

The SQL Server 2012 Visual Studio tools update is described in "Next SQL Server Updates Visual Studio Tools" on page 17 of the June 2011 Update.

Enhancements to Data-tier Applications in SQL Server 2012 were described in "SQL Server 'Denali' to Simplify Application Deployment" on page 27 of the May 2011 Update.

I Am A... I Am A...