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;