Systems Management

The difficulty of systems management is probably the single biggest obstacle that has inhibited 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 7, Enterprise Manager is a snap-in to Microsoft Management Console (MMC). MMC is 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 7 CD for use with MMC is the Microsoft SQL Server OLAP Services snap-in.)

Easy to use, SQL Server Enterprise Manager supports centralized management of all aspects of multiple installations of SQL Server, including managing security, events, alerts, scheduling, backup, server configuration, tuning, and replication. SQL Server Enterprise Manager allows SQL Server database schemas and objects such as tables, views, and triggers to be created, modified, and copied . 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 Workstation or Windows NT Server in the environment. SQL Server Enterprise Manager also runs on Windows 95 and Windows 98, although a few capabilities aren't available in these environments (most notably the ability to use Service Control Manager, a feature of Windows NT, 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, Windows 95, and Windows 98 for administration and management.

SQL Server Enterprise Manager provides an easy-to-use interface, as shown in Figure 2-2. You can perform even complex tasks with just a few mouse clicks.

Figure 2-2. Easy-to-use interface for SQL Server Enterprise Manager.

SQL Server Enterprise Manager relieves you from having to know the specific steps and syntax to complete a job. SQL Server provides over 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

In the Windows 95, Windows 98, and Windows NT operating systems, Microsoft SQL Server Distributed Management Objects (SQL-DMO) provides 32-bit Automation (formerly known as OLE Automation). These objects, properties, methods , and collections are used to write scripts and programs that can 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 in the form of objects, methods, and properties. The SQL-DMO model simplifies the management surface of SQL Server by organizing management functions in terms of the SQL Server object model. The primary object is SQLServer, which contains a collection of Database objects. The Database object contains a collection of Table, View, and StoredProcedure objects. Objects contain properties ( SQLServer. Name = "MARKETING_SVR" ) and 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.
Database.Dump	Performs a database dump.
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, a subset of which is shown in Figure 2-4 , greatly reduces the task of learning and fully using SQL Server management components .

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

Figure 2-4. The SQL-DMO model.

Automation and Visual Basic Scripting

The power of using an ActiveX interface (Automation) for SQL Server management becomes clear when you consider the potential of using a robust 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 powerful. (Traditionally, programming such a task would have required several pages of much more complex C code.)

 Dim MyServer as New SQLServer    ' Declare the SQL Server object  MyServer.Name = "MARKETING_SVR"  MyServer.Login = "sa"  MyServer.Connect        ' Connect to the SQL Server  ' List the name and space available for all databases  For Each MyDB In MyServer.Databases          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 designed to support 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_based service that can be started when Windows NT starts. It can also be controlled and configured 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 Windows NT 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 the Windows NT application log and job history table in SQL Server and optionally sends e-mail to the designated administrator reporting the job status.

In SQL Server 7, scheduled jobs can be made up of multiple steps, and each step defines the action to take on success and the action to take on failure. Thus, you can control the execution of subsequent steps when a step fails.

SQL Server 7 allows you to define a server as a master server, for the purpose of defining 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, and 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 Windows NT application log. The application log then notifies SQL Server Agent that 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 administrator or administrators can be alerted via e-mail, pager, or with a net send message. Or a SQL Server Agent task 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 are 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 administrators 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 Windows NT Performance Monitor thresholds being crossed. Normally, crossing a threshold isn't considered an error, and no event is written to the Windows NT application log. However, the alert system in SQL Server 7 is integrated with the performance counters available through the Windows NT Performance 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 above: the administrator or administrators can be alerted via e-mail, pager, or with a net send message. Or a SQL Server Agent task 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 the Windows NT Performance Monitor can detect the condition of a database file reaching a certain percentage of its capacity, an alert can be raised. Figure 2-5 shows the definition of an alert based on a Performance Monitor threshold. This alert will be fired when the log file for the Northwind database exceeds 80 percent of its capacity.

Figure 2-5. Alert definition for an 80 percent full condition in the Northwind database.