SQL Server 2005 – Management Utilities

In the first
article
of this short series covering new and improved utilities of SQL
Server 2005 Beta 2, we presented the SQL Server Management Studio, which
provides a single, uniform interface for the majority of administration and
programming tasks. We also discussed SQL Profiler and Database Tuning Advisor,
which, while operating independently, can be invoked from its Tools menu. Now
it is time to review the remaining graphical and command line programs, which
should be part of the toolkit of every SQL Server 2005 database administrator
and programmer.

SQL Computer Manager

This utility, accessible from the Microsoft SQL Server 2005 group in the
Programs menu and implemented as Microsoft Management Console snap-in with
functionality based on Windows Management Instrumentation, combines features
present in three distinct SQL Server 2000 utilities (represented by three nodes
in its tree pane) – SQL Server Manager (replaced by the Services node, which
includes such entries as Full Text, Microsoft SQL Server Analysis Services,
MSDTC, MSSEARCH, ReportServer, SQL Agent, and SQL Server), Server Network
Configuration, and Client Network Configuration (both nodes are labeled the
same as their predecessors). SQL Computer Manager is backward compatible with
SQL Server 2000 and 7.0 and supports older versions of Analysis Services and
Reporting Services.

SQLCMD.EXE

This command-line utility serves as a new, improved version of OSQL and ISQL
programs available in earlier versions of SQL Server (they are also available
in SQL Server 2005 Beta 2, but since they are deprecated, their use is
discouraged). It allows you to connect to any instance of SQL Server via OLE DB
(rather than via older technologies such as DB-library and ODBC, used by OSQL
and ISQL, respectively) and run batches of T-SQL statements. Its additional
benefit is the ability to connect to a server via a Dedicated Administrative
Connection (by including -A switch when invoking it from the Command Prompt),
which comes handy in case other means of connectivity fail (e.g. when server is
locked due to its abnormal state). This is possible since a certain amount of
resources is reserved for it every time SQL Server is started.

SQLCMD.EXE has a number of functional enhancements, accommodating the
creation of more elaborate scripts, which can significantly simplify automation
of a majority of administrative tasks. In particular, the new features allow
you to accomplish the following:

  • Prior to connecting to a SQL Server instance using SQL Server
    authentication, it is possible to set the SQLCMDPASSWORD environment variable
    (instead of specifying the password with the -P option), which is applied for
    the duration of the subsequently invoked session (similar to OSQLPASSWORD used
    with the OSQL utility). This eliminates the need for hard-coding the password
    into batch files. As with OSQL and ISQL, by default, in absence of this
    environment variable or -U and -P options, a trusted connection, relying
    Windows Authentication is attempted (or if -E option is used explicitly).

  • You can assign values to custom and predefined scripting
    variables to be used within scripts invoked from the command line. Custom
    variables are set either with -v switch (as part of SQLCMD syntax) or with the setvar
    command (within a script), while predefined variables have their designated
    names and switches. SQLCMD.EXE also has the ability to reference environment
    variables (which you can list by typing SET at the Command Prompt) as long as
    they have not been defined using the previous two methods. Among the more
    useful predefined variables are:

    • SQLCMDSERVER and SQLCMDDBNAME – set with -S and -d switches,
      designate the target SQL server and database (respectively) to connect to.

    • SQLCMDWORKSTATION – set with -H switch, assigns the name that
      will appear in the hostname column of the sys.processes catalog view (default
      is the computer name from which the session is initiated).

    • SQLCMDUSER and SQLCMDPASSWORD – set with -U and -P switches,
      determine the credentials to be applied for connection to a target server
      (assuming that SQL Server authentication is used).

    • SQLLOGINTIMEOUT and SQLCMDSTATTIMEOUT – set with -l and -t
      switches, define the number of seconds that it takes before a login to OLE DB
      provider or a SQL statement execution (respectively) time out.

    Custom scripting variables are designated within a
    script by $ prefix and enclosed in a pair of parenthesis. For example, a simple
    script listed below contains four of them:

    USE $(dbName)
    SELECT * 
    FROM $(TableName)
    WHERE $(ColumnName) = $(ValueName)
    

    In order to assign their values you could invoke the
    SQLCMD utility with the following syntax (for the sake of our example, let’s
    assume that the script is saved as "c:Documents and SettingsdboscriptsRunMe.sql"):


    SQLCMD -E -v dbName=”TVh3ll” ColumnName=”sID” TableName=”dbo.Teletubbies” ValueName=”Twinky-Winky” _
    -i “c:Documents and SettingsdboscriptsRunMe.sql”

    (Note that the entire command needs to be typed in
    on a single line). Entries following the -v switch are used to associate values
    with script variables and the file system path after the -i switch specifies
    the location of the script, (you can provide multiple file names that will be
    processed in sequence). If you want to store the output to a file, use the -o
    switch along with its name. You could also set a value for each variable (or
    selected variables only) using the setvar command (preceded by the single
    ":" character) within the script (or specify a target server by applying
    :connect command, followed by the server name). In such a case, our script
    would change to:


    :setvar dbName “TVh3ll”
    :setvar ColumnName “sID”
    :setvar TableName “dbo.Teletubbies”
    :setvar ValueName “Twinky-Winky”
    USE $(dbName)
    SELECT *
    FROM $(TableName)
    WHERE $(ColumnName) = $(ValueName)

    While our example is rather trivial, it illustrates
    the main advantage of using parameters – they make code easily reusable. You
    can rerun the same script with a different set of values by either changing the
    command line used to invoke it or by altering its header. On the other hand, if
    you have to run a simple query, you can specify its content, enclosed in double
    quotes following the -Q or -q switch (depending on whether you want to exit
    SQLCMD or not after the query completes).

Note that, as we mentioned before, it is possible to execute SQLCMD scripts
within Query Editor by switching it to SQLCMD mode (which, behind the scenes,
relies on .NET SqlClient, rather than OLE DB, like its command-line
counterpart).

Other Command Line Utilities

Among other command line utilities introduced or improved in SQL Server 2005
are the following:

  • SQLWB.EXE – launches SQL Server Management Studio from the
    Command Prompt or Start -> Run text box. Through its switches, you can
    specify which type of server (-t S, -t A, or -t C for SQL Server, Analysis
    Server, or SQL Server Mobile Edition, respectively), server name (-S), and
    database (-d) you want to connect to, provide authentication information, or
    designate which queries, projects, or solutions to open (-i filename). The
    defaults (in absence of switches) are defined in the Tools -> Options menu
    of the SQL Server Management Studio.

  • Profiler90.EXE – launches SQL Profiler from the Command
    Prompt or Start -> Run text box. It has the ability to connect to a specific
    server (/S and /A followed by a server name will allow you target SQL or
    Analysis Services Server) and database (/D), to load a desired trace table
    (/B), template (/T), or file (/I), and write results to a file (/f).

  • DTA.EXE – operates as a command line version of Database
    Tuning Advisor, analyzing performance and providing recommendations regarding
    its improvements (such as the addition or removal of indexes, indexed views, or
    partitions). You have an option of running it on the same server you analyze or
    a separate one (-R switch) in order to offload the processing burden.
    Connection settings are specified using the same switches as the ones present
    in the previously described utilities. Others include information specific to
    the DTA functionality, such as tables to be tuned (-T switch), workload in the
    form of SQL Profiler trace, SQL file, or SQL Server trace file (-i switch),
    workload trace table (-t switch), or maximum time spent on tuning (-A switch).

We will cover other tools introduced in SQL Server 2005 Beta 2, such as lrtest
(Language Resource Test) or DTEXEC and DTUTIL when covering Full-Text Search
and Data Transformation Services.

Business Intelligence Development Studio

Primarily a development utility, this program is intended for creating
Business Intelligence solutions, integrating projects involving Analysis
Services, Reporting Services, and Data Transformation Services. Similar to SQL
Server Management Studio, its interface is solution oriented, with each
solution grouping individual projects, such as reports (utilizing Reporting
Services) or DTS packages. The tool also provides integration with Visual
SourceSafe. We will be presenting this tool, as well as others related to
Analysis, Reporting, and Notification Services in more details in future
articles of this series, when dealing with each of these areas of SQL Server
2005 functionality.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles