In Part I I covered all the various ways you can start and stop SQL Server. In this follow up I'd like to point out some additional items related to that process that you may find helpful.

Typically SQL runs as a service. What is a service? In general it's an

application that needs to be available (running) even if there is no logged in

user and has no user interface. The application provides a service to

it's users. There are some nice advantages to running your application as a

service:

• You can set it to automatically restart itself if it fails
• It understands dependencies. For example, if you shutdown SQL it will

  remind you that SQL Agent is a dependency and needs to be shutdown as well.

• You specify the credentials it runs under
• It doesn't require you to login and start the application. This is

  critical for two reasons. One is that it's not a good security practice to

  leave a server running with a user logged in and the other is that if the

  server reboots SQL will (assuming its configured to do so) automatically

  restart itself. Otherwise you'd have to wait for someone to realize the

  service was down, connect to the machine and start the service.

SQL should almost always be set to start automatically (the biggest exception

is the passive server in an active/passive cluster). You can change this in a

variety of places, the most common are Enterprise Manager (right click on

server, select Properties) and via the Services Applet (look at startup type).

Figure 1

Figure 2

Earlier I mentioned dependencies. Here you can see that SQL Agent is a

dependency of SQL Server.

Figure3

I also mentioned that you can have the application restart if it fails. In

the second image you can see where I'm changing the option for the first failure

to restart the service. In the third image I've elected to restart the computer

if the service fails a second time and that brings up a message dialog that can

be used to notify everyone on your network. These options give you a decent

amount of flexibility if and when something goes wrong.

Figure 4

Figure 5

Figure 6

Now for the gotcha. Let's say you've configured both SQL and SQL Agent to

autostart. You decide to stop the service and you get the message asking if you

want to stop SQL Agent as well - and you elect to do that. You finish whatever

work you had to do, then restart SQL - say via the services applet. Will SQL

Agent be running? No! If you don't reboot the server, you have to remember to

restart the Agent manually. This is an easy step to forget and you usually

realize it only after some mildly critical job doesn't run.

The default settings for SQL and the agent are pretty good to start with, my

only suggestion is to change the service to restart automatically if it fails.

There's still more to learn, in the next installment we'll look at checkpointing

and command line parameters.