Configure Azure Elastic Jobs for Azure SQL DB - Old Method

/*

New September 2018 Azure Job Agent

Azure Job agent allows you to run SQL queries on multiple databases in one go

You require one database to manage the jobs and schedules

*/

--we need two logins

--ElasticJobUser will run the queries on each target database

--ElasticJobMaster will run on the job database

--These must be created on each target server well

--USE MASTER

-- ElasticJobUser

CREATE LOGIN ElasticJobUser WITH PASSWORD = 'xxxxxx3';

GO

-- ElasticJobmaster

CREATE LOGINElasticJobMaster WITH PASSWORD = 'xxxxxx3';

GO

--if you will be querying databases on a different sql server create both these logns on that server as well

--switch to job database to create the Jobmaster user

--USE ElasticJobDB

CREATE USERElasticJobMaster FOR LOGIN ElasticJobMaster WITH DEFAULT_SCHEMA =dbo;

GO

EXEC sp_addrolemember N'db_owner', N'ElasticJobMaster';

GO

--Elastic Job Agent will use credentials to connect to the target databases and login as the Elasticuser user

--Create these in the the jobs database

-- Create a db master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD='xxxxxx3';

GO

-- Create a database scoped credential for job execution.

CREATE DATABASE SCOPED CREDENTIAL ElasticJobUserCredential WITH IDENTITY = 'ElasticJobUser', SECRET = 'xxxxxx3';

GO

-- Create a database scoped credential for the master user

CREATE DATABASE SCOPED CREDENTIAL ElasticJobMasterCredential WITH IDENTITY = 'ElasticJobMaster', SECRET = 'xxxxxx3';

GO

--USE ShardDB1 / 2 / 3 / 4

-- create login each target server

-- and a user on each target database

--since I am on the same server I just need to create the user account

CREATE USER ElasticJobUser FOR LOGIN ElasticJobUser WITH DEFAULT_SCHEMA = dbo;

GO

--give suitable permissions to the Job user. In my case read and write

EXEC sp_addrolemember N'db_datareader', N'ElasticJobUser';

EXEC sp_addrolemember N'db_datawriter', N'ElasticJobUser';

GO

--Now we have configured the accounts to run Elastic Jobs we can create target groups, jobs and schedules

--USE ElasticJobDB

-- First we need a Target group

EXEC jobs.sp_add_target_group 'ShardServerGroup1';

--We add targets to the target group. these are can be either SQL servers or individual databases on a server

-- We can add a complete server as a target member. The job will then run on every database on that server

--in this case it needs to know the credential name of the Jobmaster login because it will use that login to enumerate the names of the databases on that server

EXEC jobs.sp_add_target_group_member

@target_group_name = 'ShardServerGroup1'

, @target_type = 'SqlServer'

, @refresh_credential_name='ElasticJobMasterCredential'

, @server_name='shardpoolserver.database.windows.net';

--More likely, since the server may have other databases which we don't want to target, we will just add the databases we want to target.

-- This doesn't require the refresh credential parameter. it will use the ElasticJobUserCredential we created earlier

EXEC jobs.sp_add_target_group_member

@target_group_name = 'ShardServerGroup1'

, @target_type = 'Sqldatabase'

, @server_name='shardpoolserver.database.windows.net'

, @Database_name = 'ShardDB1';

EXEC jobs.sp_add_target_group_member

@target_group_name = 'ShardServerGroup1'

, @target_type = 'Sqldatabase'

, @server_name='shardpoolserver.database.windows.net'

, @Database_name = 'ShardDB2';

--if we had used the server option we can exclude databases that we don't want to target

EXEC jobs.sp_add_target_group_member

@target_group_name = 'ShardServerGroup1'

, @target_type = 'Sqldatabase'

, @server_name='shardpoolserver.database.windows.net'

, @Database_name = 'ShardDB3'

, @Membership_type='Exclude';

--

EXEC jobs.sp_add_target_group_member

@target_group_name = 'ShardServerGroup1'

, @target_type = 'Sqldatabase'

, @server_name='shardpoolserver.database.windows.net'

, @Database_name = 'ShardDB4'

, @Membership_type='Include';

-- View members of target group

SELECT target_group_name, membership_type, target_type, server_name,database_name,target_id FROM jobs.target_group_members

WHERE target_group_name='ShardServerGroup1';

-- Now we need to create a job

EXEC jobs.sp_add_job @job_name='ShardJob1'

, @description='Run Query on Shards';

-- And add the job step

EXEC jobs.sp_add_jobstep

@job_name='ShardJob1'

, @command = N'SELECT @@Servername;'

, @credential_name= 'ElasticJobUserCredential'

, @target_group_name='ShardServerGroup1'

-- View the job step definition

SELECT [job_name]

, [step_id]

, [command_type]

, [command]

, [credential_name]

, [target_group_name]

FROM [jobs].[jobsteps]

WHERE job_name = 'ShardJob1';

-- Execute the job

exec jobs.sp_start_job 'ShardJob1'

-- View latest execution. There is no output or log returned from job so there is no way of seeing what it has done. We just get success or failure

SELECT is_active

, lifecycle

, last_message

, target_type

,target_resource_group_name

, target_server_name

, target_database_name

, target_elastic_pool_name

FROM jobs.job_executions

WHERE job_name = 'ShardJob1'

AND job_execution_id = (SELECT job_execution_id FROM jobs.job_executions WHERE step_id IS NULL and create_time =

(SELECT MAX(create_time) FROM jobs.job_executions WHERE step_id IS NULL))

ORDER BY start_time DESC;

GO

-- We can create a job that writes the output of our query to a table on the jobs database

EXEC jobs.sp_add_job @job_name ='ShardJob2', @description='Get Row Counts';

-- Add a job step to collect results

-- here I am running a query to return the number of rows in a table on each database in my target group that I created earlier

--it will create and write to a table called ShardCount on the ElasticJob database

EXEC jobs.sp_add_jobstep

@job_name='ShardJob2',

@command= N'SELECT Count(*) as [Row Count],C.Country_ID,Country_Name FROM[dbo].[addresses] A INNER JOIN countries C On A.Country_ID = C.Country_ID GROUP BY C.Country_ID, C.Country_Name;',

@credential_name='ElasticJobUserCredential',

@target_group_name='ShardServerGroup1',

@output_type='SqlDatabase',

@output_credential_name='ElasticJobMasterCredential',

@output_server_name='shardpoolserver.database.windows.net',

@output_database_name='ElasticJobDB',

@output_schema_name='dbo',

@output_table_name='ShardCount',

@retry_attempts = 2;

-- Execute the job

exec jobs.sp_start_job 'ShardJob2'

-- View execution history. Again there is no log of the output.

SELECT is_active

, lifecycle

, last_message

, target_type

,target_resource_group_name

, target_server_name

, target_database_name

, target_elastic_pool_name

, start_time

FROM jobs.job_executions

WHERE job_name = 'ShardJob2'

ORDER BY start_time DESC;

GO

-- if we refresh the table list in ElasticJobDB we will have a new table called ShardCount

--Scheduling Jobs

DECLARE @ScheduleStartTime DATETIME2 = DATEADD(mi,1,getdate());

DECLARE @ScheduleEndTime DATETIME2 = DATEADD(mi,45,getdate());

EXEC jobs.sp_update_job @job_name = 'ShardJob2',

@schedule_interval_type = 'Days',

@schedule_interval_count = 1,

@schedule_start_time = @ScheduleStartTime,

@schedule_end_time = @ScheduleEndTime,

@enabled = 1;

-- return to the portal to view the execution list.