Resource Stack - Background Job Service

Resource Stack - Background Job Service

What is BJS?

BJS is a battle tested framework to schedule and run jobs. Originating from AzureUX-ResourceStack It's used by the PowerApps to run their large amount of background jobs.

Features

Job scheduling
Job sequencing using DAGs
Repeat logic for high flexibility on scheduling
Multiple options for retry strategies (different back-off strategies, max retries, etc.)
Support for checkpoint and continuation
High scalability
Simple interface
Does not require any additional processes for queue grooming or consistency checks

Contents of this example

This example builds two executables.

Name	Description
BJS-Admin	Schedule and query various types of jobs.
BJS-Worker	Runtime environment for jobs, also contains system job to ensure data consistency.

Another component needed is CosmosDB, for testing the local emulator is sufficient.

flowchart LR
    Admin --> |Schedule new jobs| CosmosDB
    Admin --> |Retrieve status of jobs| CosmosDB
    Worker --> |Check and run jobs| CosmosDB
    Worker --> |Schedule system jobs| CosmosDB
    CosmosDB[(fa:fa-database CosmosDB)]
    Worker(fa:fa-cog Worker)
    Admin(fa:fa-play Admin)

Caveats

BJS requires a specific consistency configuration in CosmosDB.

Cosmos DB consistency model

BJS depends on Read-Your-Writes consistency model to function properly. By default, Cosmos DB account is configured with weaker Session consistency. Please reconfigure this to at least Bounded-Staleness. Failure to update Cosmos DB consistency setting will result in failures and intermittent job execution delays.
BJS is a DotNet library, no other languages are supported.

Pre-reqs

Visual Studio Community
Intall Cosmos DB Emulator on a Windows machine from here

How to run this tutorial

Run the Cosmos DB Emulator - browsable at https://localhost:8081/_explorer/index.html
Put the Primary Key into App.config of both BJS-Admin and BJS-Job - so both can connect:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="documentServiceEndpoint" value="https://localhost:8081"/>
    <add key="documentAuthorizationKey" value="C2y6yDj...=="/>
  </appSettings>
</configuration>

Run both apps in seperate windows:
BJS-Admin spins up Jobs, BJS-Job executes them:
Cosmos DB will store execution state:
All Jobs are done:

Exploring different state-stores

1. Azure Storage

TODO

2. Cosmos DB

See above.

3. SQL Server

Full Admin

Set this in the App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="sqlServerConnectionString" value="Server=localhost;Database=msdb;Trusted_Connection=True;" />
  </appSettings>
</configuration>

Least privilege

You must enable mixed-mode for the below demo to work

The script below contains the add and removal permissions:

----------------------------
-- ADD PERMISSIONS AND LOGIN
----------------------------

USE [master];
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'bjs')
BEGIN

	-- Create server login
	USE [master]; CREATE LOGIN bjs WITH PASSWORD = 'password123!';

	-- Create database user
	USE [msdb]; CREATE USER bjs FOR LOGIN bjs;

	-- Grant database roles
	USE [msdb]; GRANT CREATE TABLE TO bjs;
	USE [msdb]; GRANT ALTER ANY SCHEMA TO bjs;
	USE [msdb]; GRANT CREATE TYPE TO bjs;
	USE [msdb]; GRANT EXECUTE TO bjs;

	-- Grant membership
	USE [msdb]; ALTER ROLE db_datawriter ADD MEMBER bjs;
	USE [msdb]; ALTER ROLE db_datareader ADD MEMBER bjs;

END
GO

---------------------
-- REMOVE PERMISSIONS
---------------------

USE [msdb];
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'bjs')
BEGIN

	-- Drop database roles
	USE [msdb]; REVOKE CREATE TABLE FROM bjs;
	USE [msdb]; REVOKE ALTER ANY SCHEMA FROM bjs
	USE [msdb]; REVOKE CREATE TYPE FROM bjs;
	USE [msdb]; REVOKE EXECUTE FROM bjs;

	-- Revoke membership
	USE [msdb]; ALTER ROLE db_datawriter DROP MEMBER bjs;
	USE [msdb]; ALTER ROLE db_datareader DROP MEMBER bjs;

	-- Drop database user
	USE [msdb]; DROP USER bjs;

END

---------------
-- REMOVE LOGIN
---------------
USE [master];
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'bjs')
BEGIN
	-- Kill connections
	DECLARE @SessionID INT;
	DECLARE @SQL NVARCHAR(MAX);

	-- Create a temporary table to store session IDs
	USE master; CREATE TABLE #ActiveSessions (SessionID INT);

	-- Insert active session IDs into the temporary table
	USE master; INSERT INTO #ActiveSessions (SessionID) SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'bjs';

	-- Loop through and kill each active session
	WHILE EXISTS (SELECT 1 FROM #ActiveSessions)
	BEGIN
		USE master;
		SELECT TOP 1 @SessionID = SessionID FROM #ActiveSessions;
		SET @SQL = 'KILL ' + CAST(@SessionID AS NVARCHAR(10));
		EXEC sp_executesql @SQL;
		DELETE FROM #ActiveSessions WHERE SessionID = @SessionID;
	END

	-- Drop the temporary table
	USE master; DROP TABLE #ActiveSessions;

	-- Drop the login
	USE master; DROP LOGIN bjs;
END
GO

Update App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="sqlServerConnectionString" value="Server=localhost;Database=msdb;User Id=bjs;Password=password123!" />
  </appSettings>
</configuration>

SQL Cleanup

And, cleanup logic for tables generated:

USE msdb;

-----------------------
-- VIEW
-----------------------

-- Only run SELECT if table exists.
IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dt' 
    AND TABLE_NAME = 'arcJobDefinitions'
)
BEGIN
	-- SELECT specific columns
    SELECT RowKey
		,TIMESTAMP
		,JobPartition
		,JobId
		,Callback
		,CurrentLocation
		,STATE
		,ExecutionState
		,StartTime
		,RetryCount
		,Timeout
	FROM dt.arcJobDefinitions
	WHERE JobPartition = 'MY:5FOLD:5FSQL2019';
END

-----------------------
-- CLEANUP
-----------------------

-- Tables -------------

DECLARE @TableName NVARCHAR(255);
DECLARE @SchemaName NVARCHAR(255);
DECLARE @DropSQL NVARCHAR(MAX);

DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND (TABLE_SCHEMA = 'dq' OR TABLE_SCHEMA = 'dt')
  ORDER BY TABLE_NAME ASC;

OPEN TableCursor;

FETCH NEXT FROM TableCursor INTO @TableName, @SchemaName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DropSQL = 'DROP TABLE [' + @SchemaName + '].[' + @TableName + '];';
    EXEC sp_executesql @DropSQL;
	PRINT 'Table [' + @SchemaName + '].[' + @TableName + '];' + 'dropped successfully.';

    FETCH NEXT FROM TableCursor INTO @TableName, @SchemaName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- Types -------------

IF EXISTS (
    SELECT 1
    FROM sys.types
    WHERE name = 'arcJobDefinitionsOperationType'
)
BEGIN
    -- Drop the type if it exists
    DROP TYPE [dt].[arcJobDefinitionsOperationType];
    PRINT 'Type "arcJobDefinitionsOperationType" dropped successfully.';
END

-----------------------
-- VERIFY
-----------------------
SELECT * FROM msdb.sys.objects
WHERE is_ms_shipped=0

4. In-Memory

TODO

ivankostic85 / resource-stack Goto Github PK

resource-stack's Introduction