MS SQL Server 2008 - New Features - SSMS Table Re-creation Prevention

One annoying new feature in SSMS is that when you make a change to a table using the Designer which requires the table to be re-created, such as creating an identity column, by default, you'll get the following message:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To prevent this message from re-appearing, perform the following steps:

1. In SSMS, click on Tools, then Options.
2. Open up the Designers node.
3. Click on "Table and Database Designers".
4. Uncheck the checkbox labeled "Prevent saving changes that require table re-creation".
5. Click on OK.
   

MS SQL Server 2008 - New Features - Auditing

You can now audit specific events in SQL Server 2008

The following steps will walk through how you can setup MS SQL Server 2008 to audit failed logons.

Auditing Failed Logons

1. Login to MS SQL Server Management Studio (SSMS).
2. Expand the Security folder.
3. Right-click on the Audits folder, then click on New Audit....
4. In the Audit name field, enter "Failed Logins" (without quotes).
5. Set the Audit Destination to Application Log.
6. Click on OK.
   
7. Under the Audits folder, right-click on Failed Logins, then click on Enable Audit.
8. Close the success dialog box.
9. Right-click on the Server Audit Specifications, then click on New Server Audit Specifications....
10. In the Name field, enter "Failed Logins" (without quotes).
11. Set the Audit field to Failed Logins.
12. In the Actions grid, set the Audit Action Type to FAILED_LOGIN_GROUP.
13. Click on OK.
    
14. Under the Server Audit Specifications folder, right-click on Failed Logins, then click on Enable Server Audit Specification.
15. Test auditing by attempting to logon to the server with invalid logon credentials.
16. Once that's done, check for an audit event by right-clicking on the Failed Logins Audit, then clicking on View Audit Logs.
17. The failed logon should appear in the log as well as the Windows' Application Log. Take a look at the statement field--it contains an explanation of the event, something like this: Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 10.1.141.146]
    
    You should also see this in the Windows' Application log:
    

MS SQL Server 2008 - New Features - SSMS Easier Navigation

Easier SQL Server Management Studio (SSMS) Navigation

One of my biggest complaints with SSMS v9 (2005) is it's more-difficult-to-get-to menu items. For example, in order to open up an Object Explorer for a Registered server, you would have to 1) right-click on the server, 2) navigate to Connect, then 3) click on Object Explorer.

With SSMS v10 (2008), all you need to do is 1) right-click on the server, then 2) click on Object Explorer, leaving out the extra navigational step. Now, you might think this is minutiae, but in the world of fast-paced, busy development and administration, every little second helps. The easier it is to get to what I need to do, the faster I can do it, and the faster I can do it, the more likely I am to meet deadlines, get more done, etc.

Object Explorer All Registered Servers in a Folder

Typically, when I'm working with a set of servers at the same time, such as copying objects from one server to another, setting up replication or mirroring, etc, I open up all of the affected servers in SSMS v9, but I need to do this once for each server. Now, with SSMS v10, I can open up ALL servers within a folder simply by 1) right-clicking on a registered folder and 2) choosing Object Explorer.

All of the servers will appear in the Object Explorer with all of the servers open and ready for managing, saving me time. Again, time is precious, so this is a cool feature in my book!

Select and Edit Top n Rows

Another time-saver is now being able to select the top 1000 rows of a table or view as well as edit the top 200 rows respectively. With prior versions, I'd have to open up Query Analyzer or a new query in SSMS and write a SELECT TOP n query against a table or view. This saves time, especially when you're retrieving rows from very large tables/views.

MS SQL Server 2008 - New Features - SSMS Activity Monitor

New SQL Server Management Studio (SSMS) Activity Monitor

There are many new, awesome features in SQL Server 2008, but one I've been anticipating is something that should've been developed years ago--a graphical, near-real-time charts of performance indicators, such as CPU usage, wait states, I/O usage, etc. In SQL Server 2000, you had to run several system stored procedures, such as sp_who, sp_lock, sp_spaceused, and sp_monitor, to get an understanding of what was going on, and in SQL Server 2005, you have an activity monitor, but it's a grid consisted of rows and columns of data, not anything graphical. Another cool feature in v2008 is a listing of SQL queries deemed "expensive". With this information, you can better ascertain which queries might be impacting SQL Server performance.

Overview Performance Charts

With SQL Server 2008, you get a chart-based view of some of the more important performance indicators. The new Activity Monitor in SQL Server 2008 provides such as view. To get to it, simply right-click on a server in the SSMS Object Explorer pane, then click on Activity Monitor (contrast this with SQL Server 2005, in which you have to drill down into Management folder to get to AM). See the following image for a look Activity Monitor's Overview real-time charts.

Recent Expensive Queries and Other Features

This feature provides a near-real-time view of expensive queries, something that can help DBAs and developers get a handle on which queries might be impacting server performance, negatively. Other Activity Monitor features include Processes, Resource Waits, and Data File I/O, all in near-real-time views.

To adjust the refresh wait of all of the information in Activity Monitor (you can't set the refresh rate per individual section), simply right-click on on the Overview/charts area, click on Refresh Interval, then choose a rate.

Filters

You can filter the data in the grid sections by clicking on the filter indicator for the appropriate column and selecting the data/value to filter by, a very cool feature, as you might not want to view all of the information on the grid, only a select view/representation.

Backward Compatibility

SQL Server 2005's Activity Monitor will work with SQL Server 2005 but not with v2000 or earlier releases. One thing I noticed with RC0 is that it doesn't show Processor Time statistics for SQL Server 2005 servers.

Comparing SQL Server 2005's Activity Monitor

In SQL Server 2005, Activity Monitor is limited by Processes, Locks by Object, and Locks by Process, and none of them are graphical. You can't view all of the information on one form. To view each search, you right-click on Activity Monitor, typically in Object Explorer in SSMS, then choose a section. With SQL Server 2008's Activity Monitor, you get to view all of the performance-based information on one form, making it easier to view and manage. SQL Server 2008's new Activity Monitor is a much-welcomed feature!

Properly Reinstalling SQL Server 2005 Reporting Services

1. Stop the default IIS web site. DO NOT SHUTDOWN IIS.
2. Delete both SQL Server 2005 Report Server databases.
3. In Control Panel, click on the Change button for the pertinent SQL Server 2005 install instance.
4. Continue to select Report Server and the option to remove it.
5. Reinstall Reporting Services by running the installer on the SQL Server 2005 disc.
6. Once setup is complete, launch the Reporting Services Configuration tool with the Configuration Tools menu in your Start menu.
7. Fix any red-X items in the list.
8. You should be good to go!