Post reply

Script To Failover AG

  • September 15, 2020 at 1:57 pm

    Go to Answer

    #3788554

    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

    ALTER AVAILABILITY GROUP AG_SQLSVR FAILOVER;

    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

    ALTER AVAILABILITY GROUP AG_SQLSVR FAILOVER;

    and specify which node to fail to within there?

  • September 16, 2020 at 9:01 am

    #3788766

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

    Johan

    Learn to play, play to learn !

    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 but most of the time this is me

  • September 16, 2020 at 12:11 pm

    #3788791

    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

  • September 16, 2020 at 1:39 pm

    Answer

    #3788836

    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

  • September 16, 2020 at 6:50 pm

    #3788929

    Thanks, Cebisa!

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

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