SQL Server 2012 Enhancements
In addition to the brand new features in SQL Server 2012, there are a number of enhancements to existing features provided by SQL Server 2012. The following sections provide an overview of some of the major enhancements.
SQL Server Management Studio Enhancements
The standout features in the SQL Server 2012 SSMS that have been added or enhanced include the following:
- Alternative keyboard shortcut schemes—SSMS support two keyboard shortcut schemes. The default is based on the Microsoft Visual Studio 2010 keyboard shortcuts. The other scheme matches the keyboard shortcuts from SQL Server 2008 R2. The SQL Server 2000 shortcuts are no longer available in SQL Server 2012.
- Query Editor T-SQL debugger—The debugger was enhanced in a number of different ways, including improved breakpoint processing, expanded Watch window capabilities, and the ability to debug prior instances of SQL Server, including SQL Server 2005 (SP2) or later.
- Query Editor snippets—T-SQL code snippets are a new type of template that was added in SQL Server 2012. These templates are another great starting point when writing new T-SQL statements in the Database Engine Query Editor.
- Query Editor IntelliSense—IntelliSense has been optimized to produce better string matches. It has also been enhanced to support the newly added snippets.
- Extended Events GUI—SSMS now provides a graphical interface to create, manage, and monitor Extended Events sessions for monitoring SQL Server.
- Database Recovery advisor—The Database Recovery tool is launched from the Restore Database window and displays visual timeline that outlines the database backups that are available for restore, allowing a user to restore to a point in time by simply clicking the visual timeline; the necessary backups will be selected for recovery.
For more information on these enhancements to SSMS, see Chapter 4, “SQL Server Management Studio,” Chapter 35, “Monitoring SQL Server Performance,” and Chapter 11, “Database Backup and Restore.”
Resource Governor Enhancements
SQL Server 2012 provides enhancements to the Resource Governor that enable you to more effectively govern performance, especially for machines with a large number of CPU cores. The enhancements include the following:
- Support for up to 64 resource pools, an increase from the previous maximum of 20, to better enable partitioning larger machines between more workloads
- A new CAP_CPU_PERCENT option for resource pools to provide a means of setting a hard-cap limit on CPU resource usage, which can provide greater predictability
- A new AFFINITY option for resource pools providing a mechanism to affinitize resource pools to one or more schedulers and NUMA nodes, which can provide greater isolation of CPU resources
For more information on these new enhancements to Resource Governor, see Chapter 37, “Managing Workloads with the Resource Governor.”
Spatial Data Enhancements
SQL Server 2012 adds four new spatial type classes: FULLGLOBE, CIRCULARSTRING, COMPOUNDCURVE, and CURVEPOLYGON. SQL Server 2012 also includes features and enhancements that improve performance for spatial data and operations, including new spatial indexes, a spatial index hint, compression, improved index build time, and a new query plan for spatial queries. In addition, SQL Server 2012 provides further performance enhancements at the spatial library level for Distance() and ShortestLineTo() methods along with performance improvements at the SQL level for methods which are based on them, including STDistance() and ShortestLineTo().
For more information on the enhancements to spatial data and working with spatial data in SQL Server 2012, see Chapter 44.
Integration Services Enhancements
In SQL Server 2012, Microsoft has further extended the capabilities of SSIS into a much more comprehensive and robust data integration platform—with the emphasis on the word platform. The following are some of the highlights of SSIS 2012:
- A new deployment model for deploying projects to the Integration Services server.
- The ability to use server environments to specify runtime values for packages contained in a project.
- New views, stored procedures, and stored functions to help in troubleshooting performance and data problems.
- The Flat File connection manager now supports parsing of flat files with embedded qualifiers.
- Improved remapping of columns by the SSIS Designer when a new data source is connected. (Columns are remapped based on their name and data type rather than by lineage ID.)
- The Integration Services Merge and Merge Join transformations are more robust and reliable.
- Integration Services now includes the DQS Cleansing transformation that enables you to more easily and accurately improve the quality of data.
- The ability to use SQL Server 2012’s Change Data Capture technology from within Integration Services.
For more information on working with SQL Server Integration Services, see Chapter 54, “SQL Server Integration Services.”
Service Broker Enhancements
In SQL Server 2012, Service Broker includes the following new features:
- The ability to send messages to multiple services.
- The ability to reconfigure poison message handling and to view this setting for any service.
- Built-in tracking for how long a message has been in a queue. (A new column message_enqueue_time now exists on every queue.)
- Built-in support for AlwaysOn Availability.
For more information on Service Broker, see Chapter 50, “SQL Server Service Broker.”
Full-Text Search Enhancements
SQL Server 2012 full-text search enhancements include improvements in performance and scale and new functionality, including the previously discuss Semantic Search capability.
Full-text search in SQL Server 2012 now scales to more than 100 million documents. Significant improvement can also be realized in the traditional full-text query performance, which is reported to be approximately 7 to 10 times faster than in the previous versions of SQL Server. Additional performance improvements are a result of improved internal implementation, improved query plans, and preventing queries from blocking index updates.
The new functionality associated with full-text search includes the following:
- Property Search—In SQL Server 2012, users can now conduct searches on document properties.
- Customizable Near—The Custom Proximity Operator, or Customizable Near, makes it possible for users to specify how close a search term must appear to others before it is considered a successful match.
- New Wordbreaks
For more in-depth coverage of these new features and an introduction to SQL Server full-text search, see Chapter 51, “SQL Server Full-Text Search.”
Analysis Services Enhancements
SQL Server 2012 provides enhancements to almost every component of SSAS, along with the addition of major scale-out and in-memory capabilities. The following are some of the top new features and enhancements in SSAS:
- A new architecture for SQL Server 2012 SP1 PowerPivot that supports a PowerPivot server outside of a SharePoint 2013 farm that leverages Excel Services for querying, loading, refreshing, and saving data.
- PowerPivot (in Microsoft Excel 2013) supports deeper integration with data exploration workflows.
- Tabular models enhancements, including optimized storage for measures and key performance indicators (KPIs), extended data categorizations, extended characters, hierarchy annotation, and improved support when importing from Data Market (external data) data feeds.
- Tabular Model Designer Diagram View, which displays tables, with relationships between them, in a graphical format.
- xVelocity In-memory Analytics Engine for tabular models.
- New trace events in multidimensional databases to help troubleshoot lock-related query or processing problems.
- Unified business intelligence (BI) semantic modeling schema.
- Increased language support, which now includes MDX, DMX, DAX, XML/A, and ASSL.
For more information on the new features and enhancements to Analysis Services, see Chapter 53, “SQL Server 2012 Analysis Services.”
Reporting Services Enhancements
SQL Server 2012 includes only a small number of significant updates to the core SSRS platform, which is not surprising considering the SSRS overhaul that came with SQL Server 2008 R2. The enhancements provided in SQL Server 2012 include the following:
- An updated rendering extension for Microsoft Excel 2007-2010, supported both by Report Builder and the SSRS web rendering controls
- An updated rendering extension for Microsoft Word 2007-2010
- The ability to view web-based reports on touch-screen Apple iOS 6 devices and on Microsoft Surface-based tablet devices
For more information on Replication Services in SQL Server 2012, see Chapter 55, “SQL Server 2012 Reporting Services.”
Master Data Services Enhancements
Several new usability and flexibility improvements and enhancements have been added in the SQL Server 2012 version of Master Data Services (MDS). Following are some of the top new features and enhancements of MDS:
- The ability to use Excel to manage master data via the Master Data Services Add-in for Excel.
- The ability to match data before loading, to confirm that you are not adding duplicate records, using SQL Server Data Quality Services (DQS) matching to compare two sources of data.
- The ability to load all members and attribute values for an entity at one time.
- A new higher-performance MDSModelDeploy command-line tool is now available to create and deploy packages with data, in addition to a Model Deployment Wizard in the web application that is used to deploy model structures only.
- A new Model Package Editor that enables you to deploy selected parts of a model rather than the entire model.
- Creating a recursive hierarchy with three or more levels is now supported in MDS.
- SharePoint workflow integration.
For a more in-depth overview of the features and capabilities of MDS, see Chapter 56, “Master Data Services and Data Quality Services.”