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) bernamajob-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.
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 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.
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, @refresh_credential_name
parameter dikomentari.
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. 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
atausp_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
dansp_update_jobstep
.- Contohnya,
@credential_name = 'job_credential'
.
- Contohnya,
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.
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
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:
- Kolom dengan nama dan jenis data yang benar untuk tataan hasil.
- Kolom tambahan untuk
internal_execution_id
dengan jenis data pengidentifikasi unik. - Indeks nonclustered bernama
IX_<TableName>_Internal_Execution_ID
padainternal_execution_id
kolom . - 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.
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 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
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk