Today, i was writing a script to detach and attach some databases. We have a set of dbs that are in 2 modes now, with 2 copies of dbs to support those modes. I needed to quickly be able to 'toggle' between them. In 1 mode, the SQL Server Agent needed to be disabled, while in the other mode, it needs to remain on.
So, I searched my local drive for a solution, some script or document. Nothing.
I turned to google. Simultaneously turned to Twitter. I ended up at SQL Server Central, and found some folks talking about it.
Ultimately, i opened up Profiler and turned a local machine db's SQL Agent on and off, while tracing the activity. After a few failed attempts, i narrowed it down to these pieces of code.
--off
EXECUTE master.dbo.xp_sqlagent_monitor 'STOP', NULL, 0, 1
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 0
reconfigure with override
--on
EXECUTE master.dbo.xp_sqlagent_monitor 'START', N'', 1
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 1
reconfigure with override
I would execute the off, while Agent was on, and then have to refresh SSMS to see the change. Sure enough it was off. I'd then execute the on code, refresh, and saw it was on. Viola! I exclaimed in a loud voice my manliness and heorism to all around me. Thats when a small voice from the next cube reminded me that the server I was to 'toggle' was a Cluster, and that the SQL Agent needed to be altered from Cluster Administrator.
Oh well. I learned something. Now I want to know if it will work with Clusters. But i do not have the means to test that thought, since the box I was toggling is live, and i shouldnt be testing on it. Someday, I'll get a better test, or find out the answer, and post it here.
No comments:
Post a Comment