Availability Group – SQL Server Agent Job to run only on Always On Primary Replica

-> I had a requirement to execute jobs only in primary replica.

-> To all jobs that should only be executed on Always On primary replica. I added 1 step as the first step of the job and another step as last step of that job.

-> 1st step of these jobs should execute below TSQL Query,

if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = ''
and ars.is_local = 1) = 'PRIMARY'
begin
Print 'This is Primary Replica'
end
else
begin
RAISERROR('This job should not run in Secondary',16,1)
end

-> For 1st step,

On success action : Go to the next step
On failure action: Go to step: [n] Last_Step ( It should basically go to the last step)

-> Last step of the job should execute the below TSQL,

print 'Job_Completion'

-> For Last step,

On success action : Quit the job reporting Success
On failure action: Quit the job reporting failure

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Viewing Allocation Unit Size of a NTFS Disk

-> Open an “Administrator – Windows Powershell ISE”.

-> Execute below command,

fsutil fsinfo ntfsinfo C:

-> Output below,

Fsutil_Alloc_Size.PNG

-> “Bytes per Cluster” provides us the Allocation unit size. In my case it is 4 KB.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

Database Backup and Maintenance job to use secondary replica on a SQL Instance with Always On Availability group

Environment

Backup_Setup.PNG

Requirement

-> Database backup should happen on secondary replica.
-> Integrity checks should happen on secondary replica.

Alwayson Availability Group Backup Preference Setting

Backup_Preference.png

Solution

-> We will be using Ola Hallengren’s SQL Server Maintenance Solutions.

-> Execute Maintenance.sql on SQL Server Instance JBSERVER1 and JBSERVER2.

-> On JBSERVER1 modify SQL Server Agent job that performs User Database – Full backup to execute below,

 	EXECUTE [dbo].[DatabaseBackup]
	@Databases = 'USER_DATABASES', -- All User DB's included
	@Directory = 'G:\SQL\BACKUP',  -- Database backup Location
	@CopyOnly='Y', -- Copy only backups
	@BackupType = 'FULL', -- Full database backup
	@Verify = 'Y', -- Verify database backup set to YES
	@CleanupTime = 74, -- Cleanup time of 74 Hours
	@CheckSum = 'Y',@LogToTable = 'Y'

-> On JBSERVER1 modify SQL Server Agent job that performs User Database – Log backup to execute below,

	EXECUTE [dbo].[DatabaseBackup]
	@Databases = 'USER_DATABASES', -- All Appropriate User DB's included
	@Directory = 'G:\SQL\BACKUP',  -- Database backup Location
	@BackupType = 'LOG',-- Log Backups
	@Verify = 'Y', -- Verify database backup set to YES
	@CleanupTime = 74, -- Cleanup time of 74 Hours
	@CheckSum = 'Y',@LogToTable = 'Y'

-> On JBSERVER1 modify SQL Server Agent job that performs User Database – Integrity Check to execute below,

	EXECUTE [dbo].[DatabaseIntegrityCheck]
	@Databases = 'USER_DATABASES', -- All User DB's included
	@AvailabilityGroupReplicas='PREFERRED_BACKUP_REPLICA', -- Preferred backup replica will be checked
        @LogToTable = 'Y'

-> Script the modified jobs in JBSERVER1 and create them on JBSERVER2 also.

-> Try executing each job and make sure the backups and Integrity checks are running on the secondary replica. Check the backup file location and also CommandLog table to confirm.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.