AlwaysOn with Basic 도입 방안

Alwayson with basic 제약사항 (개선 방안)

제약사항

• 1. AG그룹 당 DB 1개만 구성 가능
  • 클라이언트측에서 장애조치시 리다이렉션 위해서는 타겟 DB의 주 노드로 호스팅해 줄 AG 리스너가 필요 
  • DB 개수만큼 AG 그룹 생성 및 AG 리스너에 대한 고정 IP 필요
• 2. SQL 로그인 SID 불일치시 Failover 수행 후 사용자-로그인 맵핑 X

개선방안

•  제약 사항 1에 의한 AG 단위 Failover시 클라이언트 리다이렉션 이슈 & AG 리스너 IP 소모
  • 주 노드 호스팅용 AG 구성 및 리스너 1개 구성
  • 하나의 AG Failover 이벤트시 전체 Failover 수행
• 제약 사항 2에 대한 신규 계정 자동 생성 배치 생성

모니터링 방안

• 상태 체크 배치
  • DB/AG상태,  Listener, 클러스터 노드 상태 비정상 시 알림 (주기적, 1분)
• 장애 및 크리티컬 이슈 경고 (검토 중)
  • 이벤트 ID 캐치하여 알림 발생 ( 이벤트 발생시)

 

개선 방안 적용 예시

하나의 AG Failover 이벤트시 전체 Failover 수행 (보조 서버)

SP 생성 (참고 : https://mozi.tistory.com/410)

--https://mozi.tistory.com/410
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
create Procedure [dbo].[AG_Auto_Failover_SP]
as
begin
DECLARE @cnt SMALLINT
DECLARE @sql VARCHAR(8000)
DECLARE @seq SMALLINT
 
IF OBJECT_ID('AG_Auto_Failover') IS NOT NULL
BEGIN
DROP TABLE AG_Auto_Failover
END
 
--1. Group Name 을 저장할 임시 테이블 생성
CREATE TABLE AG_Auto_Failover ( cnt SMALLINT IDENTITY(1,1), AGName varchar(30) )
 
--2.다른 노드에 Online 되어 있는 AG Name Insert
INSERT INTO AG_Auto_Failover(AGName)
SELECT
b.Name
FROM sys.dm_hadr_availability_replica_states as a
INNER JOIN sys.availability_groups as b on a.group_id = b.group_id
WHERE is_local = 1
AND role =2
GROUP by b.name
 
--3.AG Failover 수행
SELECT @cnt = max(cnt) FROM AG_Auto_Failover
SET @seq = 1
IF @cnt <> 0
BEGIN
WHILE(@seq <= @cnt)
BEGIN
SET @SQL = ''
SET @SQL = 'USE MASTER '
 
SELECT @SQL = @SQL + 'ALTER AVAILABILITY GROUP ' + '[' + AGName + ']' + ' FAILOVER'
FROM AG_Auto_Failover
WHERE cnt = @seq
EXEC(@sql)
SET @seq = @seq + 1
END
END
 
--4. 임시 테이블 삭제
DROP TABLE AG_Auto_Failover
END

배치생성

USE [msdb]
GO
 
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AG_Auto_Failover',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'설명이 없습니다.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [failoverSP] Script Date: 2021-06-11 오전 12:27:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'failoverSP',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec [AG_Auto_Failover_SP]
go',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

경고 생성

신규 계정 자동 생성 배치 생성 (보조 서버)

SP 생성 (참고 : https://burning-dba.tistory.com/80)

--https://burning-dba.tistory.com/80
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_hexadecimal] Script Date: 2021-06-11 오전 12:33:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_hexadecimal]
@binvalue varbinary ( 256),
@hexvalue varchar ( 514) OUTPUT
AS
DECLARE @charvalue varchar ( 514 )
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char( 16 )
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH ( @binvalue )
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length )
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT( int , SUBSTRING (@binvalue , @i, 1 ))
SELECT @firstint = FLOOR( @tempint /16 )
SELECT @secondint = @tempint - ( @firstint *16 )
SELECT @charvalue = @charvalue +
SUBSTRING (@hexstring , @firstint + 1, 1 ) +
SUBSTRING (@hexstring , @secondint + 1, 1 )
SELECT @i = @i + 1
END
 
SELECT @hexvalue = @charvalue
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_help_revlogin] Script Date: 2021-06-11 오전 12:33:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar ( 1 )
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary ( 514 )
DECLARE @PWD_string varchar ( 1000 )
DECLARE @SID_varbinary varbinary ( 85 )
DECLARE @SID_string varchar ( 514 )
DECLARE @tmpstr varchar ( 1024 )
DECLARE @is_policy_checked varchar ( 3 )
DECLARE @is_expiration_checked varchar ( 3 )
 
-- Password Varbinary를 임시적으로 담기 위한 테이블 추가
DECLARE @temp_Login TABLE (PW varbinary(514))
DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
 
-- NODE1 서버의 sys.server_principals , sys.syslogins 테이블을 조회하여 Standby 서버에 없는 계정 조회
SELECT sp . sid, sp. name , sp .type , sp . is_disabled, sp. default_database_name , l .hasaccess , l . denylogin
FROM NODE1.master.sys.server_principals sp
LEFT JOIN sys. server_principals p ON sp.name = p.name
LEFT JOIN NODE1.master.sys. syslogins l on ( l .name = sp .name )
WHERE sp . type IN ( 'S', 'G', 'U' ) AND sp. name <> 'sa' and p.name is null and sp.name Not like 'NODE1\%'
 
ELSE
DECLARE login_curs CURSOR FOR
 
-- Standby 서버에 존재하지 않는 계정을 커서에 넣음
SELECT p . sid, p. name , p .type , p . is_disabled, p. default_database_name , l .hasaccess , l . denylogin
FROM NODE1.master.sys .server_principals p
LEFT JOIN NODE1.master.sys. syslogins l ON ( l .name = p .name )
WHERE p . type IN ( 'S', 'G', 'U' ) AND p. name = @login_name
 
OPEN login_curs
 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb , @hasaccess , @denylogin
IF (@@fetch_status = - 1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN - 1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT ( varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> - 1)
BEGIN
IF ( @@fetch_status <> -2 )
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G' , 'U' ))
BEGIN -- NT authenticated account/group
 
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
 
-- NODE1 서버의 계정 Password Varbinary 값 가져와서 변환
INSERT INTO @temp_Login
SELECT PW FROM Openquery([NODE1],'SELECT CAST ( LOGINPROPERTY ( name , ''PasswordHash'' ) AS varbinary ( 514) )as [PW] ,name FROM sys.syslogins ')
WHERE name = @name
 
-- OPENQuery를 통해 즉시 담기가 불가능하므로, 임시 테이블에 저장된 정보를 @PWD_varbinary 변수에 저장
SELECT @PWD_varbinary = CONVERT(varbinary ( 514 ), PW)
FROM @temp_Login
 
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
 
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys . sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys .sql_logins WHERE name = @name
 
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
 
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1 )
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME ( @name )
END
ELSE IF ( @hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME ( @name )
END
IF (@is_disabled = 1 )
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME ( @name ) + ' DISABLE'
END
EXEC (@tmpstr)
PRINT @tmpstr
END
 
-- 커서에 새로운 정보를 담기 위해 임시 테이블 초기화
DELETE @temp_Login
 
FETCH NEXT FROM login_curs INTO @SID_varbinary , @name , @type , @is_disabled , @defaultdb , @hasaccess , @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

배치 생성

USE [msdb]
GO
 
/****** Object: Job [Sync_Login] Script Date: 2021-06-11 오전 12:32:02 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2021-06-11 오전 12:32:02 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Sync_Login',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'설명이 없습니다.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'JYOH\sqlserver', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [sync] Script Date: 2021-06-11 오전 12:32:02 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sync',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'sp_help_revlogin',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'5q',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20210609,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'1c0fd64c-d586-48b3-b05a-92984310a052'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

 

모니터링 방안 적용 예시

상태 체크 배치 (주/보조 서버)

SP 생성

--https://docs.microsoft.com/ko-kr/sql/database-engine/availability-groups/windows/monitor-availability-groups-transact-sql?view=sql-server-ver15
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- [AG_Monitoring_SP] 2
ALTER Procedure [dbo].[AG_Monitoring_SP]
@param int
as
begin
--1: 클러스터 맴버 (노드/쿼럼)의 상태 체크
--2: AG Listener 상태
--3: AG/DB상태
 
declare @rstTxt nvarchar(max) = ''
,@error_no int =0,@row_count int , @error_message nvarchar(max)
 
 
 
if( 1 =( SELECT 1
FROM sys.dm_hadr_availability_replica_states as a
INNER JOIN sys.availability_groups as b on a.group_id = b.group_id
INNER JOIN sys.availability_group_listeners as c on a.group_id = c.group_id
WHERE is_local = 1 AND role =2) --AG 리스너를 기준으로 보조서버이면 미수행
)
begin
print'Not Primary'
return 0
end
 
 
if (@param =1)
begin
select @rstTxt = @rstTxt + '#| 멤버명: '+member_name + ' | 역할: '+member_type_desc +' | 상태: ' +member_state_desc +'|#' + char(10)+char(13)+ char(10)+char(13)
from sys.dm_hadr_cluster_members
where member_state_desc !='UP'
 
SELECT @error_no = @@error ,@row_count = @@rowcount
IF (@error_no <> 0 or @row_count <> 0)
BEGIN
SET @error_message = ' [클러스터 맴버 상태 이상] ' + char(10)+char(13)+ @rstTxt
SET @error_no = 1
GOTO OnError
END
ELSE
PRINT '클러스터 맴버 상태 정상'
end
 
 
 
if (@param =2)
begin
select @rstTxt=@rstTxt+ '#| 리스너명: '+ agl.dns_name+ ' | IP: '+aglia.ip_address+' | 상태: '+aglia.state_desc +'|#' + char(10)+char(13)+ char(10)+char(13)
from sys.availability_group_listener_ip_addresses aglia
left join sys.availability_group_listeners agl
on aglia.listener_id = agl.listener_id
where state_desc !='ONLINE'
 
SELECT @error_no = @@error ,@row_count = @@rowcount
IF (@error_no <> 0 or @row_count <> 0)
BEGIN
SET @error_message = ' [AG Listener 상태 이상] ' + char(10)+char(13)+ @rstTxt
SET @error_no = 1
GOTO OnError
END
ELSE
PRINT 'AG Listener 상태 정상'
end
 
 
 
if (@param =3)
begin
select @rstTxt=@rstTxt+ '#| AG명: '+ag.name+ ' | 서버명: '+ ar.replica_server_name + ' | DB명: '+rcs.database_name
+ ' | 동기화: '+drs.synchronization_state_desc+ ' | 동기화 상태: '+ drs.synchronization_health_desc
+ ' | 데이터손실: '+ CASE rcs.is_failover_ready WHEN 0 THEN 'Data Loss' WHEN 1 THEN 'No Data Loss' ELSE '' END
+ ' | DB상태: '+ drs.database_state_desc collate Korean_Wansung_CI_AS+'|#' + char(10)+char(13)+ char(10)+char(13)
from sys.dm_hadr_database_replica_states drs
inner join sys.availability_groups ag on drs.group_id=ag.group_id
inner join sys.availability_replicas ar on drs.replica_id = ar.replica_id
and drs.group_id = ar.group_id
inner join sys.dm_hadr_database_replica_cluster_states rcs on drs.replica_id = rcs.replica_id
where 1=1 and drs.is_primary_replica =1
and (drs.synchronization_state_desc != 'SYNCHRONIZED' or drs.synchronization_health_desc!='HEALTHY'
or rcs.is_failover_ready!=1 or drs.database_state_desc!='ONLINE')
group by ag.name, ar.replica_server_name,rcs.database_name, drs.synchronization_state_desc,
drs.synchronization_health_desc, rcs.is_failover_ready,drs.is_primary_replica, drs.database_state_desc
 
SELECT @error_no = @@error ,@row_count = @@rowcount
IF (@error_no <> 0 or @row_count <> 0)
BEGIN
SET @error_message = ' [AG/DB상태 이상] ' + char(10)+char(13)+ @rstTxt
SET @error_no = 1
GOTO OnError
END
ELSE
PRINT 'AG/DB상태 정상'
end
 
 
 
 
/*******************************************************************************
* WORK COMPLETE
********************************************************************************/
IF @@TRANCOUNT > 0 COMMIT TRAN
RETURN 0
 
/*******************************************************************************
* Error Handling
********************************************************************************/
OnError:
IF @@TRANCOUNT > 0 ROLLBACK TRAN
--PRINT @error_message
RAISERROR(@error_message, 16, 1)
RETURN (@error_no*(-1))
 
end

배치 생성

USE [msdb]
GO
 
/****** Object: Job [AG_MONITORING] Script Date: 2021-06-23 오후 11:30:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2021-06-23 오후 11:30:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AG_MONITORING',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'설명이 없습니다.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'JYOH\sqlserver', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [클러스터 맴버 (노드/쿼럼)의 상태 체크] Script Date: 2021-06-23 오후 11:30:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'클러스터 맴버 (노드/쿼럼)의 상태 체크',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec [AG_Monitoring_SP] 1',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [AG Listener 상태] Script Date: 2021-06-23 오후 11:30:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'AG Listener 상태',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec [AG_Monitoring_SP] 2',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [AG/DB상태] Script Date: 2021-06-23 오후 11:30:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'AG/DB상태',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec [AG_Monitoring_SP] 3',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1분',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20210623,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'cedd094d-0c6f-4a97-bb58-64457cfbccbb'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

실행 예시