Manageability Enhancements (Database Engine)
Manageability of the SQL Server 2012 Database Engine is improved by enhancements to tools and monitoring features.
SQL Server Management Studio
SQL Server Management Studio introduces the following features in SQL Server 2012:
Alternative keyboard shortcut schemes
SQL Server Management Studio supports two keyboard shortcut schemes. The new default keyboard shortcuts are based on the Microsoft Visual Studio 2010 keyboard shortcuts. You can configure Management Studio to use the keyboard shortcuts from SQL Server 2008 R2.
[!UWAGA]
SQL Server 2012 does not include the SQL Server 2000 Enterprise Manager shortcuts.
For more information, see SQL Server Management Studio Keyboard Shortcuts.
Query Editor
The Database Engine Query Editor introduces enhanced functionality for Transact-SQL debugging and IntelliSense.
The Transact-SQL debugger introduces the following new features:
You can now debug Transact-SQL scripts running on instances of SQL Server 2005 Service Pack 2 (SP2) or later.
Transact-SQL breakpoints now support the following functionality:
A breakpoint condition is a Transact-SQL expression whose evaluation determines whether the breakpoint is invoked. For more information, see Specify a Breakpoint Condition.
A breakpoint hit count specifies the number of times a breakpoint is encountered before it is invoked. For more information, see Specify a Hit Count.
A breakpoint filter limits the breakpoint to operating only on specified computers, processes, or threads. For more information, see Specify a Breakpoint Filter.
A breakpoint action specifies a custom task that is performed when the breakpoint is invoked. For more information, see Specify a Breakpoint Action.
You can edit a breakpoint location to move a breakpoint from one Transact-SQL statement to another. For more information, See Edit a Breakpoint Location.
The Watch window and Quick Watch now support watching Transact-SQL expressions. For more information, see Transact-SQL Debugger Information.
When you move the cursor over a Transact-SQL identifier, a Quick Info pop up displays the name of the expression and its current value. For more information, see Transact-SQL Debugger Information.
Transact-SQL IntelliSense
Transact-SQL IntelliSense introduces the following new features:
Breakpoint validation prevents setting a breakpoint in an invalid location.
Transact-SQL code snippets are templates you can use as starting points when building Transact-SQL statements in batches and scripts. For more information, see Insert Transact-SQL Snippets.
Transact-SQL Surround with snippets are templates you can use as staring points when enclosing sets of Transact-SQL statements in a BEGIN, IF, or WHILE block. For more information, see Insert Surround-with Transact-SQL snippets.
Database Recovery Advisor
To recover a database from a failure, a database administrator has to restore a set of backups in a logically correct and meaningful restore sequence. The Database Recovery Advisor facilitates constructing restore plans that implement optimal correct restore sequences. Many known database restore issues and enhancements requested by customers have been addressed. Major enhancements introduced by the Database Recovery Advisor include the following:
**Restore-plan algorithm: ** The algorithm used to construct restore plans has improved significantly, particularly for complex restore scenarios. Many edge cases, including forking scenarios in point-in-time restores, are handled more efficiently than in previous versions of SQL Server.
**Point-in-time restores: ** The Database Recovery Advisor greatly simplifies restoring a database to a given point in time. A visual backup timeline significantly enhances support for point-in-time restores. This visual timeline allows you to identify a feasible point in time as the target recovery point for restoring a database. The timeline facilitates traversing a forked recovery path (a path that spans recovery forks). A given point-in-time restore plan automatically includes the backups that are relevant to the restoring to your target point in time (date and time). For more information, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).
For more information, see about the Database Recovery Advisor, see the following SQL Server Manageability blogs:
Page Restore dialog
Starting in SQL Server 2012, SQL Server Management Studio supports page restores. A new Page Restore dialog enables you to check database pages for corruption and to restore selected corrupt pages from a database backup and subsequent log backups. For information about page restore, see Restore Pages (SQL Server).
Startup Options
Database Engine startup options are now configured by using a new Startup Parameters tab of SQL Server Configuration Manager. For more information, see Configure Server Startup Options (SQL Server Configuration Manager).
Contained Databases
Moving databases from one instance of the SQL Server Database Engine to another instance of the Database Engine is simplified by introducing contained databases. Users in a contained database are no longer associated with logins on the instance of SQL Server. Many other dependencies on the instance are also removed. For more information, see Contained Databases.
Data-tier Applications
The following changes have been made for data-tier applications (DACs) in CTP3:
The data-tier application (DAC) upgrade has been changed to an in-place process that alters the existing database to match the schema defined in the new version of the DAC. This replaces the side-by-side upgrade process, which created a new database with the new schema definitions. The Upgrade a Data-Tier Application wizard has been updated to perform an in-place upgrade. The Upgrade method of the DacStore type is now deprecated, and replaced with a new IncrementalUpgrade method. Upgrades are also supported for DACs deployed to SQL Database. For more information, see Upgrade a Data-tier Application.
In addition to just extracting a schema definition as a new DAC package file, you can now export both the schema definition and data from a database as a DAC export file. You can then import the file to create a new database with the same schema and data. For more information, see Export a Data-tier Application and Import a BACPAC File to Create a New User Database.
Data-tier applications now support many more objects than in SQL Server 2008 R2. For more information, see DAC Support For SQL Server Objects and Versions.
Windows PowerShell
Starting with SQL Server 2012, Windows PowerShell is no longer installed by SQL Server Setup. Windows PowerShell 2.0 is a pre-requisite for installing SQL Server 2012. If PowerShell 2.0 is not installed or enabled on your computer, you can enable it by following the instructions on the Windows Management Framework page. For more information about SQL Server PowerShell, see SQL Server PowerShell.
SQL Server 2012 now uses the new Windows PowerShell 2.0 feature called modules for loading the SQL Server components into a PowerShell environment. Users import the sqlps module into PowerShell, and the module then loads the SQL Server snap-ins. For more information, see Run Windows PowerShell from SQL Server Management Studio.
The sqlps Utility is no longer a PowerShell 1.0 mini-shell; it now starts PowerShell 2.0 and imports the sqlps module. This improves SQL Server interoperability by making it easier for PowerShell scripts to also load the snap-ins for other products. The sqlps utility is also added to the list of deprecated features starting in SQL Server 2012.
The SQL Server PowerShell provider includes two new cmdlets: backup-sqldatabase and restore-sqldatabase. For more information, use the get-help cmdlet after loading in the sqlps module.
-K Option Added to bcp.exe and sqlcmd.exe
The bcp Utility and sqlcmd Utility utilities now have -K, which allows you to specify read-only access to a secondary replica in an AlwaysOn availability group. sqlcmd also has –M, to support AlwaysOn Availability Groups.
sqlcmd.exe Behavior Change
If you use sqlcmd.exe with XML mode (:XML ON command) when executing a SELECT * from T FOR XML …, expect behavior changes.
Behavior prior to SQL Server 2012:
Text data containing a single quote was replaced with the ' escape sequence.
Legacy datetime data values with 0 fractional seconds were shown with three decimal digits.
money data values with no decimal value were represented as whole numbers.
Beginning in SQL Server 2012:
Text data containing a single quote is no longer replaced with '. It is still valid XML and an XML parser will give the same result.
Legacy datetime data values with no fractional seconds will not return 3 decimal digits. Other date/time types are not affected.
money data values with no decimal value shows the 4 decimal digits.
Database Engine Tuning Advisor Enhancements
In SQL Server 2012, you can use the query plan cache as a Database Engine Tuning Advisor (DTA) workload. By doing this, you can avoid having to manually create a workload from a script or trace file. When you specify the plan cache as the DTA workload, the Database Engine Tuning Advisor selects the top 1,000 events to use for analysis. The number of events can be changed using the –n option of the DTA utility. For more information, see Start and Use the Database Engine Tuning Advisor.
New and Modified Dynamic Management Views and Functions
The following system views have been added or modified.
System View |
Description |
|---|---|
Added four columns to help troubleshoot long running queries. You can use the total_rows, min_rows, max_rows and last_rows aggregate row count columns to separate queries that are returning a large number of rows from problematic queries that may be missing an index or have a bad query plan. |
|
This dynamic management functions returns information about the operating system volume (directory) on which the specified databases and files are stored. Use this dynamic management function to check the attributes of the physical disk drive or return available free space information about the directory. |
|
This dynamic management view returns one row that displays Windows operating system version information such as the OS version or language ID. |
|
sys.dm_server_memory_dumps (Transact-SQL) |
These dynamic management views return property information associated with the SQL Server, Full-text, and SQL Server Agent services that are installed on the host server. These views also contain configuration, installation, and memory dump file information. |