I wanted to show you some of the things the group has been working on. You might not see all these features in your latest CTP, but they are scheduled for the Final Release. I hope you like the list!
What’s the big deal?
Connection String Additions
Users can enter additional connection strings, which enables full use of Analysis Services. Previously, there was no way to connect with special behavior to AS.
Increased management information
Allows automatic, declarative collection of trace data, performance counters or transact-sql queries into a single database collection system.
Increased speed in install
Re-worked UI to enable faster installations. Also, SSMS Express is now a combined codeset with SSMS (although with less features), allowing a consistent management experience. SSMS can now also be shipped “out of band”, meaning that releases of CE can be combined with SSMS so that you don’t have to wait on a service pack to connect to CE.
Policy Based Management (formerly DMF)
Policy-based framework for managing, controlling and monitoring a SQL Server instance.
Greater control of system resources
Enables you to specify limits on the amount of CPU and memory that incoming application requests can use.
PowerShell integrated to work with SQL Server
Total and complete scripting control
PowerShell, which will ship with SQL Server, is now aware of SQL Server 2008 objects. You have the full scripting constructs of PowerShell against SQL Server Objects. Navigate the SQL Server instance using “CD” commands.
Create a Data Collector Set (for Data Collector) from a Profiler Trace
Use the same tool to monitor as to control
You can use a Profiler trace definition to create a data collector set, which allows you to find expensive queries and control them.
Profiler launchable from SSMS.
Profiler can be launched directly from a Query Editor Results window to the SPID of the query.
Run a query against multiple servers
Run a single query across multiple server. Results have the server name pre-pended to them. Results show in a color-customizable result set.
Auto-fill for keywords and objects, prevents mistakes, increases speed
Shows proper syntax and object names automatically.
Customizable tabs in Query Editor
You can set the tab text at the top of the results pane.
Launch Profiler from Query Editor
Quick access to activity information
You can launch Profiler directly from a Query Editor Results window to the SPID of the query.
Error List in Query Editor
Recall last errors in T-SQL
Shows a list of the last errors generated by Query Editor
New Right-Click options on Results Grid
Query Results Window
Easier Export of data
Users can now select a range of rows in the Results Grid and copy the headings as well. Also, you can select individual cells and copy them with headers if you wish.
Framework Improvements for SSMS
SQL Server Management Studio
Multiple internal code cleanups and fixes to improve the load time of the Object Explorer tree to over 20 times it’s current performance. Also takes much less memory per object to load.
SQL Server Management Studio
Quickly locate and work on SQL Server objects
Using PBM (DMF) under the covers, the user enters a full or partial string to match, which is searched within the current database to bring objects into Object Explorer Details.
Multiple fixes and framework changes to enable faster loading, and faster population. Connection consolidations where possible to take a lighter load on the server.
Activity Monitor built for the DBA
Quickly diagnose performance data with little overhead
The new Activity Monitor was written from the ground up with the perspective of the DBA needing to chase down a performance problem in real time. Modeled after the new Windows Resource Monitor, DBAs can quickly see the active sessions, wait states, file I/O, and long running queries in a command console like UI.
UI Framework Enhancements
Better feedback and navigation
A new “info bar” appears in many screens that guide the user with pre-validation (saving time and frustration) and visual navigation cues.
New Object Explorer Details Pane
Quick access to customizable information
The header columns for each object can be selected. Selections are saved. Single-click on object instantly returns property information in a sizable panel at the bottom of SSMS like Vista.
Security – Admin can reset password
Easier to manage security
The Security Admin can now reset a password for a user from SSMS.
Security – More information on effective permissions
Easier to determine permissions
Enhanced the “effective permissions” display to show exactly what a principal has access to.
Security – reduced clicks for permissions
Faster implementation of security
Reworked the screens to make it easier and faster to set permissions
“Open Table” enhancements
Takes a lighter load on the server
A limited amount of rows is returned to the “Open Table” dialog to avoid taking locks and load on server.
Database Diagram/Table Designer safety additions
Prevents accidental operations
The Table Designer and Database Designer tools have been reworked so that safety checks are taken for operations that would drop an object or cause data loss.
Activity Monitor can launch Profiler
Instant access to activity information
Activity Monitor can launch SQL Server Profiler for the SPID it is centered on.
Showplan – Extract Query from graphical plan
Quickly show a query from a graphical display
You can right-click a graphical showplan and extract the SQL query that generated it.
Showplan – Extract XML from graphic
Quickly access query breakdown
You can right-click a showplan graphic and see the XML version of the plan.
Showplan – XML is now formatted
The XML created by the showplan is now formatted instead of showing as a large string.
The sys.dm_exec_query_plan XML output can generate graphic
You can go from the xml to the graphical plan in a single click
The output from the sys.dm_exec_query_plan stored procedure includes an XML column of the query plan. Clicking it now generates a graphical display of the query.
Complete performance tuning information with navigable reports
Tracks historical performance metrics from SQL Server and the operating system, stores them in a repository, using drill-through reports.
Register servers at a central location
Enables teams to register servers in a single location, once.
Add GUI for Table Partitioning Setup
Easier to set up table partitioning
You can now set up Table Partitioning (which previously required a lot of typing) with SSMS. Includes a Manage Partition wizard to implement the “sliding window” scenario for managing partitioned tables, where for every unit of time (could be month/week/quarter), you bring in a new partition for the latest data and slide out the oldest partition for archival purposes.
Service Broker UI enhancements
Easier setup of Service Broker
Hooked in the creation/alter templates with the context menu in the object-explorer. We have also added read-only property pages for each of these object types.
Transact SQL Debugger integrated in SSMS
It’s about time!