Replicating Jobs Between SQL Instances – Wrapping Up
As part of starting writing blog posts, I wanted to force myself to stop and reflect on what I have learnt to do. This is something that I’ve always been bad at doing and I think that’s true for many in the IT world. As a DBA/Programmer I tend to solve a problem and then move on to the next one, but I don’t take the time to reflect and evaluate what I’ve achieved.
My first attempt at writing a job to replicate jobs between instances in an AlwaysOn Availability Group (AAG) involved doing insert/delete/update statements directly on the tables in the msdb database. This worked great to copy the jobs across, but nothing notified the SQL agent that something had changed – jobs wouldn’t run or they might even run a cached version of themselves. This led to a lot of confusion as the job appeared to be there correctly, but it didn’t run. I noticed the problem resolved itself after restarting the service.
This lead me to investigating how to create jobs and getting the SQL Agent to start doing it. It was obviously possible as you can do it through SSMS which just issues SQL commands. After a bit of research I realised that it was done through SQL Stored Procs. This took me down the path of running cursors to call the correct stored procs to copy new and changed jobs and schedules.
I’m pretty happy with the final result. I think there is still a bug in the schedule copy that I need to work out (using ID’s instead of names), but the result seems to be working for us for now.
It’s been hard to do this work in my “spare time” – my family has had a pretty big struggle the first 6 months of this year that has taken up a lot of my time, and work has been pretty full on the rest of the time. Remembering decisions that I made 3-4 months ago and continuing with them has been hard. I’ve learnt to take a lot more notes about what I’m doing.
It’s been great learning more about SQL Agent, particularly seeing how it integrates with SQL Server via Extended Stored Procedures.