Azure Logic Apps 워크플로에서 저장 프로시저 실행 시 큰 SQL 결과 집합 및 시간 제한을 제어하기

적용 대상: Azure Logic Apps(사용량 + 표준)

SQL 데이터베이스를 사용하는 비즈니스 작업을 보다 쉽게 자동화하기 위해 워크플로는 Azure Logic Apps에서 사용할 워크플로에 많은 백 엔드 기능을 제공하는 SQL Server 커넥터 작업을 사용할 수 있습니다.

경우에 따라 워크플로에서 큰 결과 집합을 처리해야 할 수 있습니다. 이러한 결과 집합이 너무 커서 SQL Server 커넥터 작업이 모든 결과를 동시에 반환하지 않을 수 있습니다. 다른 상황에서는 결과 집합의 크기와 구조를 더 자세히 제어할 수 있습니다. 원하는 방식으로 결과를 구성하려면 저장 프로시저를 만들 수 있습니다.

예를 들어 SQL Server 커넥터 작업이 여러 행을 가져오거나 삽입하는 경우 워크플로는 이러한 제한 내에서 작동하는 Until 루프 를 사용하여 이러한 행을 반복할 수 있습니다. 워크플로에서 수천 또는 수백만 개의 행을 처리해야 하는 경우 SQL Database에 대한 SQL Server 커넥터 작업 호출로 인한 비용을 최소화하려고 합니다. 자세한 내용은 SQL 커넥터를 사용하여 대량 데이터 처리를 참조하세요.

이 가이드에서는 SQL Server 커넥터 작업을 사용하여 큰 결과 집합을 처리할 때 크기, 구조 및 시간 제한을 제어하는 방법을 보여 줍니다.

저장 프로시저 실행에 대한 제한 시간 제한

SQL Server 커넥터에는 2분 미만의 시간 제한으로 저장 프로시저 실행 작업이 있습니다. 일부 저장 프로시저는 완료하는 데 이 제한보다 오래 걸릴 수 있으므로 504 시간 제한 오류가 발생합니다. 경우에 따라 장기 실행 프로세스는 이 목적을 위해 명시적으로 저장 프로시저로 코딩됩니다. 시간 제한으로 인해 Azure Logic Apps에서 이러한 절차를 호출하면 문제가 발생할 수 있습니다.

SQL Server 커넥터 작업은 기본적으로 비동기 모드를 지원하지 않습니다. 이 제한을 해결하려면 다음 항목을 사용하여 이 모드를 시뮬레이션합니다.

• SQL 완료 트리거
• 네이티브 SQL 패스스루 쿼리
• 상태 테이블
• 서버 쪽 작업

예를 들어 다음과 같은 장기 실행 저장 프로시저가 있다고 가정합니다. 실행을 완료하려면 프로시저가 제한 시간을 초과합니다. 저장 프로시저 실행이라는 SQL Server 커넥터 작업을 사용하여 워크플로에서 이 저장 프로시저를 실행하면 HTTP 504 게이트웨이 시간 제한 오류가 발생합니다.

CREATE PROCEDURE [dbo].[WaitForIt]
   @delay char(8) = '00:03:00'
AS
BEGIN
   SET NOCOUNT ON;
   WAITFOR DELAY @delay
END

저장 프로시저를 직접 호출하는 대신 작업 에이전트를 사용하여 백그라운드에서 프로시저를 비동기적으로 실행할 수 있습니다. 입력 및 출력을 상태 테이블에 저장한 다음 워크플로를 통해 액세스하고 관리할 수 있습니다. 입력 및 출력이 필요하지 않거나 저장 프로시저의 테이블에 결과를 이미 쓰고 있는 경우 이 방법을 간소화할 수 있습니다.

클라우드 기반 SQL Server에 대한 작업 에이전트를 사용하여 백그라운드에서 프로시저를 비동기적으로 실행하려면 단계에 따라 Azure SQL Database용 Azure 탄력적 작업 에이전트를 만들고 사용합니다.

온-프레미스 SQL Server 및 Azure SQL Managed Instance의 경우 대신 SQL Server 에이전트를 만들고 사용합니다 . 기본 단계는 Azure SQL Database에 대한 작업 에이전트를 설정하는 것과 동일하게 유지합니다.

Azure SQL Database에 대한 작업 에이전트 만들기

Azure SQL Database에 대한 저장 프로시저를 실행할 수 있는 작업 에이전트를 만들려면 Azure Elastic Job Agent를 만들고 사용합니다. 그러나 이 작업 에이전트를 만들려면 먼저 Azure Elastic Job Agent 설명서에 설명된 대로 사용 권한, 그룹 및 대상을 설정해야 합니다. 또한 다음 섹션에 설명된 대로 대상 데이터베이스에 지원 상태 테이블을 만들어야 합니다.

작업 에이전트를 만들려면 Azure Portal에서 이 작업을 수행합니다. 이 접근법은 에이전트 데이터베이스라고도 하는 에이전트 전용 데이터베이스에 여러 저장 프로시저를 추가합니다. 그런 다음 대상 데이터베이스에서 저장 프로시저를 실행하고 완료되면 출력을 캡처하는 작업 에이전트를 만들 수 있습니다.

매개 변수를 등록하고 입력을 저장하기 위한 상태 테이블 만들기

SQL 에이전트 작업은 입력 매개 변수를 허용하지 않습니다. 대신 대상 데이터베이스에서 매개 변수를 등록하고 저장 프로시저 호출에 사용할 입력을 저장하는 상태 테이블을 만듭니다. 모든 에이전트 작업 단계는 대상 데이터베이스에 대해 실행되지만 작업의 저장 프로시저는 에이전트 데이터베이스에 대해 실행됩니다.

상태 테이블을 만들려면 다음 스키마를 사용합니다.

CREATE TABLE [dbo].[LongRunningState](
   [jobid] [uniqueidentifier] NOT NULL,
   [rowversion] [timestamp] NULL,
   [parameters] [nvarchar](max) NULL,
   [start] [datetimeoffset](7) NULL,
   [complete] [datetimeoffset](7) NULL,
   [code] [int] NULL,
   [result] [nvarchar](max) NULL,
   CONSTRAINT [PK_LongRunningState] PRIMARY KEY CLUSTERED
      (   [jobid] ASC
      )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

결과 테이블은 SMSS(SQL Server Management Studio)에서 다음과 같습니다.

성능이 양호하고 작업 에이전트가 연결된 레코드를 찾을 수 있는지 확인하기 위해 테이블은 작업 실행 ID(jobid)를 기본 키로 사용합니다. 원하는 경우 입력 매개 변수에 대한 개별 열을 추가할 수도 있습니다. 앞에서 설명한 스키마는 일반적으로 여러 매개변수를 처리할 수 있지만, NVARCHAR(MAX) 함수가 계산한 크기로 제한됩니다.

저장 프로시저를 실행하는 최상위 작업 만들기

장기 실행 저장 프로시저를 실행하려면 에이전트 데이터베이스에서 이 최상위 작업 에이전트를 만듭니다.

EXEC jobs.sp_add_job 
   @job_name='LongRunningJob',
   @description='Execute Long-Running Stored Proc',
   @enabled = 1

저장 프로시저를 매개 변수화, 실행 및 완료하는 단계를 작업에 추가합니다. 기본적으로 업무 단계는 12시간 후에 타임아웃됩니다. 저장 프로시저에 더 많은 시간이 필요하거나 프로시저가 더 일찍 시간 초과되도록 하려면 step_timeout_seconds 매개 변수를 초 단위로 지정된 다른 값으로 변경할 수 있습니다. 기본적으로 각 재시도 사이에 대기 시간을 두고 10번의 자동 재시도가 설정되어 있으며, 이를 활용하여 유리하게 사용할 수 있습니다.

추가하는 단계는 다음과 같습니다.

1. 매개 변수가 테이블에 나타날 LongRunningState 때까지 기다립니다.

   첫 번째 단계는 매개 변수가 LongRunningState 테이블에 추가되기를 기다리는 것입니다. 이는 작업이 시작된 직후 발생합니다. 작업 실행 ID (jobid)가 LongRunningState 테이블에 추가되지 않으면 단계가 단순히 실패합니다. 기본 다시 시도 또는 백오프 시간 제한은 다음과 같이 대기합니다.

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name= 'Parameterize WaitForIt',
      @step_timeout_seconds = 30,
      @command= N'
         IF NOT EXISTS(SELECT [jobid] FROM [dbo].[LongRunningState]
            WHERE jobid = $(job_execution_id))
            THROW 50400, ''Failed to locate call parameters (Step1)'', 1',
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

2. 상태 테이블에서 매개 변수를 쿼리하고 저장 프로시저에 전달합니다. 또한 이 단계에서는 백그라운드에서 프로시저를 실행합니다.

   저장 프로시저에 매개 변수가 필요하지 않은 경우 저장 프로시저를 직접 호출합니다. 그렇지 않으면, @timespan 매개 변수를 전달하기 위해 @callparams을 사용하세요. 또한, 더 많은 매개 변수를 전달하기 위해 @callparams을 확장할 수 있습니다.

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name='Execute WaitForIt',
      @command=N'
         DECLARE @timespan char(8)
         DECLARE @callparams NVARCHAR(MAX)
         SELECT @callparams = [parameters] FROM [dbo].[LongRunningState]
            WHERE jobid = $(job_execution_id)
         SET @timespan = @callparams
         EXECUTE [dbo].[WaitForIt] @delay = @timespan', 
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

3. 작업을 완료하고 결과를 기록합니다.

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name='Complete WaitForIt',
      @command=N'
         UPDATE [dbo].[LongRunningState]
            SET [complete] = GETUTCDATE(),
               [code] = 200,
               [result] = ''Success''
            WHERE jobid = $(job_execution_id)',
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

작업을 시작하고 매개 변수 전달

작업을 시작하려면 SQL 쿼리 실행 작업과 함께 패스스루 네이티브 쿼리를 사용하고, 작업의 매개변수를 즉시 상태 테이블로 푸시하십시오. 대상 테이블의 jobid 특성에 대한 입력을 제공하기 위해 Azure Logic Apps는 이전 작업의 테이블 출력을 반복하는 For 각 루프를 추가합니다. 각 작업 실행 ID에 대해 명명된 동적 데이터 출력을 사용하여 작업의 매개 변수를 추가하여 압축을 풀고 대상 저장 프로시저에 전달하는 ResultSets JobExecutionId 작업을 실행합니다.

작업이 완료되면 테이블 LongRunningState 을 업데이트합니다. 다른 워크플로에서 항목이 수정될 때라는 트리거를 사용하여 결과를 트리거할 수 있습니다. 출력이 필요하지 않거나 출력 테이블을 모니터링하는 트리거가 이미 있는 경우 이 부분을 건너뛸 수 있습니다.

SQL Server 또는 Azure SQL Managed Instance에 대한 작업 에이전트 만들기

온-프레미스 SQL Server 및 Azure SQL Managed Instance의 경우 SQL Server 에이전트를 만들고 사용합니다. Azure SQL Database의 클라우드 기반 작업 에이전트에 비해 일부 관리 세부 정보는 다르지만 기본 단계는 동일하게 유지됩니다.

다음 단계

• Azure Logic Apps의 워크플로에서 SQL 데이터베이스에 연결