A Look at Microsoft SQL Server 2012

Excerpted from Global Knowledge White Paper: Microsoft SQL Server 2012: What to Expect

In this post, we will explore just some of the new features under each theme to give a feel for the capabilities of the upcoming release. Other features and a pre-release version of the software are available on the Microsoft web site.

Mission Critical Confidence

AlwaysOn for High Availability and Disaster Recovery

Before SQL Server 2012, we had four options for High Availability (HA): Server Clustering, Database Mirroring, Log Shipping, and Peer-to-Peer (P2P) Replication. Each solution had its pros and cons.

SQL Server 2012 effectively combines the pros of each of these solutions with its AlwaysOn feature. With AlwaysOn, you will have the automatic failover of Mirroring at the Database level but with the added ability to create multiple secondaries for read-only load balancing. AlwaysOn will provide HADR and Load Balancing in one effective solution.

Project Apollo Provides Column Compression and Performance Gains

SQL Server 2008 introduced Data Compression at the Row and Page Levels to provide not only storage savings but also, surprisingly, performance gains based on less I/O. In SQL Server 2008 R2, the VertiPaq technology allowed massive column-level compression in conjunction with the PowerPivot add-in. In SQL Server 2012, the new column-store index, as part of Project Apollo, will provide the same massive column-level compression within the Database Engine itself. Data Warehouse queries against Star or Snowflake schemas will be optimized to give potential performance gains and storage savings in the order of 100 times improvement.

Organizational Compliance With Enterprise-Wide Security Controls

On the security front, new features in SQL Server 2012 include the ability to create user-defined server roles. This allows specific permissions to be applied to groups of logins instead of using the fixed server roles or having to give permissions directly to each login.

Another interesting security feature is the Contained Database that allows a database to be moved or copied to a new instance without having the dependency on login objects. Users can connect to the database without authenticating a login at the Database Engine-level. There exist some specific security threats that relate uniquely to Contained Databases (documented in SQL Server 2012 Books Online), but if you need a database to be easily portable, the benefits may outweigh the drawbacks.

The SQL Audit feature, which was introduced in SQL Server 2008 Enterprise Edition, allows Server- and Database- level auditing without the need for DDL or DML Trigger, giving a more efficient method of auditing that is built in to the Database Engine. SQL Server 2012 allows new features such as filtering audit output and now supports Server level audits for all SQL Server editions.

Breakthrough Insight

SSAS 2012: VertiPaq, BI Semantic Model and PowerPivot

SQL Server 2008 R2 introduced support for PowerPivot through Excel 2010 and SharePoint 2010 using its VertiPaq technology for massive column compression. This allowed advanced users to develop their own Business Intelligence solutions via Excel or SharePoint. The Vertipaq technology allowed literally hundreds of millions of rows of data to be imported and analyzed efficiently on the client via Excel and on the Server via SharePoint.

Based on the popularity of the technology, SSAS 2012 enables Vertipaq on the server-side within Analysis Services itself in a new configuration option named Tabular Mode that supports the BI Semantic Model (BISM).

Consistent data via Data Quality Services

SQL Server Data Quality Services (DQS) is new in SQL Server 2012 and provides a knowledge-based platform for data cleansing, including data correction and deduplication. The DQS Client application is intended for the end user to automate data correction in addition to an interactive mode.

Parallel Data Warehouse and Reference Architectures

When Microsoft acquired DATAllegro, its stated direction was to support Very Large Databases (VLDB) through a scalable appliance architecture using the combination of hardware and software to support databases in excess of 100TB. Now with SQL Server 2012, that vision is closer to reality with many hardware vendors supporting the Fast Track configurations and reference architectures. The Parallel Data Warehouse edition supports features such as Remote BLOB Storage and large-scale data partitioning while using up to 256 logical CPUs for high performance in the VLDB arena.

Cloud on Your Terms

SQL Azure is an exciting new SQL Server edition that introduces the cloud as a data platform for the future. SQL Azure now supports Open Data (OData) protocol for querying and updating data in the cloud. Project Juneau extends the Visual Studio 2010 shell to support SQL Azure and SQL Server applications using the .NET framework through standard data APIs such as ADO.NET, ODBC, JDBC, etc.


Microsoft SQL Server 2012 has many great new features that will allow you to develop higher performing, more scalable next-generation applications. Using the same architecture and management tools, customers will be able to smoothly upgrade their systems and skills based on the need for the new features and according to their own schedule.

In this article

Join the Conversation