The Collateral Services

This very powerful collection of server components (collateral services is my term) makes up the “surrounding technology” of the SQL Server 2005 database engine. The collection consists of the following components:

• SQL Server Agent   The job scheduler and operator manager.

• Full-Text Search   The indexer for keyword-based queries of text data in SQL Server databases.

• Microsoft Distributed Transaction Coordinator   The service for facilitating distributed transactions.

• SQL Server Analysis Services (SSAS)   The technologies used for online analytical processing (OLAP) and data mining.

• SQL Server Integration Services (SSIS)   The technologies used for extraction, transformation, and loading (ETL). It replaces the SQL Server 2000 Data Transformation Service.

• SQL Server Notification Services (SSNS)   The technologies used for developing and deploying applications that generate and send messages to SQL Server subscribers.

• SQL Server Reporting Services (SSRS)   The technologies used for developing and deploying reports.

• Service Broker   The technologies used for the interapplication messaging service that can be used by developers for loosely coupled applications.

• Replication   The technologies used for replicating data from one database server to another.

We are going to be bumping into all these technologies and services throughout this book; however, as mentioned in the introduction, extensive coverage of SSAS, SSIS, and SSRS is not possible.

SQL Server 2005 can also be installed in multiple instances, even on the same host. Each instance is controlled by its own set of services; however, the Distributed Transaction Coordinator (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.

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

• On startup of the operating system, as determined by the Service Control Manager (SCM)

• Via the services of the SQL Server Configuration Manager. The SQL Server Service Manager provides the same services as the SCM, dedicated to the SQL Server services

• From the command line or command prompt using the net commands

SQL Server Agent

The 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 Server Management Studio-and fully investigated in Chapter 4.

The SQL Server Agent 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 the Active Directory 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. Jobs can comprise packages built for SQL Server Integration Services or SSIS (successor to DTS) and include maintenance plans for backing up databases and performance management.

In addition to the SQL Server Agent service, you can interface with SQL Server Agent from applications that use SQL Management Objects architecture (SQL-SMO), and from any SQL Server client application that is programmed to transmit T-SQL statements to the server engine.

Full-Text Search

Full-Text Search extends the SQL-92 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 in separate files on the server, which are administered by the search service. Coverage of Full-Text Search will be limited to the section Table Design Considerations for Searchable Databases covered in Chapter 6.

The Microsoft Distributed Transaction Coordinator

The Microsoft Distributed Transaction Coordinator (MSDTC or just 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. DTC services are discussed in Chapter 16. DTC is an essential component of a Web services data-access infrastructure.

The Analysis Services

SQL Server was originally conceived as an online transaction processing (OLTP) system or DBMS, built around the need to store large amounts of records, operational data, or transaction data culled from the concurrent transactions of a larger number of human users and computer processes. It has excelled at this capability, and this is what makes it ideal not only for the data storage needs of large enterprises and public institutions but for those of small-to medium-sized businesses as well. (See Chapter 17 on concurrency and transactions.)

However, operational data and the storage and retrieval thereof are only a part of what companies need in a DBMS. The ability to query data, and generate any type of business intelligence or management report, is one of the cornerstones of a relational database application, hence the invention of a query language like SQL.

The ubiquitous SELECT statement allows us to obtain information from our data and allows us to drill into the data to obtain information that could assist with decisions, and with business rules and direction.

An example: The statement SELECT * FROM CUSTOMERS WHERE CREDIT EQUALS ‘GREEN’ lets a marketing team safely derive a new list from the customer base list to which additional product offerings can be made. Marketing people will have a measure of sales confidence using the list because they know that any new customers garnered from their marketing efforts will be able to pay for the goods and thus generate cash flow.

This example is very simple because SQL SELECT statements can be vastly more sophisticated, complex, and deep. The more advanced and sophisticated the query, the more demanding it is to make it against a huge database holding thousands, millions, even billions of items. Aggregate queries, for example, are extremely taxing on huge databases. Reports might typically take days to generate. For the most part, therefore, relational data and relational (normalized) tables do not make effective analytical data storage facilities, but the specialized warehousing facilities offered by the Analysis Services do.

Apart from the strain, a more scientific analysis of data is needed to support the decision-making processes of enterprises, fields of research and endeavor, and even the whims of individuals. Humans have the uncanny habit of going around in circles, always making the same mistakes, never breaking out of the cycle. And they do this with their companies. It takes a lot of effort to make changes…and we need to be convinced by hard facts.

SQL Server’s analysis services were introduced to the product back in version 7.0. Known as OLAP, these features are used to support decision support systems (DSSs) that can, for example, help a company to position itself in new and emerging markets, and to use data to detect shifts in market perception, buying trends, consumer habits, opinion, corporate health, and much more.

OLAP data is organized into multidimensional structures known as CUBES, and the analysis data is stored in data structures known as data warehouses and data marts. OLAP services are supported by a number of SQL Server components that form the Analysis Services.

The Analysis Services and the transformation of data for analysis make up a highly complex subject that is obviously beyond the scope of this book. Preparing OLTP data for the Analysis Services is covered in Chapter 17. The Analysis Services architecture is a major and critical component of SQL Server’s collateral services architecture and not of the core database engine, which is why it is described as a Relational Database Management and Analysis System, and why I call it the “DBMS for everyone and everything.” (For a comprehensive treatment of Analysis Services and Business Intelligence, see Delivering Business Intelligence with Microsoft SQL Server 2005 [McGraw-Hill/Osborne, 2005] by Brian Larson.)

I hope you are ready to tackle the complex material that lies in the chapters ahead. I also know that you are keen to see SQL Server 2005 in action and to get a feel for what it is capable of. You will be seeing code in this book, most of it in Part III. The chapters remaining in Part I will help you understand how SQL Server works, how to assess your needs, and how to install and deploy the product.