Introduction
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.

What can be done?
Wouldn’t it be nice if I can remind myself in case I forgot to do something? 🙂

In order to do so, I have written the below 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.

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.

/********************************************************************** 
* Name: AO_Check_create_login_and_linked_server.sql
* Created by: Arun Yadav
* Created on: July-25-2017
* Purpose: 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 replicas.
* Updated By: 
* Updated on: 
* Comments:
* Disclaimer: Though the script has been tested however, I do not take any guarantees, 
* it is advised to test the scripts again before implementing in production and after
* providing due credit.
**********************************************************************/

USE [master]
GO
SET NOCOUNT ON
GO

DECLARE @AO_checkusr nvarchar (20), @AO_checkPasswd nvarchar (20), @Replica_List nvarchar(100)
SET @Replica_List = (SELECT Replica_name = STUFF((SELECT ', '+ replica_server_name from [sys].[availability_replicas]
FOR XML PATH('')), 1, 2, ''))
PRINT '******** Make sure there are no spaces in the login name. ********'
PRINT '******** This script has to be executed on all the availability replicas. ********
Below is the list of configured Availability Replicas:
'+@Replica_List

/*START OF EDITABLE PART*/

SET @AO_checkusr = '<Provide a user name>' -- SET THE NAME OF THE LOGIN HERE (without space) which will be used to connect to the replicas via the linked server
SET @AO_checkPasswd = '<Provide a strong password>' -- SET THE PASSWORD HERE for the login which will be used to connect to the replicas via the linked server

/*END OF EDITABLE PART*/




IF EXISTS(SELECT 1 from SYSLOGINS WHERE NAME = @AO_checkusr)
 BEGIN
 PRINT 'A login with name ['+@AO_checkusr+'] already exists.'
 END
ELSE
 BEGIN
 DECLARE @LOGINQRY NVARCHAR(200)
 SET @LOGINQRY = 'CREATE LOGIN ['+@AO_checkusr+'] WITH PASSWORD=N'''+@AO_checkPasswd+''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
 --PRINT @LOGINQRY
 EXEC (@LOGINQRY)
 END

DECLARE @PERMISSIONQRY NVARCHAR (2000)
--DECLARE @AO_checkusr nvarchar (20)
--SET @AO_checkusr = 'AO_check' 
SET @PERMISSIONQRY = 'IF EXISTS(SELECT 1 FROM SYSUSERS WHERE NAME ='''+@AO_checkusr+''')
BEGIN
PRINT ''The user already exists in master db.''
END
ELSE
BEGIN
PRINT ''Creating user in master db.''
CREATE USER ['+@AO_checkusr+'] FOR LOGIN ['+@AO_checkusr+']
END
GRANT VIEW SERVER STATE TO ['+@AO_checkusr+']
ALTER ROLE [db_owner] ADD MEMBER ['+@AO_checkusr+']
'
--PRINT @PERMISSIONQRY
EXEC (@PERMISSIONQRY)

USE [msdb]

--DECLARE @PERMISSIONQRY NVARCHAR (2000)
--DECLARE @AO_checkusr nvarchar (20)
SET @AO_checkusr = 'AO_check' 
SET @PERMISSIONQRY = 'IF EXISTS(SELECT 1 FROM SYSUSERS WHERE NAME ='''+@AO_checkusr+''')
BEGIN
PRINT ''The user already exists in msdb db.''
END
ELSE
BEGIN
PRINT ''Creating user in msdb db.''
CREATE USER ['+@AO_checkusr+'] FOR LOGIN ['+@AO_checkusr+']
END
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER ['+@AO_checkusr+']
ALTER ROLE [db_datareader] ADD MEMBER ['+@AO_checkusr+']
ALTER ROLE [ServerGroupReaderRole] ADD MEMBER ['+@AO_checkusr+']
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER ['+@AO_checkusr+']
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER ['+@AO_checkusr+']
ALTER ROLE [SQLAgentUserRole] ADD MEMBER ['+@AO_checkusr+']
'
--PRINT @PERMISSIONQRY
EXEC (@PERMISSIONQRY)

USE [master]

SELECT replica_server_name INTO #Temp1
FROM [sys].[availability_replicas] WHERE replica_server_name <> @@servername;

DECLARE @LINKEDSRV NVARCHAR(50)
DECLARE LINKKURSOR CURSOR FOR 
SELECT * FROM #Temp1

OPEN LINKKURSOR
FETCH NEXT FROM LINKKURSOR INTO @LINKEDSRV
WHILE @@FETCH_STATUS = 0 
BEGIN 
print @LINKEDSRV
IF EXISTS (SELECT srvname FROM sysservers WHERE srvname = @LINKEDSRV)
 BEGIN
 PRINT 'Linked server with name '+ @LINKEDSRV +'already exists. Either drop the linked server and re-run this script or, jump to next script.'
 --RAISERROR('Linked Server already exists', 20, -1) with log
 END
ELSE 
 BEGIN
 Print '********Creating linked server named '+ @LINKEDSRV +'********'
 EXEC master.dbo.sp_addlinkedserver @server = @LINKEDSRV, @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@LINKEDSRV,@useself=N'False',@locallogin=NULL,@rmtuser=@AO_checkusr,@rmtpassword=@AO_checkPasswd

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'rpc', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'rpc out', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@LINKEDSRV, @optname=N'remote proc transaction promotion', @optvalue=N'true'
 Print '********Linked server '+ @LINKEDSRV +' created********'
 END
FETCH NEXT FROM LINKKURSOR INTO @LINKEDSRV 
END
CLOSE LINKKURSOR 
DEALLOCATE LINKKURSOR
DROP TABLE #Temp1
GO

Once the required login and linked servers are created, use the below script to create the first stored procedure to check and email if there are any databases which are present on a given availability replica and are missing from any other replicas.

/********************************************************************** 
* Name: Check_DBs_not_configured_in_AG.sql
* Created by: Arun Yadav
* Created on: July-25-2017
* Purpose: This script will check and e-mail if there are any databases which are present 
* on a given availability replica and are missing from any other replicas.
* Updated By: 
* Updated on: 
* Comments:
* Disclaimer: Though the script has been tested, I do not take any guarantees, 
* it is advised to test the scripts again before implementing in production and after
* providing due credit.
**********************************************************************/

use [master]
GO

CREATE PROCEDURE usp_Check_DBs_not_configured_in_AG
AS
BEGIN
SET NOCOUNT ON

if exists (select name from sys.databases 
WHERE name Not IN ('master', 'model', 'msdb', 'tempdb','ReportServer','ReportServerTempDB')
and replica_id Is NULL)
BEGIN
DECLARE @servername sysname = (select @@servername)
DECLARE @Databases varchar (100) = (select name = STUFF((select ', ' + name from sys.databases 
WHERE name Not IN ('master', 'model', 'msdb', 'tempdb','ReportServer','ReportServerTempDB')
and replica_id Is NULL
FOR XML PATH('')), 1, 2, ''))
DECLARE @listenername varchar (100) = ''
if exists (select serverproperty('IsHADREnabled'))
BEGIN
SET @listenername = (select Listener_Name = STUFF((select ', ' + dns_name from sys.availability_group_listeners 
FOR XML PATH('')), 1, 2, ''))
END

DECLARE @emailsubject varchar (100) = 'Attn: Database(s) not a member of AOAG on '+@servername
DECLARE @Email_body varchar(400) = 'This is an informational message only:
The below databases on '+ @servername +', are not in an AlwaysOn Availibility Group.

Database Name(s): 
'+@Databases+'

Below is the list of configured Availability Group Listener(s)
'+@listenername

print @Email_body

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your SQL Server Database Mail Account',
--@recipients = '<Email Address>',
@recipients = '<Email Address>',
--@copy_recipients = '<Email Address>',
@body = @Email_body,
@subject = @emailsubject; 
END
END
GO

Once done, use the below script to create another stored procedure which will check and e-mail if there are any logins which are present on a given availability replica and are missing from any other replicas.

/********************************************************************** 
* Name: Check_async_logins_and_alert.sql
* Created by: Arun Yadav
* Created on: July-25-2017
* Purpose: This script will check and e-mail if there are any logins which are present 
* on a given availability replica and are missing from any other replicas.
* Updated By: 
* Updated on: 
* Comments:
* Disclaimer: Though the script has been tested, I do not take any guarantees, 
* it is advised to test the scripts again before implementing in production and after
* providing due credit.
**********************************************************************/
use [master]
GO

CREATE PROCEDURE usp_Check_async_logins_and_alert
AS
BEGIN
SET NOCOUNT ON

DECLARE @replicas nvarchar(50), @Replica_List nvarchar(100)

SET @Replica_List = (SELECT Replica_name = STUFF((SELECT ', '+ replica_server_name from [sys].[availability_replicas]
FOR XML PATH('')), 1, 2, ''))
--print @Replica_List

DECLARE kursor cursor for 
SELECT replica_server_name from [sys].[availability_replicas]
WHERE replica_server_name <> @@servername

OPEN kursor
FETCH NEXT FROM kursor INTO @replicas
WHILE @@FETCH_STATUS = 0 
BEGIN
DECLARE @login_qry nvarchar(600), @login_chk nvarchar(5)
SET @login_chk = 'SELECT count (name) from [sys].[server_principals]
WHERE name not in (SELECT name from ['+@replicas+'].[master].[sys].[server_principals])
and type not in (''C'', ''R'')
and name not like ''##%''
and name not like ''NT SERVICE%''
and name not like ''NT AUTHORITY%'''

SET @login_qry = 'SELECT name from [sys].[server_principals]
WHERE name not in (SELECT name from ['+@replicas+'].[master].[sys].[server_principals])
and type not in (''C'', ''R'')
and name not like ''##%''
and name not like ''NT SERVICE%''
and name not like ''NT AUTHORITY%'''
print @login_qry

if (@login_chk is not null)
BEGIN
DECLARE @servername sysname = (SELECT @@servername)
DECLARE @loginnames nvarchar (500)

CREATE TABLE #temp(name sysname)
INSERT INTO #temp
EXEC (@login_qry)
--SELECT * from #Temp
--Drop table #Temp

SET @loginnames = (SELECT name = STUFF((SELECT ', '+name from #temp 
FOR XML PATH ('')), 1, 2, ''))

--print @loginnames

DECLARE @listenername nvarchar (100) = ''
if exists (SELECT serverproperty('IsHADREnabled'))
BEGIN
SET @listenername = (SELECT Listener_Name = STUFF((SELECT ', ' + dns_name from sys.availability_group_listeners 
FOR XML PATH('')), 1, 2, ''))
END

DECLARE @emailsubject nvarchar (100) = 'Attn: Matching login not found on Availability replica: '+@replicas
DECLARE @Email_body nvarchar(1000) = 'This is an informational message only:
The below logins from instance: '+ @servername +' were not found on Availability replica: '+@replicas+'

Login Name(s):
'+@loginnames+'

Please create a login on all the availability replicas with the same SID.
Example to generate creation script for a specific login: Exec [dbo].[sp_help_revlogin] <Login_name>

Below is the list of configured Availability Group Listener(s):
'+@listenername+'

Below is the list of configured Availability Replicas:
'+@Replica_List

print @Email_body

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your SQL Server Database Mail Account',
--@recipients = '<Email Address>',
@recipients = '<Email Address>',
--@copy_recipients = '<Email Address>',
@body = @Email_body,
@subject = @emailsubject;




Drop table #Temp
END
FETCH NEXT FROM kursor INTO @replicas 
END

CLOSE kursor 
DEALLOCATE kursor
END
GO

Use the below script to create another stored procedure which will check and e-mail if there are any sql agent jobs which are present on a given availability replica and are missing from any other replica(s).

/********************************************************************** 
* Name: Check_async_SQLAgent_jobs_and_alert.sql
* Created by: Arun Yadav
* Created on: July-25-2017
* Purpose: This script will check and e-mail if there are any sql agent jobs which are present 
* on a given availability replica and are missing from any other replica(s).
* Updated By: 
* Updated on: 
* Comments: 
* Disclaimer: Though the script has been tested, I do not take any guarantees, 
* it is advised to test the scripts again before implementing in production and after
* providing due credit.
**********************************************************************/

use [master]
GO

CREATE PROCEDURE usp_Check_async_SQLAgent_jobs_and_alert
AS
BEGIN
SET NOCOUNT ON

DECLARE @replicas nvarchar(50), @Replica_List nvarchar(100)

SET @Replica_List = (SELECT Replica_name = STUFF((SELECT ', '+ replica_server_name from [sys].[availability_replicas]
FOR XML PATH('')), 1, 2, ''))
--print @Replica_List

DECLARE kursor cursor for 
SELECT replica_server_name from [sys].[availability_replicas]
WHERE replica_server_name <> @@servername

OPEN kursor
FETCH NEXT FROM kursor INTO @replicas
WHILE @@FETCH_STATUS = 0 
BEGIN

DECLARE @login_qry nvarchar(600), @jobcheck nvarchar(200)

SET @jobcheck = 'SELECT count (sj.name)
from msdb.dbo.sysjobs as sj
where sj.enabled = 1
and sj.name not in (SELECT rsj.name from ['+@replicas+'].[msdb].[dbo].[sysjobs] rsj
WHERE rsj.enabled = 1)
'
--print @jobcheck

SET @login_qry = 'SELECT sj.name
from msdb.dbo.sysjobs as sj
where sj.enabled = 1
and sj.name not in (SELECT rsj.name from ['+@replicas+'].[msdb].[dbo].[sysjobs] rsj
WHERE rsj.enabled = 1)
'
--print @login_qry

--if (isnumeric(@jobcheck) <> 0)
if (@jobcheck is not null)
BEGIN
print 'not null'
DECLARE @servername sysname = (SELECT @@servername)
DECLARE @jobnames nvarchar(2000)
CREATE TABLE #temp(name sysname)
INSERT INTO #temp
EXEC (@login_qry)
--SELECT * from #Temp
--Drop table #Temp

SET @jobnames = (SELECT name = STUFF((SELECT ', '+name from #temp 
FOR XML PATH ('')), 1, 2, ''))

DECLARE @listenername nvarchar (100) = ''
if exists (SELECT serverproperty('IsHADREnabled'))
BEGIN
SET @listenername = (SELECT Listener_Name = STUFF((SELECT ', ' + dns_name from sys.availability_group_listeners 
FOR XML PATH('')), 1, 2, ''))
END

DECLARE @emailsubject nvarchar (100) = 'Attn: Matching SQL Agent job not found on Availability replica: '+@replicas
DECLARE @Email_body nvarchar(1000) = 'This is an informational message only:
Below SQL Agent job(s) from instance: '+ @servername +' were not found on Availability replica: '+@replicas+'

Job Name(s):
'+@jobnames+'

These jobs will not run in case the replica '+ @servername +' goes down

Below is the list of configured Availability Group Listener(s):
'+@listenername+'

Below is the list of configured Availability Replicas:
'+@Replica_List

print @Email_body

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your SQL Server Database Mail Account',
--@recipients = '<Email Address>',
@recipients = '<Email Address>',
--@copy_recipients = '<Email Address>',
@body = @Email_body,
@subject = @emailsubject




Drop table #Temp
END
ELSE
BEGIN
PRINT 'Jobs found'
END

FETCH NEXT FROM kursor INTO @replicas 
END

CLOSE kursor 
DEALLOCATE kursor
END
GO
Use the below script to create a SQL Agent job which can either be executed on demand or,  a required schedule can be attached to get notified whenever required.

/********************************************************************** 
* Name: Check_async_SQLAgent_jobs_and_alert.sql
* Created by: Arun Yadav
* Created on: July-25-2017
* Purpose: This script will check and e-mail if there are any sql agent jobs which are present 
* on a given availability replica and are missing from any other replica(s).
* Updated By: 
* Updated on: 
* Comments: 
* Disclaimer: Though the script has been tested, I do not take any guarantees, 
* it is advised to test the scripts again before implementing in production and after
* providing due credit.
**********************************************************************/
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'AlwaysON-Async-Login-Job-DB-check', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=2, 
 @notify_level_netsend=2, 
 @notify_level_page=2, 
 @delete_level=0, 
 @description=N'Step 1 will check and e-mail if there are any databases which are not a part of Availability group.
Step 2 will check and e-mail if there are any logins which are present on a given availability replica and are missing from any other replica(s).
Step 3 will check and e-mail if there are any SQL Agent jobs which are present on a given availability replica and are missing from any other replica(s).', 
 @category_name=N'[Uncategorized (Local)]', 
 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'AlwaysON-Async-Login-Job-DB-check', @server_name = @@servername
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AlwaysON-Async-Login-Job-DB-check', @step_name=N'Check_DBs_not_configured_in_AG', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=3, 
 @on_fail_action=2, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'exec dbo.usp_Check_DBs_not_configured_in_AG', 
 @database_name=N'master', 
 @flags=4
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AlwaysON-Async-Login-Job-DB-check', @step_name=N'Check_async_logins_and_alert', 
 @step_id=2, 
 @cmdexec_success_code=0, 
 @on_success_action=3, 
 @on_fail_action=2, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'exec dbo.usp_Check_async_logins_and_alert', 
 @database_name=N'master', 
 @flags=4
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AlwaysON-Async-Login-Job-DB-check', @step_name=N'Check_async_SQLAgent_jobs_and_alert', 
 @step_id=3, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_fail_action=2, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'exec dbo.usp_Check_async_SQLAgent_jobs_and_alert', 
 @database_name=N'master', 
 @flags=4
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AlwaysON-Async-Login-Job-DB-check', 
 @enabled=1, 
 @start_step_id=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=2, 
 @notify_level_netsend=2, 
 @notify_level_page=2, 
 @delete_level=0, 
 @description=N'Step 1 will check and e-mail if there are any databases which are not a part of Availability group.
Step 2 will check and e-mail if there are any logins which are present on a given availability replica and are missing from any other replica(s).
Step 3 will check and e-mail if there are any SQL Agent jobs which are present on a given availability replica and are missing from any other replica(s).', 
 @category_name=N'[Uncategorized (Local)]', 
 @owner_login_name=N'sa', 
 @notify_email_operator_name=N'', 
 @notify_netsend_operator_name=N'', 
 @notify_page_operator_name=N''
GO

What can be done once an alert is received?
Once an alert is received, the issues have to be fixed manually.
You can also refer to the following article to download and install a plugin provided by sqlskills. Synchronize Availability Group Logins and Jobs

Be Happy! Be the error free DBA. 🙂