September 24, 2013 at 9:04 am
Hello everybody,
I am a SQL DBA and need some help.
We currently have two 2012 SQL Servers in High Availability.
I scripted and placed the jobs on both Servers and added the 1st step to all them utilizing the following script:
IF dbo.fn_hadr_is_primary_replica ('db_name') <> 1
RAISERROR('This is not the preferred replica, exiting with success',11,1)
So, the jobs are basically running according to their schedules on both Servers, and exiting the 1st step with Success if: dbo.fn_hadr_is_primary_replica ('db_name') <> 1
It works fine. but this is not a fancy solution at all.
For example, since the SSIS Packages reside in the Catalogs that I created, for executing them it does not matter from which job and on which Server they are running.
Is there a way do something like that with the jobs; anotherwords set some Centralized location for them and run all the jobs from this location?
Or maybe there anybody is aware of a better way to set up the jobs in AlwaysOn?
Any help with this matter will be greatly appreciated.
Thank you.
Alex
September 25, 2013 at 6:25 am
You could move them to a (3rd) centralised location I suppose, but I don't see why that's a better solution than what you've already done, which I'd say was the gold standard as it's both DR and HA assuming your AlwaysOn nodes are in two separate locations.
Say you moved them to a SQL Agent on server 3, although it's now unaffected by switchover (assuming everything's configured with the AlwaysOn listener) this is then not HA if the 3rd server fails.
So you have to make server 3 HA (and DR) and you have two locations to configure jobs again.
September 25, 2013 at 8:16 am
That make sense, thanks Howard
September 26, 2013 at 5:55 am
My jobs are set up just about exactly as you have set up your jobs. I think it's the best solution and ran this by a MS Specialist who thought it was good architecture.
The jobs are set up on each server, for "failover" purposes and not on a 3rd server, due to the fact that the 3rd server would need to be in HA, and also not all of my jobs run packages, some just run sprocs.
Step one checks for primary replica by group, and if not it exits outs. The one difference I'm doing is that I wrote a function and pass in "group name" because I'm using multiple availability groups which can run on the different nodes as a type of load balancing.
I feel this is the closest way to mimic clustering, when we clustered at the instance level and not the database level as we do in "Always On".
Steve
February 25, 2016 at 9:09 pm
Hi, I am not DBA!, but I am on similar requirement. I do not see enough step by step materials to achieve the AlawaysOn with SSRS, SQL Agent Jobs , File Streams, MDS setup.
Kindly guide me with resources/video links/articles
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply