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 jalankan pekerjaannya
• Membuat kredensial cakupan database dalam database target sehingga agen menyambungkan 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 cakupan database 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 GRANT perintah 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 target/database, buat pengguna mandiri yang dipetakan ke UMI.

• Jika pekerjaan elastis memiliki server logis atau target kumpulan, Anda harus membuat pengguna yang terkandung yang dipetakan ke UMI dalam master database server logis target.
• Misalnya, untuk membuat login database mandiri dalam master database, dan pengguna dalam database pengguna, berdasarkan identitas terkelola yang ditetapkan pengguna (UMI) bernama job-agent-UMI:

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

• 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 jobuser;
GRANT CREATE TABLE TO jobuser;

Menggunakan kredensial cakupan database untuk eksekusi pekerjaan

Kredensial cakupan database digunakan untuk menyambungkan ke database target Anda untuk eksekusi skrip. Info masuk memerlukan izin yang sesuai, pada database yang ditentukan oleh grup target, untuk dapat menjalankan skrip dengan berhasil. Saat menggunakan server SQL logis dan/atau anggota grup target kumpulan, disarankan untuk membuat kredensial untuk digunakan untuk me-refresh kredensial sebelum perluasan server dan/atau kumpulan pada saat eksekusi pekerjaan. Kredensial cakupan database dibuat pada database agen pekerjaan.

Kredensial yang sama harus digunakan untuk Membuat Login dan Membuat Pengguna dari Masuk untuk memberikan Izin Database Masuk 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='<EnterStrongPasswordHere>';  

-- 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 = '<EnterStrongPasswordHere>';
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 = '<EnterStrongPasswordHere>';
GO

Kemudian, buat login di server target, atau pengguna database mandiri 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='<Enter_same_StrongPassword_as_database_scoped_credential>';

--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 elastis ini.

--Create a contained database user on a user database mapped to a Microsoft Entra account
CREATE USER [job_credential] WITH PASSWORD='<Enter_same_StrongPassword_as_database_scoped_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;

Menentukan server dan database target

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

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

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 parameter dikomentari.

Koneksi 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, @refresh_credential_name parameter dikomentari.

Koneksi 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. Perintah T-SQL adalah sintaksis mirip dengan langkah-langkah yang diperlukan untuk membuat pekerjaan SQL Agent dan langkah-langkah pekerjaan di SQL Server.

Anda tidak boleh memperbarui tampilan katalog internal dalam database 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 berikut menyediakan panduan untuk membuat langkah-langkah pekerjaan dan 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.

Koneksi 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

Setiap kali dalam pekerjaan elastis berada di zona waktu UTC.

Memantau performa 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, database utama yang terkait dengan output info masuk setidaknya harus 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 nonclustered bernama IX_<TableName>_Internal_Execution_ID pada internal_execution_id kolom .
4. Semua izin yang tercantum sebelumnya kecuali untuk CREATE TABLE izin 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.

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

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

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

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

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

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

Koneksi 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 rekaman riwayat untuk pekerjaan.

Tampilan pekerjaan

Tampilan berikut ini tersedia dalam database pekerjaan.

Tampilan	Deskripsi
job_executions	Menunjukkan riwayat pelaksanaan pekerjaan.
jobs	Menunjukkan semua pekerjaan.
job_versions	Menunjukkan semua versi pekerjaan.
jobsteps	Memperlihatkan semua langkah dalam versi setiap tugas saat ini.
jobstep_versions	Menunjukkan semua langkah di semua versi setiap tugas.
target_groups	Menunjukkan semua grup target.
target_group_members	Menunjukkan semua anggota dari semua grup target.

Langkah selanjutnya

Membuat dan mengelola pekerjaan elastis dengan menggunakan PowerShell