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