Understanding SQL Server Services, Part I

Introduction:

Recently, I served as part of the technical interview process when my current company was seeking to hire another DBA. Part of this process included questions on the SQL Server services and I was surprised to find that very few candidates understood them. Then I thought about it afterwards and realized that with Microsoft’s easy installation method for SQL Server 7, I shouldn’t be surprised. It’s often been joked that almost anyone can install SQL Server now, at least a version that’s up and running. It’s probably because of this that many of the candidates that we interviewed didn’t have a good working understanding of SQL Server services. However, knowing how the services work and what they effect can be a key piece of any troubleshooting effort when something does go wrong. So we’ll look at the two crucial services for SQL Server, MSSQLServer and SQLServerAgent, in more detail.

This is part 1 of a 2 part series in understanding SQL Server services. In this part we’ll look at the services themselves, what permissions they require and how SQL Server 2000 changes the mix a little. In Part 2 we’ll look at some considerations on what kind of user accounts to use for our services as well as alternate means of managing them other than Enterprise Manager or Service Manager.

When SQL Server isn’t running as a service:

The Windows 95 and 98 operating systems don’t support services. As a result, SQL Server simulates running as a service, but in reality it doesn’t. Because of this, SQL Server installs on these two operating systems don’t enter into our discussion. With Windows 2000 and NT, SQL Server can be run from the command line in a non-service fashion. This is excluded from our discussion as well.

The Two Services:

SQL Server runs under the NT service MSSQLServer and SQL Server Agent runs under SQLServerAgent. Both of these services can be set to run under the following user contexts:

  • Local System Account
  • Local User Account
  • Domain User Account

MSSQLServer and SQLServerAgent do not have to be run under the same user context. Usually they do, but there may be some reasons why we wouldn’t do so. A more thorough discussion of how to select our user contexts will come in Part 2. For now, let’s look at these three options in greater detail.

The Local System Account is an account that exists only on the machine that SQL Server is installed upon. It has administrative rights and can run as a service, thereby knocking out everything a stand-alone SQL Server install needs or wants. Because it is a local account, that account won’t have privileges anywhere else on the network.

A Local User Account is different in that it is an account specifically created by an administrator on the machine for SQL Server. It may not be part of the local Administrator group. Neither SQL Server nor SQL Server Agent requires administrative rights just to run. There is additional functionality that won’t be possible unless the two are running in a user context with administrative rights, but just to run, that’s not an absolute requirement. Like a Local System Account, the Local User Account won’t have privileges elsewhere on the network.

The Domain User Account is much like the Local User Account in that an administrator defines what permissions it has to the machine for SQL Server. It doesn’t have to be part of the local Administrator group, just like the Local User Account. Both require the following permissions:

  • Ability to log on as a service
  • Ability to access and change the MSSQL7 directory
  • Ability to access and change applicable .mdf, .ndf, and .ldf files
  • Ability to read and write to certain registry keys (see Books Online for the list)

Microsoft recommends a Domain User Account that is part of the local Administrators group. We’ll discuss in part 2 why this is usually a good idea.

SQL Server 2000:

SQL Server 2000 allows us to run multiple instances of itself all on the same machine. So if we’ve got the processing power and memory capacity to run 64 different instances of SQL Server 2000, we could, if we really wanted to. It’ll also run alongside an existing SQL Server 7 instance. So obviously if there are multiple instances, we can’t just be using two services. SQL Server handles multiple instances with a naming scheme that should keep the SQL Servers separate and running under different services. Here’s how it works:

Service Default Instance Named Instance
MS SQL Server MSSQLServer MSSQ$InstanceName
SQLServerAgent SQLServerAgent SQLAgent$InstanceName

In cases where we have multiple instances, we’ve had to give unique names to each instance. Let’s say we’ve decided to run 2 instances, appropriately named SQL1 and SQL2. Our services are then:

Service Named Instance: SQL1 Named Instance: SQL2
MS SQL Server MSSQ$SQL1 MSSQ$SQL2
SQL Server Agent SQLAgent$SQL1 SQLAgent$SQL2

If we’re running a SQL Server 2000 instance alongside a SQL Server 7 instance, the SQL Server 7 instance will keep MSSQLServer and SQLServerAgent. I’ve got the two running at the same time on my development platform. I’ve named the SQL Server 2000 instance (even though there’s only one SQL Server 2000 instance, because it’s running with a SQL Server 7 instance and can’t use the default instance services, it’s required to be named) SQL2K. So when I look at the services, I see the following:

Service SQL Server 7 SQL Server 2000
MS SQL Server MSSQLServer MSSQ$SQL2K
SQL Server Agent SQLServerAgent SQLAgent$SQL2K

It’s a pretty straight forward naming scheme so there isn’t a great deal of adjustment for the multiple instances possible with SQL Server 2000.

Concluding Remarks:

We’ve taken a brief look at the MSSQLServer and SQLServerAgent services, a familiarization tour, if you will. We’ve looked at the three options available for user contexts as well as pointed out that Microsoft recommends a domain user account with administrative rights. Finally, we’ve taken a look at how SQL Server 2000 handles multiple instances. In the next part, we’ll explore the considerations we need to take into account when defining our user contexts, what the various contexts and permissions might affect, as well as look as alternate methods to managing our SQL Server services.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles