Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 28, 2005

SQL Server 2005 Part 1 - Tools

By Marcin Policht

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM