What's New in SQL Server 2012

This topic describes the new features in SQL Server 2012, including service packs and any other releases, such as cumulative updates, that include new features.

For information about features added in SQL Server 2012 Service Pack 1 Cumulative Update 4, see New or Enhanced Features in SQL Server 2012 Service Pack 1 Cumulative Update 4

For information about features added in SQL Server 2012 Service Pack 1 Cumulative Update 2, see New or Enhanced Features in SQL Server 2012 Cumulative Update 2

For information about features added in SQL Server 2012 Service Pack 1, see New or Enhanced Features in SQL Server 2012 SP1

New or Enhanced Features in SQL Server 2012 Service Pack 1 Cumulative Update 4

SQL Server 2012 Service Pack 1 Cumulative Update 4 expands the supported features for SQL Server backup and restore with Windows Azure Storage. In this release, existing PowerShell cmdlets have been enhanced and new PowerShell cmdlets have been added. For more information, see Use PowerShell to Backup Multiple Databases to Windows Azure Blob Storage Service.

New or Enhanced Features in SQL Server 2012 Service Pack 1 Cumulative Update 2

SQL Server 2012 Service Pack 1 Cumulative Update 2 expands the supported features for SQL Server Sysprep to include SQL Server Analysis Services, SQL Server Integration Services, and shared features, which enables SQL Server Sysprep to be used in a wider variety of image preparation scenarios. For more information, see Expanded SysPrep Support in SQL Server 2012 SP1 CU2.

SQL Server Backup and Restore with the Windows Azure Blob Storage Service

Starting in SQL Server 2012 SP1 Cumulative Update 2, you can back up to and restore from the Windows Azure Blob Storage service. This enhancement can be used to backup SQL Server databases on an on-premises instance or SQL Server running on a Windows Azure Virtual Machine. With this new functionality, you can now back up directly to and restore from the Windows Azure Blob storage in addition to DISK and TAPE. Backing up to the Windows Azure Blob storage service has specific benefits. It can be an offsite storage option for the on-premises database backups, or used when you migrate an on-premises database to an instance of SQL Server running in a Windows Azure Virtual Machine. For more details on benefits, see SQL Server Backup and Restore with Windows Azure Blob Storage Service.

The following are limitations specific to this release:

  • The maximum backup size supported is 1 TB.

  • In this implementation, you can issue backup or restore statements by using TSQL or SMO. A backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled.

  • For more details, see SQL Server Backup and Restore with Windows Azure Blob Storage Service

For more information, see the following:

New or Enhanced Features in SQL Server 2012 SP1

Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade

SQL Server 2012 SP1 introduces support for cross-cluster migration of AlwaysOn Availability Groups deployments to a new Windows Server Failover Clustering (WSFC) cluster. A cross-cluster migration moves one AlwaysOn availability group or a batch of availability groups to the new, destination WSFC cluster with minimal downtime. The cross-cluster migration process enables you to maintain your service level agreements (SLAs) when upgrading to a Windows Server 2012 cluster.  SQL Server 2012 SP1 must be installed and enabled for AlwaysOn on the destination WSFC cluster.  The success of cross-cluster migration depends on thorough planning and preparation of the destination WSFC cluster.

For more information, see Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade.

Selective XML Index

SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. For more information, see Selective XML Indexes (SXI).

DBCC SHOW_STATISTICS works with SELECT permission

In earlier releases of SQL Server, customers need administrative or ownership permissions to run DBCC SHOW_STATISTICS. This restriction impacted the Distributed Query functionality in SQL Server because, in many cases, customers running distributed queries did not have administrative or ownership permissions against remote tables to be able to gather statistics as part of the compilation of the distributed query. While such scenarios still execute, it often results in sub-optimal query plan choices that negatively impact performance. SQL Server 2012 SP1 modifies the permission restrictions and allows users with SELECT permission to use this command. Note that the following requirements exist for SELECT permissions to be sufficient to run the command:

  • Users must have permissions on all columns in the statistics object

  • Users must have permission on all columns in a filter condition (if one exists)

Customers using Distributed Query should notice that statistics can now be used when compiling queries from remote SQL Server data sources where they have only SELECT permissions. Trace flag 9485 exists to revert the new permission check to SQL Server 2012 RTM behavior in case of regression in customer scenarios.

New function returns statistics properties

The dynamic management function sys.dm_db_stats_properties returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database. You can use this function to return information such as the last time the statistics object was updated for the table or indexed view, or the number of rows that were sampled for statistical calculations. For more information, see sys.dm_db_stats_properties (Transact-SQL).

SSMS Complete in Express

Customers who use the SQL Server Express With Tools, SQL Server Express COMP, SQL Server Express with Advanced Services and SQL Server Management Studio Express now have SQL Server Management Studio complete. These Express Editions now ship the SSMS feature with functionalities that exist with the SSMS from full editions of SQL Server.

SlipStream Full Installation

Provides customers with a pre-built "Slipstream image" that consists of a compressed, self-extracting .exe and a '.box' payload file that contains a SQL Server 2012 RTM image (Setup.exe, MSI’s, etc.) along with the most recent Service Pack. When the package is executed, the two images are merged in real-time providing the user with a single Setup workflow experience, landing the operation (Install, Upgrade, AddNode, etc) at the encompassed SP1 level, executing any updates made to Setup components themselves in the Service Pack.

Customers can perform new instance installations (or SQL Server 2008/2008 R2 upgrades) at the Service Pack functional level using a single click install workflow experience. This can be performed directly by utilizing the slipstream .exe from the command line just as they would use Setup.exe by supplying all appropriate setup command line parameters. This is in contrast to having to install RTM media, find and download the desired Service Pack (for the correct language and CPU), apply the Service Pack, then load Setup.exe to perform the desired setup action.

Business Intelligence highlights (with SQL Server 2012 SP1, Office and SharePoint Server 2013 )

Business Intelligence highlights (with SQL Server SP1, Office and SharePoint Server 2013 Preview) are:

Note

For a summary of installing SQL Server 2012 SP1 and SQL Server BI features, see Upgrade SQL Server BI Features to SQL Server 2012 SP1.

Management Object Support Added for Resource Governor DDL

In SQL Server 2012 SP1, SQL Server Management Objects are updated to support the syntax of CREATE RESOURCE POOL (Transact-SQL):

  • CAP_CPU_PERCENT

  • AFFINITY SCHEDULER

  • AFFINITY NUMANODE

The following management objects limit CPU usage and affinitize resource pools to schedulers and NUMA nodes:

Additional Information on the Web

SQL Server Resource Center

SQL Server BI Resource Center

SQLCat Web Site

SQL Server Release Notes