October 30, 2015 at 8:10 am
So this is my first foray into setting up AG on 2014, which will contain about 100 + SQL Agent jobs that are being converted from SQL 2008 R2.
My plan for handling the converted jobs is as follows
1) Install the same jobs on both nodes, but with the jobs disabled on the read-only node.
2) Add an alert on both nodes for when failover occurs and the passive node is becoming active.
3) Fire a special SQL Agent job off of this alert that will call a stored procedure that reads the job's names from a 'synch me' table that will then disable the jobs on the new passive node and activate the jobs on the new active node.
For longer term, i plan to add a block of code to the jobs to determine if its sitting on the passive or active before it fires and runs, but for the conversion project, there are 2 many for this approach to be feasible.
Am i missing anything that might make this process simpler?
October 30, 2015 at 8:18 am
Take a look at sys.fn_hadr_is_primary_replica, its written to return true false depending which node in the group is the primary.
Also these two links may help
October 30, 2015 at 1:06 pm
Would this scenario work?
My SQL Agent jobs for this thread connect to the instances of the databases, not the local versions, so if I had another SQL server that had all of the jobs that hit the AG group installed, would I even need to worry about this scenario? Wouldn't they run as normal, connecting to the listener, which should in theory direct their requests to the appropriate node that is currently the primary?
I guess the main concern would be is if the 'job runner' went down, but it seems easier logistically to have everything installed once instead of 'n' times and trying to sync all of that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply