Manageability Enhancements (Database Engine)

SQL Server Audit is a new feature of SQL Server 2008 that lets you create customized audits of Database Engine events. SQL Server Audit uses extended events to record the information for the audit and provides the tools and processes you must have to enable, store, and view audits on various server and database objects. For more information, see Auditing (Database Engine).

SQL Server 2008 Enterprise and later supports compressing backups, and every edition of SQL Server 2008 or later can restore a compressed backup. You can change the backup compression behavior for an individual backup, backup job, or log shipping configuration. For more information, see Backup Compression (SQL Server).

By default, backup compression significantly increases CPU usage, which can adversely impact concurrent operations. You can create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

Change data capture offers an effective solution to the challenge of efficiently performing incremental loads from source tables to data marts and data warehouses. Change data captures insert, update, and delete activity applied to SQL Server tables, and makes the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred. For more information, see Basics of Change Data Capture.

SQL Server change tracking allows applications to obtain incremental changes to user tables. Where two-way synchronization is required, change tracking also allows applications to check for data conflicts. With change tracking integrated into SQL Server 2008, developers no longer have to create complicated custom change-tracking solutions.

Prior to the integration of change tracking capabilities into SQL Server, developers often created custom change tracking solutions that used a combination of triggers, timestamp columns, other additional columns, and additional tables. Now, developing synchronization applications is easier and faster.

For more information, see Change Tracking.

SQL Server 2008 introduces a data collector that you can use to obtain and save data that is gathered from several sources. The data collector provides data collection containers that you can use to determine the scope and frequency of data collection on a SQL Server server system. For more information, see Introducing the Data Collector.

Use the sys.dm_db_persisted_sku_features (Transact-SQL) view to identify features that would prevent a database from moving to a different edition of SQL Server 2008.

Deprecation Counters

Performance counters and trace events are available to track the usage of deprecated features. For more information, see SQL Server, Deprecated Features Object and Deprecated Database Engine Features in SQL Server 2008.

DDL Triggers and Event Notifications

The class of events on which you can create DDL triggers and event notifications is expanded to include numerous stored procedures that perform DDL-like operations. For a list of events, see DDL Events and DDL Event Groups. Additionally, the XML schema for events is installed with the Database Engine and is also available on the Internet. For more information, see EVENTDATA (Transact-SQL).

The access check cache quota and access check cache bucket count options control the number of entries and number of hash buckets used for access check result cache. For more information, see access check cache Options.

SQL Server 2008 introduces a new method of administering multiple servers by enabling you to designate Central Management Servers. An instance of SQL Server that is designated as a Central Management Server maintains a list registered servers. For more information, see Administering Multiple Servers Using Central Management Servers.

There are five new dynamic management views to present memory information:

• sys.dm_os_memory_brokers (Transact-SQL)

• sys.dm_os_memory_nodes (Transact-SQL)

• sys.dm_os_nodes (Transact-SQL)

• sys.dm_os_process_memory (Transact-SQL)

• sys.dm_os_sys_memory (Transact-SQL)

The sys.dm_os_sys_info dynamic management view has discontinued the cpu_ticks_in_ms column, and has added two new columns, sqlserver_start_time_ms_ticks and sqlserver_start_time.

SQL Server 2008 supports dynamically adding CPUs to a running system. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. For more information, see Hot Add CPU.

The optimize for ad hoc workloads option is a new server configuration option used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. For more information, see Setting Server Configuration Options.

Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to limit the amount of CPU and memory that incoming application requests can use. For more information, see Managing SQL Server Workloads with Resource Governor.

SQL Server 2008 introduces SQL Server Extended Events, an event infrastructure for server systems. This release of Extended Events enables you to open windows into the run time of the host process by using events as trace points. Those events can then be aggregated in memory, sent to a file, or output to Event Tracing for Windows (ETW). For more information, see Introducing SQL Server Extended Events.

Transact-SQL Debugger

The Database Engine Query Editor now includes a Transact-SQL debugger similar to the Visual Studio debuggers. The Transact-SQL debugger helps you find problems in your Transact-SQL code by pausing execution on specific statements and then displaying data values and system information, such as the Transact-SQL call stack and the values stored in variables and parameters. For more information, see Using the Transact-SQL Debugger.

IntelliSense

The Database Engine Editor now provides IntelliSense functionality such as word completion, error underlining, parameter help, colorizing, Quick Info, outlining, and syntax pair matching. IntelliSense is provided for frequently used Transact-SQL elements. It will be extended to other Transact-SQL elements in future releases. For more information, see Using IntelliSense.

Database Engine Error List Window

SQL Server Management Studio includes an Error List window that displays the syntax and semantic errors generated from the IntelliSense code in the Database Engine Query Editor. For more information, see Error List Window (Management Studio).

The Object Explorer Details window of SQL Server Management Studio has been enhanced in the following ways:

• You can customize the columns that are displayed by the Object Explorer Details window. To see a list of possible columns for the selected object type, right-click the column headings. To reorder the display, you can drag the columns.

• The properties of a selected item appear at the bottom of the Object Explorer Details window. To see additional properties, make the status bar larger by dragging the topic border of the status bar upward.

The code editor for Transact-SQL and MDX has a color-coded status bar at the bottom. The status bar provides information about the editor connection, and changes color when a code editor has more than one connection.

You can add or remove the names of items from the title bar of the code editor windows. To customize the tab name, on the Tools menu, click Options, and then select the Text Editor and Editor Tab and Status Bar Page.

Management Studio includes an enhanced details display for items that are selected in the Object Explorer. Columns can be resized, sorted and reorganized; and new navigation buttons enable you to move to a parent object or the last object visited. You can select multiple objects in the details area to perform actions on them individually or as a group.

Synchronization from an object in Object Explorer Details now selects the individual object in Object Explorer instead of its parent object. In addition, you can select columns to display from a list of properties, and the sorting and selections stay constant as you navigate. Also, for a selected object, a list of properties is shown in a sizeable area at the bottom of the details area. For more information, see Using Object Explorer Details and SQL Server Object Search.

Service Broker Nodes in Object Explorer

Object Explorer has a new node for Service Broker conversation priorities. In addition, the other Service Broker nodes now display additional menu options, including Properties, when you right-click the nodes. For more information, see Service Broker Object Properties F1 Help.

SQL Server Management Studio has the following customer-requested improvements:

• In the Query Editor window, you can query multiple servers at the same time by opening query windows from registered server groups. The query results can be combined into a single results pane, or can be returned in separate results panes. For more information, see Administering Multiple Servers Using Central Management Servers.

• You can access SQL Server Profiler from the Query Editor window, from the Query menu, the Query Editor toolbar, or by pressing CTRL+ALT+P.

• You can now open tables by using a Return Top n rows option.

• You can configure the number of rows that are returned when you are opening tables.

• On the Tools/Option menu, you can specify the action that results by double-clicking tables in Object Explorer.

• You can block the table designer from re-creating tables when you are implementing design changes.

• The Object Details page can be customized with additional columns and now supports search. For more information, see Using Object Explorer Details and SQL Server Object Search.

• Additional connection parameters can be added to the connection string when you are connecting by using SQL Server Management Studio. For more information, see Connect to Server (Additional Connection Parameters Page).