Chapter 4: Connecting to and Administering the Database Engine

The responsibilities of the SQL Server database administrator (DBA) have changed a lot in recent years. As popular as this DBMS is now, its DBA of the not-too-distant past was not your typical data center resident guru. After all, it was not too long ago that a gigabyte of memory in a Windows server was as wild an idea as growing asparagus on Mars.

Prior to version 7.0, SQL Server was outclassed in many high-end services, such as mission-critical support for huge numbers of concurrent users, very large databases (VLDBs), and a service level supporting tens of thousands of transactions per minute. The mainframe and midrange DBAs had it all-and nothing could challenge their eminence. Or so they thought.

This chapter highlights the administrative functionality that ships with SQL Server 2005, as well as the APIs and architectures that provide all the necessary hooks that can be used to directly program its maintenance and administrative services.

The Server-Side Components

A very powerful collection of server components makes up the so-called “back end” of SQL Server 2005. The collection consists of the following components:

• Database Engine   Implemented in the MSSQLServer service

• SQL Server Agent   Implemented in the SQL Server Agent service

• Full-Text Search   Implemented as the SQL Server FullText Search

• Microsoft Distributed Transaction Coordinator (MS DTC or just DTC)   Implemented as the Distributed Transaction Coordinator service

The SQL Server FullText Search (formerly Microsoft Search) service is not available on any client or workstation operating system. SQL Server 2005, like its predecessors, can also be installed in multiple instances, even on the same host Windows Server 2003 computers. Each instance is controlled by its own set of services; however, the DTC (overview coming up) is shared among all the instances on the same host. You can also run different versions of SQL Server on the same server. Working with multiple instances and different versions is not further covered in this book.

The Server components can be started and stopped in a number of ways:

• On startup of the operating system, determined by the Service Control Manager (SCM) and cluster resources on failover nodes.

• From the command line or command prompt using the net commands or directly executing the service executable.

Let’s now look briefly at the four server components that, combined, make up the SQL Server 2005 DBMS.

The Database Engine

As we have discussed in the first three chapters, the Microsoft SQL Server 2005 database engine, MSSQLServer, manages the databases and all related files and mechanisms that its instance owns on the host server. It is also responsible for processing all the T-SQL statements sent to it by client applications and processes. It also controls the execution of SQL Server components (the objects) installed in the database, such as stored procedures, triggers, CLR-hosted stuff, and the various integrity mechanisms we will be discussing in forthcoming chapters.

The SQL Server Database Engine is the DBMS. It is the only engine on the server that creates and manages the databases, juggles concurrent connections, enforces security, processes queries, builds and applies indexes, and so on. The database engine, which we will refer to as the DBMS from here on, is the largest and most important SQL Server entity The collateral services such as Analysis Services and the reporting components are beyond the scope of this book.

SQL Server Agent

SQL Server Agent has been designed to make the job of the DBA a lot easier, lowering TCO and the administrative burden. It is essentially a service that is used to schedule maintenance routines and activities on a target server. It is also used to monitor DBMS activities and alert operators or the DBAs, to potential threats and problems. The SQL Server Agent service is accessed and managed in Management Studio and is divided into the following three services. This architecture has been similarly implemented in a number of Windows services, such as the Removable Storage Services:

• Alerts   These are the actions that need to be taken when a specific event occurs on the server. Errors are-good examples of alerts that a DBA or operator needs to be made aware of. For example, errors generated by incorrect logins or security problems can be sent to a DBA via e-mail, paging or beeper services, the Windows messenger service, or a telephone call. The alert mechanism can also be configured to run certain jobs.

• Operators   The Operators service lets you define operators using their domain accounts or e-mail IDs. These people are typically members of a “database administrators group” defined in a Windows domain. You can send messages to all operators using a single e-mail message that gets sent to a group.

• Jobs   The jobs are the collections of steps that get executed by the Agent service. Jobs are defined in the service and then scheduled for execution at a later time or in response to an alert or event.

In addition to the Agent services in Management Studio, you can interface with the SQL Server Agent from applications that use SQL Server Management Objects architecture (SQL-SMO) and from any SQL Server client application that is programmed to transmit T-SQL statements to the server. We will investigate SQL Server Agent and its components later in this chapter.

Full-Text Search

Microsoft Full-Text Search extends the SQL-92 and SQL-2003 definitions for standard character-based searching, which is based on a system of comparison operators (equal to, less than, and greater than) applied to character constants and character strings stored in the database.

The engine, first, supports a powerful indexing model, which is implemented as full-text catalogs and indexes that you define for a database. These are not stored as objects in a SQL Server database but rather as independent files. Second, the engine processes full-text queries and determines which entries in the index meet the full-text selection criteria obtained from user input. The criteria can be words or phrases, words that are within a certain proximity to each other, or the inflections on verbs and nouns.

The search algorithm works as follows: for each search criterion processed by the engine, the identity of a row that meets the criterion and its ranking value is returned to the DBMS, which then builds the result set to return to the client.

The full-text catalogs and indexes are stored on the server in separate files, which are administered by the search service.

The Distributed Transaction Coordinator

The MS DTC is a service that lets you program applications that can work with data drawn from several different data sources in one transaction. It also coordinates the updating of data to different databases, even databases that are installed in remote servers. The DTC is a key component of Microsoft’s maximum availability strategy in that it ensures that an UPDATE succeeds on all distributed servers or on none of the servers. We use the DTC when users update data in distributed partitioned views on a federation of SQL Servers.

You can easily enlist the services of DTC in your applications by calling remote stored procedures from your application that apply to all “enlisted” servers. The DTC service can be managed from Management Studio and the SQL Server Configuration Manager. We will return to the DTC in Chapter 9, as part of cluster installation.

SQL Server Logs and Activities

SQL Server logs a huge number of events and activities that you can use for troubleshooting and management from a variety of components. You use the Log File Viewer to display the log files. With the Log File Viewer open, you work within the Select Logs pane to select the logs you wish to display. We will discuss the Log File Viewer later in this chapter.