Menangani waktu habis prosedur tersimpan di konektor SQL untuk Azure Logic Apps

Artikel
04/01/2024

Berlaku pada: Azure Logic Apps (Konsumsi)

Saat aplikasi logika Anda bekerja dengan tataan hasil yang sangat besar sehingga konektor SQL tidak menampilkan semua hasil sekaligus, atau jika ingin lebih mengontrol ukuran dan struktur untuk tataan hasil, Anda dapat membuat prosedur tersimpan yang mengatur hasil sesuai keinginan. Konektor SQL menyediakan banyak fitur backend yang dapat diakses menggunakan Azure Logic Apps sehingga Anda dapat dengan lebih mudah mengotomatiskan tugas bisnis yang berfungsi dengan tabel database SQL.

Misalnya, saat mengambil atau menyisipkan beberapa baris, aplikasi logika Anda dapat memeriksa kembali baris tersebut menggunakan perulangan Sampai dalam batas ini. Namun, saat aplikasi logika Anda harus bekerja dengan ribuan atau jutaan baris, Anda sebaiknya meminimalkan biaya yang dihasilkan dari panggilan ke database. Untuk informasi selengkapnya, lihat Menangani data massal menggunakan konektor SQL.

Batas waktu habis pada eksekusi prosedur tersimpan

Konektor SQL memiliki batas waktu habis prosedur tersimpan yang kurang dari 2 menit. Beberapa prosedur tersimpan mungkin memerlukan waktu lebih lama dari batas ini untuk diselesaikan, sehingga menyebabkan kesalahan 504 Timeout. Terkadang proses jangka panjang ini dikodekan sebagai prosedur tersimpan secara eksplisit untuk tujuan ini. Karena batas waktu habis, memanggil prosedur ini dari Azure Logic Apps dapat menyebabkan masalah. Meskipun konektor SQL tidak secara native mendukung mode asinkron, Anda dapat mengatasi masalah ini dan menyimulasikan mode ini menggunakan pemicu penyelesaian SQL, permintaan kirim langsung SQL native, tabel status, dan tugas sisi server. Untuk tugas ini, Anda bisa menggunakan Azure Elastic Job Agent untuk Azure SQL Database. Untuk SQL Server lokal dan Azure SQL Managed Instance, Anda dapat menggunakan SQL Server Agent.

Misalnya, Anda memiliki prosedur tersimpan jangka panjang berikut, yang memerlukan waktu lebih lama dari batas waktu habis untuk selesai beroperasi. Jika menjalankan prosedur tersimpan ini dari aplikasi logika menggunakan konektor SQL, Anda mendapatkan kesalahan HTTP 504 Gateway Timeout sebagai hasilnya.

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

Daripada langsung memanggil prosedur tersimpan, Anda dapat secara asinkron menjalankan prosedur ini di latar belakang menggunakan agen tugas. Anda dapat menyimpan input dan output dalam tabel status yang nantinya dapat digunakan melalui aplikasi logika. Jika tidak memerlukan input dan output, atau jika sudah menulis hasilnya ke tabel dalam prosedur tersimpan, Anda dapat menyederhanakan pendekatan ini.

Penting

Pastikan bahwa prosedur tersimpan dan semua tugas Anda idempotent, yang berarti keduanya dapat dijalankan beberapa kali tanpa memengaruhi hasil. Jika pemrosesan asinkron gagal atau kehabisan waktu, agen tugas mungkin mencoba kembali langkah tersebut, dan juga prosedur tersimpan Anda beberapa kali. Untuk menghindari duplikasi output, sebelum Anda membuat objek apa pun, tinjau praktik dan pendekatan terbaik ini.

Bagian berikutnya menjelaskan cara menggunakan Azure Elastic Job Agent untuk Azure SQL Database. Untuk SQL Server dan Azure SQL Managed Instance, Anda dapat menggunakan SQL Server Agent. Beberapa detail manajemen akan berbeda, tetapi langkah-langkah dasarnya tetap sama dengan menyiapkan agen tugas untuk Azure SQL Database.

Agen tugas untuk Azure SQL Database

Untuk membuat tugas yang bisa menjalankan prosedur tersimpan untuk Azure SQL Database, gunakan Azure Elastic Job Agent. Buat agen tugas Anda di portal Microsoft Azure. Pendekatan ini akan menambahkan beberapa prosedur tersimpan ke database yang digunakan oleh agen, yang juga dikenal sebagai database agen. Anda kemudian dapat membuat tugas yang menjalankan prosedur tersimpan di database target dan mengambil output saat selesai.

Sebelum bisa membuat tugas, Anda perlu menyiapkan izin, grup, dan target seperti yang dijelaskan di dokumentasi lengkap untuk Azure Elastic Job Agent. Anda juga perlu membuat tabel pendukung di database target seperti yang dijelaskan di bagian berikut.

Membuat tabel status untuk mendaftarkan parameter dan menyimpan input

SQL Agent Jobs tidak menerima parameter input. Sebagai gantinya, di database target, buat tabel status tempat Anda mendaftarkan parameter dan menyimpan input yang akan digunakan untuk memanggil prosedur tersimpan. Semua langkah tugas agen berjalan terhadap database target, tetapi prosedur tersimpan tugas berjalan terhadap database agen.

Untuk membuat tabel status, gunakan skema ini:

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]

Berikut adalah tampilan tabel yang dihasilkan di SQL Server Management Studio (SMSS):

Cuplikan layar menunjukkan tabel status yang dibuat berisi input untuk prosedur tersimpan.

Untuk menjamin performa yang baik dan memastikan bahwa tugas agen dapat menemukan data yang terkait, tabel menggunakan ID eksekusi tugas (jobid) sebagai kunci primer. Jika ingin, Anda juga dapat menambahkan kolom untuk tiap-tiap parameter input. Skema yang dijelaskan sebelumnya dapat lebih umum menangani beberapa parameter tetapi terbatas pada ukuran yang dihitung oleh NVARCHAR(MAX).

Membuat tugas tingkat atas untuk menjalankan prosedur tersimpan

Untuk menjalankan prosedur tersimpan jangka panjang, buat agen tugas tingkat atas ini di database agen:

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

Sekarang, tambahkan langkah-langkah ke tugas yang menentukan parameter, menjalankan, dan menyelesaikan prosedur tersimpan. Secara default, waktu habis langkah tugas adalah 12 jam. Jika prosedur tersimpan memerlukan waktu lebih lama, atau jika ingin waktu habis prosedur lebih singkat, Anda dapat mengubah parameter step_timeout_seconds ke nilai lain yang ditentukan dalam hitungan detik. Secara default, langkah dilengkapi 10 pengulangan bawaan dengan waktu habis backoff di antara pengulangan, yang dapat Anda manfaatkan sebagai keuntungan.

Berikut adalah langkah-langkah untuk menambahkan:

Tunggu hingga parameter muncul dalam tabel LongRunningState.

Langkah pertama ini menunggu parameter ditambahkan dalam tabel LongRunningState, yang terjadi segera setelah tugas dimulai. Jika ID eksekusi tugas (jobid) tidak ditambahkan ke tabel LongRunningState, langkah akan gagal, dan pengulangan atau waktu habis backoff default akan dimulai:

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'

Lakukan kueri pada parameter dari tabel status dan teruskan ke prosedur tersimpan. Langkah ini juga menjalankan prosedur di latar belakang.

Jika prosedur tersimpan tidak memerlukan parameter, langsung saja panggil prosedur tersimpan. Jika tidak, untuk meneruskan parameter @timespan, gunakan @callparams, yang juga dapat Anda perluas untuk meneruskan parameter tambahan.

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'

Selesaikan tugas dan catat hasilnya.

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'

Memulai tugas dan meneruskan parameter

Untuk memulai tugas, gunakan permintaan native kirim langsung dengan Menjalankan tindakan kueri SQL dan segera kirim parameter tugas ke dalam tabel status. Untuk memberikan input ke atribut jobid dalam tabel target, Logic Apps menambahkan perulangan Untuk setiap yang diulangi melalui output tabel dari tindakan sebelumnya. Untuk setiap ID eksekusi tugas, jalankan tindakan Sisipkan baris yang menggunakan output data dinamis, ResultSets JobExecutionId, untuk menambahkan parameter bagi tugas guna membongkar dan meneruskan ke prosedur tersimpan target.

Cuplikan layar menunjukkan tindakan yang digunakan untuk memulai tugas dan meneruskan parameter ke prosedur tersimpan.

Ketika sudah selesai, tugas memperbarui tabel LongRunningState sehingga Anda dapat dengan mudah memicu hasilnya menggunakan pemicu Saat item dimodifikasi. Jika tidak memerlukan output, atau jika sudah memiliki pemicu yang memantau tabel output, Anda dapat melewati bagian ini.

Cuplikan layar menunjukkan pemicu SQL saat item dimodifikasi.

Agen tugas untuk SQL Server atau Azure SQL Managed Instance

Untuk skenario yang sama, Anda dapat menggunakan SQL Server Agent untuk SQL Server lokal dan Azure SQL Managed Instance. Meskipun beberapa detail manajemen berbeda, langkah-langkah dasarnya tetap sama dengan menyiapkan agen tugas untuk Azure SQL Database.

Langkah berikutnya

Menyambungkan ke SQL Server, Azure SQL Database, atau Azure SQL Managed Instance

Bagikan melalui