SSMS Features in SQL Server 2005

As more companies try to embrace the next version of SQL Server they will surely have their hands on SQL Server 2000 servers too. When I first saw this new tool I was amazed to see how this new tool tried to give the best of both the worlds as Query Analyzer and Enterprise manager. With due respect there is lots to learn from using this tool and I would recommend you using this for managing SQL Server 2000 machines too. So lets get started with some of the feature description.

Grouping of Logical Objects

So as in Query Analyzer we can now see a much more granular grouping of objects. Interesting part is the system objects (database, tables, views) are kept in a separate node allowing users to have ease of administration and viewing their objects only.

Filtering Option

This is an interesting bit of addition when compared to Query Analyzer. Let us take the tables node for example, with applications that might have 1000's of objects if you were to expand the same it would keep on scrolling. Now to facilitate easy of access to the required objects, we can now filter on the tables node.

In the above example we have filtered on tables with "customer" in their name. So on AdventureWorks database we would get Customer and CustomerAddress tables.

One tool for all

This surely is a USP for this tool when compared to SQL Server 2000 version where we had a separate tool and we had a MMC based application in the form of Enterprise manager. Even though these tools have worked wonders for us in the past it is time for us to look forward.

It is amazing to see that we can now use the Mangement Studio to connect to SQL Server Database Engine, Analysis Services, Integration Services, Reporting Services and SQL Server Mobile databases. You can connect to any of these by registering the servers as we did in Enterprise manager or adding them to the current Object Explorer as in Query Analyzer.

Backward Compatible

I did mention this before that SQL Server Management Studio can also be used to connect to SQL Server 2000 machine. Doing so enjoy the benefits of features introduced in SSMS. Continuing on the same discussion I have seen many times people complaining that the feature in Query Analyzer to customize the keyboard shot cut was removed. Nope !!! It is part of the Tools -> Options. Select the "SQL Server 2000" Option and you are on.

Reports Integration

This is a fabulous addition to the tool. Now you have about 20+ report integrated with the Management Studio that you can capitalize on. These reports are at multiple levels: a. Database Level and b. Server Level.

The Database level reports give an indication of the health of the database and the settings that are affecting the database activities. These are a hand full of reports and give amazing information that are gathered from the Dynamic Management System Views. To get a glimpse of the query that gets fired, just feel free to run profiler in the background and collect the queries :).

A completely different set of reports can be got using the server level node that monitors the instance level details. Some of the reports that I recommend would include the Memory and Performance related reports that give us Top queries based on IO, CPU, Memory etc.

Remember, to get the CPU cycles, Memory using the PerfMon on a server one would need to be administrator for the machine. But now with management studio you can get a snapshot these parameters on the fly without being an administrator of the machine.

Scripting Extension

Now this is a great addition to all the tools users. Using tools like Enterprise Manager over a period of time, DBA's and developers really forget how T-SQL statements for the tasks are actually written. This many a times comes under the microscope only when people prepare for interviews :). So here is a highly recommended feature that I consider every needs to use it out-of-the-box. Now all the pop-up windows in SQL Server Management Studio comes with a "Script" button on the top. This allows you to script the tasks with the options selected into a scriptable query window on the screen or takes the commands to the clipboard or script to a job scheduler or can be used to save into a file. In the diagram below we can see the script option with the Database wizard.

I would recommend this feature strongly to all the developers using a simple example. Consider for some application you write you need to create a low privilege user in the system. Invariably on the developer machine or development environment the developer would use the enterprise manager and create the user and would have coded all his application requirements. Now at the point of moving into the functional or the QA environment is when there would be a showstopper which comes because the developer misses on giving the scripts. More often than not this can be avoided if the developer created a script file for his development environment too.

VSS Integration

This is a superb addition. Thanks to the VS IDE that we get these options with the tool out of the box. Now all the script files can be added to the Visual SourceSafe and projects can be tracked with source control. Create a SourceControl node and add your files / solution to the node or feel free to use the controls available with Management studio for the same. The tools contain:

Scripting Server Registrations

This is yet another addition to the wish list that Enterprise manager had for a long time. Now we can also script out the servers we register with the Management studio outside to be deployed on different machines. Most often than not DBA's move between servers and would like a standard set of connections made available when they work. And this feature facilitates the same. Right Click on the Registered Server node and select Export.

Just repeat the process now with import to setup on a different machine.

Template Explorer

This is yet another addition towards standardizing the formats used within organizations. I love this features and can be of great value to organizations that plan to use some sort of standards on their projects. Incidentally these have been there on the Query Analyzer tool too under the name of "Templates". But I must confess that the "Template Explorer" is far more better when compared to the Query Analyzer one.

These are some standard set of templates that are available and the files can be found at: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql\" directory. Interestingly, now you can extend them too by adding templates to the template explorer.

Dynamic Help

You can use a Dynamic help that changes contents of the help based on the command we write on the fly. Another VS IDE enhancement that we capitalize inside Management studio. Select Help -> "Dynamic Help" menu item. The Dynamic help side bar is introduced. And as you type content on the query window the context changes on the Dynamic help pane. For example, type CREATE and the Dynamic Help list all the DDL commands available with CREATE but the context switches once you type "CREATE PARTITION" and all the entries are related to partitions.

Open a SQLCMD Script

Another nifty addition to the Management Studio is the ability to open SQLCMD script files. This allows DBA's to not only get some sort of color coding but also an environment to test the scripts that they write. To enable this select Tools -> Options and enable the SQLCMD command prompt option.

Conclusion

Even though I have taken a small tour to what is available as options around with SQL Server Management Studio, these are not the only features that are around that you can use. The tool is far more interesting and as you keep playing with the tool more we learn using them efficiently. Now we have more variety and more options available in hand to play with. Hope you enjoyed the article. Feel free to pass your comments.