MSSQL Server AlwaysON: Get alerted for inconsistencies in logins, jobs and databases

/********************************************************************** 
* Name: AO_Check_create_login_and_linked_server.sql
* Created by: Arun Yadav
* 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
**********************************************************************/
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

/********************************************************************** 
* Name: Check_DBs_not_configured_in_AG.sql
* Created by: Arun Yadav
* 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
**********************************************************************/
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

/********************************************************************** 
* Name: Check_async_logins_and_alert.sql
* Created by: Arun Yadav
* 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
**********************************************************************/

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


/********************************************************************** 
* Name: Check_async_SQLAgent_jobs_and_alert.sql
* Created by: Arun Yadav
* 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
**********************************************************************/


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

/********************************************************************** 
* Name: AlwaysON-Async-Login-Job-DB-check-SQLagentjob.sql
* Created by: Arun Yadav
* Purpose: Use this 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.
* 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
**********************************************************************/

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