Ever realize you’ve been doing something a certain way for so long and you don’t remember why? Or what the consequences would be if you changed how you did things?
I was testing our new SQL Server installation process this week. We create a configuration file programmatically from user input and some set defaults and then run the install unattended followed by executing a series of T-SQL and PowerShell scripts to make sure certain configurations are done.
Vicky Harp (blog | twitter) has a presentation on edge case testing which really caught my attention a few months back. For years, I’ve always made sure all parameters, required and optional, were explicitly defined for our installs. But, what if someone else runs this and isn’t as precise as I tend to be?(I know some of you are thinking of another word.) For the current test I was doing with our new process, I was only providing the absolute minimum required parameters and letting everything else default. The install ran great, and then the configuration files started executing. Surprisingly, I had two scripts report failure. I had successfully run the same set of scripts on a prior install earlier in the day. What changed? You’ve probably already guessed, but bear with me through my analysis.
My first thought was a SQL Server 2012 difference from SQL Server 2008 R2. The current install I was performing was 2012 while the earlier one was for 2008 R2. So, that seemed like the logical place to start – a difference between versions. But, I was confident that these scripts worked for both versions and had previously tested them independently of the install process. Then I looked a little more carefully at the error. The failing scripts were calling system stored procedures to modify SQLAgent’s job history settings. The error being reported was that the Agent XPs weren’t enabled. I was confused – why did my SQL 2008 R2 install work fine with the same scripts and SQL 2012 fail?
While I’m pondering the mystery of what was different I remembered that the SQL 2012 Setup had returned error 3010 which means that a reboot is needed following the install; so I rebooted that box. In the meantime, I searched and verified that I indeed did not have a script which was explicitly enabling the Agent XPs, but somehow they were enabled on the SQL 2008 R2 system. The SQL 2012 system came back up and I logged back on to continue my investigation. That was when I noticed that SQLAgent had not started following the reboot. Its start mode was set to “manual” which is the default if you do not specify the AGTSVCSTARTUPTYPE=”AUTOMATIC” in your configuration file. I opened SQL Server Configuration Manager and changed the SQLAgent service’s start mode to “Automatic” and started the service. Then, I checked the value for sp_configure ‘Agent XPs’ – and sure enough it was now 1 (enabled) and I could execute the scripts which previously failed.
At first I thought it was just changing the start mode to Automatic triggered the enabling of the Agent XPs, but I finally verified after testing several scenarios that whenever the SQLAgent service is started or stopped regardless of the start mode, it will enable or disable the Agent XPs respectively. This is really confusing if you know that you have explicitly run:
sp_configure ‘Agent XPs’, 1;
Then you stop SQLAgent and don’t restart – it is now disabled as if you’d never run the above command. If you want to modify some aspect of SQLAgent while it is now stopped, you’ll have to run the above command again.
sp_configure ‘Agent XPs’, 1;
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=150000;
So, you can modify the properties while SQLAgent is not running, you just have to remember to always enable the Agent XPs immediately beforehand. Since this behavior is by design for security purposes, then you’ll probably want to get the current setting before enabling it and if it was disabled, then set it back to disabled when you’ve finished running your command(s).
You can leave the SQLAgent service stopped and run the sp_configure command to enable the XPs, and then you can access the SQLAgent properties in SSMS. Most of the time this shouldn’t be an issue, you’ll have SQLAgent running and the XPs will have been automatically enabled. But, just in case you ever run into an unexpected error with the Agent XPs like I did, hopefully, you’ll remember this behavior.