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.
Click through for the SQL Agent job details. I have only created a couple synonyms and don’t really like them that much, but if they’re your bag, then at least you know you can use them safely with Availability Groups.