Systems Management

The difficulty of systems management is probably the single biggest obstacle to mass deployment of client/server solutions. Far from being a downsized version of the mainframe, today's distributed client/server system can be deployed on dozens or even hundreds of distributed servers, all of which must be controlled to the same exacting standards as mainframe production software systems. The issues here reside both inside and outside the database environment. SQL Server provides a comprehensive architecture and tools for managing the database and related activities.

SQL Server Enterprise Manager

SQL Server Enterprise Manager is a major advancement in making client/server deployments manageable. In SQL Server 2000, SQL Server Enterprise Manager is a snap-in to Microsoft Management Console (MMC), a tool that provides a common interface for managing various server applications in a Windows network. (Another snap-in that's provided on your SQL Server 2000 CD for use with MMC is the SQL Server OLAP Services snap-in.)

The easy-to-use SQL Server Enterprise Manager supports centralized management of all aspects of multiple installations of SQL Server, including security, events, alerts, scheduling, backup, server configuration, tuning, and replication. SQL Server Enterprise Manager allows you to create, modify, and copy SQL Server database schemas and objects such as tables, views, and triggers. Because multiple installations of SQL Server can be organized into groups and treated as a unit, SQL Server Enterprise Manager can manage hundreds of servers simultaneously.

Although it can run on the same computer as the SQL Server engine, SQL Server Enterprise Manager offers the same management capabilities while running on any Windows NT/2000-based machine. SQL Server Enterprise Manager also runs on Windows 98, although a few capabilities aren't available in this environment (most notably the ability to use the Service Control Manager, a feature of Windows NT/2000, to remotely start and stop SQL Server). In addition, the efficient client/server architecture of SQL Server makes it practical to use the remote access (dial-up networking) capabilities of Windows NT/2000 as well as Windows 98 for administration and management.

Enterprise Manager's easy-to-use interface is shown in Figure 2-2. You can perform even complex tasks with just a few mouse clicks.

Figure 2-2. The SQL Server Enterprise Manager interface.

SQL Server Enterprise Manager relieves you from having to know the specific steps and syntax to complete a job. It provides more than 20 wizards to guide you through the process of setting up and maintaining your installation of SQL Server. A partial list is shown in Figure 2-3.

Figure 2-3. Some of the wizards available for setting up and maintaining your server.

Distributed Management Objects

Microsoft SQL Server Distributed Management Objects (SQL-DMO) is the SQL Server Automation object model. You can use the SQL-DMO objects, properties, methods, and collections to write scripts and programs that administer multiple servers running SQL Server distributed across a network. SQL Server Enterprise Manager is built entirely with SQL-DMO. You can customize your own specific management needs using SQL-DMO, or you can integrate management of SQL Server into other tools you use or provide.

All SQL Server functions are exposed through the SQL-DMO object model. The primary object is SQLServer, which contains a collection of Database objects. Each Database object contains a collection of Table, View, and StoredProcedure objects. You can control SQL Server by changing SQL-DMO object properties (SQLServer.Name = "MARKETING_SVR") and by invoking SQL-DMO object methods (SQLServer.Start or SQLServer.Shutdown).

Table 2-1 provides some examples of SQL-DMO objects and methods.

Table 2-1.SQL-DMO objects and methods.

Object.Method	Action
SQLServer.Shutdown	Stops SQL Server.
SQLServer.Start	Starts SQL Server.
Index.UpdateStatistics	Updates optimizer information for indexes.
Database.Tables.Add	Adds a table to a database.

The SQL-DMO object model is comprehensive, consisting of dozens of distinct objects and hundreds of Component Object Model (COM) interfaces. The organization of these objects greatly simplifies the task of learning and fully using SQL Server management components.

Any Automation-controlling application can harness the power and services of SQL-DMO. Probably the most common such Automation controller is Microsoft Visual Basic.

Windows Management Instrumentation

The SQL Server 2000 CD contains a technology preview of a new API for supporting your SQL Server installations called Windows Management Instrumentation (WMI). WMI, which is similar to SQL-DMO and in fact maps directly to SQL-DMO methods and objects, lets you access and manipulate not only your SQL Server objects but also other components in your Windows network. These other components can include servers, printers, registry keys, performance monitor counters (system monitor counters in Windows 2000), and events from Windows NT/2000's application log, system log, and security log.

WMI is not installed by the SQL Server 2000 installation program but is available in the \x86\other\wmi folder on the same CD. Unlike SQL-DMO, the WMI components are installed on the server instead of on the client machines. In future releases, WMI will communicate directly with SQL Server rather than going through an SQL-DMO layer.

SQL-DMO and Visual Basic Scripting

The power of using the SQL-DMO object model for SQL Server management becomes clear when you consider the potential of using a rapid application development (RAD) language such as Visual Basic as a scripting environment for administrative tasks. The following sample code lists the name of and space available on all databases on a server. This code is simple, compact, and easy to write and read, yet it's powerful. (Traditionally, programming such a task would have required several pages of much more complex C code.)

 Dim MyServer As New SQLServer    ' Declare the SQLServer object Dim MyDB As Database MyServer.Name = "MARKETING_SVR" MyServer.Login = "sa" MyServer.Password = "password"  MyServer.Connect           ' Connect to the SQL Server ' List the name and space available for all databases For Each MyDB In MyServer.Databases         Debug.Print MyDB.Name, MyDB.SpaceAvailable Next MyDB MyServer.DisConnect        ' Disconnect 

SQL Server Agent

SQL Server Agent is an active, intelligent agent that plays an integral role in the management of the SQL Server environment. It provides a full-function scheduling engine that supports regular tasks for the management and administration of SQL Server, and it allows you to schedule your own jobs and programs.

SQL Server Agent is a Windows NT/2000-based service that can be started when the operating system starts. You can also control and configure it from within SQL Server Enterprise Manager or SQL Server Service Manager. SQL Server Agent is entirely driven by entries in SQL Server tables that act as its control block. Clients never directly communicate with or connect to SQL Server Agent to add or modify scheduled jobs. Instead, they simply make the appropriate entries in the SQL Server tables (although this typically occurs through SQL Server Enterprise Manager via a simple dialog box that's similar to a typical calendar program). At startup, SQL Server Agent connects to an installation of SQL Server that contains its job table and then loads the list of jobs, the steps that make up each job, and the possible alerts that can be fired.

SQL Server Agent, like the SQL Server engine, is a single multithreaded process. It runs in its own process space and manages the creation of operating system threads to execute scheduled jobs. Its discrete managed subsystems (for replication, task management, and event alerting) are responsible for all aspects of processing specific jobs. When jobs are completed (successfully or not), the subsystem returns a result status (with optional messages) to SQL Server Agent. SQL Server Agent then records the completion status in both the operating system's application log and the job history table in SQL Server and optionally sends an e-mail report of the job status to the designated operator.

In SQL Server 2000, scheduled jobs can be made up of multiple steps; each step defines the action to take if it succeeds and the action to take if it fails. Thus, you can control the execution of subsequent steps when a step fails.

SQL Server 2000 allows you to define a server as a master server for the purpose of scheduling jobs that will be carried out by one or more target servers. These target servers periodically poll their master server to determine whether any new jobs have been defined. If so, the master server downloads the job definitions to the target server. After the jobs are run on the target server, the success or failure status is reported back to the master server. The master server keeps the complete job history for all its target servers.

The event/alert subsystem gives SQL Server its ability to support proactive management. The primary role of the event/alert subsystem is to respond to events by raising alerts and invoking responses. As triggering activities (or user-defined activities) occur in the system, an event is posted to the operating system application log. SQL Server Agent monitors the application log to detect when an event has occurred.

SQL Server Agent determines whether any alerts have been defined for this event by examining the event's error number, severity, database of origin, and message text. If an alert has been defined (in the alert table), the operator(s) can be alerted via e-mail or pager or with a net send message. Alternatively, a SQL Server Agent job can be invoked when the alert is fired and can take corrective action. (For example, SQL Server Agent might automatically expand a full database.)

If no alerts have been defined locally, the event can be forwarded to another server for processing. This feature allows groups of servers to be monitored centrally so that alerts and operators can be defined once and then applied to multiple servers. Beyond the database environment, Microsoft Systems Management Server (SMS)—a BackOffice component—is available to provide key services to manage the overall software configuration of all the desktops and servers in the environment.

Alerts can also be fired in response to System Monitor (or Performance Monitor) thresholds being crossed. Normally, the crossing of a threshold isn't considered an error, and no event is written to the application log. However, the alert system in SQL Server 2000 is integrated with the performance counters available through System Monitor (discussed later in this chapter), and SQL Server can take action when a counter rises above or falls below a specified value. The kinds of actions possible are the same actions described previously: the operator(s) can be alerted via e-mail or pager or with a net send message. Alternatively, a SQL Server Agent job can be invoked to take corrective action when the alert is fired.

For example, SQL Server Agent might automatically expand a database that's almost full. Note that being almost full is technically not an error: no error number or severity level is associated with this situation. However, because Performance Monitor can detect the condition of a database file reaching a certain percentage of its capacity, an alert can be raised. Figure 2-4 shows the definition of an alert based on a Performance Monitor threshold. This alert is fired when the log file for the Northwind database exceeds 80 percent of its capacity.

Figure 2-4. Alert definition for a greater-than-80-percent-full condition in the Northwind database log file.