Potential pitfalls using Always On Availability Groups

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 Advise (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. Download for free at aireforge.com.

How to use Aireforge Studio as a free RDP & SSMS Manager

Working with an estate of hundreds of servers? Finding it difficult to keep a shared, central list of server information up to date?

Everyone seems to have their own solution for maintaining the details their team needs for SQL Server Management Studio/Remote Desktop. Maybe you store this information in a spreadsheet or text file on a shared network drive, have a local copy, or use a specialised separate program to coordinate this knowledge. Maybe you even have it printed out and taped to the wall!

All these methods come with their pros and cons, but besides the pricier options, most require regular manual work. We’ve got another way for you to try, and it’s free.

How Aireforge Studio can help

When using Aireforge Studio, you start by setting up all your servers in the Estate module. The list then becomes your central workspace for you to compare settings, run health checks, scripts etc.

To launch SSMS or Remote Desktop, there’s a right-click option, then the relevant address details are pre-populated. No more moving between documents or copy and paste! You can even give servers friendly names to make your workspace faster to navigate.

For teams, there’s an alternative to a centralised document: share a secure, password protected Aireforge profile. That way, you’re only inputting this data once and everyone is always getting the recent information.

Download your free tools today at aireforge.com.

Visit our Knowledge Base to learn how to set it up.

remotedesktop