There can be times during a typical day of a DBA, when there are a lot of urgent requests coming and the DBA is busy resolving issues. These are the times when the DBA works on a request such as granting access on an AlwaysON instance and though being fully knowledgeable of what all pre/ post steps should be performed, the steps are either left to be completed later or, are completely missed.

Issue

So, let us assume it was one of those days and an application login was created on the primary replica, however, was missed from the secondary replica(s).

In such cases, where a login, database or, SQL Agent job is only created on the primary replica, in an event of a failover, connectivity issues and issues where jobs not being executed will be experienced.

Solution

I have written the below set of scripts to check and e-mail, if there are any inconsistencies in logins, SQL agent jobs, and databases on AlwaysON cluster having ‘n’ number of replicas.

• AO_Check_create_login_and_linked_server.sql: Based on the number of replicas in an availability group, this script will create the necessary login and linked servers on a given availability replica. Please ensure that the script is executed on all the availability replicas.
• Check_DBs_not_configured_in_AG.sql: Once the required login and linked servers are created, use this script to create the first stored procedure to check and email if there are any databases that are present on a given availability replica and are missing from any other replicas.
• Check_async_logins_and_alert.sql: Use this script to create another stored procedure that will check and e-mail if there are any logins that are present on a given availability replica and are missing from any other replicas.
• Check_async_SQLAgent_jobs_and_alert.sql: Use this script to create another stored procedure that will check and e-mail if there are any SQL agent jobs that are present on a given availability replica and are missing from any other replica(s).
• AlwaysON-Async-Login-Job-DB-check-SQLagentjob.sql: Use this script to create a SQL Agent job that can either be executed on-demand or,  a required schedule can be attached to get notified whenever required.

Originally published on my personal blog: https://itnoesis.com

The post MSSQL Server AlwaysON: Get alerted for inconsistencies in logins, jobs and databases appeared first on SQLServerCentral.