{"id":71851,"date":"2017-07-17T20:02:33","date_gmt":"2017-07-17T20:02:33","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=71851"},"modified":"2021-08-24T13:39:23","modified_gmt":"2021-08-24T13:39:23","slug":"auto-starting-sql-server-agent","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/auto-starting-sql-server-agent\/","title":{"rendered":"Auto Starting SQL Server Agent"},"content":{"rendered":"<p>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.<\/p>\n<p>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.<\/p>\n<p>When we stop SQL Server service, SQL Server Agent service also stops, but when we start SQL Server again, SQL Server Agent won&#8217;t start together and the existing configurations weren&#8217;t enough to solve this:<\/p>\n<ul>\n<li>As a service, we can configure recovery actions for SQL Server Agent, but they are all for errors, this situation isn&#8217;t an error.<\/li>\n<li>We can configure SQL Server Agent to start automatically if it stops unexpectedly, however this will not help either.<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>This solution depends on enabling <strong>xp_cmdshell<\/strong> 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.<\/p>\n<p>&nbsp;<\/p>\n<p>1) Enabling &#8216;xp_cmdshell&#8217;<\/p>\n<blockquote>\n<div><span style=\"font-family: Courier New;font-size: 10pt\"> <span style=\"color: green;font-style: italic\">&#8212;\u00a0To\u00a0allow\u00a0advanced\u00a0options\u00a0to\u00a0be\u00a0changed.\u00a0<\/span> <br \/>\n <span style=\"color: blue\">EXEC<\/span>\u00a0<span style=\"color: #ff0080;font-weight: bold\">Sp_configure<\/span> <span style=\"color: red\">&#8216;show\u00a0advanced\u00a0options&#8217;<\/span><span style=\"color: silver\">,<\/span> <span style=\"color: black\">1<\/span><span style=\"color: silver\">;<\/span> <br \/>\n <span style=\"color: maroon\">go<\/span> <br \/>\n <span style=\"color: green;font-style: italic\">&#8212;\u00a0To\u00a0update\u00a0the\u00a0currently\u00a0configured\u00a0value\u00a0for\u00a0advanced\u00a0options.\u00a0<\/span> <br \/>\n <span style=\"color: #ff0080;font-weight: bold\">RECONFIGURE<\/span><span style=\"color: silver\">;<\/span> <br \/>\n <span style=\"color: maroon\">go<\/span> <br \/>\n <span style=\"color: green;font-style: italic\">&#8212;\u00a0To\u00a0enable\u00a0the\u00a0feature.\u00a0<\/span> <br \/>\n <span style=\"color: blue\">EXEC<\/span>\u00a0<span style=\"color: #ff0080;font-weight: bold\">Sp_configure<\/span> <span style=\"color: red\">&#8216;xp_cmdshell&#8217;<\/span><span style=\"color: silver\">,<\/span> <span style=\"color: black\">1<\/span><span style=\"color: silver\">;<\/span> <br \/>\n <span style=\"color: maroon\">go<\/span> <br \/>\n <span style=\"color: green;font-style: italic\">&#8212;\u00a0To\u00a0update\u00a0the\u00a0currently\u00a0configured\u00a0value\u00a0for\u00a0this\u00a0feature.\u00a0<\/span> <br \/>\n <span style=\"color: #ff0080;font-weight: bold\">RECONFIGURE<\/span><span style=\"color: silver\">;<\/span> <br \/>\n <span style=\"color: maroon\">go<\/span>\u00a0 <\/span><\/div>\n<\/blockquote>\n<p>2) Create the stored procedure to execute &#8216;xp_cmdshell&#8217; to start SQL Server Agent<\/p>\n<blockquote>\n<div><span style=\"font-family: Courier New;font-size: 10pt\"> <span style=\"color: blue\">CREATE<\/span>\u00a0<span style=\"color: blue\">PROCEDURE<\/span>\u00a0<span style=\"color: #ff0080;font-weight: bold\">Autoagent<\/span> <br \/>\n <span style=\"color: blue\">AS<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">EXEC<\/span>\u00a0<span style=\"color: #ff0080;font-weight: bold\">Xp_cmdshell<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: red\">&#8216;NET\u00a0START\u00a0SQLSERVERAGENT&#8217;<\/span>\u00a0 <\/span><\/div>\n<\/blockquote>\n<p>3) Configure the stored procedure as an auto-start stored procedure. It will be executed every time SQL Server Service starts<\/p>\n<blockquote>\n<div><span style=\"font-family: Courier New;font-size: 10pt\"> <span style=\"color: blue\">EXEC<\/span>\u00a0<span style=\"color: #ff0080;font-weight: bold\">Sp_procoption<\/span> <span style=\"color: maroon\">autoagent<\/span><span style=\"color: silver\">,<\/span> <span style=\"color: maroon\">startup<\/span><span style=\"color: silver\">,<\/span>\u00a0<span style=\"color: maroon\">true<\/span>\u00a0<\/span><\/div>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>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,&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143527],"tags":[],"coauthors":[],"class_list":["post-71851","post","type-post","status-publish","format-standard","hentry","category-blogs","category-database-administration-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71851","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71851"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71851\/revisions"}],"predecessor-version":[{"id":71852,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71851\/revisions\/71852"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71851"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}