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;
— To update the currently configured value for advanced options.
— To enable the feature.
EXEC Sp_configure ‘xp_cmdshell’, 1;
— To update the currently configured value for this feature.
2) Create the stored procedure to execute ‘xp_cmdshell’ to start SQL Server Agent
CREATE PROCEDURE Autoagent
‘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