/**********************************************************************
* 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 = '' -- 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 = '' -- 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 = '',
@recipients = '',
--@copy_recipients = '',
@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] 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 = '',
@recipients = '',
--@copy_recipients = '',
@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 = '',
@recipients = '',
--@copy_recipients = '',
@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: 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 [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 | 
