Chapter 5. SQL Server 2005 Tools and Utilities

IN THIS CHAPTER

• Tools and Utilities Overview

• Management and Authoring

• Performance Monitoring, Tuning, and Optimization Tools

• Operations and Configuration

• Management APIs

Microsoft SQL Server was the first RDBMS to provide graphical management and diagnostic tools bundled with the product, in addition to several command-prompt utilities. Since then, the existing tools have evolved, and new tools have been introduced in every SQL Server release. The SQL Server 2005 release is no different. As a matter of fact, because it is the biggest SQL Server release to date, with hundreds of new features, it was very important for Microsoft to deliver a suite of integrated graphical tools, command-prompt utilities, and new management programming APIs that simplify database administration, management, authoring, and operations. This chapter provides an in-depth look at the new tools and utilities introduced in SQL Server 2005.

Tools and Utilities Overview

With SQL Server 2000, tools are kind of disjointed and sometimes have overlapping capabilities. For instance, you have to use Enterprise Manager for common administration and DTS-related tasks; Query Analyzer as a query and authoring tool; separate client network, server network, and service management tools; Analysis Manager to administer and develop Analysis Services objects; and so on. SQL Server 2000 offers minimal tool integration by providing main menu options to launch one tool from another.

When the SQL Server 2005 team started building the new toolset, it devised two primary guiding principles: integration and “no secrets.” Integration refers to tools consolidation so that administrators do not have to deal with multiplicity of tools in order to get things done. The new SQL Server Management Studio, Business Intelligence Development Studio, and SQL Server Configuration Manager are examples of this:

• SQL Server Management Studio—This is an integrated management and authoring graphical tool that combines the functionality of Enterprise Manager, Query Analyzer, and the administration part of Analysis Manager.

• Business Intelligence Development Studio—This is a new integrated graphical tool to design and develop business intelligence applications using SQL Server Integration Services (SSIS; formerly DTS), Analysis Service, and Reporting Services.

• SQL Server Configuration Manager—This is a graphical tool that consolidates Client and Server Network Utilities and Service Manager into a single interface.

With SQL Server 2000, you can find developers and administrators running SQL Profiler to find out about what Enterprise Manager is doing behind the scenes to perform a particular function. The “no secrets” design guideline for SQL Server 2005 tools development was devised to fix this. The scriptable dialogs in Management Studio and the new management objects APIs (SMO, RMO, and AMO) are examples of this. Every management dialog in SQL Server Management Studio provides a Script toolbar button that can be used to generate a script for the task performed by that dialog. Everything that you can do using graphical tools can be automated by using new .NET-based management object libraries such as SQL Management Objects (SMO), Replication Management Objects (RMO), and Analysis Management Objects (AMO).

The SQL Server 2005 tools and utilities can be grouped into four categories:

• Management and authoring—This category includes SQL Server Management Studio, Business Intelligence Development Studio, and Express Manager.

• Performance monitoring, tuning, and optimization—This category includes Profiler, Database Engine Tuning Advisor, and several new performance monitor counters.

• Operations and configuration—This category includes the new SQLCMD command-prompt tool, SQL Server Configuration Manager, Database Mail, SQL Agent and maintenance wizard enhancements, other command-prompt utilities and enhancements (such as XML format file for bcp.exe), and the Microsoft Operations Manager (MOM) management pack for SQL Server 2005.

• Management application development APIs—This category includes the enhanced WMI support and the new managed object libraries, such as SMO, RMO, and AMO.

Table 5.1 maps SQL Server 2000 tools to their equivalents in SQL Server 2005.

Table 5.1. SQL Server 2000 Tools and Their SQL Server 2005 Equivalents

Management and Authoring

The management aspect of SQL Server 2005 administration refers to tasks such as creating new databases and database objects, performing backups and restores; managing logins, users, and other security settings; scheduling jobs and reviewing job history; importing and exporting data; and processing Analysis Services objects such as cubes and dimensions. Authoring, on the other hand, refers to the ability to write and execute queries and scripts, design new SSIS packages, design and create new Analysis Services objects such as cubes and dimensions, and create new reports. SQL Server Management Studio and Business Intelligence Development Studio are the two tools that provide the management and authoring capabilities in SQL Server 2005. SQL Server 2005 Express Edition includes Express Manager, which can be considered a highly trimmed-down version of Management Studio for managing and authoring relational database objects.

SQL Server Management Studio

SQL Server Management Studio can be used to manage SQL Server versions 7, 2000, and 2005, Analysis Services version 2005, Report Servers 2005, SQL Server 2005 Mobile Edition (formerly SQL CE) databases, and SSIS (formerly DTS) servers. Management Studio also extends the management capabilities to support other SQL Server subsystems, such as notification services, Service Broker, and Database Mail.

The following are some of the features of Management Studio:

• You no longer have to use different tools to manage SQL Server and Analysis Services, author and test queries, and manage reports. A single integrated application provides a consistent interface to manage servers and create queries across all SQL Server components.

• All the management dialog boxes in Management Studio are non-modal, resizable, scriptable, and schedulable. Each such dialog includes the link View Connection Properties that can be used to see the server (operating system and hardware), instance (SQL Server version, language, and collation), connection, and authentication details. If you launch a dialog or wizard in Management Studio, because the dialogs are non-modal, you can still go back and do other things in Management Studio, unlike in Enterprise Manager, where dialogs are modal and you have to either close a dialog or launch a new Enterprise Manager instance. Another nice improvement to these dialogs is a progress indicator. If a dialog is performing some lengthy operation, it shows a progress indicator along with the percentage complete.

• Because Management Studio uses SMO, it leverages all the new enhancements made in the API. This includes the cached object model, optimized instantiation, capture mode, and unique resource name (URN) reference. These features are discussed later in this chapter. Because of these SMO enhancements, Management Studio offers better user interface response and performance, facilitates doing multiple, nonblocking actions, and includes advanced scripting support. Management Studio was designed from the ground up to better support a large number of servers, databases, database objects, and records.

• Much like Visual Studio .NET, Management Studio also offers the capability to work with solutions and projects, and it offers complete SourceSafe integration. The first time you launch Management Studio, the interface might look like Visual Studio .NET to you. But note that you do not need to learn, license, or install Visual Studio .NET in order to use Management Studio. A SQL Server Management Studio solution is a collection of one or more SQL Server Management Studio projects. A SQL Server Management Studio project is a set of connection definitions and queries that provide a convenient way to organize related queries and connection information.

• With SQL Server 2000 Query Analyzer, you cannot edit queries unless you connect to a server. The Query Editor does not show up unless a connection is made to the server. The Query Editor inside Management Studio introduces a new feature called disconnected editing that lets you write and edit queries without connecting to any server. If you try to run a query, however, you are prompted to connect to the server.

• With SQL Server 2000, if you needed help on any Transact-SQL (T-SQL) construct, you had to switch back and forth between Query Analyzer and Books Online. The Dynamic Help feature introduced in SQL Server 2005 displays the context-sensitive help topics as you type T-SQL in the Query Editor. Management Studio provides the option to show the help inside it when you select a help topic from the dynamic help window. This way, you don’t have to leave the Query Editor to get help on a topic.

• The new tabbed interface for queries and the ability to split the current query window to see different parts of the same query file improve the usability experience. Management Studio includes several other usability enhancements. For example, a disabled trigger shows up with a different icon than an enabled trigger in Object Explorer; the XML column value shows up as a hyperlink that you can click in order to view formatted XML in a new tab window; and word wrap and line numbering functionality are provided in Query Editor.

• The new Template Explorer window in Management Studio provides hundreds of built-in T-SQL, Multidimensional Expressions (MDX), Data Mining Prediction (DMX), and XML for Analysis (XMLA) templates for SQL Server, Analysis Services, and SQL Mobile. You can also create your own folders and templates to standardize the query authoring throughout your development team or organization. You can press Ctrl+Alt+T or select View | Template Explorer to view the Template Explorer window. As in the Registered Servers window, the toolbar buttons on the top of the Template Explorer window allow switching the context between SQL Server, Analysis Server, and SQL Mobile. The right-click menu in Template Explorer provides options for creating a new folder, creating a template, or searching.

• In case of any error, the error or informational dialog box now displays much more and more detailed information, and it allows you to easily copy the information to the clipboard or email the error details.

• The Profiler integration with Management Studio allows you to capture deadlock graphs and XML showplans in Profiler and then view and analyze them inside Management Studio.

• Activity Monitor and Log File Viewer now support filtering. The Activity Monitor also support automatic refresh.

• Management Studio supports running queries in SQLCMD mode to enable scripting support.

Figure 5.1 shows Management Studio in action. The Registered Servers window shows a SQL Server 2005 instance and a SQL Server 2000 instance; the Object Explorer window shows a connection to an instance of SQL Server 2005, Analysis Services 2005, SQL Mobile Database, SSIS Server, and a SQL Server 2000 instance; the Properties window shows the current query window options; typing the T-SQL word BEGIN in the Query Editor brings up the related help topics in the Dynamic Help Window; Template Explorer shows the SQL Server templates and a list of recently used templates; finally, the Solution Explorer window shows a project open.

Figure 5.1. SQL Server 2005 Management Studio is a new application for managing servers and authoring queries.

The SQL Server Management Studio windows are discussed in detail in the following sections.

Registered Servers

The Registered Servers window allows you to register SQL Server, Analysis Server, Report Server, SQL Mobile Database, and SSIS Server. For each type of server, it lets you create a new server group; add, edit, and remove server registration; and connect Object Explorer or Query Editor to the selected registered server. The icon next to each server indicates whether the server is running. You can export the server registration details into an XML file and import it onto another server by using the Registered Servers window (by right-clicking in the window and selecting either Import or Export). You can double-click a registered server to connect and view the selected server in the Object Explorer window.

Object Explorer

A hierarchical tree view allows you to interact with the objects on the server. For SQL Server, the tree looks similar to the one shown by SQL Server 2000 Enterprise Manager; it contains databases, tables, views, replication, security, and so on. For Analysis Server, the tree looks similar to one shown in Analysis Manager; it contains data sources, cubes, dimensions, and so on. For Report Server, Object Explorer provides options similar to the Report Manager web interface. This includes managing reports and report folders, security, schedules, and jobs. For Mobile Database, Object Explorer allows you to manage tables, views, and Merge Replication subscriptions. For SSIS Server, Object Explorer can be used to view the status of running packages and enables you to work with packages stored in the file system and the msdb system database.

Unlike Enterprise Manager, Object Explorer is designed to handle and work well with a large number of servers, databases, and database objects. When you expand a tree node or folder, Object Explorer asynchronously retrieves only the information required for that node and not for child nodes. Object Explorer allows you to filter the nodes based on various parameters, such as name, creation date, and schema. The Object Explorer tree is limited to 65,536 objects. If you have more than 65,536 objects, Object Explorer does not list them, and the only way to see those objects is to use filtering.

The Connect button on the Object Explorer toolbar allows you to connect to a server that is not registered. After you connect to the server, you can right-click it and select Register to add that server to the Registered Servers list.

For SQL Server, Object Explorer groups system databases in one folder, group database snapshots under another folder, and lists all other user databases in the tree. Unlike Enterprise Manager, which lists the objects on the right side in a grid view, Management Studio shows all the tables, views, and other database objects as tree nodes. If you need to script more than one object, you can right-click a database and select Tasks | Generate Scripts. This brings up the Generate SQL Server Scripts Wizard, in which you can select a database and database objects that you want to script. Pressing the F7 key or by selecting View | Summary brings up a read-only report on the right side for the currently selected item in Object Explorer. Depending on the item selected in the Object Explorer tree, the Summary tab may provide more than one reports. The Report toolbar button on the top of the Summary tab can be used to navigate between multiple reports.

The stored procedures, database-level DDL triggers, functions, .NET assemblies, types, rules, and defaults are grouped under the Programmability node. The full-text catalog and partitioning schemes and functions are shown under the Storage node. The database users, roles, schemas, and symmetric keys are under the Security node.

In addition to databases, these are the other nodes in the Object Explorer tree:

• Security—This node allows you to manage logins, server roles, and credentials.

• Server Objects—This node allows you to manage backup devices, linked servers, and server-level DDL triggers.

• Notification Services—This node allows you to manage notification services applications.

• Replication—This node allows you to configure and manage publications and subscriptions. Unlike Enterprise Manager, which shows the Replication Monitor as part of the tree, Management Studio launches a separate application (sqlmonitor.exe) when you right-click the Replication or Local Publications node and select Launch Replication Monitor.

• Management—This folder allows you to configure and view SQL Server error logs, create and manage maintenance plans, monitor server activity, and configure SMTPbased Database Mail. The Legacy folder under the Management node allows you to configure MAPI-based SQL Mail, open and migrate SQL Server 2000 DTS packages, and view the database maintenance plans history.

• SQL Server Agent—This node allows you to manage Agent jobs, alerts, operators, and proxies, and it lets you view Agent error logs.

The Filter icon on the Object Explorer toolbar is enabled when you select nodes such as Tables, Views, or Stored Procedures. Figure 5.2 show the Object Explorer and Summary windows inside Management Studio. Note that system objects are hidden in Object Explorer, the tables list is filtered, and that you can select multiple objects on the Summary tab.

Figure 5.2. Object Explorer allows you to filer objects, and the Summary tab supports multi-selection.

The Query Editor

SQL Server Management Studio includes a rich Query Editor that can be used to author and edit T-SQL, MDX, DMX, XMLA, and SQL Server Mobile Edition queries. Much like Query Analyzer, the Query Editor window in Management Studio provides syntax coloring; can output results to a grid, text, or file; and can display estimated or actual execution plan and client statistics.

The Options dialog box provides various settings that you can change to tune the Query Editor environment. You can view query, results, messages, and other windows as tabbed or MDI child windows; view line numbers and enable word wrapping; set query execution options such as various SET options; set query result options such as maximum number of characters displayed in a grid cell or a text column; switch between SQL Server 2005 and SQL Server 2000 keyboard schemes; and so on.

You can select the File | New (or press Ctrl+N) to start a new query window, or you can right-click the server in Registered Servers select Connect | New Query to launch a new query window. Selecting File | New | Project (or pressing Ctrl+Shift+N) allows you to create a new project and a new solution or add a new project to an existing solution. The three project types available include SQL Server Scripts, Analysis Server Script, and SQL Mobile Scripts. After you create a new project, you can use the Solution Explorer window to define new connections and create new queries.

You might have noticed that if any Microsoft Office product, such as Word or Excel, shuts down abnormally, the next time you launch the application, you are prompted to recover the document you were working on. The SQL Server Management Studio supports a similar feature. Let’s say you were working on a bunch of script files and did not save all the changes you made. Now, if somehow the Management Studio tool shuts down unexpectedly, the next time you launch Management Studio, it will allow you to recover the script files that you were working on, preserving the changes you made in the last session.

Table 5.2 lists few useful keyboard shortcuts that you can use while working in Query Editor.

Table 5.2. Query Editor Keyboard Shortcuts

Microsoft has removed some of the functionality from Management Studio Query Editor that is available in Query Analyzer. For example, you can play a .wav sound file when the query finishes executing in Query Analyzer. The Results tab of the Options dialog in SQL Server 2000 Query Analyzer provides an option to play a Windows message beep or a .wav file when a query finishes. No such feature is available in Management Studio.

The other windows in Management Studio include Template Explorer, which can be used to access commonly used T-SQL, MDX, DMX, XMLA, and Mobile queries; Solution Explorer, which lets you manage multiple scripts and connections as a SourceSafe integrated project; the Properties window, which shows the context-specific metadata; and Help windows, which include dynamic help, an index, and the contents.

The General tab of the Options dialog in SQL Server 2000 Query Analyzer provided an option to change the template file location used by the Templates window in Query Analyzer. However, to create a new template folders and template files, you had to go to the file explorer and create folders and files there, and then you had to press F5 or right-click and select the Refresh menu item in the Templates window for the new folders/files to appear in the tree. In other words, the Templates window did not provide any option to add or remove template files and folders. With SQL Server 2005, the template folder for the Template Explorer window is fixed to %ProgramFiles%Microsoft SQL Server90ToolsBinnVSShellCommon7IDEsqlworkbenchnewitems, and Template Explorer right-click menu options are provided for creating and removing template folders and files.

Common Administration Tasks

The goal of the following sections is to show you how to perform some common administration tasks by using SQL Server Management Studio. First, you need to start SQL Server Management Studio and try out the following to see how to get things done using Management Studio.

Registering a Server

Following are the steps to register a SQL Server instance:

1. Press Ctrl+Alt+G or select View | Registered Servers to bring up the Registered Servers window, if it is not already visible.
2. Make sure that the Registered Servers window is showing servers of type Database Engine. If it is not, click the first toolbar button on the Registered Servers window or select View | Registered Server Types | Database Engine.
3. Right-click the Microsoft SQL Servers node in the tree. Select New | Server Group if you would like to create a new group; otherwise, select New | Server Registration to register a server.
4. On the New Server Registration dialog box that appears, type the server name, including the instance name, or select a server from the combo box and provide the authentication details. Use the Connection Properties tab to set other connection properties, such as the default database, the client network protocol to use, the network packet size, the connection and execution timeout values (in seconds), and whether the connection should be encrypted. Click the Test button to verify the connection and click Save when you’re done.

Viewing and Changing Server Properties

Following are the steps for viewing or changing SQL Server instance properties:

1. Right-click the SQL Server instance in the Registered Servers window and select Connect | Object Explorer or simply double-click a registered server to connect to an instance in the Object Explorer window.
2. Right-click the server in Object Explorer and select Properties.
3. Note the changes in this dialog compared to the Server Properties dialog in Enterprise Manager. This dialog is non-modal (that is, you can go back to Management Studio without closing the dialog), is resizable, allows you to schedule or script the changes you make instead of applying them immediately, and contains a progress indicator to show status if applying the change takes a long time. Some other changes on this dialog include the new Permissions page, which allows you to manage the new server-level security settings, such as allowing or denying the ability to create/alter endpoints; and the new Advanced page, which can be used to view and set various sp_configure settings.

Similarly, you can right-click the SQL Server Agent folder in Object Explorer and select Properties to view and set agent properties such as job history, log size limit, SQL Server event forwarding, and so on.

Creating a New Database

Following are the steps for creating a new SQL Server database:

1. Connect to a SQL Server instance in Object Explorer mode.
2. Right-click Databases and select the New Database menu item.
3. Use the General tab to specify the database name, owner, collation, and recovery model; add or remove database files; and set file properties such as autogrowth, file size, and filegroup.
4. Use the Filegroups page to create or remove filegroups and to set the default filegroup.
5. Use the Options page to view and set various database options such as Auto Create Statistics and Auto Update Statistics.
6. Click OK to create the database immediately, Schedule to create a job that will create the database later on, at a scheduled time, or Script to generate the script to create the database. If you schedule or script the task, you can click the Cancel button on the New Database dialog to avoid action being performed immediately.

Backing Up or Restoring a Database

Following are the steps for backing up an existing database:

1. Connect to a SQL Server instance in Object Explorer mode.
2. Expand the Databases folder in the Object Explorer tree, right-click the database you would like to back up and select Tasks | Back Up. The Backup Database dialog appears.
3. The Backup Database dialog in Management Studio is very similar to one in the Enterprise Manager, except it now shows the database recovery model and provides option to perform a checksum before writing to the media in order to produce reliable backups. Depending on the recovery model of the database, you can perform full or differential database or backups of files/filegroups or the transaction log. Select the backup type, give a name to the backup set, select the backup destination, and set the various media, reliability, and transaction log settings on the Options page and click OK to immediately perform the backup operation, click Schedule to create a job, or click Script to generate a script to perform the backup.

Similarly, to restore a database or transaction log, you follow these steps:

1. Right-click a particular database and select Tasks | Restore or right-click the Databases node and select Restore Database.
2. In the Restore Database dialog that appears, select to restore replication settings or to restore to a particular point in time.

The right-click Tasks menu on any user database allows other operations as well, including attaching, detaching, copying a database, shrinking, importing/exporting data, and generating scripts.

Managing Indexes, Triggers, and Constraints on a Table

Under each database, Object Explorer shows folders such as Database Diagrams, Tables, Views, Synonyms, Programmability, Service Broker, Storage, and Security. All the user tables are shown under the Tables folder. If you expand a table node, you see folders such as Columns, Keys, Constraints, Triggers, Indexes, and Statistics. You can expand each such folder to see existing objects and right-click a folder to create a new item of the selected type. For example, you can right-click Triggers and select the New Trigger menu item to launch Query Editor window to create a new trigger. Let’s create a table, and then create an index and a constraint on this new table:

1. With the Object Explorer window open and while you are connected to a SQL Server 2005 instance, expand a database folder node, right-click Tables, and select the New Table menu item.
2. The Table Designer appears as a tabbed window inside Management Studio. Specify the first column name as RecordID and the data type as int, uncheck Allow Nulls, and turn on the Identity property for this column, using the Column Properties tab. Right-click this column row and select the Set Primary Key menu item. Add two more columns, named PostType and MemberID, of data type int, and uncheck the Allow Nulls check box. Add two more columns, PostTitle (as nvarchar(100)) and PostText (as ntext).
3. Press Ctrl+S or select File | Save and name the table tblPosts. The Table Designer toolbar (which you open by selecting View | Toolbars | Table Designer) provides various buttons for managing relationships, indexes and keys, check constraints, and full-text indexes. Try them out if you like.
4. The tblPosts table should now appear in Object Explorer under the Tables node. If it does not, refresh the tree.
5. Next, create a nonclustered index and include one non-key column in this index. To do this, expand the tblPosts table, right-click Indexes, and select the New Index menu item. Type the index name as ncIdxMember; leave the type as Nonclustered, click the Add button for the Index Key Columns group, and add the MemberID column. The Options page on the New Index dialog lets you enable or disable the index, set the fill factor, control the locking mechanism, and select an index as online or offline. Click the Include Columns page and add the PostTitle column. The Storage page lets you choose the filegroup on which the index should be created. Click OK to immediately create the index. This new index should appear under the Indexes folder.
6. Double-click the ncIdxMember index or right-click and select Properties and note that the index properties dialog now includes a new page called Fragmentation that shows details similar to the results returned by running DBCC SHOWCONTIG or accessing the sys.dm_db_index_physical_stats dynamic management function. As a matter of fact, when this page is accessed, Management Studio runs a query against the sys.dm_db_index_physical_stats dynamic management function to obtain the index fragmentation details.
7. Next, create a new constraint that PostType must have a value between 1 and 5. Right-click Constraints and select New Constraint. The Table Designer and Check Constraints windows appear. On the Check Constraints dialog, type the constraint name as chkPostType, type the expression as [PostType] > 0 AND [PostType] < 6, and click Close. Press Ctrl+S or select File | Save tblPosts and close the Table Designer.
8. Refresh the Constraints node in Object Explorer, and you should see chkPostType there. If you need to modify or view this constraint, you can right-click it and select the Modify menu item.
9. Start the Query Editor, select the database in which you created the tblPosts table, and run the following queries:
   

   INSERT INTO tblPosts VALUES (1, 1, N'Test 1', N'Test 1'),
   GO
   INSERT INTO tblPosts VALUES (0, 1, N'Test 2', N'Test 2'),
   GO
   INSERT INTO tblPosts VALUES (6, 1, N'Test 3', N'Test 3'),
   GO
   SELECT * FROM tblPosts;
   GO

   The first INSERT should succeed, and the next two INSERT statements should fail because they violate the chkPostType check constraint.

Viewing SQL Server and Agent Error Logs

The Log File Viewer in SQL Server Management Studio has been enhanced to consistently show SQL Server and Agent log files, as well as Windows event logs. A single screen allows you to view zero or more SQL Server log files, zero or more Agent log files, and all the Windows event log sources. It also allows filtering, searching, exporting, and loading of log files. You can rearrange the columns shown, but the first column, the Date column, is fixed, and if you scroll to the right, the Date column does not scroll, so you can continue to see the date while viewing the other columns on the right side.

Figure 5.3 shows the Log File Viewer screen with the current SQL Server log, current Agent log, and System Windows NT event log source selected, and the log rows are filtered for text containing the word SQL. Different icons in front of the Date column value and the Source column indicate the type and source of the log file row.

Figure 5.3. The Log File Viewer in SQL Server Management Studio shows SQL Server and Agent log files and Windows NT event log data, on a single screen.

You can use the SQL Server Logs folder under the Management folder, or the Error Logs folder under the SQL Server Agent node in Object Explorer to launch the Log File Viewer. Once the Log File Viewer window is open, using it to view, filter data, and so on is very intuitive. The blue filter icon on the toolbar means no filter has been applied, and the green filter icon means rows are filtered based on the provided conditions.

By default, SQL Server keeps the last six log files. You can change this number by right-clicking SQL Server Logs and selecting Configure. Similarly, you can right-click Error Logs under SQL Server Agent and select Configure to set Agent log properties such as the error log file location and the kind of information to log.

Some Miscellaneous Administration Tasks

Before concluding this section, let’s look at some other things that you can do by using Object Explorer in Management Studio:

• To view the current server activity that includes processes and locks information, you can expand the Management folder and double-click the Activity Monitor node. The Activity Monitor shows the active processes. You can right-click a process and select Details to view the DBCC INPUTBUFFER value for that process or to kill that process. The two other views shown by the Activity Monitor dialog are Locks by Process and Locks by Object. The top-level combo box allows you to select a process or an object, and the grid shows all the locks acquired by that process or on that object, respectively. By default, Activity Monitor filters system processes. You can click the Filter toolbar button to set Show System Processes to True to view all processes.

• To create a new login account, you can right-click Security and select New | Login or right-click Logins under Security and select New Login. On the new login dialog that appears, you select the authentication type (Windows or SQL Server) and select the default database and language. If it’s a SQL Server login, you need to determine whether password policy and password expiration should be applied to this login. You use the Server Roles page to make this new login part of one or more server roles, such as dbcreator. You use the Database Access page to create a user for this login in one or more databases, assign the default schema in that database, and assign the database roles, such as db_datareader. You use the Permissions page to grant or deny server-level permissions such as Create Endpoint.

• You can right-click any database and select Tasks | Generate Scripts to launch the Generate SQL Server Scripts Wizard, which walks you through scripting one or more objects in the selected database.

• You can right-click any user database, select Properties, and use the Mirroring page to set up database mirroring for that database. Setting up database mirroring is discussed in Chapter 8, “Reliability and High Availability in the Database Engine.”

Business Intelligence Development Studio

As mentioned in Chapter 4, “A Tour of New Features in SQL Server 2005,” Microsoft’s strategy for business intelligence in SQL Server 2005 is to integrate, analyze, and report. The Business Intelligence (BI) Development Studio is a new integrated application that supports this strategy. The BI Development Studio tool can be used to build and deploy end-to-end business intelligence solutions.

Like SQL Server Management Studio, BI Development Studio also borrows the Visual Studio .NET shell and supports working with SourceSafe integrated solutions and projects.

BI Development Studio allows you to create SSIS projects, SQL Server Analysis Services 2005 (SSAS) projects, and SQL Server 2005 Reporting Services (SSRS) projects. You can create a BI Development Studio solution containing a mix of projects of type SSIS, SSAS, and SSRS and then deploy the entire business intelligence solution to a test or a production environment. Figure 5.4 shows the BI Development Studio environment with an open solution containing an SSIS project, an SSAS project, and an SSRS project.

Figure 5.4. BI Development Studio supports designing, developing, and deploying end-to-end business intelligence solutions.

Integration Using SSIS

As mentioned previously, in SQL Server 2005, the DTS platform has been renamed SSIS. It is a new platform that lets you bring data from various sources such as file systems, databases, XML files, and web services; transform and integrate the data; and send the data to various types of destinations, such as file systems, databases, mobile devices, analysis and reporting servers, and so on.

You can author SSIS packages by creating an Integration Services Project in BI Development Studio. The SSIS package designer inside BI Development Studio provides an enhanced interface that simplifies the authoring and debugging of the SSIS packages. The packages created using the SSIS designer are saved as XML files with the .dtsx file extension. You can right-click a package and select View Code to see the XML for an SSIS package definition.

The SSIS designer now separates control flow, which includes process-oriented and discrete tasks, from data flow, which includes data-oriented tasks. Examples of control flow tasks include For and Foreach Loop Container tasks, the File System task, the Send Mail task, the Execute SQL task, and so on. You can either convert your DTS 2000 packages by building new SSIS packages from scratch and making use of the new SSIS features, or you can use the Execute DTS 2000 Package Task control flow task and execute a SQL Server 2000 DTS package from within an SSIS package. The data flow tasks are categorized in three groups: source, transformation, and destination. Examples of data flow tasks include Flat File Source, XML Source, OLE DB Source, Fuzzy Grouping, Lookup Transform, Sort Transform, Flat File Destination, SQL Server Mobile Destination, DataReader Destination, and so on. Figure 5.5 shows the new SSIS designer environment. The SSIS package in this figure contains an OLE DB source, a data conversion transform, and an OLE DB destination. The data conversion error rows are directed to a flat-file destination.

Figure 5.5. The new SSIS package designer inside BI Development Studio separates control flow from data flow, provides several built-in task controls, and allows you to send erroneous rows to a different route.

SSIS provides a very robust debugging environment. You can set breakpoints at various steps in the extraction, transformation, and loading (ETL) process and view data as it is flowing through the steps. SSIS also offers you the flexibility to route erroneous rows to one path and all other rows to another path. The SSIS designer in BI Development Studio includes several built-in control and data flow tasks that you can use in your SSIS package. If you don’t find a task control that suits your need, you can use the SSIS.NET-based extensibility programming model to build custom control and data flow task controls. The enhanced configuration and logging support in SSIS facilitates building and troubleshooting complex SSIS packages. See Chapter 12, “SQL Server Integration Services Overview,” for more details on SSIS.

Analysis Using Analysis Services 2005

The things that business intelligence information workers used to do in Analysis Manager with SQL Server 2000 are now available inside BI Development Studio, with several usability and user experience enhancements. After you create a new Analysis Service project, you can use various wizards and templates provided by BI Development Studio to create the rest of the Analysis Services objects, such as data sources, data source views, cubes, dimensions, mining models, and so on, to be deployed in a single Analysis Services database. You can either create a new solution for the project or add the project to an existing solution. Figure 5.6 shows an Analysis Services project open inside BI Development Studio.

Figure 5.6. The SQL Server 2000 Analysis Manager functionality is now available inside BI Development Studio, with several user interface and usability enhancements, as well as support for new Analysis Services 2005 features.

As shown in Figure 5.6, the Analysis Services project contains items such as data sources, data source views, cubes, dimensions, and so on. You can right-click Data Source and select New Data Source to launch the Data Source Wizard; similarly, you can right-click Cubes and select New Cube to launch the Cube Wizard. Analysis Services is discussed in further detail in Chapter 13, “What’s New in SQL Server Analysis Services 2005.”

Building Reports Using Reporting Services

The final major component of BI Development Studio is the ability to author and edit reports. Prior to this, you had to use Visual Studio .NET to author the Reporting Services reports. Now, because reporting is an integral part of the business intelligence development life cycle, it is integrated into BI Development Studio. You can add reports to your business intelligence solution by creating a new report project, and you can either add it to the current solution or create a new solution for the project.

When you select a project of type Report Project, BI Development Studio presents an empty environment. You start by defining data sources, and then you create new reports, update the project properties to specify the deployment report server, and so on. BI Development Studio also contains another project type, called Report Project Wizard, that walks you through defining a data source, creating a report, and specifying the deployment server information. When you finish that wizard, you have a report ready for preview and deployment.

The report designer in BI Management Studio is very similar to the Visual Studio .NET report designer. It has a Toolbox window from which you can drag and drop items onto the designer, a Data tab that lets you define the source for report data, a Layout tab that is the primary design surface, and the Preview tab that lets you view your report in action. (Refer to Figure 5.4 to see a report in preview mode.) You can right-click the project node in the Solution Explorer tree and select Properties; then, under the General tab you can set the TargetServerURL property where the report should be deployed. More details on Reporting Services can be found in Chapter 13.

Express Manager

As you might know, Microsoft SQL Server 2000 Desktop Engine (MSDE) does not include any graphical tools. The only querying tool that is bundled with MSDE is osql.exe. With SQL Server 2005, Microsoft changed the name from MSDE to SQL Server 2005 Express Edition. This free, redistributable, limited edition of the SQL Server engine includes two graphical tools: SQL Server Configuration Manager and Express Manager. Express Manager is a lightweight database management and query authoring tool that can be used to manage SQL Server 2005 Express Edition, SQL Server 2005 Developer Edition, and SQL Server 2000 MSDE instances. As shown in Figure 5.7, Express Manager shows a Management Studio Object Explorer–like window on the left side and a T-SQL Query Editor window on the right side.

Figure 5.7. SQL Server 2005 Express Edition includes graphical tools for managing SQL Server instance and services and for authoring T-SQL queries.

The Express Manager application limits you to connecting to only one SQL Server instance at a time. However, you can have multiple instances of Express Manager running, and each can connect to a different server.

The Express Manager executable (xm.exe) accepts various command-line parameters that you can use to connect to a particular instance and to open one or more files into the editor. For instance, the following command connects to a local SQL Server 2005 Express Edition named instance (-S parameter) by using a trusted connection (-E), and it opens two script files (-f):

"C:Program FilesMicrosoft SQL Server 2005 Express Managerxm.exe " -S .SQLEXPRESS -E -f c:1.sql, c:2.sql

Performance Monitoring, Tuning, and Optimization Tools

SQL Server 2005 provides a number of tools you can use to proactively monitor SQL Server, perform trend analysis, troubleshoot performance issues, and maximize the SQL Server performance.

The SQL Profiler and PerfMon (also known as System Monitor) are still the primary tools for tracing and monitoring the SQL Server performance and system throughout. These two tools have been upgraded to support new SQL Server 2005 features and to provide more useful information to aid in troubleshooting performance issues.

In addition to using Profiler and PerfMon, other performance monitoring techniques include running system stored procedures such as sp_who2, sp_monitor, and sp_lock; running DBCC commands such as DBCC MEMORYSTATUS; accessing dynamic management views (DMVs) and dynamic management functions (DMFs) to view the current state of the SQL Server 2005 system; generating client statistics and execution plans by using Query Editor in SQL Server Management Studio; using Activity Monitor inside Management Studio; implementing DDL triggers and the event notification–based monitoring system; and using command-line tools such as SQLDiag.exe to obtain detailed system information. These techniques are discussed in detail in Chapter 9, “Performance Analysis and Tuning.”

SQL Server 2005 Profiler

SQL Server 2005 Profiler (Profiler90.exe) is a graphical tool you use to trace T-SQL or MDX queries being submitted to the server. You can use Profiler to find out about slow-performing queries and stored procedures, to trace locking and deadlocking issues, and to audit SQL Server activity.

The following are some of the new features introduced in Profiler:

• Support for SSAS—As mentioned earlier, Profiler now allows you to connect to an Analysis Server instance to trace MDX queries being submitted to the server. When you launch Profiler and start a new trace, you can select Analysis Server from the Server Type combo box, provide the credentials, and click Connect. After you click the Connect button, you see the Trace Properties dialog and Analysis Server–specific trace events in the Events Selection tab. You can select the events you want to monitor and other settings, such as the trace name, the trace template, whether to save to a file or table, and so on and click Run to begin tracing.

• Saving a trace as an XML file—After capturing a trace, you can select File | Save As | Trace XML File to save the captured trace data as an XML file. You can edit this XML file and then use it later for replay. Having trace data available as XML opens up opportunities for third-party application vendors.

• XML-based trace definitions—SQL 2005 Profiler uses XML-based trace definitions to show different events for different types of servers. For instance, if you connect to SQL Server 7, Profiler shows one set of events on the Events Selection tab in the Trace Properties dialog. If you connect to a SQL Server 2000 instance, you see a different set of events that you can capture. Similarly, for SQL Server 2005 and Analysis Services 2005, you see different trace definitions. These trace definition XML files are stored in the Program FilesMicrosoft SQL Server90ToolsProfilerTraceDefinitions folder. If you are not running the Profiler on the server itself, these XML files are downloaded to the client machine from where you are running the Profiler tool. This ensures that you have up-to-date trace definition events data from which you can select the events to monitor.

• Capturing of the showplan for queries executed on the server—The showplan is shown graphically in the Profiler, and you can also save that as XML, which you can later load and analyze in Query Editor in Management Studio to view the showplan graphically without connecting to the server. To use this feature, you select the Showplan XML event under the Performance event class in the Events Selection tab on the Trace Properties dialog. After you select this event, a third tab, named Events Extraction Settings, appears, and in it you can optionally select the file in which the Showplan XML event should be saved. This is discussed in detail in the next section.

• Performance Monitor integration with Profiler—This integration allows you to correlate Performance Monitor counter data with the Profiler trace collected at the same time. You select File | Import Performance Data to use this performance counters correlation feature.

• Enhanced trace replay—If you have multiple rollover files, Profiler can now automatically play them one after the other continuously and in order. Profiler also provides options to control the replay options, such as the number of reply threads. Look for these options under Tools | Options.

• Improved Trace Properties dialog—When you connect to a server instance, Profiler brings up the Trace Properties dialog, which allows you to select events to monitor, columns to show, filters to set, and options for saving a trace to a file or a table. The Trace Properties dialog in SQL Server 2000 Profiler contains four tabs: General, Events, Data Columns, and Filters. It is very cumbersome to move back and forth among the Events, Data, and Columns tabs to select and view the events settings. SQL Server 2005 combines these three tabs under a single screen called Events Selection. This dialog now allows you to use a single screen to select events to monitor and columns to view and to set column filters. In addition, the ability to see only selected events and selected columns is a great usability enhancement. You can click the column header or click the Column Filters button to view or edit the column filter settings. When you click the column header, in addition to the filter settings, you can also specify whether Profiler should exclude rows that do not contain any value for that column. Figure 5.8 shows the new Events Selection tab.

Figure 5.8. The new Events Selection tab is a single easy-to-use screen that allows you to select events to monitor and columns to view and to set column filters.

• New trace events—Profiler contains several new trace events that either support new features or provide better tracing functionality. For instance, you can use the Broker event class to trace Server Broker activity, the Deprecation events to capture features that are deprecated and might not be available at all in future releases, the Progress Report event class to see the status of tasks such as online index building, OLEDB events to capture events raised by any feature (such as linked servers) that makes use of OLEDB, database mirroring events, several new events for full-text searching under the Performance and Full text event classes, new Server trace events such as Mount Tape, new CLR events such as Assembly Load, and so on.

Let’s now look at using some of the new Profiler features. The following section shows three examples: how to detect blocking by using SQL Profiler, how to detect deadlock by using Profiler and viewing the deadlock graph, and how to obtain the query showplan as XML by using Profiler.

The Blocked Process Report Profiler Event

SQL Server 2005 introduces a new feature called blocked process report, which is provided via the new sp_configure advanced setting blocked process threshold. This setting has a default value of 0, which means the feature is turned off. You can enable advanced options and then set this option to a numeric value, say 10, to indicate the number of seconds, and then if there is blocking on the server for 10 seconds, SQL Server 2005 will raise an event at that time and every 10 seconds after that until blocking is not resolved. This event can be captured by using the event notification mechanism to run some T-SQL code and also can be seen in Profiler. Here are the steps for capturing a blocked process report event by using Profiler:

1. Start SQL Server Management Studio and open two query windows. Connect to a SQL Server 2005 instance in each of the query windows.
2. Set the blocked process threshold sp_configure setting to 10 seconds. Execute the following script in one of the query windows:
   

   EXEC sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE WITH OVERRIDE;
   GO
   EXEC sp_configure 'blocked process threshold', 10;
   GO
   RECONFIGURE WITH OVERRIDE;
   GO
   EXEC sp_configure;
   /*
   --When done with the demo, execute following T-SQL to clean up
   
   EXEC sp_configure 'blocked process threshold', 0;
   GO
   RECONFIGURE WITH OVERRIDE;
   GO
   EXEC sp_configure 'show advanced options', 0;
   GO
   RECONFIGURE WITH OVERRIDE;
   GO
   */

3. Verify that the run value for blocked process threshold is 10. Clear the query text and type the following T-SQL into in the first query window:
   

   USE [AdventureWorks];
   GO
   
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   GO
   
   BEGIN TRANSACTION;
   GO
   
   UPDATE Production.ProductInventory SET Quantity = Quantity - 1
   WHERE ProductID = 1;
   GO
   -- Execute till above line first
   -- And then run query in second connection
   -- Finally, To end blocking in other window, execute following code
   ----------------------------------------------------------
   
   ROLLBACK TRANSACTION;
   GO
   
   SELECT @@TRANCOUNT;
   GO

4. Type the following T-SQL into in the second query window:
   

   USE [AdventureWorks];
   GO
   
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   GO
   
   BEGIN TRANSACTION;
   GO
   
   SELECT SUM(Quantity) FROM Production.ProductInventory
   WHERE ProductID = 1;
   GO
   
   ROLLBACK TRANSACTION;
   GO
   
   SELECT @@TRANCOUNT;
   GO

   Do not run the queries yet. You need to first set up Profiler to capture the blocking event.

5. Start SQL Profiler by running Profiler90.exe or by selecting Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.
6. Click the New Trace toolbar button or select File | New Trace.
7. Connect to a SQL Server 2005 instance.
8. In the Trace Properties dialog box, select Blank from the Use the Template combo box.
9. Click the Events Selection tab, expand the Errors and Warnings event class, and check the Blocked Process Report event. Click Run to start tracing.
10. In the first query window, execute the queries up to the UPDATE statement. Then execute the entire batch in the second query window. Because the first connection updates the data without committing it and the second connection is trying to access the same data in READ COMMITTED isolation mode, the query blocks on the SELECT statement in the second window. Note that the query in the second connection will not be blocked if the READ_COMMITTED_SNAPSHOT database option is turned on. You can turn off the READ_COMMITTED_SNAPSHOT database option by running the ALTER DATABASE statement.
11. Switch to the Profiler window and wait 10 seconds, and you should see the blocked process report event in the Profiler. The event is generated again every 10 seconds.
12. Switch to the first Query Editor window and roll back the transaction in which the UPDATE statement was executed. This should end the blocking in the second query window, the SELECT statement should execute, and even that transaction should be rolled back. You should not see Blocked Process Report events in the Profiler after this.
13. Reset the sp_configure settings by running the commented query block in the ProfilerSP_Configure.sql script file. Close the Profiler and Management Studio tools.

Deadlock Graphs

A deadlock occurs when two connections have permanently blocked each other because each connection has a lock on a resource that the other connection is trying to acquire. SQL Server 2000 Profiler contains two events related to deadlocks under the Locks event class: Lock:Deadlock and Lock:Deadlock Chain. The former indicates that the engine has detected a deadlock, and the later results in printing the SPIDs involved in the deadlock. Neither of these events provides any useful information for troubleshooting deadlocks. SQL Server 2005 fixes this by providing detailed deadlock information in the form of XML and also as a graphical picture. Refer to Figure 4.4 in Chapter 4 to see a deadlock graph generated by the Profiler.

Here are the steps for generating a deadlock scenario by simultaneously running two query batches in Query Editor windows and by monitoring the Deadlock Graph event in the Profiler:

1. Start SQL Server Management Studio and open two query windows. Connect to the same SQL Server 2005 instance in both query windows.
2. Type the following script into the first query window:
   

   --Connection 1
   USE [tempdb];
   GO
   
   CREATE SCHEMA Forums;
   GO
   
   CREATE TABLE Forums.tblPosts
      (PostID INT IDENTITY(1,1) PRIMARY KEY,
       Title NVARCHAR(256) NOT NULL,
       Question NTEXT NOT NULL DEFAULT ''),
   GO
   
   INSERT INTO Forums.tblPosts VALUES(N'Post 1', N'Question 1'),
   INSERT INTO Forums.tblPosts VALUES(N'Post 2', N'Question 2'),
   GO
   
   DECLARE @RetryCount INT
   SET @RetryCount = 5
   WHILE (@RetryCount > 0)
   BEGIN
      BEGIN TRY
         BEGIN TRANSACTION
   
         UPDATE Forums.tblPosts SET Title = N'Session 1 Post 1'
             WHERE PostID = 1
   
         WAITFOR DELAY '00:00:10'
         UPDATE Forums.tblPosts SET Title = N'Session 1 Post 2'
             WHERE PostID = 2
   
         SET @RetryCount = -1 --Update successful, break
      END TRY
      BEGIN CATCH
         IF (ERROR_NUMBER() = 1205)
            SET @RetryCount = @RetryCount - 1 --retry
         ELSE --Some other error, break
            SET @RetryCount = -1
   
         IF (@@TRANCOUNT > 0)
               ROLLBACK TRANSACTION
      END CATCH
   END
   
   IF (@@TRANCOUNT > 0)
      COMMIT TRANSACTION
   
   DROP TABLE Forums.tblPosts;
   GO
   DROP SCHEMA Forums;
   GO

3. Type the following script into the second query window:
   

   --Connection 2
   USE [tempdb];
   GO
   
   DECLARE @RetryCount INT
   SET @RetryCount = 5
   WHILE (@RetryCount > 0)
   BEGIN
      BEGIN TRY
         BEGIN TRANSACTION
   
         UPDATE Forums.tblPosts SET Title = N'Session 2 Post 2'
             WHERE PostID = 2
         WAITFOR DELAY '00:00:10'
         UPDATE Forums.tblPosts SET Title = N'Session 2 Post 1'
             WHERE PostID = 1
         SET @RetryCount = -1 --Update successful, break
      END TRY
   
      BEGIN CATCH
         IF (ERROR_NUMBER() = 1205)
            SET @RetryCount = @RetryCount-1 --retry
         ELSE --Some other error, break
            SET @RetryCount = -1
   
         IF (@@TRANCOUNT > 0)
               ROLLBACK TRANSACTION
      END CATCH
   END
   
   IF (@@TRANCOUNT > 0)
      COMMIT TRANSACTION

4. Start SQL Server 2005 Profiler.
5. Click the New Trace toolbar button or select File | New Trace and connect to the same SQL Server 2005 instance as in step 1.
6. On the General tab of the Trace Properties dialog that appears, select Blank from the Use the Template combo box.
7. Click the Events Selection tab. Expand the Locks event class and then check the Deadlock Graph event.
8. Click the Run button on the Trace Properties dialog.
9. Switch back to the Management Studio query windows.
10. Run the entire batch in the first query window and immediately switch to the second query window and run that batch also.
11. Switch back to the Profiler window. Wait about 12 or 15 seconds, and you should see a Deadlock Graph entry in the Profiler window, with a picture shown in the bottom part of that Profiler window and deadlock XML data in the TextData column.
12. The first query window updates the record that has a PostID of 1, and the second query window updates the record that has a PostID of 2. Each waits for 10 seconds and then tries to update the record that is updated by the other query window. Because this all happens within a transaction scope, it creates a deadlock scenario. Close the query windows and Profiler.

XML Showplans

One of the techniques used to troubleshoot query performance issues is to obtain the detailed query execution plan and study it to find about which query components can be optimized. SQL Server 2000 provides SET statements such as SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL that can be used to obtain query execution plan details. When you turn on these options and then execute the query batch, SQL Server does not actually run the queries, but it returns the execution plan details for the queries.

SQL Server 2005 introduces a new SET SHOWPLAN statement option, SET SHOWPLAN_XML, which can be used to obtain the query execution plan as XML text. Let’s try it out:

1. Start a new query window in Management Studio and connect to a SQL Server 2005 instance.
2. Run the SET SHOWPLAN_XML ON statement and then run any query. Instead of executing that query, the Query Editor result window contains execution plan details as XML text.
3. If you are viewing the results in the grid mode, click XML Showplan, and Management Studio launches a new editor window to show the XML results. Save this XML into a text file and give .sqlplan as the file extension.
4. Double-click the .sqlplan file, and the file opens through Management Studio, which shows the execution plan graphically, without even connecting to any server.

This can be very useful in tuning queries.

You can obtain the same XML showplan by using SQL Server 2005 Profiler. The Profiler also shows the execution plan graphically and also allows viewing and saving the showplan as XML. Here are the steps for obtaining an XML showplan by using Profiler and then viewing the execution plan graphically by using Management Studio:

1. Start SQL Server Management Studio; open a query window by connecting to a SQL Server 2005 instance.
2. Type the following script into the query window:
   

   USE AdventureWorks;
   GO
   
   DECLARE @countRows INT
   SET @countRows = 10
   SELECT TOP(@countRows) * FROM Production.Product;
   GO

3. Start SQL Server 2005 Profiler.
4. Click the New Trace toolbar button or select File | New Trace.
5. Connect to a SQL Server 2005 instance.
6. On the General tab of the Trace Properties dialog that appears, select Blank from the Use the Template combo box.
7. Click the Events Selection tab and select Showplan XML under the Performance event class. Check Show Only Selected Events and make sure only one entry is listed.
8. As soon as you click the Showplan XML event, you should see a third tab called Events Extraction Settings on the Trace Properties dialog. Select this tab and check the Save XML Showplan Events Separately check box.
9. Select the path and type a filename in the Save As dialog. The XML Showplan details will be saved in this file. The file will have .SQLPlan as the file extension. Leave the All XML Showplan Batches in a Single File option checked and click the Run button to begin tracing.
10. Switch back to the query window in Management Studio and execute the query batch.
11. Switch back to the Profiler window and stop the trace by clicking the Stop Selected Trace toolbar button or by selecting File | Stop Trace.
12. Start Windows Explorer and browse to the folder that contains the .showplan file generated by the Profiler. Double-click the file to have the file opened in Management Studio. Notice how Management Studio reads the showplan XML file and graphically displays the execution plan.

New Performance Monitor Counters

Performance Monitor (PerfMon.exe), also known as System Monitor, is a tool that is provided with Windows and can be used to identify bottlenecks in application, database, and resource utilizations. Compared to previous releases, SQL Server 2005 installs several new counters to monitor new subsystems, such as Service Broker, Database Mirroring, HTTP Web Services, Notification Services, Reporting Services, and Database Mail, and it also provides additional information for existing components. The sys.dm_os_performance_counters dynamic management view can be used to see a list of SQL Server performance counters and their current values. You start PerfMon and press Ctrl+I or click the Add toolbar button (the plus sign) to launch the Add Counters dialog. Then you expand the Performance Object combo box to see the new SQL Server 2005 counters.

If you have a named instance, then the counters’ object names for the relational engine will start with MSSQL$<instance_name>, and SQLAgent$<instance_name> for Agent counters, MSOLAP$<instance_name> for Analysis Server counters, and MSFTESQL$<instance_name> for Full-Text Search counters, and for the default instance, the counter object names will start with SQLServer for Relational Server and Analysis Server for Analysis Services. For each instance of the Notification Services application, PerfMon provides a set of counters that can be used to monitor subscriptions, events, notifications, and so on. All the Notification Services counter object names begin with NS$<app_instance_name>. The Reporting Services counters are provided under the RS Windows Service and RS Web Service objects. The SSIS performance counters are provided under the SQLServer:SSIS Pipeline and SQLServer:SSIS Service objects.

Database Engine Tuning Advisor

The physical database design is one of the crucial factors on which the performance and manageability of an enterprise database system depends. Therefore, Microsoft wanted to provide an automated physical design tool that DBAs can use to figure out things like what indexes should or should not exist, how and if the data should be partitioned, whether indexed views should be created, and so on. Some of this functionality was provided by the Index Tuning Wizard in SQL Server 2000.

In SQL Server 2005, the Index Tuning Wizard has been replaced with a full-fledged application called Database Engine Tuning Advisor (DTA). It’s not just a direct replacement of a wizard with an application. Rather, the DTA has significant improvements and contains new features to make better and integrated recommendations for indexes, indexed views, and partitioning. Here are some of the enhancements introduced in the DTA:

• The DTA can be executed either from a graphical user interface (dtashell.exe) or from the command line (dta.exe). The command-line executable accepts several switches that can be used to configure the tuning settings.

• The DTA supports tuning multiple databases simultaneously.

• The DTA is designed to scale well with large databases and workloads, while maintaining good recommendation quality. The DTA achieves this scalability goal by using innovative techniques such as workload compression (looking for “similar” queries to templatize and partition the workload), intelligent algorithms to reduce the number of statistics created, and the ability to tune in a production/test server scenario, which enables tuning a production server while offloading most of the tuning load onto a test server, without actually having the data on the test server. The DTA copies the metadata, necessary statistics, views, stored procedures, triggers, and so on from the production server to the test server, and then it tunes the workload on the test server. When the what-if analysis and tuning are complete on the test server, the recommendation can be applied to the production server. This functionality is provided only via the -ix switch to the DTA command-line executable (dta.exe) and is not available in the DTA graphical user interface.

• The recommendations made by DTA are not solely based on performance. Rather, they can be based on both performance and manageability.

• The DTA supports more T-SQL constructs than the Index Tuning Wizard. For instance, your workload can now have table-valued functions and reference a temporary table, and the DTA will be able to consider that, analyze the workload, and make recommendations accordingly. In addition, the DTA can now tune triggers.

• The DTA allows you to evaluate recommendations and specify which recommendations to implement. You can immediately apply a selected recommendations or schedule it for later, or you can save recommendations to a script file.

• The input (databases to tune, query workload, and other constraints) and output (physical design recommendations) to the DTA are in XML format with a public schema. The use of XML makes scripting and customization easy and also enables other tools to build value-added functionality on top of the DTA. The XML schema file is named dtaschema.xsd, and it is available in the folder

%ProgramFiles%Microsoft SQL
Server90ToolsBinnschemassqlserver20033dta.

• The DTA provides richer user interface experience and contains a lot of usability enhancements.

• Unlike the Index Tuning Wizard, which only sysadmin server role members can use, the DTA can be executed by any members of the db_owner database role.

• The DTA works very closely with the Query Optimizer. This ensures that the recommendations made by the DTA will actually be effective when the Query Optimizer runs the same workload. Also, as the Query Optimizer’s cost model evolves over time, the DTA will be able to automatically benefit from improvements to it.

• The DTA allows you to control whether the physical database design should be aligned. That is, a table and all its indexes can be partitioned identically, which simplifies the partition management.

• The DTA has been enhanced to take into consideration the new indexing options, such as online index, non-key columns with nonclustered indexes, and so on. In addition to providing recommendations regarding indexes and indexed views, the DTA can make several other recommendations, such as how to range partition the data, indexes, and indexed views; recommendations on including non-key columns with nonclustered indexes, implementing XML indexes, and index rebuilding/reorganizing suggestions to reduce the cost of index scans and seeks.

• The DTA provides the ability to limit the tuning time and have the tuning operation end at a particular time. The default is one hour for the graphical tool and 0 for the command-line dta.exe, which means unlimited time. This is useful if you are tuning the production server and want to make sure that the tuning finishes well before your maintenance window ends.

The input to the DTA is one or more user databases on a server, a workload to tune, which can be a SQL script file, a Profiler trace file saved as a .trc or .xml file, a table containing a Profiler trace, features to tune (indexes, indexed views, partitioning, and a combination of these), an optional alignment constraint, an optional time constraint indicating by when the tuning should end, and an optionally bound on the size of the physical design it recommends (for instance, a restriction on the size of indexes). The output of the DTA is a physical design recommendation consisting of indexes (online and offline), indexed views, and partitioning recommendations.

Let’s now see the DTA in action. Follow these steps to create a large table and optimize the query against this table by using the DTA:

1. Start SQL Server Management Studio and connect to a SQL Server 2005 instance in a Query Editor mode.
2. Execute the following script in Query Editor:
   

   USE AdventureWorks;
   GO
   
   IF OBJECT_ID('dbo.TestOrderDetails') IS NOT NULL
       DROP TABLE dbo.TestOrderDetails;
   GO
   
   CREATE TABLE dbo.TestOrderDetails(
      [SalesOrderID] [int] NOT NULL,
      [SalesOrderDetailID] [int] NOT NULL,
      [CarrierTrackingNumber] [nvarchar](25),
      [OrderQty] [smallint] NOT NULL,
      [ProductID] [int] NOT NULL,
      [SpecialOfferID] [int] NOT NULL,
      [UnitPrice] [money] NOT NULL,
      [UnitPriceDiscount] [money] NOT NULL,
      [LineTotal] NUMERIC(17, 2) NULL,
      [rowguid] [uniqueidentifier],
      [ModifiedDate] [datetime] NOT NULL);
   GO
   
   DECLARE @counter INT;
   SET @counter = 1;
   WHILE @counter <= 10
   BEGIN
       INSERT INTO dbo.TestOrderDetails
       SELECT * FROM Sales.SalesOrderDetail;
       SET @counter = @counter + 1;
   END
   
   SELECT COUNT(*) FROM dbo.TestOrderDetails;
   GO
   
   EXEC sys.sp_help 'TestOrderDetails';
   GO
   checkpoint;
   dbcc freeproccache;
   dbcc dropcleanbuffers;
   GO

   This script creates a table and copies rows in a loop from an existing table, Sales.SalesOrderDetail, in the AdventureWorks sample database. After you execute this script, you should have a table with about 1,213,170 rows in it.

3. The query workload is a single query to retrieve all orders that have a quantity greater than 20. Let’s assume that the following query is available in a file named DTAWorkload.sql:
   

   USE [AdventureWorks];
   GO
   
   SELECT SalesOrderID, SalesOrderDetailID, OrderQty
      FROM TestOrderDetails WHERE OrderQty > 20;
   GO

   Run this query and note the time it takes to finish. Also obtain the execution plan for the query by highlighting the query and selecting Query | Display Estimated Execution Plan or by pressing Ctrl+L. Because the table does not have any indexes, the execution plan should show Table Scan as the step taking 100% of the cost. Hover the mouse over the Table Scan icon and note the various parameters, such as I/O Cost, CPU Cost, and Operator Cost. You need to try to optimize this query by using DTA.

4. Start the DTA by running dtashell.exe or selecting Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | Database Engine Tuning Advisor.
5. Connect to the same SQL Server instance as in step 1.
6. A new DTA session should be created by default. If it is not, select File | New Session to start a new DTA tuning session.
7. Type the session name as TestSession.
8. Select DTAWorkload.sql as the workload file.
9. Check AdventureWorks from the list of databases to tune.
10. Expand the Selected Tables list box for AdventureWorks and clear the top check box to unselect all tables. Select TestOrderDetails and click anywhere else on the DTA to close the Selected Tables list box. Ensure that the Selected Tables list box shows that only one table is selected.
11. Select the Tuning Options tab. Check the Indexes radio button from the Physical Design Structure (PDS) to use in database group. Check the No Partitioning radio button from the Partitioning strategy to employ group, and check Do Not Keep Any Existing PDS from the Physical Design Structures (PDS) to Keep in database group.
12. Click Advanced Options to see that the DTA provides options to limit the size of recommendations and that it recommends online or offline indexes. Close the dialog without changing anything.
13. Note the Limit Tuning Time option on the Tuning Options tab. If you would like, uncheck that option.
14. Click the green arrow on the toolbar or select Actions | Start Analysis or press F5 to begin analysis.
    

    After you start the analysis, you should see the Progress tab with the progress indicator. When the tuning is over, you should see two more tabs: Recommendations and Reports. Figure 5.9 shows the Recommendations tab after the analysis is over.

    Figure 5.9. Based on the workload and other settings, the DTA recommends creating a nonclustered index with a few other non-key columns included in the index.

    

15. You can click the link under the Definition column to see the T-SQL script to implement that recommendation. The check box in the first column lets you choose which recommendations to apply. You can evaluate the recommendations by clicking Actions | Evaluate Recommendations or apply the recommendations by clicking Actions | Apply Recommendations. When you select to evaluate the recommendations, the DTA creates a new session and passes it the configuration XML file that contains the recommendation details. You can view the XML configuration text used for evaluating the recommendation by clicking the Click Here to See the Configuration Section link provided at the bottom of the Workload tab for the new session created for evaluation. You have to provide the session name, start the analysis, and review the reports to see the evaluation results. At this point, you need to just apply the recommendations instead of evaluating them.
16. Select Actions | Save Recommendations to save recommendations as a T-SQL script file. Then select Actions | Apply Recommendations to either apply the recommendations immediately or schedule them to run at a later time, which creates a job with one T-SQL step.
17. After you apply the recommendations, again run the SELECT query in the DTAWorkload.sql file and see how fast it runs. Also obtain the execution plan for the query by highlighting the query and selecting Query | Display Estimated Execution Plan or by pressing Ctrl+L. This time, the execution plan should show Index Seek instead of Table Scan. Hover the mouse over the Index Seek icon and note that parameters such as I/O Cost, CPU Cost, and Operator Cost have gone down significantly.

You can import or export the session details as XML by using the File | Import Session Definition and File | Export Session Definition menu items. You can export the analysis results as an XML file by using File | Export Session Results. You can duplicate the DTA session settings by selecting Actions | Clone Session. DTA uses the msdb system database to store session and tuning log result details. You can execute the following query to obtain a list of tables and stored procedures used by the DTA:

SELECT * FROM msdb.sys.all_objects
   WHERE ([name] LIKE 'dta%' OR [name] LIKE 'sp_DTA%') AND
         ([type] = 'U' OR [Type] = 'P')
   ORDER BY [name], [type];
GO

Operations and Configuration

This section focuses on the tools that you can use to configure and manage services, administer client and server network configurations, configure email support in SQL Server 2005, and run SQL Server scripts from the DOS command prompt.

SQL Server Configuration Manager

SQL Server Configuration Manager is yet another example of Microsoft’s initiative to combine multiple tools into a single integrated tool. SQL Server Configuration Manager provides the functionality of three tools provided with SQL Server 2000: Service Manager, Server Network Utility, and Client Network Utility. SQL Server Configuration Manager is an MMC snap-in (%SystemRoot%system32SQLServerManager.msc) that provides the means to manage SQL Server services and connectivity settings. SQL Server Configuration Manager internally uses WMI to access and update SQL Server services and connectivity details.

Figure 5.10 shows the SQL Server Configuration Manager. The three top-level items on the tree on the left are SQL Server 2005 Services, SQL Server 2005 Network Configuration, and SQL Native Client Configuration. When you select SQL Server 2005 Services from the tree, the grid on the right shows various SQL Server 2005 services including Relational Engine, Analysis Services, Integration Services, Notification Services, Reporting Services, Agent, Full-Text, and the SQL Server Browser service. You can right-click a service and select Properties to view and change service settings. Figure 5.10 shows SQL Server Configuration Manager, listing various SQL Server 2005 services.

Figure 5.10. SQL Server Configuration Manager is an MMC snap-in application that helps you manage services and connectivity settings.

You can use SQL Server Configuration Manager to start, pause, resume, or stop services and to view and change service properties, such as service start mode (automatic, manual, or disabled) and the account name under which the service runs. If you would like to change the account under which the services should run, it is recommended that you use SQL Server Configuration Manager to do that. This ensures that the new account is assigned the required permissions on the disk folders and registry hives.

The SQL Server 2005 Network Configuration node in the SQL Server Configuration Manager tree can be used to configure network libraries that SQL Server listens on. If you expand this node in the tree, you should see items such as Shared Memory, Named Pipes, TCP/IP, and VIA for Virtual Interface Architecture, which is a network library designed to support highly reliable, fast, efficient data transfer between servers by using hardware from Giganet. You can right-click any network library and select Properties to view and configure network library settings or to enable or disable the protocol.

The SQL Native Client Configuration node can be used to manage the client net-libraries and define server alias names. Click Client Protocols and you should see all the client network libraries on the right hand side. You can right-click or double-click any entry to set the properties for the selected client network library. To create a new client alias, you expand the SQL Native Client Configuration node, right-click Aliases, and select New Alias.

The SQL Server Configuration Manager can also connect to a different server and manage services and connectivity settings for that server. To connect to a different server, you select the Start menu, right-click My Computer, and click Manage. In Computer Management, you right-click Computer Management (Local) and then select Connect to Another Computer. You connect to the remote server and under Services and Applications, you should see SQL Server Configuration Manager, which you can use to control SQL Server services and network settings on that server.

SQL Server Configuration Manager ships with all SQL Server 2005 editions, including SQL Server 2005 Express Edition.

The SQLCMD Utility

SQLCMD is a new DOS command-line utility introduced in SQL Server 2005 to interactively execute T-SQL statements and also to execute scripts from the command line.

SQL Server 2005 no longer ships the DB-library–based isql.exe, and it discourages the use of the ODBC-based osql.exe. SQLCMD.exe uses the SQL native client, which is OLE DB based, to communicate with SQL Server. SQLCMD doesn’t just replace osql.exe, but it also contains various enhancements that make it more robust and usable.

SQLCMD Basics

SQLCMD accepts several command-line switches, similarly to osql.exe. Also as with osql.exe, the SQLCMD command-line parameters are case sensitive. You can pass the -? or /? command-line switch to get a list of all the parameters that SQLCMD accepts. If you pass the -S parameter along with the server name, such as MyServer or MyServerInstanceName, SQLCMD connects to the specified SQL Server instance by using Windows authentication.

Let’s look at some basic SQLCMD examples.

The following statement connects to an instance named YUKON on the server DDGXP (-S) by using a trusted connection (-E); update the statement to connect to your SQL Server 2005 instance. It defaults to a database named AdventureWorks, and the -p switch tells SQLCMD to print performance statistics after every query is executed:

SQLCMD -S DDGXPYUKON -E -d AdventureWorks -p

After you run this statement from a DOS command prompt, you should see the 1> prompt, where you can type T-SQL commands, press Enter, type GO, and press Enter to execute the query. You can type EXIT to end the SQLCMD session.

You use the -Q switch to execute the specified query and exit:

SQLCMD -S DDGXPYUKON -E -d AdventureWorks -Q "SELECT Name FROM Sales.Store"

If you pass the -q switch, SQLCMD executes the query and stays in interactive query mode, and you have to type EXIT to end the SQLCMD session.

You can use the -Z switch to change the SQL Server password:

SQLCMD -S DDGXPYUKON -U sa -P OldPassword2005 -Z NewPWD1234

If you pass the -z switch, SQLCMD changes the password and stays in interactive query mode, and you have to type EXIT to end the SQLCMD session.

One of the nice enhancements introduced in SQLCMD is the ability to provide multiple input script files. The following statement asks SQLCMD to connect to a SQL Server 2005 instance named YUKON, run the script files c:1.sql and c:2.sql, save the output results in a file called results.txt, separate the columns using the tilde character (~), and save the results.txt file as a Unicode file (-u switch) instead of using the default ANSI file format:

SQLCMD -S DDGXPYUKON -i c:1.sql,c:2.sql -o results.txt -s~ -u

The -L switch is for getting a list of local and other remote SQL Server machines that are broadcasting on the network:

SQLCMD -L

If you pass -Lc instead of -L, SQLCMD generates “clean” output, which does not have the Servers: heading line or any formatting spaces in front of the listed server names.

Table 5.3 lists all the SQLCMD switches.

Table 5.3. SQLCMD.exe Command-Line Switches

SQLCMD and Environment Variables

One way to instruct SQLCMD about the server and database to connect to, the authentication method to use, the network packet size, and other settings, is by passing the appropriate command-line switches, as described in the previous section. The other method is to set some environment variables. For instance, if you set an environment variable named SQLCMDDBNAME to a database name string, either by using a SET statement at the command prompt or in a batch file or by using the Environment Variables button on the Advanced tab of System Properties dialog (which you open by selecting Control Panel | System), SQLCMD connects to the database specified by the environment variable, provided that the -d switch is not passed to it. Similarly, if you set the SQLCMDSERVER environment variable and you don’t pass the -S parameter to SQLCMD, SQLCMD connects to the server specified by the environment variable. In other words, when you run SQLCMD, in addition to command-line switches, SQLCMD also looks at certain environment variables. If an environment variable is set to some value, and if an overriding switch is not provided on the command line, SQLCMD honors the environment variable. Here is how it works:

C:>SET SQLCMDSERVER=DDGXPYUKON
C:>SET SQLCMDDBNAME=AdventureWorks
C:>SET SQLCMDPACKETSIZE=8192
C:>SQLCMD -p1
1>SELECT TOP 1 Name FROM Sales.Store;
2>GO
Name
--------------------------------------------------
A Bike Store
(1 rows affected)
8192:1:1:1.00:1000.00

These statements at the DOS command prompt set environment variables for server, database, and network packet size and then run SQLCMD with -p1 to have colon statistics printed after each line. Note that SQLCMD automatically connects to a SQL Server 2005 instance, makes AdventureWorks the current database, and sets the packet size to 8192.

You can use the :setvar SQLCMD command to change the SQLCMD environment variable value. SQLCMD commands such as :setvar are discussed in the next section.

The following DOS command-prompt statements connect to a SQL Server 2005 instance and set ~ as the column separator:

C:>SQLCMD -S DDGXPYUKON -E -dAdventureWorks -s~
1> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store;
2> GO
Name           ~SalesPersonID
---------------~-------------
A Bike Store   ~          280
1> :setvar SQLCMDCOLSEP ","
2> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store;
3> GO
Name           ,SalesPersonID
---------------,-------------
A Bike Store   ,          280
1> :setvar SQLCMDCOLSEP ";"
2> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store;
3> GO
Name           ;SalesPersonID
---------------;-------------
A Bike Store   ;          280

After executing one SELECT statement, this script sets SQLCMDCOLSEP to a comma (,), and the results returned by a subsequent SELECT statement separate the columns by using a comma instead of a tilde. This is repeated again to change the column separator to a semicolon (;).

Table 5.4 lists the SQLCMD-specific environment variables and their command-line switch equivalents.

Table 5.4. SQLCMD-Specific Environment Variables

SQLCMD Commands

In addition to T-SQL commands, SQLCMD supports few other commands that you can run in interactive mode or inside T-SQL scripts executed using SQLCMD. The previous section shows an example of this—using the setvar command to set environment and scripting variables.

Every SQLCMD command must be preceded with a colon character. For backward compatibility with osql.exe, a colon is not required before EXIT, QUIT, ED, !!, and RESET, but it is recommended that you use the colon in front of all SQLCMD commands except GO. The !! is used to execute operating system commands, much like xp_cmdshell in SQL Server 2000 extended stored procedures.

Try out the following:

C:>SQLCMD -S DDGXPYUKON -E -dAdventureWorks
1> :Help
<Output omitted for brevity; shows help on all SQLCMD commands>
1> :ServerList
<Output omitted for brevity; List of servers, output similar to -L switch>
1> !!DIR
<Output omitted for brevity; Runs Operating System command>
1> :!!CLS
<Runs Operating System command; clears the screen>
1> INSERT INTO tblTest VALUES (10);
2> GO 100
< Output omitted for brevity; Runs the INSERT statement 100 times>
1> :r c:1.sql
< Output omitted for brevity; Runs another script file>

The final command in this example can be particularly very helpful. You can use the :r command to execute some other script from within your T-SQL script. Also note the command before the :r line. The number 100 is passed with the GO statement, which leads to executing the batch—the INSERT statement, in this case—100 times.

Table 5.5 lists the available SQLCMD commands.

Table 5.5. SQLCMD Commands

SQLCMD and Variables

The support for using variables with SQLCMD script enables you to write generic scripts so that one script can be used for multiple scenarios. Your T-SQL scripts can use $(VariableName) syntax in the T-SQL statements, and then you can pass the variable values from the command line (by using the -v switch), by using the :setvar SQLCMD command, or by setting their values as environment variables, using the SET statement.

Let’s say that the following text is available in a script file called SQLCMDVariables.sql:

EXEC master.dbo.sp_addumpdevice
    @devtype = N'disk',
    @logicalname = '$(DeviceLogicalName)',
    @physicalname = '$(DevicePhysicalName)';
GO

BACKUP DATABASE
   $(DBName) TO
   $(DeviceLogicalName)
GO

This script creates a new backup device and then performs a full database backup. The logical device name, physical file location, and database to back up are variables that can be set by using the :setvar command in the script (which is not done in this case), or they can be environment variables that are set by using the SET statement or by using the Control Panel, or they can be passed to SQLCMD by using the -v switch. Here is how to pass variables by using the -v switch:

SQLCMD -E -S DDGXPYUKON -v DeviceLogicalName="AW1" DevicePhysicalName="c:AW1.bkp" DBName="AdventureWorks" -i "c:SQLCMDVariables.sql"

Instead of passing the variable values on the command line by using the -v switch, you can set an environment variable with the same name as the variable name in the script, and SQLCMD uses the environment variable value if the variable value is not passed using -v:

C:>SET DeviceLogicalName=AW2
C:>SET DevicePhysicalName=c:AW2.bkp
C:>SET DBName=AdventureWorks
C:>SQLCMD -E -S DDGXPYUKON -i "c:SQLCMDVariables.sql"

The third way to set variable values is by using the :setvar SQLCMD command, as illustrated earlier.

You can also use the $(VariableName) syntax to access environment variables:

1> SELECT '$(NUMBER_OF_PROCESSORS)'
2> GO

These statements, when executed in a SQLCMD session, return the value of the NUMBER_OF_PROCESSORS environment variable, indicating the number of processors on the local machine.

Startup Scripts

You can set the SQLCMDINI environment variable to a SQL script file that will be run every time SQLCMD is started. Assume that the following text is available in the c:SQLCMDStartup.sql file:

SET NOCOUNT ON
PRINT ''
PRINT @@SERVERNAME
PRINT @@VERSION
PRINT GETDATE()
PRINT SYSTEM_USER
PRINT USER
PRINT DB_NAME()
PRINT ''

Now, if you set the SQLCMDINI environment variable to the c:SQLCMDStartup.sql file, this script will be run every time you start SQLCMD:

C:>SET SQLCMDINI=c:SQLCMDStartup.sql
C:>SQLCMD -E -S DDGXPYUKON
<executes script c:SQLCMDStartup.sql; output omitted for brevity>

Using a Dedicated Administrator Connection

In situations where SQL Server is locked or running in an abnormal state and not responding when you try to connect to it, the members of sysadmin server role can connect to a local or remote SQL Server 2005 instance over TCP/IP by using SQLCMD and the -A switch to troubleshoot problems on the SQL Server machine. This feature is called dedicated administrator connection (DAC). SQL Server allows only one DAC per instance. When you connect to SQL Server by using the -A switch, it is strongly recommended that you restrict your usage to certain diagnostic and troubleshooting commands only and set the isolation level to READ UNCOMMITTED to avoid any blocking. The 'remote admin connections' sp_configure option must be turned on in order to allow remote connections using DAC. If you are connecting to a named instance, the SQL Browser service must be started.

Introducing Database Mail

SQL Server 2005 introduces a highly scalable, reliable, and secure method of sending emails from within T-SQL code. This new feature, called Database Mail, uses Simple Mail Transfer Protocol (SMTP) to send email messages. Unlike the SQL Mail feature in SQL Server 2000, Database Mail does not require Microsoft Outlook or MAPI to be installed on the server.

Database Mail scales well because it uses Service Broker to asynchronously send email messages. Service Broker in turn activates an external process (DatabaseMail90.exe) to send email messages. This external Database Mail process uses a standard ADO.NET connection to communicate with SQL Server. This process isolation increases server reliability because even if the external process stops or fails, it does not affect the SQL Server process. The other reliability feature introduced in Database Mail is the ability to specify a failover SMTP server. If Database Mail cannot send by using one SMTP server, it tries to use another specified SMTP server to send emails. Unlike SQL Mail in SQL Server 2000, Database Mail is cluster aware and is fully supported on a cluster. Database Mail is fully supported on 64-bit SQL Server 2005 installations.

Some of the ways in which Database Mail offers a secure emailing solution are the MaxFileSize (to limit the size of email attachments) and ProhibitedExtensions (to restrict attachment file types) configuration settings, the fact that it does not require Outlook or MAPI on the SQL Server machine, and auditing and logging.

Configuring Database Mail

Following the “secure by default” principal, SQL Server 2005 does not enable Database Mail support by default. You have to configure Database Mail before you can use msdb.dbo.sp_send_dbmail stored procedure to send emails. You can enable and configure Database Mail by using the Database Mail Configuration Wizard. You can launch the Database Mail Configuration Wizard from within Management Studio by right-clicking the Database Mail node under Management folder in Object Explorer and selecting the Configure Database Mail menu item.

The Database Mail Configuration Wizard allows you to perform multiple tasks. You can use this wizard to enable and install Database Mail, to manage Database Mail profiles and accounts, to manage Database Mail security settings, and to configure Database Mail system parameters, such as MaxFileSize and ProhibitedExtensions.

The msdb database is the Database Mail host database. It contains the stored procedures and messaging objects for Database Mail. Database Mail stores the SMTP server information, profiles, accounts, security, and system parameters in the msdb system database.

Figure 5.11 shows various things that you can do by using the Database Mail Configuration Wizard.

Figure 5.11. The Database Mail Configuration Wizard can be used to enable and configure Database Mail.

The msdb.dbo. sp_send_dbmail stored procedure posts the email message in a Service Broker queue. The Service Broker activation feature launches an external application (DatabaseMail90.exe) when there is a message in the queue. This external process reads the messages from the Service Broker queue and the SMTP account and profile setting, including the SMTP server settings, from the msdb mail host database; and sends messages to the email server. The SMTP server is then responsible for actually delivering the messages.

Here are the steps for enabling Database Mail and sending an email by using Database Mail. These steps assume that you have an SMTP server installed and working. These steps use the local SMTP service installed as part of an IIS Web server. The SMTP virtual server is configured to grant connection and relay access to the IP address on which SQL Server 2005 is installed (local, in this case):

1. Launch SQL Server Management Studio. Connect Object Explorer to a SQL Server 2005 instance.
2. Expand the Management folder, right-click Database Mail, and select Configure Database Mail.
3. The Database Mail Configuration Wizard appears. Click Next on the Welcome screen.
4. Select the first option, Set up Database Mail by performing the following tasks, and then click Next.
5. Set the Profile Name to TestProfile. Click the Add button next to the SMTP Accounts heading and provide the SMTP account details. Type the account name as TestAccount; specify outgoing and reply-to email addresses, and specify the SMTP server. For a local IIS SMTP service, specify localhost. Provide SMTP authentication information if needed. Click OK and then click Next on the New Profile screen.
6. Make TestProfile a public profile by selecting the check box next to it. Also make it the default profile. Click Next.
7. The next wizard screen is Configure System Parameters. This is where you can specify things like the maximum file size for attachments, prohibited file extensions, and so on. For this demo, leave the defaults and click Next.
8. Verify the Database Mail configuration settings and click Finish when you’re done reviewing. When the wizard finishes, click Close to end the wizard. Database Mail is now configured.
9. Send an email by using Database Mail:
   

   EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'TestProfile',
       @recipients = '[email protected]',
       @body = 'Simple Database Mail Message',
       @subject = 'Database Mail';

   When you run these statements, you should get the “Mail queued.” message, and the recipient should get an email if the SMTP server is configured properly.

The msdb.dbo.sp_send_dbmail stored procedure takes various parameters, which you can use, for instance, to send results of a query in text or HTML format, attach files, and specify CC recipients, importance, sensitivity, and so on. Refer to SQL Server 2005 Books Online for details on other parameters.

Monitoring and Troubleshooting Database Mail

You can monitor and troubleshoot Database Mail by using the auditing and logging tables in the msdb mail host database.

For each outgoing email, Database Mail adds a status/logging record to the sysmail_log table, and the actual mail details are saved in the sysmail_mailitems table. You need to run a SELECT query on these two tables to find out whether Service Broker was able to activate the DatabaseMail90.exe application, whether DatabaseMail90.exe encountered problems while sending messages, and the status of each outgoing email. The event_type and description fields in the sysmail_log table and the sent_status field in the sysmail_mailitems table can be used to determine the success or failure for each outgoing email. The value of 0 for the sent_status field means that the DatabaseMail90.exe application is yet to process the message; 1 means success, indicating DatabaseMail90.exe was able to deliver message to the SMTP server; and 2 means that DatabaseMail90.exe received an error while delivering the message to the SMTP server, and in this case you should look at the sysmail_log table for more details about the failure.

In addition to the above-mentioned two tables, you can also access other Database Mail tables also in the msdb mail host database to verify the profile, SMTP account, security, and other system configuration settings. Table 5.6 lists these Database Mail-related tables in the msdb database.

Table 5.6. Database Mail-Specific Tables in the msdb System Database

In addition to tables mentioned in Table 5.6, you should also check the system and application event log (eventvwr.exe) entries for any errors related to Database Mail or SMTP. You should also check the folders under the C:Inetpubmailroot directory to see whether emails are being queued.

Management APIs

Earlier in this chapter you saw how to register a server in SQL Server Management Studio. What if you have to register hundreds of servers? You can certainly do it through the user interface or create an XML file by hand and import it into the user interface, but that is still a lot of work. You are likely to run into a similar situation when managing a large number of servers, databases, or database objects. For situations like this, you need the ability to automate common management tasks.

SQL Server 6.5 first introduced a COM-based application programming interface (API) called SQL-DMO, which allowed automating management tasks using languages such as VBScript, C++, and Visual Basic. This API was further extended in SQL Server 7.

SQL Server 2000 continued supporting SQL-DMO to automate all the tasks that you can do by using SQL Server Enterprise Manager. SQL Server 2005 still ships SQL-DMO COM libraries, but it is not upgraded to support newer features, such as Service Broker, Database Mail, Database Mirroring, and so on.

SQL-DMO is now being superseded by a .NET-based object library called SQL Server Management Objects (SMO). In addition to SMO, SQL Server 2005 introduces other .NET object libraries to automate replication tasks (Replication Management Objects [RMO]) and to automate Analysis Services management tasks (Analysis Management Objects [AMO]). This chapter provides details on SMO and WMI support in SQL Server 2005. Refer to Chapter 4 for an overview of WMI.

SQL Server Management Objects (SMO)

The SMO API’s primary goal is to provide a powerful yet easy-to-use programming interface to automate administration, deployment, and maintenance of SQL Server. In addition to providing maximum coverage of SQL Server 2005 features, the SMO API contains several other improvements over SQL-DMO. SMO is designed from the ground up so that the API can perform better, scale well, and increases flexibility. The SQL Server Management Studio internally uses the SMO API to interact with SQL Server. Therefore, anything you can do by using Management Studio, you can automate by using SMO. You can use SMO from any .NET programming language, such as Visual Basic .NET or C#. In addition, SQL Server 2005 provides a COM wrapper over this .NET library so that you can use SMO from scripting and unmanaged languages to automate management tasks.

The following are some of the improvements introduced in the SMO API:

• Delayed instantiation—If you access any of the instance class objects, SQL-DMO retrieves all the properties up front. On one hand, this might reduce the roundtrips to the server, but on the other hand, it requires more memory and also does not scale well. SMO gives developers the option to determine whether they want to retrieve objects and properties as needed (delayed instantiation), prefetch entire collections, or retrieve objects by using a set of predefined properties. In other words, SMO gives developers a fine level of control over how data is retrieved from the server.

• Cached object model—Unlike SQL-DMO, SMO does not propagate object changes to the server immediately. It caches the changes until you decide to apply or discard the changes, and hence it reduces the roundtrips to the server by sending updates in batches.

• WMI provider—If using WMI API seems complex to you, SMO has an answer for you. SMO provides a simple programming interface for performing tasks that you can do by using WMI API. Things that you can do using SQL Server Configuration Manager, such as managing service accounts, network protocols, and so on can now be easily done by using the SMO API.

• COM wrappers—The fact that SMO is a .NET-based object library does not mean you can use it only from .NET applications. SMO ships with a COM wrapper that can be used from scripting languages such as VBScript and JScript, and from other unmanaged programming languages, such as Visual Basic and Visual C++.

• Capture mode—Earlier in this chapter you learned that all the management dialogs in SQL Server Management Studio are scriptable. That means that a T-SQL script can be generated for the changes you perform in the dialog. The management dialogs provide this functionality by using the capture mode feature in SMO, which lets you record or capture changes made to SMO objects and generate a T-SQL script for those changes.

• Exception hierarchy—SMO exceptions are almost always chained, providing context at every level. This means that if there is an error, you can find out about the entire error chain.

• Releasable state—The SQL-DMO object model consists of a static object named Application as the root, and this object is a starting point for accessing SQL Server objects. SQL-DMO does not allow releasing this object. With SMO, the Application object is gone; the new top-level class in the SMO object hierarchy is Server, which can be released (set to null) at any time, freeing the memory.

• XPath-style syntax—Let’s say you would like to find out the total number of rows in a table. You can first get the Server object, then the Database object, and then the Table object, and then you can access the RowCount properties. SMO provides an alternative, simpler, and more efficient syntax to do this. This syntax, which looks like folder hierarchy syntax or like the unique resource name (URN) syntax, allows you to specify the path of the object you want to access.

• Advanced scripting support—With SQL-DMO, scripting was deeply embedded in SQL-DMO classes, each having a Script method. SMO continues to support this approach for backward compatibility, but it also introduces a standalone object, a class called Scripter that provides advanced scripting support such as discovering object dependencies.

The SMO API provides three types of classes:

• Instance classes—These classes provide access to all SQL Server objects and their properties. Examples of instance classes include Server, Database, Table, View, and StoredProcedure.

• Utility classes—These classes can be used for management and administration tasks such as backup/restore, jobs, Database Mail, and Service Broker management.

• Scripter class—This class provides advanced scripting functionality.

Here is an example of using SMO in VBScript:

Dim smoSvr
Dim svrName
Dim smoDB

svrName = "DDGXPYUKON"

Set smoSvr = CreateObject("Microsoft.SqlServer.Management.Smo.Server")

smoSvr.ConnectionContext.ServerInstance = svrName

For Each smoDB in smoSvr.Databases
    WScript.Echo smoDB.Name
Next
Set smoSvr = Nothing

This script lists all the databases on a server. You can update the svrName variable with your SQL Server 2005 instance name, save the script, and run it. The script displays a message box that lists each database on the specified server.

Here is another example of using SMO in VBScript. This VBScript creates a new database on the specified server.

Dim smoSvr
Dim smoDB

Dim svrName
Dim dbName

svrName = "DDGXPYUKON"
dbName = "TestDatabase"

Set smoSvr = CreateObject("Microsoft.SqlServer.Management.Smo.Server")

smoSvr.ConnectionContext.ServerInstance = svrName

'Create the database
Set smoDB = CreateObject("Microsoft.SqlServer.Management.Smo.Database")
smoDB.Parent = smoSvr

smoDB.Name = dbName
smoDB.Create()
WScript.Echo "Done! Database created successfully."

Set smoSvr = Nothing
Set smoDB = Nothing

Before concluding this chapter, let’s see an example of WMI support in SQL Server 2005.

Windows Management Instrumentation (WMI)

As described in Chapter 4, WMI provides an industry-standard programming interface for managing an enterprise environment. WMI provides a consistent way of managing hardware such as disk drives and software such as IIS Web server and SQL Server. (Refer to Chapter 4 for an overview of WMI.)

WMI support in SQL Server 2005 primarily allows you to do the following:

• Manage SQL Server services, service accounts, and network protocols. SQL Server Configuration Manager uses WMI and hence anything that you can do using SQL Server Configuration Manager can be programmed using WMI API.

• Monitor SQL Server events. As you will see in an example later in this section, you can write VBScript or code in some other language, use WMI, and monitor the events in SQL Server. In the example provided in this chapter, the VBScript monitors any DDL events, such as creating a table or dropping a table.

• Generate SQL Server Agent alerts in response to WMI events. This is essentially a counterpart to the previous point. You can write T-SQL code to raise an Agent alert in response to a WMI event. You can use the new @wmi_namespace and @wmi_query parameters added to the msdb.dbo.sp_add_alert stored procedure to implement this functionality.

Not enough time to work on this one! It’s ok. Here is a VBScript example that uses WMI to monitor DDL events in SQL Server 2005:

Dim evtToMonitor
Dim currentEvent
Set evtToMonitor = GetObject("winmgmts:{impersonationLevel=impersonate}! //./root/Microsoft/SqlServer/ServerEvents/YUKON"). ExecNotificationQuery("select * from DDL_EVENTS")

WScript.Echo "Running...Click OK to continue..."

Do
Set currentEvent = evtToMonitor.NextEvent

If Err <> 0 then
WScript.Echo Err.Number, Err.Description, Err.Source
Exit Do
Else
WScript.Echo "DDL Event in database '" & _
      currentEvent.DatabaseName & "' for object '" & _
      currentEvent.ObjectName & "'" & vbNewLine & vbNewLine & _
      currentEvent.TSQLCommand & vbNewLine & vbNewLine
End If
Loop

Type this VBScript text into a text file or use the WMIEventMonitor.vbs script file provided with the code download, update the WMI namespace to include your SQL Server 2005 instance name in place of YUKON, and then run the VBScript file by double-clicking it in File Explorer.

The period (.) in the WMI namespace indicates the local server. If you want to monitor SQL Server on a different machine, you type the machine name instead of a period. Also, the word YUKON in the WMI namespace is the SQL Server 2005 instance name. You should change it to your named instance; for default or unnamed instance, you can replace YUKON with the word MSSQLSERVER.

After you launch the VBScript file, click OK, and then start SQL Server Management Studio or a SQLCMD session and connect to the server specified in the WMI script. Then you should create a table by using the CREATE TABLE DDL or by using Object Explorer. Or you can alter or drop a table, and for each DDL action you perform on this server, the script shows a message box showing the database in which DDL action is performed, the object name in context, and the XML formatted DDL action. To end the monitoring, you launch Windows Task Manager and end the wscript.exe process.

Summary

In this chapter, you have learned about the new suite of graphical and command-line tools introduced in SQL Server 2005.

The new SQL Server Management Studio consolidates the functionality provided by Enterprise Manager, Query Analyzer, and Analysis Manager. The new Business Intelligence Development Studio supports the complete business intelligence development life cycle by providing integrated tools to integrate, analyze, and report. The free SQL Server edition, SQL Server 2005 Express Edition, now bundles graphical tools such as Express Manager that can be used to manage server and author queries.

SQL Profiler is enhanced to support SQL Server 2005 features and also to provide better information to aid in troubleshooting performance issues. The Index Tuning Wizard from the previous release has now been replaced with a brand-new application called the Database Engine Tuning Advisor that assists in physical database design by recommending indexes, indexed views, and partitioning.

The new SQL Server Configuration Manager integrates Service Manager, Server Network Utility, and Client Network Utility into one application. SQLCMD is the new command-prompt utility that replaces osql.exe and also contains better scripting support. SQL Server 2005 introduces a SMTP-based emailing solution called Database Mail that provides a scalable, reliable, and secure method of sending emails.

The final section in this chapter shows you how to automate management tasks by using SMO and WMI. Chapter 6, “Transact-SQL Enhancements,” describes the new features and enhancements introduced in the T-SQL language.