Stored Procedures with Linked Servers - Failover Question

• dajonx

  SSCertifiable

  Points: 6950

  More actions

  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!

• adaddio

  Mr or Mrs. 500

  Points: 539

  More actions

  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]    

• dajonx

  SSCertifiable

  Points: 6950

  More actions

  January 11, 2018 at 12:56 pm

  #1975604

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

  My Blog!