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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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' begin -- do your job's task end else begin -- don't do anything (this 'else' block can be omitted) -- or if you want to handle accordingly, do so end
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.