| title | description | services | ms.service | ms.subservice | ms.custom | ms.devlang | ms.topic | author | ms.author | ms.reviewer | manager | ms.date |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Manage groups of Azure SQL databases | Microsoft Docs |
Walk through creation and management of an elastic job. |
sql-database |
sql-database |
scale-out |
conceptual |
stevestein |
sstein |
craigg |
07/16/2018 |
[!INCLUDE elastic-database-jobs-deprecation]
Elastic Database jobs simplify management of groups of databases by executing administrative operations such as schema changes, credentials management, reference data updates, performance data collection or tenant (customer) telemetry collection. Elastic Database jobs is currently available through the Azure portal and PowerShell cmdlets. However, the Azure portal surfaces reduced functionality limited to execution across all databases in an elastic pool. To access additional features and execution of scripts across a group of databases including a custom-defined collection or a shard set (created using Elastic Database client library), see Creating and managing jobs using PowerShell. For more information about jobs, see Elastic Database jobs overview.
- An Azure subscription. For a free trial, see Free trial.
- An elastic pool. See About elastic pools.
- Installation of elastic database job service components. See Installing the elastic database job service.
-
Using the Azure portal, from an existing elastic database job pool, click Create job.
-
Type in the username and password of the database administrator (created at installation of Jobs) for the jobs control database (metadata storage for jobs).
-
In the Create Job blade, type a name for the job.
-
Type the user name and password to connect to the target databases with sufficient permissions for script execution to succeed.
-
Paste or type in the T-SQL script.
-
Click Save and then click Run.
When you run a script against a set of databases, you must be sure that the script is idempotent. That is, the script must be able to run multiple times, even if it has failed before in an incomplete state. For example, when a script fails, the job will be automatically retried until it succeeds (within limits, as the retry logic will eventually cease the retrying). The way to do this is to use the an "IF EXISTS" clause and delete any found instance before creating a new object. An example is shown here:
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_ProductVendor_VendorID')
DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (VendorID);
Alternatively, use an "IF NOT EXISTS" clause before creating a new instance:
IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'TestTable')
BEGIN
CREATE TABLE TestTable(
TestTableId INT PRIMARY KEY IDENTITY,
InsertionTime DATETIME2
);
END
GO
INSERT INTO TestTable(InsertionTime) VALUES (sysutcdatetime());
GO
This script then updates the table created previously.
IF NOT EXISTS (SELECT columns.name FROM sys.columns INNER JOIN sys.tables on columns.object_id = tables.object_id WHERE tables.name = 'TestTable' AND columns.name = 'AdditionalInformation')
BEGIN
ALTER TABLE TestTable
ADD AdditionalInformation NVARCHAR(400);
END
GO
INSERT INTO TestTable(InsertionTime, AdditionalInformation) VALUES (sysutcdatetime(), 'test');
GO
After a job has begun, you can check on its progress.
-
From the elastic pool page, click Manage jobs.
-
Click on the name (a) of a job. The STATUS can be "Completed" or "Failed." The job's details appear (b) with its date and time of creation and running. The list (c) below the that shows the progress of the script against each database in the pool, giving its date and time details.
If a job fails, a log of its execution can found. Click the name of the failed job to see its details.
[!INCLUDE elastic-scale-include]