T-SQL을 사용하여 탄력적 작업 생성 및 관리

적용 대상: Azure SQL Database

이 문서에서는 T-SQL을 사용하여 탄력적 작업을 시작하기 위한 자습서와 예제를 제공합니다. 탄력적 작업을 사용하면 여러 데이터베이스에서 동시에 하나 이상의 T-SQL(Transact-SQL) 스크립트를 실행할 수 있습니다.

이 문서의 예제에서는 작업 데이터베이스에서 사용할 수 있는 저장 프로시저와 보기를 사용합니다.

이 포괄적인 자습서에서는 여러 데이터베이스에서 쿼리를 실행하는 데 필요한 단계를 알아봅니다:

탄력적 작업 에이전트 만들기

T-SQL(Transact-SQL)은 작업을 생성, 구성, 실행 및 관리하는 데 사용할 수 있습니다.

탄력적 작업 에이전트 생성하기는 T-SQL에서 지원하지 않으므로, 먼저 Azure Portal을 사용하여 탄력적 작업 에이전트를 생성하거나 PowerShell을 사용하여 탄력적 작업 에이전트를 생성해야 합니다.

작업 인증 만들기

탄력적 작업 에이전트는 각 대상 서버 또는 데이터베이스를 인증할 수 있어야 합니다. 작업 에이전트 인증 만들기에서 다루는 것처럼 UMI(사용자가 할당한 관리 ID)와 함께 Microsoft Entra 인증(구 Azure Active Directory)을 사용하는 것이 좋습니다. 이전에는 데이터베이스 범위 자격 증명이 유일한 옵션이었습니다.

작업 실행을 위해 UMI와 함께 Microsoft Entra 인증 사용

UMI(사용자가 할당한 관리 ID)에 대한 Microsoft Entra(구 Azure Active Directory)의 권장 인증 방법을 사용하려면 다음 단계를 수행합니다. 탄력적 작업 에이전트는 Microsoft Entra 인증을 통해 원하는 대상 논리 서버/데이터베이스에 연결합니다.

로그인 및 데이터베이스 사용자 외에도 다음 스크립트에서 추가적인 GRANT 명령에 주목하세요. 이러한 사용 권한은 이 예제 작업에서 선택한 스크립트에 필요합니다. 작업에는 다른 권한이 필요할 수 있습니다. 이 예제에서는 대상 데이터베이스에서 새 테이블을 만들기 때문에 성공적으로 실행하려면 각 대상 데이터베이스의 데이터베이스 사용자에게 적절한 권한이 필요합니다.

각 대상 서버/데이터베이스에서 UMI에 매핑되었으며 포함된 사용자를 만듭니다.

• 탄력적 작업에 논리 서버 또는 풀 대상이 있는 경우 대상 논리 서버의 master 데이터베이스에서 UMI에 매핑되었으며 포함된 사용자를 만들어야 합니다.
• 예를 들어 다음과 같이 master 데이터베이스에서 포함된 데이터베이스 로그인을 만들고 job-agent-UMI UMI(사용자가 할당한 관리 ID)에 따라 사용자 데이터베이스에서 사용자를 만듭니다:

--Create a login on the master database mapped to a user-assigned managed identity (UMI)
CREATE LOGIN [job-agent-UMI] FROM EXTERNAL PROVIDER; 

--Create a user on a user database mapped to a login.
CREATE USER [job-agent-UMI] FROM LOGIN [job-agent-UMI];

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;

• 논리 서버에 로그인이 필요하지 않은 경우 포함된 데이터베이스 사용자를 만들려면 다음을 수행합니다:

--Create a contained database user on a user database mapped to a user-assigned managed identity (UMI)
CREATE USER [job-agent-UMI] FROM EXTERNAL PROVIDER; 

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;

작업 실행에 데이터베이스 범위 자격 증명 사용

데이터베이스 범위 자격 증명은 스크립트 실행을 위해 대상 데이터베이스에 연결하는 데 사용합니다. 자격 증명에는 스크립트를 성공적으로 실행하기 위해 대상 그룹에서 지정한 데이터베이스에 대한 적절한 권한이 필요합니다. 논리 SQL 서버 및/또는 풀 대상 그룹 구성원을 사용하는 경우 작업 실행 시 서버 및/또는 풀을 확장하기 전에 자격 증명을 새로 고치는 데 사용할 자격 증명을 만드는 것이 좋습니다. 데이터베이스 범위 자격 증명은 작업 에이전트 데이터베이스에 만듭니다.

모든 대상 데이터베이스의 로그인 만들기 및 로그인에서 사용자를 만들어 로그인 데이터베이스 권한 부여에 동일한 자격 증명을 사용해야 합니다.

--Connect to the new job database specified when creating the elastic job agent

-- Create a database master key if one does not already exist, using your own password.  
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<password>';  

-- Create two database-scoped credentials.  
-- The credential to connect to the Azure SQL logical server, to execute jobs
CREATE DATABASE SCOPED CREDENTIAL job_credential WITH IDENTITY = 'job_credential',
    SECRET = '<password>';
GO
-- The credential to connect to the Azure SQL logical server, to refresh the database metadata in server
CREATE DATABASE SCOPED CREDENTIAL refresh_credential WITH IDENTITY = 'refresh_credential',
    SECRET = '<password>';
GO

그런 다음 대상 서버에 로그인을 만들거나 대상 데이터베이스에 포함된 데이터베이스 사용자를 만듭니다.

논리 SQL 서버의 master 데이터베이스에 로그인을, 각 사용자 데이터베이스에 사용자를 만듭니다.

--Create a login on the master database
CREATE LOGIN job_credential WITH PASSWORD='<password>';

--Create a user on a user database mapped to a login.
CREATE USER [job_credential] FROM LOGIN [job_credential];

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;

논리 서버에 로그인이 필요하지 않은 경우 포함된 데이터베이스 사용자를 만듭니다. 일반적으로 이 탄력적 작업 에이전트를 사용하여 관리할 단일 데이터베이스가 있는 경우에만 이 작업을 수행합니다.

--Create a contained database user on a user database mapped to a Microsoft Entra account
CREATE USER [job_credential] WITH PASSWORD='<password>';

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;

대상 서버 및 데이터베이스 정의

다음 예제에서는 서버의 모든 데이터베이스에 대해 작업을 실행하는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행하여 대상 그룹 및 대상 구성원을 추가합니다:

-- Connect to the job database specified when creating the job agent

-- Add a target group containing server(s)
EXEC jobs.sp_add_target_group 'ServerGroup1';

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ServerGroup1',
@target_type = 'SqlServer',
@server_name = 'server1.database.windows.net';

--View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1';
SELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';

개별 데이터베이스 제외

다음 예제에서는 MappingDB라는 데이터베이스를 제외한 서버의 모든 데이터베이스에 대해 작업을 실행하는 방법을 보여줍니다.

Microsoft Entra 인증(구 Azure Active Directory)을 사용하는 경우 데이터베이스 범위 자격 증명을 사용할 때만 제공해야 하는 @refresh_credential_name 매개 변수를 생략합니다. 다음 예제에서는 @refresh_credential_name 매개 변수를 주석으로 처리합니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

-- Add a target group containing server(s)
EXEC [jobs].sp_add_target_group N'ServerGroup';
GO

-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
--@refresh_credential_name = N'refresh_credential', --credential required to refresh the databases in a server
@server_name = N'London.database.windows.net';
GO

-- Add a server target member
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@target_type = N'SqlServer',
--@refresh_credential_name = N'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server2.database.windows.net';
GO

--Exclude a database target member from the server target group
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroup',
@membership_type = N'Exclude',
@target_type = N'SqlDatabase',
@server_name = N'server1.database.windows.net',
@database_name = N'MappingDB';
GO

--View the recently created target group and target group members
SELECT * FROM [jobs].target_groups WHERE target_group_name = N'ServerGroup';
SELECT * FROM [jobs].target_group_members WHERE target_group_name = N'ServerGroup';

대상 그룹(풀) 만들기

다음 예제에서는 하나 이상의 탄력적 풀에 있는 모든 데이터베이스를 대상으로 지정하는 방법을 보여줍니다.

Microsoft Entra 인증(구 Azure Active Directory)을 사용하는 경우 데이터베이스 범위 자격 증명을 사용할 때만 제공해야 하는 @refresh_credential_name 매개 변수를 생략합니다. 다음 예제에서는 @refresh_credential_name 매개 변수를 주석으로 처리합니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

-- Add a target group containing pool(s)
EXEC jobs.sp_add_target_group 'PoolGroup';

-- Add an elastic pool(s) target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'PoolGroup',
@target_type = 'SqlElasticPool',
--@refresh_credential_name = 'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server1.database.windows.net',
@elastic_pool_name = 'ElasticPool-1';

-- View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name = N'PoolGroup';
SELECT * FROM jobs.target_group_members WHERE target_group_name = N'PoolGroup';

작업 및 단계 만들기

T-SQL을 사용해 작업 데이터베이스의 시스템 저장 프로시저(jobs.sp_add_job 및 jobs.sp_add_jobstep)를 사용하여 작업을 만듭니다. T-SQL 명령은 SQL Server에서 SQL Agent 작업 및 작업 단계를 만드는 데 필요한 단계와 유사한 구문입니다.

작업 데이터베이스에서 내부 카탈로그 뷰를 업데이트해서는 안 됩니다. 이러한 카탈로그 뷰를 수동으로 변경하면 작업 데이터베이스가 손상되어 오류가 발생할 수 있습니다. 이러한 보기는 읽기 전용 쿼리만을 위한 것입니다. 작업 데이터베이스의 jobs 스키마에 저장 프로시저를 사용할 수 있습니다.

• Microsoft Entra ID 또는 사용자가 할당한 관리 ID에 Microsoft Entra 인증을 사용하여 대상 서버/데이터베이스를 인증하는 경우 @credential_name 인수를 sp_add_jobstep 또는 sp_update_jobstep에 제공하지 않아야 합니다. 마찬가지로 선택적 @output_credential_name 및 @refresh_credential_name 인수를 생략합니다.
• 데이터베이스 범위 자격 증명을 사용하여 대상 서버/데이터베이스를 인증하는 경우 @credential_name 매개 변수가 sp_add_jobstep 또는 sp_update_jobstep에 필요합니다.
  • 예들 들어 @credential_name = 'job_credential'입니다.

다음 예제에서는 탄력적 작업을 사용하여 일반적인 작업을 수행하기 위해 T-SQL을 사용해 작업 및 작업 단계를 만드는 가이드를 제공합니다.

샘플

여러 데이터베이스에 새 스키마 배포

다음 예제에서는 새 스키마를 모든 데이터베이스에 배포하는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

--Add job for create table
EXEC jobs.sp_add_job @job_name = 'CreateTableTest', @description = 'Create Table Test';

-- Add job step for create table
EXEC jobs.sp_add_jobstep @job_name = 'CreateTableTest',
@command = N'IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(''Test''))
CREATE TABLE [dbo].[Test]([TestId] [int] NOT NULL);',
@target_group_name = 'PoolGroup';

기본 제공 매개 변수를 사용하여 데이터 수집

대부분의 데이터 수집 시나리오에서 다음 스크립팅 변수 중 일부를 포함하여 작업 결과를 사후 처리하는 것이 유용할 수 있습니다.

• $(job_name)
• $(job_id)
• $(job_version)
• $(step_id)
• $(step_name)
• $(job_execution_id)
• $(job_execution_create_time)
• $(target_group_name)

예를 들어 동일한 작업 실행의 모든 결과를 함께 그룹화하려면 다음 명령과 같이 $(job_execution_id)를 사용합니다:

@command= N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());'

데이터베이스 성능 모니터링

다음 예제에서는 새 데이터베이스를 만들어 여러 데이터베이스에서 성능 데이터를 수집합니다.

기본적으로 작업 에이전트는 반환된 결과를 저장할 출력 테이블을 만듭니다. 따라서 출력 자격 증명과 관련된 데이터베이스 주체는 최소한의 CREATE TABLE데이터베이스에서 ALTER, SELECT, INSERT, DELETE 는 출력 테이블 혹은 해당 스키마 및SELECTsys.indexes 카탈로그 보기에서 사용 권한을 가지고 있어야 합니다.

테이블을 미리 수동으로 만들려는 경우 다음 속성이 있어야 합니다:

1. 결과 집합에 대한 데이터 형식 및 올바른 이름을 사용하는 열.
2. 고유 식별자의 데이터 형식을 사용하는 internal_execution_id에 대한 추가 열.
3. internal_execution_id열에 있는 IX_<TableName>_Internal_Execution_ID이라는 비클러스터형 인덱스입니다.
4. 데이터베이스에 대한 CREATE TABLE 권한을 제외하고 앞서 나열한 모든 권한.

작업 데이터베이스에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

-- Add a job to collect perf results
EXEC jobs.sp_add_job @job_name ='ResultsJob', @description='Collection Performance data from all customers'

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name = 'ResultsJob',
@command = N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());',
@target_group_name = 'PoolGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.windows.net',
@output_database_name = '<resultsdb>',
@output_table_name = '<output_table_name>';

--Create a job to monitor pool performance

--Connect to the job database specified when creating the job agent

-- Add a target group containing elastic job database
EXEC jobs.sp_add_target_group 'ElasticJobGroup';

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ElasticJobGroup',
@target_type = 'SqlDatabase',
@server_name = 'server1.database.windows.net',
@database_name = 'master';

-- Add a job to collect perf results
EXEC jobs.sp_add_job
@job_name = 'ResultsPoolsJob',
@description = 'Demo: Collection Performance data from all pools',
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name='ResultsPoolsJob',
@command=N'declare @now datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @poolLagMinutes datetime
DECLARE @poolStartTime datetime
DECLARE @poolEndTime datetime
SELECT @now = getutcdate ()
SELECT @startTime = dateadd(minute, -15, @now)
SELECT @endTime = @now
SELECT @poolStartTime = dateadd(minute, -30, @startTime)
SELECT @poolEndTime = dateadd(minute, -30, @endTime)

SELECT elastic_pool_name , end_time, elastic_pool_dtu_limit, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent,
        avg_storage_percent, elastic_pool_storage_limit_mb FROM sys.elastic_pool_resource_stats
        WHERE end_time > @poolStartTime and end_time <= @poolEndTime;
',
@target_group_name = 'ElasticJobGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.windows.net',
@output_database_name = 'resultsdb',
@output_table_name = '<output_table_name>';

작업 실행

다음 예제에서는 작업을 계획하지 않은 수동 작업으로 즉시 시작하는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

-- Execute the latest version of a job
EXEC jobs.sp_start_job 'CreateTableTest';

-- Execute the latest version of a job and receive the execution ID
declare @je uniqueidentifier;
exec jobs.sp_start_job 'CreateTableTest', @job_execution_id = @je output;
select @je;

-- Monitor progress
SELECT * FROM jobs.job_executions WHERE job_execution_id = @je;

작업 실행 예약

다음 예제에서는 추후에 15분마다 작업을 정기적으로 실행하도록 예약하는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_update_job
@job_name = 'ResultsJob',
@enabled=1,
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;

작업 정의 보기

다음 예제에서는 현재 작업 정의를 보는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

-- View all jobs
SELECT * FROM jobs.jobs;

-- View the steps of the current version of all jobs
SELECT js.* FROM jobs.jobsteps js
JOIN jobs.jobs j
  ON j.job_id = js.job_id AND j.job_version = js.job_version;

-- View the steps of all versions of all jobs
SELECT * FROM jobs.jobsteps;

작업 실행 상태 모니터링

다음 예제에서는 모든 작업에 대한 실행 상태 세부 정보를 보는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

--View top-level execution status for the job named 'ResultsPoolJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob' and step_id IS NULL
ORDER BY start_time DESC;

--View all top-level execution status for all jobs
SELECT * FROM jobs.job_executions WHERE step_id IS NULL
ORDER BY start_time DESC;

--View all execution statuses for job named 'ResultsPoolsJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob'
ORDER BY start_time DESC;

-- View all active executions
SELECT * FROM jobs.job_executions
WHERE is_active = 1
ORDER BY start_time DESC;

작업 취소

다음 예제에서는 작업 실행 ID를 검색한 다음 작업 실행을 취소하는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

-- View all active executions to determine job execution ID
SELECT * FROM jobs.job_executions
WHERE is_active = 1 AND job_name = 'ResultPoolsJob'
ORDER BY start_time DESC;
GO

-- Cancel job execution with the specified job execution ID
EXEC jobs.sp_stop_job '01234567-89ab-cdef-0123-456789abcdef';

이전 작업 기록 삭제

다음 예제에서는 특정 날짜 이전의 작업 기록을 삭제하는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

-- Delete history of a specific job's executions older than the specified date
EXEC jobs.sp_purge_jobhistory @job_name='ResultPoolsJob', @oldest_date='2016-07-01 00:00:00';

--Note: job history is automatically deleted if it is >45 days old

작업 및 관련된 모든 작업 기록 삭제

다음 예제에서는 작업 및 관련된 모든 작업 기록을 삭제하는 방법을 보여줍니다.

job_database에 연결하고 다음 명령을 실행합니다:

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_delete_job @job_name='ResultsPoolsJob';
EXEC jobs.sp_purge_jobhistory @job_name='ResultsPoolsJob';

--Note: job history is automatically deleted if it is >45 days old

작업 저장 프로시저

작업 데이터베이스에 있는 저장 프로시저는 다음과 같습니다. 이름은 비슷하지만 SQL Server 에이전트 서비스에 사용하는 시스템 저장 프로시저와는 뚜렷하게 다릅니다.

작업 보기

작업 데이터베이스에서 사용할 수 있는 보기는 다음과 같습니다.

다음 단계