Services for SQL Server 2000

Introduction

In the last couple of months I've been working primarily with NT

administrators and Visual Basic developers with regards to interfacing with SQL

Server 2000.  One of the things I've discovered on the NT side of the house

is a fundamental lack of understanding of how SQL Server 2000 runs in the

OS.  This was a topic I discovered with dismay when interviewing DBA

candidates for my current company.  Only two of a whole slew of potential

employees understood SQL Server and the services it uses, which made me

pause.  Because of the way SQL Server is installed and administered, I

wasn't terribly surprised by the DBA candidates.  I did, however, impress

upon them it was essential to understand how SQL Server ran, because that could

ultimately answer certain problems encountered in a typical production

environment.  As for the developers, I'll forgive them, because it isn't

really their job to know.  The NT administrators on the other hand... but

that's another story.

So this article will concentrate on the basic services under which SQL Server

2000 runs.  By understanding how SQL Server operates and the permissions

thereof, we can quickly narrow down on issues that will occur in a production

environment.  Also, we can help our NT administrators understand what they

shouldn't stop and/or disable when SQL Server needs to be kept running. 

Ever have a system administrator shut down the SQL Server Agent service running

backups by mistake?  Not a pretty sight!

We'll look at several things in this article:

• The Basic Services
• The Impact of Multiple Instances
• A Brief Word about Permissions

Basic Services

Looking at Server Manager (NT 4), we might see the following:

or looking at Services under Windows 2000:

We're interested in the following services:

• Distributed Transaction Coordinator
• Microsoft Search
• MSSQLServer
• MSSQLServerADHelper
• MSSQLServerOLAPService
• SQLSERVERAGENT

Under a normal SQL Server 2000 installation on a Windows 2000 Server or

Advanced Server, we'd see all but MSSQLServerOLAPService. MSSQLServerOLAPService

is not seen unless SQL Server 2000 Analysis Services is installed.  Now

that we're familiar with the names of the services, let's look at each in turn.

Distributed Transaction Coordinator

Microsoft Distributed Transaction Coordinator (MS DTC) is not a part of SQL

Server, per se.  Under Windows 2000, it is administered under Component

Services.  MS DTC serves to coordinate distributed transactions across

several servers.  Under SQL Server 2000, we would begin a distributed

transaction (provided Distributed Transaction Coordinator is installed and

running) with:

BEGIN DISTRIBUTED TRAN

or

BEGIN DISTRIBUTED

TRANSACTION

It is important to note that distributed transaction resources are not

confined to SQL Server or even to RDBMS services such as Oracle or DB2. 

Message queues are one non-RDBMS resource that can be accessed by MS DTC. 

For the most part we don't delve into MS DTC as DBAs except with respect to

failover clustering.  Failover clustering depends on MS DTC running in

clustered mode.  The Cluster Wizard (comclust.exe) can be used to configure

MS DTC to run in clustered mode.

Microsoft Search

Microsoft Search is the service which supports Full Text Indexing.  If

Full Text Indexing wasn't part of the install (it is by default on SQL Server

2000 Standard and Enterprise installations), the Microsoft Search service is

likely not present in the Services list.  The advantage we get with Full

Text Indexing is that indexes are stored on the file system and retrievals for

text searches are often faster through Microsoft Search than through SQL Server

if we're having to deal with wildcards.  Also, full text searches allow us

to do searches against forms of words which isn't possible with a standard T-SQL

query using SQL Server's native engine.  However, the draw back is that

full text indexes aren't constantly being updated.  Jobs which update full

text indexes will need to be built with time schedules reflecting how often data

is being updated. 

Finally, Microsoft Search is not available for SQL Server installations on

the Microsoft Windows 9x platforms.  It has to run as an NT service and

therefore a SQL Server running on a non-NT platform cannot install Full Text

Search.  However, Windows 9x clients can use Full Text searches if an

NT-based SQL Server has it installed.

MSSQLServer

This is the base service for a default instance of SQL Server.  We'll

talk about named instances in a bit, but with respect to default instances, the

SQLServerAgent service is dependent upon this service running.  Also, the

account under which the MSSQLServer service is where SQL Server will look for a

MAPI profile for use with SQLMail. More on this later as well.

MSSQLServerADHelper

Generally, the MSSQLServer runs as a domain user account in networked

installations.  Though it it is typical for this domain user account to be

given local Administrator rights on the server where the service is running,

this isn't enough to register SQL Server with Active Directory.  As a

result, the MSSQLServerADHelper service has been added to the mix for SQL Server

2000. Both the MSSQLServer and MSSQLServerOLAPService will use it in order to

register SQL Server engines and Analysis Services with Active Directory. 

In order to register objects with Active Directory, the account doing the

registering either needs to be in the Domain Admins group or be the localsystem

Windows account for a particular server.  Since we typically run

MSSQLServer as a domain user (which does not have Domain Admin rights), the

MSSQLServer service would be unable to register itself with Active

Directory.  The work around by Microsoft is MSSQLServerADHelper. 

Whenever MSSQLServer or MSSQLServerOLAPService needs to register an instance,

the MSSQLServerADHelper service will be started and utilized.  After

registration is done, it will be stopped.  So it is normal to see this

service set to Manual and not to be running if we were to look at the list of

Services at any given time. On a side note, regardless of how many instances of

SQL Server that may be running on a particular server, only one

MSSQLServerADHelper service is required because all instances will use it.

MSSQLServerOLAPService

Unless Analysis Services is installed on a particular server, this service

will not be present.  It is installed separately of any normal MS SQL

Server installs and as a result is not a common sight on most SQL Server

installations.  MSSQLServer is not required for Analysis Services, as it by

default uses a Microsoft Access (.mdb) database for its Repository, though the

Repository can be migrated to SQL Server.  With respect to permissions for

the service, it is important that the MSSQLServerOLAPService has sufficient

permissions to access any and all data sources with which to build and process

objects from.  It is the service's user account, not that of an Analysis

Services administrator, that is used to access the data source.

 SQLSERVERAGENT

As can be deduced by the name, this is the service for the SQL Server

Agent.  If one selects all of the default choices on a SQL Server 2000

installation, the SQL Server Agent will be setup with the exact same user

account as the MSSQLServer service.  However this is not a requirement,

though it is usual.  As SQL Mail looks for a MAPI profile under the user

account the MSSQLServer service is running under, SQLAgent Mail looks for a MAPI

profile under the user account the SQLSERVERAGENT runs under.  In addition,

any jobs which require access to network resources will run under the context of

the user account specified for SQLSERVERAGENT.  This is a significant point

when trying to diagnose why a xp_cmdshell command or DTS package runs when

manually executed and fails when run through a job. If this is the case, the

usual culprit is the user account which SQLSERVERAGENT runs under does not have

sufficient permissions to access the resources required.

Multiple Instances

Starting with SQL Server 2000, multiple instances of the SQL Server engine

can be installed on a system.  This also allows one to have SQL Server 7.0

running as the "default" instance with a SQL Server 2000 engine

running as a named instance.  Or multiple SQL Server 2000 engines can be

installed, which isn't all that unusual if one is looking to make the most of a

database cluster.  An Active/Passive means one server of a two server

cluster is essentially remaining unused.  Install a named instance and one

can go to an Active/Active configuration with a database engine running on each

physical server, thereby maximizing the use of hardware, and providing the

fail-over necessary to maintain high-availability.

With named instances, additional services for both SQL Server and SQL Server

Agent are required per instance.  How Microsoft handles this is to combine

MSSQL$ and SQLAgent$ with the name of the instance.  So if I were to have

both a default instance and a named instance called Server2, I'd see services

with the following names:

The other services related to a SQL Server engine install, MS DTC, Microsoft

Search, and the AD Helper service, stay at only one service apiece.  The

multiple instances of the SQL Server engine will use these singular services as

needed.

Permissions

I've already spoken briefly when considering each service in turn about

permissions and it's not my intent to go into a lot of detail here.  Here

are the basics:

• Each service can run as the localsystem account, a domain account, or a

  local user account (account created explicitly on the server)

• Choice of permissions determines the extent of what each service can do
• From a security perspective, give a service account only the permissions

  it needs and no more (e.g., no Domain Admin accounts under normal

  circumstances)

Here are some specifics:

• SQL Mail requires a MAPI profile.  As a result, if SQL Mail is to be

  used, a domain account is necessary.

• SQLAgent Mail also requires a MAPI profile.  It too requires a domain

  account in order to be used.

• If network resources are required such as replication, the MSSQLServer

  service requires a domain user account with sufficient privileges

• If CmdExec and ActiveScripting is required for jobs not owned by sysadmins,

  a domain user or local user account is required for SQLSERVERAGENT

• If network resources are required for jobs, SQLSERVERAGENT requires a

  domain user account with sufficient privileges

• If autorestart features are to be used, SQLSERVERAGENT must be running as

  a domain or local user account

• On-idle jobs require a domain or local user account for SQLSERVERAGENT
• MSSQLServerADHelper either needs to be the localsystem account or a domain

  account with Domain Admin privileges (the former is best)

• MSSQLServerOLAPService requires an account with sufficient privileges to

  access required data sources

Books Online (BOL) goes into a great amount of detail for the MSSQLServer and

SQLSERVERAGENT accounts, so I won't try to reproduce all the information

here.  Look in BOL for the absolute minimum permissions for these services

and also to see the pros and cons of using various accounts with varying levels

of privileges.