SQL Server 2005 Part 1 – Tools

In our series dedicated to new features implemented in SQL Server 2005 Beta
2, we have focused so far mostly on areas of design and functionality
improvements, covering availability, scalability, and, more recently, security (including
authentication, authorization, encryption, and digital signatures). In this article,
we will shift our attention to interface related modifications, starting by
discussing a set of tools providing SQL server management capabilities.

The most prominent and immediately noticeable change is the lack of SQL
Enterprise Manager, which used to serve as the primary utility for SQL Server
administration. Its replacement, SQL Server Management Studio offers
significant functionality enhancements and sports a new look, implemented using
Winforms and .NET Framework, drastically different from its Microsoft
Management Console-based predecessor. Among the benefits resulting from this
change are resizable and non-modal dialog boxes, which allow comparing their content
and simplify switching between them when performing multiple actions in
parallel. The name of the new tool as well as its appearance, which closely
resembles the Visual Studio Interactive Development Environment (IDE), reflects
an increasing significance of programming skills in SQL Server management. They
result from a new design philosophy, which provides access to administration
and programming features through the same interface, ensuring consistent
experience and accommodating streamlining of both types of activities.

While the new tool is backward compatible and can be used to manage
instances of SQL Server 2000, in order to appreciate the full range of its
capabilities, you need to operate it against a SQL Server 2005 installation.
You can also use it to control SQL Server Mobile Edition (providing that you
have ActiveSync installed on the local system and the mobile device connected
to it). This range encompasses features available previously in SQL Server
Enterprise Manager, Query Analyzer (implemented as Query Editor), Analysis
Manager, Reporting Services, Data Transformation Services, and Notification
Services. From its Tools menu, you can also launch a number of other SQL
Server-based utilities, such as SQL Profiler or Database Tuning Advisor.

The interface of SQL Server Management Studio is modular and consists of the
following main windows (which can be, depending on personal preference,
assigned floating, dockable, or tabbed characteristics, as well as be hidden or
displayed using options in the View menu):

  • Object Explorer – provides the ability to register,
    browse, and manage servers, as well as create, browse, and manage their
    components, including databases, their objects, and features such as security,
    Notification Services, replication, SQL Server Agent (such as jobs, alerts,
    operators, and proxies), support services (Distributed Transaction Coordinator
    and Full-Text Search), backups and maintenance plans, or SQL Server logs.
    Objects are arranged in a hierarchical structure and can be ordered according
    to their type and schema or filtered (using the Filter setting in the Object
    Explorer toolbar) based on their name, schema, or date. Their comprehensive
    information is available from the Summary View window, generated using
    Reporting Services (and available from the View menu). There is also a new,
    asynchronous way of refreshing the main window, allowing you to access and
    manipulate already loaded objects without waiting for the process to be
    completed for others. The majority of administrative tasks can be easily
    scripted with the Script option available from the menu of the Properties
    window of an active node. With older versions of SQL Server, accomplishing the
    same goal required more creativity and time (you could, for example, run the
    SQL Profiler trace to find out what T-SQL commands needed to be executed in
    order to perform a specific activity). Once a task is scripted, it is also
    straightforward to schedule it for later execution (using the Schedule option
    present in the same Properties window), which automatically creates an SQL
    Server Agent job (note that schedules are identifiable by a name and might be
    assigned to multiple jobs). Creating new objects, such as functions or stored
    procedures is made easier with assisted editors, which help you with
    formulating headers of T-SQL statements via a graphical interface, containing
    such settings as target object and database name, schema, Execute As context,
    parameters, or encryption settings.

    The Maintenance Plans node serves as a replacement
    for the Database Maintenance Wizard from the previous versions of SQL Server.
    Unlike its predecessors though, it integrates with Data Transformation
    Services. This enhances its functionality, giving you the ability to design a
    workflow of a variety of maintenance tasks (including standard T-SQL scripts
    and SQL Agent jobs), with flexible logic controlling transition between them.

  • Registered Servers – allows you to register new servers
    and record connection information for each, with all associated settings such
    as authentication type (and credentials, in case of SQL Server authentication),
    default database, network protocol characteristics, encryption, and time-out
    parameters. Server registration can be exported from one instance of the SQL
    Server Management Studio and imported to another, simplifying management from
    multiple locations.

  • Solution Explorer – implements the programming paradigm
    introduced in Visual Studio, which groups pieces of related code (and
    associated information, such as connection settings) into projects, which,
    in-turn, are grouped together to form a solution. In addition, Solution
    Explorer has the ability to integrate with Visual SourceSafe for supporting
    version control (tracking changes to the code and providing rollback
    capability). Note, however, that unlike its Visual Studio equivalent, Solution
    Explorer does not offer the ability to compile programs written in .NET
    languages, but supports instead T-SQL and XML Query Language (Query) scripts as
    well as XML for Analysis Services (XMLA) related code, such as XMLA methods,
    MDX (Multidimensional Expressions) cube calculations, or DMX (Data Mining Expressions)
    operations.

  • Query Editor – combines the features of Query Analyzer and
    Analysis Manager from previous releases, providing the ability to execute
    queries written in T-SQL, XMLA, MDX, and DMX against SQL Server, SQL Server
    2005 Mobile Edition, and Analysis Services in both connected and disconnected
    modes (this constitutes an improvement over Query Analyzer, which required an
    active connection to a target server). Query Editor integrates with SourceSafe
    version control. As with Query Analyzer, you can display a query execution
    plan, although this feature is further enhanced by including both estimated and
    actual plans. It is possible to save the showplan outcome as an XML document,
    which makes it portable, simplifying the logistics of troubleshooting and
    monitoring processes. Another improvement is the ability to run Query Editor in
    the SQLCMD mode, simply by selecting the appropriate entry from the Options
    menu (or the corresponding button on the SQL Editor Toolbar), which permits the
    execution of scripts written for the SQLCMD Command Prompt utility. Query
    Editor can be invoked by selecting the New Query option from the File menu or
    New Query button from the Standard toolbar.

  • Template Explorer – is intended for creating queries based
    on existing templates. While this functionality was included in the SQL Server
    2000 version of Query Analyzer, the new process of creating new templates and
    grouping them into categories is simplified (this option is accessible directly
    from the context sensitive menu).

  • Dynamic Help – available from the Help menu of SQL Server
    Management Studio, constitutes a single point of access to a number of
    locations providing SQL Server 2005 related information, including the
    installed locally Books Online as well as accessible via Internet MSDN Online
    and SQL Server Community web sites. From the same interface, you can also post
    questions to SQL Server newsgroups. Its content is automatically updated to
    reflect your current activity in SQL Server Management Studio.

From the SQL Server Management Studio interface (Tools menu), you also have
an option to invoke the following SQL Server management utilities:

  • SQL Profiler – while the naming and basic functionality of
    this utility remain the same as its SQL Server 2000 Server version, there are
    several important enhancements introduced in the new edition. One of them is the
    ability to analyze execution of MDX (Multidimensional Expressions) Analysis
    Services statements, extending the scope of monitoring beyond SQL Server
    activities only. Similarly, you can monitor the operation of Data
    Transformation Services. Among others are:

    • Permission to run the utility is no longer limited to members of SysAdmin
      fixed server role – instead they can be granted by including designated logins
      in the Analysis Services server role.

    • Aggregated views allow grouping recorded events based on
      arbitrarily selected criteria.

    • Events of a specific type can be extracted and stored as
      corresponding file types (e.g. T-SQL statements as SQL files, or MDX statements
      as XML files). Similarly, Showplan and Deadlock trace files can be saved in XML
      format, and subsequently loaded for analysis in SQL Server Management Studio,
      which significantly simplifies troubleshooting.

    • Create Trace Wizard guides you through the creation of traces.

    • Deadlocks can be displayed in a graphical view, simplifying
      analyzing the problem, by presenting more clearly the processes, types of
      access requests, and requested objects.

    • Integration with Windows System Monitor helps troubleshoot
      problems which might be somehow related to performance issues (with older
      versions of SQL Server, this used to require separate analysis of SQL Profiler
      traces and System Monitor logs). In SQL Server 2005, after you capture SQL
      Server specific events with SQL Profiler and Windows performance
      characteristics with System Monitor, you can easily find the correlation
      between them by displaying both in the SQL Profiler interface. (You can load
      System Monitor performance logs using Import Performance Data from the File
      menu and SQL Profiler trace using Open Trace option from the File menu).
  • Database Tuning Advisor – an enhanced version of Index Tuning
    Wizard available in earlier versions of SQL Server, goes beyond the capabilities
    of its predecessor through integration with SQL Profiler, offering such
    features as restricting the amount of time spent on tuning (as well as
    scheduling it), tuning user defined functions and triggers, or evaluating the impact
    of changes to existing indexes. The Advisor can also help you determine whether
    aligned or nonaligned layout should be used to benefit performance when dealing
    with partitioning related tables.

In our next article, we will continue the overview of SQL Server 2005 Beta 2
management utilities, which operate independently of SQL Server Management
Studio.

»


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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles