Potential Pitfalls Using Always on Availability Groups

Potential Pitfalls Using Always on Availability Groups

Written by
Avatar
Gillian Johnson
Published on
2019-01-14

Always On Availability Groups are a great way to improve uptime and protect against data loss. However, whilst the databases within the availability groups are synchronized, the instance objects, users and configuration settings that the system relies on are not. This could cause the following to happen when you failover:

  • Authentication issues. Your users and database roles are synchronized but do not match up to a server login or server role. Your systems are down and you're helpless.
  • Missing/incorrect Agent Jobs. A difference in job steps or schedules can result in jobs not being run or data issues due to missing changes and bug fixes.
  • Missing server objects. Issues with Linked Servers, Trigger or an incorrect configuration setting could result in queries failing or performing much slower than the primary.

It's very easy for server objects to become out of sync, causing the above. Changing a user, fixing a script within jobs, updating the job schedule: these regular tasks can all cause major issues after failing over.

"You should routinely maintain the same set of user logins and SQL Server Agent jobs on every primary database of an Always On availability group and the corresponding secondary databases." Microsoft Docs

How do I fix it?

Here are some ways you can protect yourself from Availability Groups becoming out of sync:

  1. Use an active directory to help mitigate the SID issues (be aware that you might still encounter differences using this method - users' rights or disabled users, for example).
  2. Manually compare the SIDs between your instance and AG using scripts.
  3. Compare job information using a text comparison tool like code compare.
  4. Create your own custom script that covers every possible object and setting and manually compare the results on a regular basis.
  5. Use the free comparison tool in Aireforge Community Edition.

I'm too busy for that - is there a faster way?

Use Aireforge Studio's instance-level comparison tool to quickly spot differences (it's free). Identify varying SIDs, user access rights, server configuration (e.g. max threshold) between your instances in a few clicks.

You can incorporate these comparisons into weekly checks. Many of our users even run these daily so they're less likely to be caught out during failover. As a minimum, you could compare and fix any differences before planned failovers or any significant changes.

I've found the changes, but I need help fixing it...

Aireforge Advisor (not free, but reasonable), will give you the SQL to fix these differences once you've identified them.

Aireforge Studio simplifies database management for SQL Server & Azure SQL databases.