Membuat dan mengelola pekerjaan elastis dengan menggunakan T-SQL

Berlaku untuk:Azure SQL Database

Artikel ini menyediakan tutorial dan contoh untuk mulai bekerja dengan pekerjaan elastis menggunakan T-SQL. Pekerjaan elastis memungkinkan berjalannya satu atau beberapa skrip Transact-SQL (T-SQL) secara paralel di banyak database.

Contoh dalam artikel ini menggunakan prosedur dan tampilan tersimpan yang tersedia dalam database pekerjaan.

Dalam tutorial end-to-end ini, Anda mempelajari langkah-langkah yang diperlukan untuk menjalankan kueri di beberapa database:

• Membuat agen pekerjaan elastis
• Membuat kredensial pekerjaan sehingga pekerjaan dapat menjalankan skrip pada targetnya
• Tentukan target (server, kumpulan elastis, database) yang ingin Anda gunakan untuk menjalankan pekerjaan
• Buat kredensial berlingkup basis data dalam basis data target agar agen dapat tersambung dan menjalankan pekerjaan
• Membuat pekerjaan
• Menambahkan langkah-langkah pekerjaan ke dalam pekerjaan
• Mulai menjalankan pekerjaan
• Memantau pekerjaan

Membuat agen pekerjaan elastis

Transact-SQL (T-SQL) dapat digunakan untuk membuat, mengonfigurasi, menjalankan, dan mengelola pekerjaan.

Membuat agen pekerjaan elastis tidak didukung di T-SQL, jadi Anda harus terlebih dahulu membuat agen pekerjaan elastis dengan menggunakan portal Azure, atau membuat agen pekerjaan elastis dengan menggunakan PowerShell.

Membuat autentikasi pekerjaan

Agen pekerjaan elastis harus dapat mengautentikasi ke setiap server atau database target. Seperti yang dibahas dalam Membuat autentikasi agen pekerjaan, pendekatan yang direkomendasikan adalah menggunakan autentikasi Microsoft Entra (sebelumnya Azure Active Directory) dengan identitas terkelola yang ditetapkan pengguna (UMI). Sebelumnya, kredensial lingkup basis data adalah satu-satunya opsi.

Menggunakan autentikasi Microsoft Entra dengan UMI untuk eksekusi pekerjaan

Untuk menggunakan metode autentikasi Microsoft Entra (sebelumnya Azure Active Directory) yang direkomendasikan ke identitas terkelola (UMI) yang ditetapkan pengguna, ikuti langkah-langkah ini. Agen pekerjaan elastis terhubung ke server logis target/database yang diinginkan melalui autentikasi Microsoft Entra.

Selain pengguna login dan database, perhatikan penambahan perintah GRANT dalam skrip berikut. Izin ini diperlukan untuk skrip yang kami pilih untuk pekerjaan contoh berikut. Pekerjaan Anda mungkin memerlukan izin yang berbeda. Karena contoh membuat tabel baru dalam database yang ditargetkan, pengguna database di setiap database target memerlukan izin yang tepat agar berhasil dijalankan.

Di setiap server atau database target, buat pengguna terbatas yang dipetakan ke UMI.

• Jika pekerjaan elastis memiliki target server logis atau kumpulan, Anda harus membuat pengguna yang ada dan dipetakan ke UMI dalam database master pada server logis target.
• Misalnya, untuk membuat login database terbatas di database master, dan pengguna di database pengguna, berdasarkan identitas terkelola yang diberikan pengguna (UMI) bernama job-agent-UMI:
• Untuk menjalankan skrip T-SQL ini, gunakan autentikasi Microsoft Entra untuk koneksi database Anda.

-- Connect to the master database of the Azure SQL logical instance of job agent
-- To run these T-SQL scripts, use Microsoft Entra authentication for your database connection.

-- 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 the master database mapped to a login
CREATE USER [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 job-agent-UMI;
GRANT CREATE TABLE TO job-agent-UMI;

• Untuk membuat pengguna database mandiri jika login tidak diperlukan di server logis:

-- 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 job-agent-UMI;
GRANT CREATE TABLE TO job-agent-UMI;

Gunakan kredensial cakupan database untuk menjalankan pekerjaan

Kredensial khusus database digunakan untuk menghubungkan ke database target Anda untuk eksekusi skrip. Kredensial memerlukan izin yang sesuai pada database yang ditentukan oleh grup target agar skrip dapat dijalankan dengan sukses. Saat menggunakan server SQL logis dan/atau anggota grup target atau kumpulan, disarankan untuk membuat kredensial yang akan digunakan untuk menyegarkan kredensial sebelum server dan/atau kumpulan diperluas pada saat pelaksanaan pekerjaan. Kredensial dengan cakupan database dibuat pada database agen tugas.

Kredensial yang sama harus digunakan untuk Membuat Login dan Membuat Pengguna dari Login untuk memberikan Izin Database pada Login pada semua database target.

-- 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

Kemudian, buatlah login di server target, atau pengguna database terisolasi pada database target.

Penting

Login/pengguna di setiap server/database target harus memiliki nama yang sama dengan identitas kredensial cakupan database untuk pengguna pekerjaan, dan kata sandi yang sama dengan kredensial cakupan database untuk pengguna pekerjaan.

Buat login di master database server SQL logis, dan pengguna di setiap database pengguna.

-- 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;

Buat pengguna database mandiri jika login tidak diperlukan di server logis. Biasanya Anda hanya akan melakukan ini jika Anda memiliki satu database untuk dikelola dengan agen pekerjaan yang fleksibel ini.

-- 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;

Tentukan server dan database target

Contoh berikut menunjukkan cara menjalankan pekerjaan terhadap semua database di server.

Sambungkan ke job_database dan jalankan perintah berikut untuk menambahkan grup target dan anggota target:

-- 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';

Mengecualikan database tertentu

Contoh berikut menunjukkan cara menjalankan pekerjaan terhadap semua database di server, kecuali untuk database bernama MappingDB.

Saat menggunakan autentikasi Microsoft Entra (sebelumnya Azure Active Directory), hilangkan @refresh_credential_name parameter, yang seharusnya hanya disediakan saat menggunakan kredensial cakupan database. Dalam contoh berikut, @refresh_credential_name parameternya dikeluarkan dari komentar.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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';

Membuat grup target (kumpulan)

Contoh berikut ini menunjukkan cara menargetkan semua database dalam satu atau beberapa kumpulan elastis.

Saat menggunakan autentikasi Microsoft Entra (sebelumnya Azure Active Directory), hilangkan @refresh_credential_name parameter, yang seharusnya hanya disediakan saat menggunakan kredensial cakupan database. Dalam contoh berikut, parameter @refresh_credential_name dinonaktifkan.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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';

Membuat pekerjaan dan langkah-langkah

Dengan T-SQL, buat pekerjaan menggunakan prosedur tersimpan sistem dalam database pekerjaan: jobs.sp_add_job dan jobs.sp_add_jobstep. Sintaksis perintah T-SQL mirip dengan langkah-langkah yang diperlukan untuk membuat tugas SQL Agent dan langkah-langkah tugas di SQL Server.

Anda tidak boleh memperbarui tampilan katalog internal dalam basis data pekerjaan. Mengubah tampilan katalog ini secara manual dapat merusak database pekerjaan dan menyebabkan kegagalan. Tampilan ini hanya untuk kueri baca-saja. Anda dapat menggunakan prosedur tersimpan dalam jobs skema pada database pekerjaan Anda.

• Saat menggunakan autentikasi Microsoft Entra untuk ID Microsoft Entra atau identitas terkelola yang ditetapkan pengguna untuk mengautentikasi ke server target/database, argumen @credential_name tidak boleh disediakan untuk sp_add_jobstep atau sp_update_jobstep. Demikian pula, hilangkan argumen @output_credential_name dan @refresh_credential_name opsional.
• Saat menggunakan kredensial cakupan database untuk mengautentikasi ke server target/database, parameter @credential_name diperlukan untuk sp_add_jobstep dan sp_update_jobstep.
  • Contohnya,@credential_name = 'job_credential'.

Contoh-contoh berikut menyediakan panduan untuk membuat pekerjaan dan langkah-langkah pekerjaan menggunakan T-SQL, untuk menyelesaikan tugas umum dengan pekerjaan elastis.

Sampel

Menyebarkan skema baru ke banyak database

Contoh berikut ini menunjukkan cara menyebarkan skema baru ke semua database.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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';

Pengumpulan data menggunakan parameter bawaan

Dalam banyak skenario pengumpulan data, mungkin berguna untuk memasukkan beberapa variabel skrip ini untuk membantu pasca-proses hasil pekerjaan.

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

Misalnya, untuk mengelompokkan semua hasil dari eksekusi pekerjaan yang sama bersama-sama, gunakan $(job_execution_id) seperti yang ditunjukkan dalam perintah berikut:

@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());'

Catatan

Semua waktu dalam pekerjaan elastis berada di zona waktu UTC.

Memantau kinerja database

Contoh berikut membuat pekerjaan baru untuk mengumpulkan data performa dari beberapa database.

Secara default, agen pekerjaan akan membuat tabel output untuk menyimpan hasil yang ditampilkan. Oleh karena itu, prinsipal database yang berasosiasi dengan kredensial output harus setidaknya memiliki izin berikut: CREATE TABLE pada database, ALTER, SELECT, INSERT, DELETE pada tabel output atau skemanya, dan SELECT pada tampilan katalog sys.indexes.

Jika Anda ingin membuat tabel secara manual sebelumnya, maka perlu untuk memiliki properti berikut:

1. Kolom dengan nama dan jenis data yang benar untuk tataan hasil.
2. Kolom tambahan untuk internal_execution_id dengan jenis data pengidentifikasi unik.
3. Indeks non-kluster bernama IX_<TableName>_Internal_Execution_ID pada kolom internal_execution_id.
4. Semua izin yang tercantum sebelumnya kecuali untuk izin CREATE TABLE pada database.

Sambungkan ke database pekerjaan dan jalankan perintah berikut ini:

-- 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>';

Menjalankan pekerjaan

Contoh berikut menunjukkan cara segera memulai pekerjaan sebagai tindakan manual yang tidak diencana.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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;

Menjadwalkan pelaksanaan pekerjaan

Contoh berikut menunjukkan cara menjadwalkan pekerjaan untuk eksekusi di masa mendatang secara berulang setiap 15 menit.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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;

Melihat definisi pekerjaan

Contoh berikut menunjukkan cara melihat definisi pekerjaan saat ini.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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;

Memantau status pelaksanaan pekerjaan

Contoh berikut menunjukkan cara melihat detail status pelaksanaan untuk semua pekerjaan.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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;

Batalkan pekerjaan

Contoh berikut menunjukkan cara mengambil ID eksekusi pekerjaan lalu membatalkan eksekusi pekerjaan.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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';

Menghapus riwayat pekerjaan lama

Contoh berikut menunjukkan cara menghapus riwayat pekerjaan sebelum tanggal tertentu.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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

Menghapus pekerjaan dan semua riwayat pekerjaannya

Contoh berikut menunjukkan cara menghapus pekerjaan dan semua riwayat pekerjaan terkait.

Sambungkan ke job_database dan jalankan perintah berikut:

-- 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

Prosedur tersimpan pada pekerjaan

Prosedur tersimpan berikut ini ada di database pekerjaan. Mereka diberi nama yang sama tetapi berbeda dari prosedur tersimpan sistem yang digunakan untuk layanan SQL Server Agent.

Prosedur Tersimpan	Deskripsi
sp_add_job	Menambahkan pekerjaan baru.
sp_update_job	Memperbarui pekerjaan yang ada.
sp_delete_job	Menghapus pekerjaan yang ada.
sp_add_jobstep	Menambahkan langkah ke pekerjaan.
sp_update_jobstep	Memperbarui langkah pekerjaan.
sp_delete_jobstep	Menghapus langkah pekerjaan.
sp_start_job	Mulai melakukan pekerjaan.
sp_stop_job	Menghentikan pelaksanaan pekerjaan.
sp_add_target_group	Menambahkan grup target.
sp_delete_target_group	Menghapus grup target.
sp_add_target_group_member	Menambahkan database atau grup database ke grup target.
sp_delete_target_group_member	Menghapus anggota grup target dari grup target.
sp_purge_jobhistory	Menghapus catatan riwayat untuk sebuah pekerjaan.

Tampilan pekerjaan

Tampilan berikut ini tersedia dalam database pekerjaan.

Tampilan	Deskripsi
eksekusi_tugas	Menunjukkan riwayat pelaksanaan pekerjaan.
pekerjaan	Menunjukkan semua pekerjaan.
versi_pekerjaan	Menunjukkan semua versi pekerjaan.
jobsteps	Memperlihatkan semua langkah dalam setiap versi tugas terkini.
jobstep_versions	Menunjukkan semua langkah di semua versi setiap tugas.
kelompok_target	Menunjukkan semua grup target.
anggota_grup_target	Menunjukkan semua anggota dari semua grup target.

Langkah selanjutnya

Membuat dan mengelola pekerjaan elastis dengan menggunakan PowerShell