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.
