SQL Server Management 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.
|