The SQL Server Enterprise database comes with many components to solve your business needs.
- Database Services – core relational database engine that executes TSQL
- SQL Server Agent – monitors database engine, fire alerts and schedules/executes jobs
- Integration Services – stores and executes ETL packages
- Reporting Services – manages, executes, and delivers reports
- Analysis Services – provides analytical processing and data mining
- SQL Server Browser – supports instance name resolution for clients
All these components have one thing in common!
They are all windows services that can be stopped, paused, and/or started when a DBA needs to perform a task.
There are at least two ways to manage a service.
One way to manage the services is to use the NET STOP and NET START commands. These commands need to be executed from a command shell in adminstrative mode. The most compilicated part of this exercise is to know the identity of default/named instances. This identity goes between the () in the commands below.
Stop windows services on default instance (stop-sql-svcs.cmd).
NET STOP "SQL Server Browser"
NET STOP "SQL Server Agent (MSSQLSERVER)"
NET STOP "SQL Server Integration Services 10.0"
NET STOP "SQL Server Analysis Services (MSSQLSERVER)"
NET STOP "SQL Server Reporting Services (MSSQLSERVER)"
Start windows services on default instance (start-sql-svcs.cmd).
NET START "SQL Server (MSSQLSERVER)"
NET START "SQL Server Browser"
NET START "SQL Server Integration Services 10.0"
NET START "SQL Server Analysis Services (MSSQLSERVER)"
NET START "SQL Server Reporting Services (MSSQLSERVER)"
Another way to manage the services is to use the SQL Server Configuration Utility. This is a more powerful tool since all service account definitions can be specified as well as start up options. Never use the Services management console! It will not set up the encryption certification like the configuration utility.
The image below shows all services stopped.
The image below shows all services started.
In short, all SQL Server components; database engine, server agent, integration services, analysis services, and reporting services; can be managed from either a administrator command shell or the SQL Server configuration utility. As a database administrator, you will be using this technique often when you perform critical tasks such as changing the location of system files or recovering a failed system database.