Is there any way how to create a startup T-SQL script/procedure for starting availability replica (when state has changed from secondary to primary)?

I want to disable SSIS jobs specific for related availability groups.


Since you are talking about Availability Groups, there isn't an instance startup like you're thinking about as there is with failover cluster instances. Because all replicas/instances are online (during normal operation of course) you can't leverage the same implementation of running a "startup procedure".

Off the top of my head, the way to do this would be one of a couple of ways.

First off, you could have a scheduled SQL Server Agent job that routinely checks to see if the local replica is currently the primary in the availability group. And then disable whatever jobs you need to given the certain condition. I don't recommend this method.

But my recommendation would be to have this in your existing SQL Server Agent jobs. So for instance, in your jobs you would have logic that does the check to see if the local replica is currently primary, and if so do what the job currently does. Something like this:

if (
    select role_desc
    from sys.dm_hadr_availability_replica_states
    where is_local = 1
) = 'primary'
        -- do your job's task
        -- don't do anything (this 'else' block can be omitted)
        -- or if you want to handle accordingly, do so

So in this example, no jobs are explicitly disabled. They'll just do a quick conditional check to see if it should actually do something in a particular role. This is basically the way that backups are typically designed in an AG.

  • This exact solution we use for classic tsql statements inside job steps. But you can't run ssis package from tsql in your "else" branche (in fact you can but it's a bit obscure). We also considered to i) raise error in this part of this if else statement and than change behaviour of step to quit and report success or ii) change job logic, but I am still looking for more straightforward solution. – dewet May 22 '13 at 10:53
  • Have these as job steps, use conditional logic to determine whether or not job step execution should continue. – Thomas Stringer May 22 '13 at 11:27
  • What exactly do you mean by "use conditional logic to determine whether or not job step execution should continue"? As I mentioned - run dtsx package in TSQL subsystem or changing the logic step workflow of running job is not the way I want to go :) – dewet May 27 '13 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.