Database Administration and Management

SQL Server is well known for its graphical management tools that allow administrators, developers, and business intelligence professionals to easily manage data and to monitor and tune servers. These graphical tools and wizards make database professionals more productive and leveraged. Hence these tools are one of the key elements to reducing the total cost of ownership (TCO).

However, there are a few issues with the tools in releases prior to SQL Server 2005:

• Database administrators and developers have to use multiple tools to get a job done. For instance, they need to use Query Analyzer to run queries, Enterprise Manager to manage the server, Service Manager to manage services, separate client and server network utilities, and so on.

• Enterprise Manager suffers performance issues with servers that have a large number of databases and database objects.

• All the dialog boxes in Enterprise Manager are modal. Hence if you open a dialog, such as the Server Properties dialog, you cannot go back to Enterprise Manager until you close that dialog.

• The Profiler tool only allows tracing activity on the relational server; it does not have any knowledge of Analysis Services.

• The tools do not offer project/solution management or integration with source code control system.

• SQL Server 2000 Query Analyzer allows editing and running Transact-SQL (T-SQL) queries only. There is no built-in editor (other than the MDX sample application) to run Analysis Services Multidimensional Expressions (MDX) queries.

Some of the design goals of SQL Server 2005 are to address these problems; to give more power to DBAs, developers, and information workers; and to simplify the management of SQL Server by providing integrated tools.

Introducing SQL Server Management Studio

SQL Server Management Studio is a new integrated tool that combines the functionality of three tools: Query Analyzer, Enterprise Manager, and Analysis Manager. In addition, it allows you to manage all other components, such as Reporting Services, SQL Server Integration Services (SSIS; formerly known as DTS), Notification Services, and SQL Server Mobile.

SQL Server Management Studio defers loading all databases and database objects in the tree, and it expands the nodes asynchronously. This is very useful if you are working on a server with a large number of databases and database objects. In addition, Management Studio allows filtering of the database objects in the tree by name, schema, and creation date. All the dialog boxes in Managed Studio are non-modal, resizable, schedulable, and scriptable.

In addition to T-SQL queries, the Query Editor in Management Studio allows you to work with MDX, Data Mining Prediction (DMX), and XML for Analysis (XMLA) queries.

Management Studio supports the notion of solutions and projects and provides full integration with source code control system such as Microsoft Visual SourceSafe.

Figure 4.1 shows various windows inside the Management Studio, such as Registered Servers, Object Explorer, Query Editor, Solution Explorer, Execution Plan, and Properties.

Figure 4.1. SQL Server 2005 Management Studio is a new integrated management environment.

In a nutshell, Management Studio is an integrated environment for accessing, configuring, managing, and administering all components of SQL Server. Management Studio and other tools are discussed in more detail in Chapter 5, "SQL Server 2005 Tools and Utilities."

SQL Server Configuration Manager

SQL Server Configuration Manager is an MMC snap-in (like Enterprise Manager in SQL Server 2000) that is intended to replace Server Network Utility, Client Network Utility, and Service Manager. SQL Server Configuration Manager was referred to as SQL Computer Manager in earlier SQL Server 2005 beta builds.

As shown in Figure 4.2, SQL Server Configuration Manager can be used to manage SQL Server 2005 services and service accounts, including Analysis Services, Agent, Reporting Services, Notification Services, SQL Server Browser, and full-text search. It can also be used to manage client and server network connectivity options.

Figure 4.2. The SQL Server Configuration Manager combines Client Network Utility, Server Network Utility, and Service Manager into a single integrated application.

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

SQLCMD and Dedicated Administrator Connection

SQLCMD is DOS command-prompt utility introduced in SQL Server 2005 that supersedes isql.exe and osql.exe. Like ISQL and OSQL, SQLCMD.exe can be used to execute T-SQL statements and scripts. Unlike ISQL and OSQL, SQLCMD has extensive support for scripting and variables. SQLCMD.exe uses OLE DB to connect and execute the T-SQL batches.

You can use the -A switch with SQLCMD.exe in situations where SQL Server is not responding or is otherwise unavailable. This switch establishes a dedicated administrator connection with SQL Server 2005 and can be very helpful while troubleshooting.

DDL Triggers and Event Notifications

With SQL Server 2000, the only way to audit the activity at the server level or at the database level for DDL events (such as CREATE/DROP/ALTER TABLE) was to use SQL Profiler. However, no DBA would prefer running SQL Profiler all the time on the production server as an auditing solution.

SQL Server 2005 introduces two solutions to this problem: DDL triggers and event notifications.

Almost all RDBMSs, including SQL Server, support DML triggers. You can write a T-SQL module called a trigger that will automatically execute if an INSERT, DELETE, or UPDATE occurs on a table on which the trigger is defined. This trigger executes synchronously in the scope of the same transaction as the action that caused the trigger to fire, and if you decide to, you can roll back the entire transaction.

DDL triggers are new in SQL Server 2005. You can define a DDL trigger at the database level or at the server level. A database-level DDL trigger allows you to capture events such as CREATE/ALTER/DROP TABLE, VIEW, USER, ROLE, and other DDL statements. The server-level DDL trigger allows you to respond to events such as CREATE/ALTER/DROP LOGIN, CERTIFICATE, and other server-level DDL statements.

Much like DML triggers, DDL triggers are also created by using CREATE TRIGGER T-SQL statements, and they also execute in the same transaction context as the action that started it. You can roll back an entire transaction from the trigger, and you can nest DDL triggers. You can use the EVENTDATA() function inside a DDL trigger to access the information about the event that fired the DDL trigger. The inserted and deleted tables are not created or available inside DDL triggers.

However, in case you would like to respond to DDL events asynchronously or process them on a different or remote server, SQL Server 2005 introduces an alternative to DDL triggers: event notifications. In addition to DDL events, you can also use the event notification mechanism to respond to various profiler trace events. For instance, an event notification mechanism can be set up to notify a DBA whenever a LOCK_DEADLOCK trace event is raised, indicating a deadlock scenario.

Unlike DDL triggers, event notifications can be processed asynchronously and, if required, on a different server. The CREATE EVENT NOTIFICATION T-SQL statement is used to set up an event notification to respond to DDL or trace events or event groups. When an event notification is raised, a message is posted to a Service Broker queue. Service Broker is discussed later in this chapter and in detail in Chapter 15, "SQL Server 2005 Service Broker." Note that because an event notification is not executed in the same transaction scope as the action that raised it, the transaction cannot be rolled back. You can use the EVENTDATA() function to access the details about an event.

Database Mail

The SQL Mail functionality in SQL Server 2000 can be used to send and receive emails from within T-SQL code. However, it has few limitations:

• SQL Mail uses extended MAPI and requires the Microsoft Outlook 2000 client or later to be installed on the SQL Server machine. (Refer to Microsoft KB Articles 263556 and 281293 for more information about this.) This poses extra configuration headaches. But more than that, having an email client installed on a SQL Server box is considered a security risk.

• SQL Mail is not supported on 64-bit platforms.

• SQL Mail does not scale well.

• SQL Mail does not impose any restriction on attachment file size or attachment file extensions.

SQL Server 2005 fixes all these problems by introducing a new SMTP-based emailing solution called Database Mail. Database Mail was referred as SQLiMail in earlier SQL Server 2005 beta builds.

Database Mail does not require MAPI or Outlook to be installed; rather, it uses SMTP to send emails. Database Mail is scalable because it uses Service Broker to send emails asynchronously. The MaxFileSize and ProhibitedExtensions parameters in the msdb..sysmail_configuration table govern the attachment file size and prohibited attachment file extensions. Management Studio provides a user interface to tune these settings. Database Mail is supported on both 32-bit and 64-bit platforms. However, note that Database Mail is not a 100% replacement for SQL Mail in this release because it does not support reading emails. Chapter 5 covers Database Mail in great detail.

Management APIs: SMO, RMO, AMO, and WMI

SQL Server 2000 included two options to programmatically administering SQL Server: using COM-based SQL Distributed Management Objects (SQL-DMO) and using the Windows Management Instrumentation (WMI) API.

SQL-DMO was first introduced in SQL Server 6.5, where it was called SQLOLE; it was then renamed SQL-DMO in SQL Server 7. Enterprise Manager in SQL Server 7 and SQL Server 2000 internally uses SQL-DMO, and hence you could use SQL-DMO to automate all the functionality provided by Enterprise Manager.

SQL-DMO is still supported, but it has been deprecated in SQL Server 2005. It has not been updated to support the new features in this release. SQL-DMO is being superseded by a new .NET-based class library called SQL Server Management Objects (SMO). Administrators can use SMO to programmatically administer SQL Server 7, 2000, and 2005. SQL Server Management Studio internally uses SMO, and hence you can use SMO to automate all the functionality provided by Management Studio to manage a relational server.

In addition to providing maximum coverage of SQL Server 2005 features, the SMO API contains several other improvements over SQL-DMO. These changes include a cached object model, delayed instantiation of objects for improved scalability and performance, enhanced scripting capabilities, and improved ease of use. Further details on the SMO can be found in Chapter 5.

The replication part of the management API is now available in a separate .NET object library called Replication Management Objects (RMO). RMO allows you to programmatically configure and manage the replication architecture.

As mentioned earlier, SQL Server Management Studio internally uses the SMO API to provide server administration and management functionality. To manage Analysis Services, it uses another .NET-based object library called Analysis Management Objects (AMO). AMO is also used by the Business Intelligence Studio tool. AMO is the successor of the COM-based API called Decision Support Objects (DSO), which is available in previous releases. DSO is still available in SQL Server 2005, but AMO is the recommended API for programmatically managing Analysis Services.

WMI Overview

WMI is a component of the Microsoft Windows operating system. WMI provides an industry-standard programming interface for managing an enterprise environment. WMI is based on the Web-Based Enterprise Management (WBEM) initiative. WBEM is a set of standard technologies designed to allow administrators to use a consistent method of managing different systems in an enterprise. The standards that comprise WBEM are developed by the Distributed Management Task Force (DMTF; www.dmtf.org).

An entity managed through WMI is called a managed object. A managed object can be a hardware entity, such as memory or a disk drive, or a software entity, such as a user account or SQL Server. WMI provides a consistent way to monitor and control managed objects, regardless of the type of object. For instance, you can use WMI to monitor the amount of free space on a disk drive, to change the permissions for files on the disk drive, to manage virtual directories and permissions on an IIS Web server, and so on. You can access the WMI API from various programming languages, such as VBScript, C++, Visual Basic, and .NET Framework languages, and you can create applications and scripts to automate administrative tasks.

WMI, like SQL-DMO, is a COM API. The WMI API communicates with the WMI Windows Service, which in turn interacts with various WMI providers, such as the SQL Server Administration provider, the Active Directory provider, the NT Event Log provider, and so on. A WMI provider is a COM object that monitors a managed object for the WMI. For instance, you can write a VBScript that uses the NT Event Log WMI provider to monitor Application event log entries that are from SQL Server. For instance, as soon as SQL Server logs anything in the Application log, your VBScript can then send an email or a pager notification.

The WMI API also supports a querying language, WMI Query Language (WQL). By using WQL queries, you can request only the data of interest from the provider rather than incur the overhead of enumerating over all the objects.

WMI and SQL Server 2000

SQL Server 2000 was the first release to introduce the WMI API support. The WMI SQL Administration provider can be used to automate various administration tasks and monitor SQL Server events.

SQL Server WMI support is not installed by default. The SQL Server 2000 CD contains the setup files you need in order to use the WMI to administer SQL Server. After you have run the WMI setup, you can then run WMI scripts and applications to automate and monitor SQL Server.

WMI and SQL Server 2005

WMI support in enabled by default in SQL Server 2005, and it is further extended to support SQL Server 2005 features such as Reporting Services. SQL Server Configuration Manager internally uses the WMI API. Hence you can use WMI to automate all the functionality provided by SQL Server Configuration Manager. The WMI may seem like a complex API to use, but the new SMO .NET object library provides an object-oriented wrapper around WMI that is simpler to use. Finally, the WMI enhancements introduced in SQL Server 2005 allow you to generate SQL Server agent alerts in response to WMI events. For instance, you can write a T-SQL script to monitor WMI disk events, and if disk space on a particular drive drops below the specified size, a SQL Server Agent alert can be raised, which in turn notifies the administrator and may also start shrinking the database or log files.

WMI support in SQL Server 2005 is discussed in more detail, with examples, in Chapter 5.