Using Synonyms with Availability Groups

I'm a big fan of using synonyms to point to objects in other databases, but if you've tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because the instance name is part of the synonym definition. In order for the synonyms to work again, they have to point to the instance they now reside in - assuming the databases were in the same instance, respectively.

The next challenge was to automate the of detection of when the primary has changed, therefore triggering an update of the synonyms. I put the following tSQL into a SQL Agent job and set it to run every five seconds. This will be Step 1 in the job, as it will have two steps total.

IF  (   SELECT role_desc
        FROM   sys.dm_hadr_availability_replica_states
               INNER JOIN sys.dm_hadr_name_id_map 
               ON sys.dm_hadr_availability_replica_states.group_id = sys.dm_hadr_name_id_map.ag_id
        WHERE  sys.dm_hadr_name_id_map.ag_name = 'AGName'
               AND is_local = 1
    ) = 'secondary'
    BEGIN
        RAISERROR('This is secondary replica. Do not countinue this job.', 11, 1);
    END;
ELSE
    BEGIN
        PRINT ('This is primary replica and you can go to next step.');
    END;

Now, if no failover has occurred, the Step 1 above will fail - as will the job as a whole obviously. If in fact a failover has occurred it will go on to Step 2, which has the below syntax. You'll note the syntax of the query in the cursor: it will query sys.synonyms to see if there are any pointing to the incorrect (not local) instance. If they are all correct, no records will be returned and it will stop. If there are synonyms that need updating, it will fall into the cursor and drop/recreate them using REPLACE syntax to point locally (@@SERVERNAME).

DECLARE @ObjectName sysname
      , @Definition VARCHAR(MAX)
      , @Schema VARCHAR(50);
DECLARE @SQL VARCHAR(MAX);

DECLARE loccur CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
    SELECT name
         , SCHEMA_NAME(schema_id)
         , base_object_name
    FROM   sys.synonyms
    WHERE  base_object_name LIKE '%OtherInstanceName%';
OPEN loccur;

FETCH NEXT FROM loccur
INTO @ObjectName
   , @Schema
   , @Definition;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Converting: Synonym, ' + @ObjectName;

        SET @SQL = 'DROP SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName);
        EXEC (@SQL);
        --PRINT @SQL

        SET @SQL = 'CREATE SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName) + 
                   ' FOR ' + REPLACE(@Definition, 'OtherInstanceName', @@SERVERNAME);
        EXEC (@SQL);
        --PRINT @SQL

        FETCH NEXT FROM loccur
        INTO @ObjectName
           , @Schema
           , @Definition;
    END;

CLOSE loccur;
DEALLOCATE loccur;