Πώς μπορούμε να προγραμματίσουμε εργασίες σε Azure SQL Databases με τη χρήση Elastic Database Jobs
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Όπως γνωρίζουμε στις Azure SQL Databases δεν υπάρχει SQL Server Agent, που σημαίνει ότι δεν μπορούμε να εκτελούμε προγραμματισμένες εργασίες μέσω αυτού. Παρόλα αυτά στις Azure SQL Databases μπορούμε να κάνουμε schedule jobs με πολλούς άλλους τρόπους όπως με χρήση Azure Data Factory, Elastic Database Jobs και Logic Apps.
Σε προηγούμενο άρθρο είχαμε δει πως λειτουργούν τα Logic Apps. Στα Logic Apps όμως πρέπει να συνδεόμαστε σε μία μία τη βάση και να φτιάχνουμε ένα workflow ανά περίπτωση.
Σε αυτό το άρθρο θα δούμε πως κάνουμε χρήση των Elastic Database Jobs. Σε αντίθεση με τα Logic Apps η εκτέλεση των Job μπορεί να γίνει εκτός από μία μόνο βάση, προς ένα group βάσεων. Επίσης η δημιουργία, η διαχείριση και το monitoring των Jobs γίνεται με κώδικα Transact SQL.
Συγκεκριμένα θα δούμε πως γίνεται να φτιάξουμε ένα Job το οποίο θα τρέχει μία φορά την εβδομάδα Rebuild των Indexes σε όλες τις βάσεις ενός target group που έχουμε ορίσει.
Το παράδειγμα
Για αρχή συνδεόμαστε στο Azure Portal. Σαν προαπαιτούμενο είναι να έχουμε φτιάξει μία Azure SQL Database*η οποία θα διαχειρίζεται όλα τα Jobs και θα είναι αυτή που θα “πατάει” πάνω ο Elastic Job agent (αν δεν γνωρίζουμε πως φτιάχνουμε μία βάση το βλέπουμε εδώ).
*H job db συμφέρει να είναι φτιαγμένη σε basic DTU μοντέλο μιaς και είναι συνέχεια online και δεν απαιτεί υψηλά resources. Το κόστος της σε αυτή τη περίπτωση είναι αρκετά χαμηλό ~ 4ευρώ το μήνα.
Στη συνέχεια ψάχνουμε το service Elastic Job agents.
Εδώ θα πρέπει αν ορίσουμε το όνομα του Agent και ως Job database την Azure SQL Database που έχουμε φτιάξει που θα διαχειρίζεται τα Jobs.
Στο επόμενο βήμα θέλει λίγο προσοχή να καταλάβουμε την λογική.
Θα πρέπει να φτιάξουμε στην Job db δύο credentials τα οποία όμως δεν θα είναι για την ίδια τη βάση αλλά για external use προς τις υπόλοιπες (scoped credential).
Αυτά τα δύο credential θα τα δημιουργήσουμε ως logins στην master db στον logical Server που κάνει host τις Azure SQL Databases. Επίσης μετά την δημιουργία του login για την master db θα πρέπει να φτιάξουμε και τον χρήστη στην ίδια.
Τέλος θα πρέπει να συνδεθούμε σε κάθε μία από τις βάσεις που περιέχει ο Server και να δημιουργήσουμε τον χρήστη από το user login που φτιάξαμε στην master db.
Οπότε ανοίγουμε ένα SQL Server Management Studio ή Azure Data Studio και τα δημιουργούμε.
Για αρχή συνδεόμαστε στην Job db για να φτιάξουμε τα scoped credentials:
USE [job-db]
GO
--Connect to the job database specified when creating the job agent
-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Patatakia1!';
-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL myjobcred WITH IDENTITY = 'myjobcred',
SECRET = 'userpass1!';
GO
-- Create a database scoped credential for the master database of server1.
CREATE DATABASE SCOPED CREDENTIAL mymastercred WITH IDENTITY = 'mymastercred',
SECRET = 'masterpass1!';
GO
Στην συνέχεια συνδεόμαστε στη master db του logical Server που κάνει host τις Azure SQL Databases για να φτιάξουμε τα logins και τον χρήστη στην master db:
-- At the target server containing the Databases USE [master] GO CREATE LOGIN mymastercred with password = 'masterpass1!'; GO CREATE USER mymastercred FROM LOGIN mymastercred; GO CREATE LOGIN myjobcred with password = 'userpass1!'; GO
Στο τέλος συνδεόμαστε ξεχωριστά στη κάθε μία βάση που περιέχετε στον target Server και θα φτιάξουμε τον user από το login.
Επειδή όμως θα θέλω να κάνω Rebuild indexes θα δώσω και ότι δικαίωμα απαιτείται στον user για να ολοκληρωθεί η διαδικασία. Δηλαδη db_ddladmin και view database state:
USE [dpgr_db] GO CREATE user myjobcred FOR LOGIN myjobcred; GO ALTER ROLE db_ddladmin ADD MEMBER myjobcred; GO GRANT VIEW DATABASE STATE TO myjobcred; GO USE [mig_db] GO CREATE user myjobcred FOR LOGIN myjobcred; GO ALTER ROLE db_ddladmin ADD MEMBER myjobcred; GO GRANT VIEW DATABASE STATE TO myjobcred; GO USE [job-db] GO CREATE user myjobcred FOR LOGIN myjobcred; GO ALTER ROLE db_ddladmin ADD MEMBER myjobcred; GO GRANT VIEW DATABASE STATE TO myjobcred; GO
Αφού τελειώσουμε με τους χρήστες γυρνάμε στην job-db και είναι ώρα να φτιάξουμε το target group . Το target group είναι η ομάδα των βάσεων που θα εκτελείται το Job.
Στην περίπτωση μας, θέλουμε να εκτελείται σε όσες βάσεις υπάρχουν στον συγκεκριμένο logical server.
USE [job-db] GO EXEC jobs.sp_add_target_group 'ServerGroup1'; GO -- Add a server target member EXEC jobs.sp_add_target_group_member 'ServerGroup1', @target_type = 'SqlServer', @refresh_credential_name='mymastercred', --credential required to refresh the databases in a server @server_name='dpgr-srv.database.windows.net'; GO SELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1'; SELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';
Βλέπουμε με τα αποτελέσματα των select ότι δημιουργήθηκαν επιτυχώς.
Στο επόμενο βήμα δημιουργούμε το Job στην Job db.
Θα πρέπει να θυμόμαστε ότι η ώρα που δέχεται είναι σε UTC time zone άρα για ώρα Ελλάδας θα πρέπει να κάνουμε -2 ή -3 ώρες την ημερομηνία που ορίζουμε (στην περίπτωση μας το κάνω με την function DATEADD).
Επίσης στην παράμετρο schedule_interval_type ορίζουμε ‘Weeks‘ με schedule_interval_count 1, ώστε να εκτελείται μία φορά την εβδομάδα. Ως schedule_start_time ειναι η ημερομηνία που θα τρέξει πρώτη φορά.
--Connect to the job database specified when creating the job agent --Add job for create table DECLARE @date DATETIME2; set @date = DATEADD(HOUR,-2,'20201226 04:00:00'); -- I am using DATEADD because it works on UTC time zone, but Greece has gmt+2 /+3. EXEC jobs.sp_add_job @job_name='Rebuild Indexes' ,@description='Rebuild Indexes' ,@Enabled = 1 ,@schedule_interval_type = 'Weeks' ,@schedule_interval_count = 1 ,@schedule_start_time = @date; /* for schedule_interval_type the options 'Once', 'Minutes', 'Hours', 'Days', 'Weeks', 'Months' */
Αφού δημιουργήσαμε και κάναμε schedule το Job, θα πρέπει να προσθέσουμε το job step στο job που θα έχει τον κώδικα που θέλουμε να τρέξουμε (στην παράμετρο command). Στην περίπτωση μας το rebuild των indexes.
Δεν ξεχνάμε ότι θα πρέπει να ορίσουμε το target_group_name που είχαμε φτιάξει και το user credential στο credential_name.
-- Add job step for create table
EXEC jobs.sp_add_jobstep @job_name='Rebuild Indexes',
@credential_name='myjobcred',
@target_group_name='ServerGroup1',
@step_name='run rebuild',
@command=N'
DECLARE @Indexes TABLE
(
ObjectSchema SYSNAME,
ObjectName SYSNAME,
IndexName SYSNAME,
FragmentationPCT FLOAT,
Command AS (REPLACE(REPLACE(REPLACE(N''ALTER INDEX {IndexName} ON {ObjectSchema}.{ObjectName} REBUILD;'',N''{IndexName}'',IndexName),N''{ObjectSchema}'',ObjectSchema),N''{ObjectName}'',ObjectName))
)
;
INSERT INTO @Indexes
(
ObjectSchema,
ObjectName,
IndexName,
FragmentationPCT
)
SELECT QUOTENAME(SCHEMA_NAME(SO.schema_id)) AS ObjectSchema,
QUOTENAME(SO.name) AS ObjectName,
QUOTENAME(I.name) AS IndexName,
IPS.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,N''Limited'') AS IPS
JOIN sys.objects AS SO ON SO.object_id = IPS.object_id
JOIN sys.indexes AS I ON I.index_id = IPS.index_id
AND I.object_id = IPS.object_id
WHERE IPS.avg_fragmentation_in_percent > 15
ORDER BY IPS.avg_fragmentation_in_percent DESC
;
DECLARE @TsqlCommand NVARCHAR(MAX);
DECLARE IndexRebuilder CURSOR LOCAL FAST_FORWARD
FOR
SELECT I.Command
FROM @Indexes AS I
;
OPEN IndexRebuilder
FETCH NEXT FROM IndexRebuilder
INTO @TsqlCommand
;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @Command = @TsqlCommand;
FETCH NEXT FROM IndexRebuilder
INTO @TsqlCommand
;
END
CLOSE IndexRebuilder
DEALLOCATE IndexRebuilder
;'Είμαστε έτοιμοι για δοκιμή. Tώρα είτε περιμένουμε να τρέξει όταν το έχουμε κάνει schedule είτε το κάνουμε start manual με το command:
--to run manually exec jobs.sp_start_job 'Rebuild Indexes'
Στο service του Agent στο Azure Portal, μπορούμε να δούμε το ιστορικό εκτέλεσης των Jobs.
Όπως επίσης στην καρτέλα Jobs, τα Jobs που υπάρχουν.
Την ίδια πληροφορία μπορούμε να την δούμε με το T-SQL query στην Job db:
-- View all jobs executions SELECT job_execution_id,job_name,lifecycle,start_time,create_time,last_message,target_type,target_server_name,target_database_name,target_elastic_pool_name,is_active FROM jobs.job_executions ORDER BY start_time DESC GO
Αν θέλουμε να σταματήσουμέ ένα Job αφού βρούμε πρώτα από το job_executions το job_execution_id, μπορούμε με το παρακάτω query:
-- View all active executions to determine job execution id SELECT * FROM jobs.job_executions WHERE is_active = 1 AND job_name = 'Rebuild Indexes' ORDER BY start_time DESC GO -- Cancel job execution with the specified job execution id EXEC jobs.sp_stop_job 'D85DF748-F940-4FD3-AFB4-D7E892ABBDF4'
Μπορούμε επίσης να σβήσουμε την ιστορικότητα εκτέλεσης ενός Job ή και να σβήσουμε ολόκληρο το Job.
--clean job history EXEC jobs.sp_purge_jobhistory @job_name = 'Rebuild Indexes' --delete the job EXEC jobs.sp_delete_job @job_name='Rebuild Indexes'
