AlwaysOn 구성환경에서 Server Role 체크  Job Agent 실행 중지하기

 

·       Version : SQL Server

 

SQL Server에서 AlwaysOn 구성하였을때, Failover  대비하여 Secondary 서버에서도 Primary서버와 동일하게 계정, Job Agent 등이 구성되어 있어야 한다그런데 Secondary 경우 DB 동기화 되고 있는 대기 서버이기 때문에 Job Agent Primary 동일하게 설정하면 Secondary에서 Job 실행  Job Fail 발생한다또한 Secondary에서 일부 Job  경우 실행이 되지 말아야  것들이 있다아래 스크립트는 AlwaysOn  role 확인하여 서버가 Primary 때만 Job Agent 수행되도록 한다각각의 Job Agent  번째 단계에 추가하여 Primary Role 아닐때 Job 수행을 중지하도록 한다.

DECLARE @SERVER_ROLE nvarchar(50)

 

SELECT

    @SERVER_ROLE = A.ROLE_DESC

FROM sys.dm_hadr_availability_replica_states AS A

    INNER JOIN sys.availability_groups AS B ON A.GROUP_ID = B.GROUP_ID

WHERE A.IS_LOCAL = 1

 

IF @SERVER_ROLE <> 'PRIMARY'

       EXEC msdb.dbo.sp_stop_job N'Job Name' ;

 

Secondary에서 Job  실행되었을때, Role 확인하는 부분이 있어 Job 중지된것을 확인할  있다.

 

[참고자료]

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-2017

 

2019-04-10 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, AlwaysOn,  Job Agent, sys.dm_hadr_availability_replica_states, AG Role check



출처: https://sqlmvp.tistory.com/1307?category=618825 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.