Simple Talk is now part of the Redgate Community hub - find out why

Auto Starting SQL Server Agent

I faced this problem some years ago. The company I was working for had a lot of clients with very low-quality communications, so each client needed its own local server and BI solutions. A very small DBA team had the task to deal remotely with hundreds of SQL Servers across low-quality communication links and sometimes, during an emergency and after restarting SQL Server service, someone forgot to start SQL Server Agent again.

The problem used to be noticed when some manager or director in the client company perceived outdated BI dashboards, becoming a severe trouble. It was a human error and the company was looking for a way to avoid it.

When we stop SQL Server service, SQL Server Agent service also stops, but when we start SQL Server again, SQL Server Agent won’t start together and the existing configurations weren’t enough to solve this:

  • As a service, we can configure recovery actions for SQL Server Agent, but they are all for errors, this situation isn’t an error.
  • We can configure SQL Server Agent to start automatically if it stops unexpectedly, however this will not help either.

The solution: Configure an auto-start procedure in SQL Server to start SQL Server Agent service, this way making the service start every time SQL Server service starts.

This solution depends on enabling xp_cmdshell stored procedure. This is always seen as a risk for security, however, only sysadmins will have access to this procedure, so if you avoid the most the use of logins with sysadmin permissions and follow the best security practices with SQL Server service account, you may have no problem.

 

1) Enabling ‘xp_cmdshell’

— To allow advanced options to be changed. 
EXEC Sp_configure ‘show advanced options’, 1;
go
— To update the currently configured value for advanced options. 
RECONFIGURE;
go
— To enable the feature. 
EXEC Sp_configure ‘xp_cmdshell’, 1;
go
— To update the currently configured value for this feature. 
RECONFIGURE;
go 

2) Create the stored procedure to execute ‘xp_cmdshell’ to start SQL Server Agent

CREATE PROCEDURE Autoagent
AS
    EXEC Xp_cmdshell
      ‘NET START SQLSERVERAGENT’ 

3) Configure the stored procedure as an auto-start stored procedure. It will be executed every time SQL Server Service starts

EXEC Sp_procoption autoagent, startup, true 

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue