Understanding and Configuring Microsoft SQL Server Services

아티클
1/17/2024

Introduction

In this wiki, I will discuss how to configure the SQL Server Services.

What is a Windows Service?

"On Microsoft Windows operating systems, a Windows service is a long-running executable that performs specific functions and it is designed not to require user intervention. Windows services can be configured to start when the operating system is booted and run in the background as long as Windows is running, or they can be started manually when required. They are similar in concept to a Unix daemon. Many appear in the processes list in the Windows Task Manager, most often with a username of SYSTEM, LOCAL SERVICE or NETWORK SERVICE, though not all processes with the SYSTEM username are services. The remaining services run through svchost.exe as DLLs loaded into memory." - Wikipedia

How to See Services Running on Your Machine?

To see all the services running on your machine you can click on start from windows and then type run and then type services.msc as shown:

Understanding and Configuring Service Account

As mentioned above a service can run under the following accounts.
a. System or Local System Account
b. Local Services
c. Network Services
d. Domain or NT user account

Local System Account

When a SQL services runs on "System account or Local system account" it means it has full access to the entire
system resource on the computer which it is running.
The Local System account is a powerful account that has full access to the computer. The actual name of the
account is NT AUTHORITY\System. The Local System account is powerful only to the machine which it is running
however it does not have any rights to access the network. It however represent itself as a computer when it access
the network.

Local Service Account

This account has minimum privileges on the local computer and presents anonymous credentials on the network.
the actual name of this account is NT AUTHORITY\LocalService

Network Services Account

This account has minimum privileges on the local computer and acts as the computer on the network.
The actual name of the account is NT AUTHORITY\NetworkService, and it does not have a password
that an administrator needs to manage.

Domain Account\NT Account

For enterprise user, It is a best practice to have a dedicated Active directory account/NT account for SQL
server services. The purpose of this is that SQL server may need to access several resources on the local
computer and on the network. For example you may need to do a backup on a SAN or Shared Drive.
You will need a Domain account for the service to do this.

The NT domain account must not be owned by any employees on the enterprise because if that employee
resigned you will have no access to the network resources and your services will not work.

The SQL Server Configuration Manager

Although you can manage the SQL services by running the windows service (services.msc) mmc plugin
You can administer it much easily with the the SQL Server Configuration Manager shown below.
You can run it from the windows start as shown or you can type "SQLServerManager11.msc" from the
command line.

* Figure 2. The SQL Server Configuration Manager*

Understanding SQL Server Service

SQL server uses several services to operate. I will be discussing them here one by one.

SQL Server Browser Services

SQL Server Browser runs as a Windows service on the server. SQL Server Browser listens for incoming requests
for SQL Server resources and provides information about SQL Server instances that are installed on the computer.
SQL Server Browser contributes to three actions:

    Browsing a list of available servers
    Connecting to the correct server instance
    Connecting to Dedicated Administrator Connection (DAC) endpoints

Scenarios For SQL Server Browser Service

A. You Do Not Need Sql Server Browser Service When:

1. You have a default instance only installation you will not need Browser Service.
    2. You have different instances of SQL Server and you want to the user to specify ports when connecting
         to the server
    3. You need to hide your SQL Server instance

B. You Need SQL Server Browser Service When:

    1. You have different instances of SQL Server and you don't want to have user to specify ports when connecting
    2. You have Sql server server assigned to dynamic ports

Reminder for Configuring SQL Browser Service.

There is only one SQL Browser Service in a box. This is not available for a per instance configuration. Do not Configure
browser services with local system.

다음을 통해 공유