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).
-
Permission to run the utility is no longer limited to members of SysAdmin
-
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.