8 things to check for a successful failover with Always On Availability Groups

Here are 8 things that are good to check so you can sleep at night not having to worry about applications stopping if a failover occurs:

Is the application built to handle a failover?

Can the application handle a failover,  can it connect to the database after a short break or is manual intervention required to get it to work again?

Logins

Are all logins on all nodes?
A common failure is to create logins on the primary node, but forget to create them also on the secondary node. If you are using SQL Logins you need copy the SID to the secondary node, because the database connects the user with the login via the local SID. (This is most easily done with sp_help_revlogin, you can find it here)

SQL Agent Jobs

All SQL Agent jobs needs to be on both nodes. It can also be good to have a check on each job to run only on primary node. You want jobs to be active on both nodes in case of an unplanned failover.

Linked Servers

If you use linked servers, they need to be on both nodes.

Database Owner

It is critical that there is a special DB owner?
When you add a database in a availability group it is the account you are logged in with that becomes Database Owner. That could pose a risk if the application is sensitive (eg SharePoint in some situations).

Trace Flags

Any trace flags that are enabled should be enabled on all the nodes.

Special Configuration

Have you made any special configuration, for example with sp_configure, it should be the same configuration on all nodes.

Server roles

Are there accounts that needs to be member in any server roles for the application to work correctly?
Make sure that the account is member of the same server roles on all nodes.

If you have regular checks of these things and do not have any differences between the nodes, you have a good chance for achieving a failover without significant interruption to users. There are various techniques to control these, both manually and automatically. If you need help, do not hesitate to contact us!

Always On Availability groups is an Enterprise Feature in SQL 2012 and later versions. There is also a limited version in SQL 2016 Standard Edition, called Always On Basic Availability Group.

Good luck !