Quartz.net persistence and development of cluster deployment

Support for high-availability cluster development scenarios

database table Structure of quartz.net

If clustering and persistence are supported, it is difficult to imagine that the memory and XML of this machine can save the various state values of the scheduling of the computation task. So support the database such a solution, how the Opensymphony organization may give omission.

A variety of official database scripts: Https://github.com/quartznet/quartznet/tree/master/database/tables

Let me show you the Quartz Task Scheduler's MS SQL Server table structure

Create a table-structured T-SQL script

Use [Enter_db_name_here]goif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Fk_qrtz_triggers_qrtz_job_details] ') and OBJECTPROPERTY (ID, N ' isforeignkey ') = 1) ALTER TABLE [dbo]. [Qrtz_triggers] DROP CONSTRAINT fk_qrtz_triggers_qrtz_job_detailsgoif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Fk_qrtz_cron_triggers_qrtz_triggers] ') and OBJECTPROPERTY (ID, N ' isforeignkey ') = 1) ALTER TABLE [dbo]. [Qrtz_cron_triggers] DROP CONSTRAINT fk_qrtz_cron_triggers_qrtz_triggersgoif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo] . [Fk_qrtz_simple_triggers_qrtz_triggers] ') and OBJECTPROPERTY (ID, N ' isforeignkey ') = 1) ALTER TABLE [dbo]. [Qrtz_simple_triggers] DROP CONSTRAINT fk_qrtz_simple_triggers_qrtz_triggersgoif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [db O]. [Fk_qrtz_simprop_triggers_qrtz_triggers] ') and OBJECTPROPERTY (ID, N ' isforeignkey ') = 1) ALTER TABLE [dbo]. [Qrtz_simprop_triggers] DROP CONSTRAINT fk_qrtz_simprop_triggers_qrtZ_triggersgoif EXISTS (SELECT * from Sys.foreign_keys WHERE object_id = object_id (N ' [dbo].[ Fk_qrtz_job_listeners_qrtz_job_details] ') and parent_object_id = object_id (N ' [dbo].[ Qrtz_job_listeners]) ALTER TABLE [dbo]. [Qrtz_job_listeners] DROP CONSTRAINT [fk_qrtz_job_listeners_qrtz_job_details]if EXISTS (SELECT * from Sys.foreign_keys WHERE object_id = objec t_id (N ' [dbo].[ Fk_qrtz_trigger_listeners_qrtz_triggers] ') and parent_object_id = object_id (N ' [dbo].[ Qrtz_trigger_listeners]) ALTER TABLE [dbo]. [Qrtz_trigger_listeners] DROP CONSTRAINT [fk_qrtz_trigger_listeners_qrtz_triggers]if EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [ DBO]. [Qrtz_calendars] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_calendars] GOif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_cron_triggers] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_cron_triggers] GOif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_bLob_triggers] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_blob_triggers] GOif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_fired_triggers] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_fired_triggers] GOif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_paused_trigger_grps] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_paused_trigger_grps] GOif EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Qrtz_job_listeners] ') and type in (N ' U ')) DROP TABLE [dbo]. [Qrtz_job_listeners] IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_scheduler_state] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_scheduler_state] GOif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_locks] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_locks] GOif EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Qrtz_trigger_listeners] ') and type in (N ' U ')) DROP TABLE [dbo]. [Qrtz_trigger_listeners] IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_job_details] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_job_details] GOif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_simple_triggers] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_simple_triggers] GOif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_simprop_triggers] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. Qrtz_simprop_triggersgoif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Qrtz_triggers] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP TABLE [dbo]. [Qrtz_triggers] Gocreate TABLE [dbo]. [Qrtz_calendars] ([Sched_name] [NVARCHAR] (+) not NULL, [Calendar_name] [NVARCHAR] (NO) null, [CALENDAR] [IMAGE] NOT NULL) Gocreate TABLE [dbo]. [Qrtz_cron_triggers] ( [Sched_name] [NVARCHAR] (+) not NULL, [trigger_name] [NVARCHAR] (() is not NULL, [Trigger_group] [NVARCHAR] () is not NULL, [cron_expres SION] [NVARCHAR] (+) not NULL, [time_zone_id] [NVARCHAR] () gocreate TABLE [dbo]. [Qrtz_fired_triggers]  ([Sched_name] [NVARCHAR] (+) not NULL, [entry_id] [NVARCHAR] (UP) not NULL, [trigger_name] [NVARCHAR] (.) Not  NULL, [Trigger_group] [NVARCHAR] () not NULL, [instance_name] [NVARCHAR] (+) not NULL, [Fired_time] [BIGINT] Not NULL, [Sched_time] [BIGINT] is not NULL, "priority" [INTEGER] NOT NULL, [state] [NVARCHAR] (+) not NULL, [Job_n AME] [NVARCHAR] (+) NULL, [Job_group] [NVARCHAR] (+) NULL, [is_nonconcurrent] BIT null, [Requests_recovery] B IT NULL) gocreate TABLE [dbo]. [Qrtz_paused_trigger_grps] ([Sched_name] [NVARCHAR] (+) not NULL, [Trigger_group] [NVARCHAR] (+) NOT NULL) Gocreate TABLE [dbo]. [Qrtz_scheduler_state] ([Sched_name] [NVARCHAR] (+) not NULL, [instance_name][NVARCHAR] (a) is not NULL, [Last_checkin_time] [BIGINT] is not NULL, [Checkin_interval] [BIGINT] is not null) gocreate TABLE [dbo]. [Qrtz_locks] ([Sched_name] [NVARCHAR] (+) not NULL, [Lock_name] [NVARCHAR] (+) NOT NULL) Gocreate TABLE [dbo]. [Qrtz_job_details] ([Sched_name] [NVARCHAR] (+) not NULL, [job_name] [NVARCHAR] (N.) not NULL, [Job_group] [NVARCHAR] ()  ULL, [DESCRipTION] [NVARCHAR] (+) NULL, [Job_class_name] [NVARCHAR] (+) not NULL, [is_durable] BIT is not NULL, [Is_nonconcurrent] Bit not NULL, [is_update_data] bit is not NULL, [requests_recovery] bit is not NULL, [Job_data] [IMAGE] null) gocreate T ABLE [dbo]. [Qrtz_simple_triggers] ([Sched_name] [NVARCHAR] (+) not NULL, [trigger_name] [NVARCHAR] (in) not NULL, [Trigger_group] [NVARCHAR] (150  Not NULL, [Repeat_count] [integer] NOT NULL, [Repeat_interval] [BIGINT] NOT NULL, [times_triggered] [integer] not NULL) gocreate TABLE [dbo]. [Qrtz_simprop_triggers] ([Sched_name] [NVARCHAR] (+) not NULL, [trigger_name] [NVARCHAR] (in) not NULL, [Trigger_group] [NVARCHAR] (in) not NULL, [ST R_prop_1] [NVARCHAR] (+) NULL, [str_prop_2] [NVARCHAR] (+) NULL, [Str_prop_3] [NVARCHAR] (+) NULL, [Int_prop_1] [ INT] NULL, [int_prop_2] [int] null, [long_prop_1] [BIGINT] null, [long_prop_2] [BIGINT] null, [dec_prop_1] [NUMERIC] ( 13,4) NULL, [dec_prop_2] [NUMERIC] (13,4) NULL, [bool_prop_1] bit null, [bool_prop_2] bit null, gocreate TABLE [dbo]. [Qrtz_blob_triggers] ([Sched_name] [NVARCHAR] (+) not NULL, [trigger_name] [NVARCHAR] (in) not NULL, [Trigger_group] [NVARCHAR] (150 ) not NULL, [Blob_data] [IMAGE] NULL) gocreate TABLE [dbo]. [Qrtz_triggers] ([Sched_name] [NVARCHAR] (+) not NULL, [trigger_name] [NVARCHAR] (in) not NULL, [Trigger_group] [NVARCHAR] (150  ) not NULL, [job_name] [NVARCHAR] (in) not NULL, [Job_group] [NVARCHAR] (in) not NULL, [DESCRIPTION] [NVARCHAR] (+) null, [Next_fire_time] [BIGINT] NULL, [Prev_fire_time] [BIGINT] NULL, [priority] [INTEGER] null, [trigger_state] [NVARCHAR] (+) not NULL, [Trigger_type] [NVARCHAR] (8) Not NULL, [start_time] [BIGINT] is not NULL, [End_time] [BIGINT] null, [Calendar_name] [NVARCHAR] ($) NULL, [ MISFIRE_INSTR] [INTEGER] null, [Job_data] [IMAGE] null) goalter TABLE [dbo]. [Qrtz_calendars] With NOCHECK ADD CONSTRAINT [pk_qrtz_calendars] PRIMARY KEY CLUSTERED ([Sched_name], [calendar_name]) Goalter TABLE [dbo]. [Qrtz_cron_triggers] With NOCHECK ADD CONSTRAINT [pk_qrtz_cron_triggers] PRIMARY KEY CLUSTERED ([Sched_name], [trigger_name], [TRI Gger_group]) Goalter TABLE [dbo]. [Qrtz_fired_triggers] With NOCHECK ADD CONSTRAINT [pk_qrtz_fired_triggers] PRIMARY KEY CLUSTERED ([Sched_name], [entry_id]) Goalter TABLE [dbo]. [Qrtz_paused_trigger_grps]  With NOCHECK ADD CONSTRAINT [Pk_qrtz_paused_trigger_grps] PRIMARY KEY CLUSTERED ([Sched_name], [Trigger_group] ) Goalter TABLE [dbo]. [qrtz_scheduLer_state] with NOCHECK ADD CONSTRAINT [pk_qrtz_scheduler_state] PRIMARY KEY CLUSTERED ([Sched_name], [Instance_ NAME]) Goalter TABLE [dbo]. [Qrtz_locks] With NOCHECK ADD CONSTRAINT [pk_qrtz_locks] PRIMARY KEY CLUSTERED ([Sched_name], [lock_name]) Goalter TABLE [D Bo]. [Qrtz_job_details] With NOCHECK ADD CONSTRAINT [pk_qrtz_job_details] PRIMARY KEY CLUSTERED ([Sched_name], [job_name], [Job_group ]) Goalter TABLE [dbo]. [Qrtz_simple_triggers] With NOCHECK ADD CONSTRAINT [pk_qrtz_simple_triggers] PRIMARY KEY CLUSTERED ([Sched_name], [trigger_name], [T Rigger_group]) Goalter TABLE [dbo]. [Qrtz_simprop_triggers] With NOCHECK ADD CONSTRAINT [pk_qrtz_simprop_triggers] PRIMARY KEY CLUSTERED ([Sched_name], [trigger_name], [ Trigger_group]) Goalter TABLE [dbo]. [Qrtz_triggers] With NOCHECK ADD CONSTRAINT [pk_qrtz_triggers] PRIMARY KEY CLUSTERED ([Sched_name], [trigger_name], [trigger_ GROUP]) Goalter TABLE [dbo]. Qrtz_blob_TRIGGERS with NOCHECK ADD CONSTRAINT [pk_qrtz_blob_triggers] PRIMARY KEY CLUSTERED ([Sched_name], [trigger_name] , [Trigger_group]) goalter TABLE [dbo]. [Qrtz_cron_triggers] ADD CONSTRAINT [fk_qrtz_cron_triggers_qrtz_triggers] FOREIGN KEY ([Sched_name], [trigger_name], [Trigger_group ]) REFERENCES [dbo]. [Qrtz_triggers] ([Sched_name], [trigger_name], [Trigger_group]) On DELETE cascadegoalter TABLE [dbo]. [Qrtz_simple_triggers] ADD CONSTRAINT [fk_qrtz_simple_triggers_qrtz_triggers] FOREIGN KEY ([Sched_name], [trigger_name], [Trigger_gro UP]) REFERENCES [dbo]. [Qrtz_triggers] ([Sched_name], [trigger_name], [Trigger_group]) On DELETE cascadegoalter TABLE [dbo]. [Qrtz_simprop_triggers] ADD CONSTRAINT [fk_qrtz_simprop_triggers_qrtz_triggers] FOREIGN KEY ([Sched_name], [trigger_name], [trigger_gr OUP]) REFERENCES [dbo]. [Qrtz_triggers] ([Sched_name], [trigger_name], [Trigger_group]) On DELETE cascadegoalter TABLE [dbo]. [Qrtz_triggers] ADD CONSTRAINT [fk_qrtz_triggers_qrtz_job_details] FOREIGN KEY ([Sched_name], [job_name], [Job_group]) REFER ences [dbo]. [Qrtz_job_details] ([Sched_name], [job_name], [Job_group]) Gocreate index Idx_qrtz_t_j on qrtz_triggers (sched_name,job_name,job_group) CREATE index IDX_QRTZ_T_JG on QRTZ_TRIGGERS (Sched_name,job_group) Create INDEX Idx_qrtz_t_c on qrtz_triggers (sched_name,calendar_name) CREATE index idx_qrtz_t_g on qrtz_triggers (sched_ Name,trigger_group) CREATE index idx_qrtz_t_state on qrtz_triggers (sched_name,trigger_state) CREATE INDEX Idx_qrtz_t_n _state on qrtz_triggers (sched_name,trigger_name,trigger_group,trigger_state) CREATE INDEX idx_qrtz_t_n_g_state on Qrtz_triggers (sched_name,trigger_group,trigger_state) CREATE INDEX idx_qrtz_t_next_fire_time on QRTZ_TRIGGERS (SCHED _name,next_fire_time) CREATE INDEX idx_qrtz_t_nft_st on Qrtz_triggers (sched_name,trigger_state,next_fire_time) CREATE INDEX Idx_qrtz_t_nft_misfire on Qrtz_triggers (sched_naMe,misfire_instr,next_fire_time) CREATE INDEX idx_qrtz_t_nft_st_misfire on Qrtz_triggers (SCHED_NAME,MISFIRE_INSTR, next_fire_time,trigger_state) CREATE INDEX idx_qrtz_t_nft_st_misfire_grp on Qrtz_triggers (SCHED_NAME,MISFIRE_INSTR, next_fire_time,trigger_group,trigger_state) CREATE INDEX idx_qrtz_ft_trig_inst_name on Qrtz_fired_triggers (SCHED_ Name,instance_name) CREATE INDEX idx_qrtz_ft_inst_job_req_rcvry on Qrtz_fired_triggers (Sched_name,instance_name, Requests_recovery) CREATE index idx_qrtz_ft_j_g on qrtz_fired_triggers (sched_name,job_name,job_group) CREATE INDEX IDX _QRTZ_FT_JG on Qrtz_fired_triggers (sched_name,job_group) CREATE INDEX idx_qrtz_ft_t_g on Qrtz_fired_triggers (SCHED_ Name,trigger_name,trigger_group) CREATE INDEX idx_qrtz_ft_tg on Qrtz_fired_triggers (sched_name,trigger_group) GO

View Code

Some of the tables and field descriptions that we used to expand our development

1. Qrtz_job_details: The details of the JOB are stored, including: [DESCRIPTION] Description, [is_durable] is persistent, [job_data] persistent objects and other basic information.

2, Qrtz_triggers: Trigger information, including: Job name, group foreign key, [DESCRIPTION] trigger description and other basic information, and [start_time] Start execution time, [end_time] End execution time, [prev_fire_ TIME] Last execution, [next_fire_time] Next execution time, [Trigger_type] Trigger type: Simple and cron,[trigger_state] execution status: waiting,paused, The acquired are: Wait, pause, run.

3. Qrtz_cron_triggers: Save CRON expression.

4, Qrtz_scheduler_state: Storage cluster Note instance information, Quartz will periodically read the table information to determine the current status of each instance of the cluster, instance_name:  The name of the Org.quartz.scheduler.instanceId configuration in the previous configuration file is written to this field, and if set to Auto,quartz a name is generated based on the physical machine name and the current time. [Last_checkin_time] Last check time, [checkin_interval] check interval time.

5. Qrtz_paused_trigger_grps: Paused task group information.

6, Qrtz_locks, pessimistic lock occurred record information.

7, Qrtz_fired_triggers, the trigger information that is running.

8, Qrtz_simple_triggers, simple departure device details.

9. Qrtz_blob_triggers, the trigger is saved as a binary large object type (for quartz users to trigger their own triggers, but Jobstore does not understand how to store instances).

. NET program configuration Quartz database parameters

                1. First create a job scheduling pool var properties = new NameValueCollection ();                Storage type properties["Quartz.jobStore.type"] = "Quartz.Impl.AdoJobStore.JobStoreTX, quartz";                Indicates that the prefix properties["quartz.jobStore.tablePrefix"] = "qrtz_"; Drive type properties["Quartz.jobStore.driverDelegateType"] = "Quartz.Impl.AdoJobStore.SqlServerDelegate, quart                Z ";                Data source name properties["Quartz.jobStore.dataSource"] = "MyDS";                Connection string properties["quartz.dataSource.myDS.connectionString"] = config.quartzconnstr;                SQL Server version properties["Quartz.dataSource.myDS.provider"] = "SqlServer-20";                Maximum number of links//properties["quartz.dataSource.myDS.maxConnections"] = "5";          First we must get a reference to a scheduler ischedulerfactory SF = new Stdschedulerfactory (properties);       IScheduler sched = sf. Getscheduler ();

The above is the creation of a scheduler, as well as the configuration and quartz database detailed parameters. After the database you will be very excited, directly write T-SQL operation database How convenient ah, and all the task scheduling information is clear, here I want to tell you, you do not need to write directly SQL statement, quart provided by the class library can automatically populate the database to complete the task scheduling management operations.

Various operation methods of quartz.net task scheduling

First of all I want to say, I have an article in front of Quartz.net do a basic operation description, here is only the code, for reference only.

1. Add a task plan and develop the execution class to trigger

                    #region Check if there is an if (Isexistjob (M.jobgroupname, m.jobname)) {                    return false;                    #endregion #region Add a task plan if (m.startime = = null)                    {m.startime = DateTime.Now;                    } DateTimeOffset starruntime = Datebuilder.nextgivenseconddate (m.startime, 1);                    if (m.endtime = = null) {M.endtime = DateTime.MaxValue.AddDays (-1);                    } DateTimeOffset endruntime = Datebuilder.nextgivenseconddate (m.endtime, 1);                    Scheduler = Getscheduler (); Ijobdetail job = jobbuilder.create<quartzfunction> (). Withidentity (M.jobname, M.jobgroupname). Withdescription (M.jobdescribe).               Build ();     Icrontrigger trigger = (Icrontrigger) triggerbuilder.create (). StartAt (Starruntime). EndAt (EndRunTime). Withidentity (M.jobname, M.jobgroupname). Withcronschedule (M.CRONSTR). Withdescription (M.jobdescribe).                    Build (); Scheduler.                    Schedulejob (Job, trigger); Scheduler.                    Start ();                    #endregion #region Associated Run interface var API = new A_runjobtriggerentity (); Api.                    Apicode = M.apicode; Api.                    Apitype = M.apitype; Api.                    Apiurl = M.apiurl; Api.                    AppID = M.appid; Api.                    Createtime = DateTime.Now; Api.      Jobdescribe = M.jobdescribe;              Api.                    Servicecode = M.servicecode; Api.                    Token = M.token; Api.                    Triggergroup = M.jobgroupname; Api.                    Triggername = M.jobname; New Arunjobrelationmanage ().                    Insert (API); #endregion

2. Removal of execution plan

Scheduler = Getscheduler (); var trigger = new Triggerkey (Jobgroup, jobName); scheduler. Pausetrigger (trigger);//Stop Trigger scheduler. Unschedulejob (trigger); Remove trigger var result = Scheduler. Deletejob (Jobkey.create (Jobname,jobgroup));

3. Suspend Scheduled Tasks

Scheduler = Getscheduler (); scheduler. Pausejob (Jobkey.create (JobName, Jobgroup));

4. Suspend all Mission plans

Scheduler = Getscheduler ();
Scheduler. Pauseall ();

5. Open the designated task plan

Scheduler = Getscheduler (); if (!scheduler. isstarted) {      Scheduler. Start (); }//scheduler. Resumetrigger (New Triggerkey (JobName, Jobgroup));               Scheduler. Resumejob (Jobkey.create (JobName, Jobgroup));

6. Open All Mission plans

Scheduler = Getscheduler (); if (!scheduler. isstarted) {     Scheduler. Start ();} Scheduler. Resumetrigger (New Triggerkey (JobName, Jobgroup));              Scheduler. Resumeall ();

7, the scheduled start state, if the state of the dispatch is not turned on, even if the state of the trigger is waiting for execution, execution, is also invalid.

Scheduler = Getscheduler (); if (scheduler. isstarted) {     return "open";} else{     return "Off";}

8, whether the cluster

Whether the cluster//properties["quartz.jobStore.clustered"] = "false";//properties["quartz.scheduler.instanceId"] = "AUTO";

The result of the test:

I deployed on two servers, set a task schedule, starting every 2 seconds, 2 machines running at the same time, no repeated execution, and a server down can still be accurate operation, to the diagram.

Quartz.net persistence and development of cluster deployment