Synonyms And Availability Groups

Allen McGuire shows how to maintain synonyms between instances in an Availability Group:

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.

Related Posts

Modifying Availability Group Endpoint URLs

Ronald Dameron shows how to change the endpoint URLs for Availability Group replicas: I recently had to modify the Endpoint URLs on our SQL Server Availability Group replicas. The reason for this blog post is that I could not answer the following questions: Do I need to suspend data movement prior to making this change?  […]

Read More

Synchronizing Availability Group Objects

Derik Hammer has a process to maintain logins, backup devices, linked servers, SQL Agent details, and more between Availability Group nodes: A notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930