Thursday, September 14, 2017

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  ys.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;