Script To Failover AG

  • RonMexico

    Hall of Fame

    Points: 3414

    I'm trying to run a script on a schedule within the Job Agent to failover an AG. I can easily do this outside of a job by running the following in SSMS with SQLCMD mode

    :Connect SQLSVR


    I tried to add an Operating System (CmdExec) step to a job like this

    sqlcmd -S SQLSVR -Q "C:\AG_SQLSVRFailover.sql"

    where the .sql file contains the failover script above. However, this does not seem to work properly. Is there a way I can simply issue a statement like


    and specify which node to fail to within there?

  • Johan Bijnens

    SSC Guru

    Points: 134302

    1. Why would you put this in a job ?
    2. Why don't you just run the Alter in a TSQL jobstep ?


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • RonMexico

    Hall of Fame

    Points: 3414

    1. So it can be automated to happen at a specific time on a specific day
    2. My hope is that it can be initiated from the primary node rather than the secondary which the alter statement I supplied does not support
  • Cebisa

    Valued Member

    Points: 51

    Sql agent job step type: powershell

    Switch-SqlAvailabilityGroup -Path SQL SERVER:Sql\othernode\instance\AvailabilityGroups\AGName -ErrorAction Stop -WarningAction SilentlyContinue | Out-Null

    Schedule as one time run

    Run as sys admin, sql agent account

  • RonMexico

    Hall of Fame

    Points: 3414

    Thanks, Cebisa!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply