Post reply

Stored Procedures with Linked Servers - Failover Question

  • January 11, 2018 at 12:21 pm

    #325396

    Hi,

    I have a SQL Server 2016 Availability Group between two geographic sites.  In a couple of our stored procedures, we are using linked servers that have to be updated manually if the AG fails over to the other site.  
    I was wondering if there was an easy way to modify the stored procedures to automatically executes the correct linked server.
    Any help is GREATLY appreciated!

    Thank you!

    My Blog!

  • January 11, 2018 at 12:35 pm

    #1975599

    Perhaps something like:


    Declare @SQL as varchar(max)
    Set        @SQL = 'Select
                ''hello world''
                as Message
                '

    --pseudo code for checking if site 1 or site 2 is down
    IF Site 1 is down
                    Then exec (@SQL) at [LinkedServerForSite2]
    else            Then exec (@SQL) at [LinkedServerForSite1]    

  • January 11, 2018 at 12:56 pm

    #1975604

    Haha!  So simple!  I was certainly overthinking it.  Thank you!

    My Blog!

Viewing 3 posts - 1 through 2 (of 2 total)

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