basquang™ on clouds

August 7, 2014

[Quartz.NET]: Configure AdoJobStore JobStore

Filed under: Quartz,Quartz.NET — basquang @ 4:05 PM
Tags: ,

JobStore‘s are responsible for keeping track of all the “work data” that you give to the scheduler: jobs, triggers, calendars, etc.

RAMJobStore

RAMJobStore is the simplest JobStore to use, it is also the most performant (in terms of CPU time). RAMJobStore gets its name in the obvious way: it keeps all of its data in RAM. This is why it’s lightning-fast, and also why it’s so simple to configure. The drawback is that when your application ends (or crashes) all of the scheduling information is lost – this means RAMJobStore cannot honor the setting of “non-volatility” on jobs and triggers. For some applications this is acceptable – or even the desired behavior, but for other applications, this may be disasterous.

Configuring Quartz to use RAMJobStore

quartz.jobStore.type = Quartz.Simpl.RAMJobStore, Quartz

To use RAMJobStore (and assuming you’re using StdSchedulerFactory) you don’t need to do anything special. Default configuration of Quartz.NET uses RAMJobStore as job store implementation.

ADO.NET Job Store (AdoJobStore)

AdoJobStore is also aptly named – it keeps all of its data in a database via ADO.NET. Because of this it is a bit more complicated to configure than RAMJobStore, and it also is not as fast. However, the performance draw-back is not terribly bad, especially if you build the database tables with indexes on the primary keys.

To use AdoJobStore, you must first create a set of database tables for Quartz.NET to use. You can find table-creation SQL scripts in the “database/dbtables” directory of the Quartz.NET distribution. If there is not already a script for your database type, just look at one of the existing ones, and modify it in any way necessary for your DB. One thing to note is that in these scripts, all the the tables start with the prefix “QRTZ_” such as the tables “QRTZ_TRIGGERS”, and “QRTZ_JOB_DETAIL”). This prefix can actually be anything you’d like, as long as you inform AdoJobStore what the prefix is (in your Quartz.NET properties). Using different prefixes may be useful for creating multiple sets of tables, for multiple scheduler instances, within the same database.

This article describe simple steps to configure ADO.NET Job Store

  1. Download Quartz.NET package from here http://sourceforge.net/projects/quartznet/files/quartznet/
  2. Run the SQL file tables_sqlServer.sql to create ADO.NET Quartz Job Store tables. After complete you will see list of tables as picture below

     

  3. Create Console Application
  4. Install Quartz.NET NuGet package
  5. Configure logging in App.config as code below

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    
      <configSections>
        <!--<section name="quartz" type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0,Culture=neutral, PublicKeyToken=b77a5c561934e089" />-->
        <sectionGroup name="common">
          <section name="logging" type="Common.Logging.ConfigurationSectionHandler, Common.Logging"/>
        </sectionGroup>
      </configSections>
    
      <common>
        <logging>
          <factoryAdapter type="Common.Logging.Simple.ConsoleOutLoggerFactoryAdapter, Common.Logging">
            <arg key="showLogName" value="true"/>
            <arg key="showDataTime" value="true"/>
            <arg key="level" value="INFO"/>
            <arg key="dateTimeFormat" value="HH:mm:ss:fff"/>
          </factoryAdapter>
        </logging>
      </common>
    
      <connectionStrings>
        <add name="QuartzStore" connectionString="Data Source=QUANGNB;Initial Catalog=QuartzNETStore;User ID=sa;Password=********" providerName="System.Data.SqlClient" />
      </connectionStrings>
    
      <!-- 
        We use quartz.config for this server, you can always use configuration section if you want to.
        Configuration section has precedence here.  
      -->
      <!--
      <quartz >
      </quartz>
      -->
    
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
      </startup>
    </configuration>
    

  6. Create quartz.config file and set Build Action = None, Copy to Output Directory = Copy Always. Then add following code to configure Quartz.NET to use ADO Job Store

    # You can configure your scheduler in either <quartz> configuration section
    # or in quartz properties file
    # Configuration section has precedence
    
    quartz.scheduler.instanceName = MyScheduler
    quartz.scheduler.instanceId = AUTO
    quartz.threadPool.type = Quartz.Simpl.SimpleThreadPool, Quartz 
    
    # this section configures the scheduler to use sqlite as the jobstore
    # to use the RAM store just comment all of this out
    # to use the sqlite store, uncomment all of this 
    quartz.jobStore.type = Quartz.Impl.AdoJobStore.JobStoreTX, Quartz 
    quartz.jobStore.dataSource = default 
    quartz.jobStore.tablePrefix = QRTZ_ 
    quartz.jobStore.clustered = false
    quartz.jobStore.lockHandler.type = Quartz.Impl.AdoJobStore.SimpleSemaphore, Quartz 
    quartz.jobStore.driverDelegateType = Quartz.Impl.AdoJobStore.StdAdoDelegate, Quartz 
    quartz.dataSource.default.provider = SqlServer-20
    quartz.jobStore.useProperties = true
    quartz.jobStore.selectWithLockSQL = SELECT * FROM {0} LOCKS UPDLOCK WHERE LOCK_NAME = @ lockName
    quartz.dataSource.default.connectionStringName = QuartzStore
    quartz.dataSource.default.connectionString = Data Source=QUANGNB;Initial Catalog=QuartzNETStore;User ID=sa;Password=******
    
    # this section configures the scheduler to use sql server ce as the jobstore
    #quartz.jobStore.type = Quartz.Impl.AdoJobStore.JobStoreTX, Quartz 
    #quartz.jobStore.dataSource = default 
    #quartz.dataSource.default.connectionString = Data Source=quartz.sdf;Persist Security Info=False;
    #quartz.jobStore.tablePrefix = QRTZ_ 
    #quartz.jobStore.clustered = false
    #quartz.jobStore.lockHandler.type = Quartz.Impl.AdoJobStore.UpdateLockRowSemaphore, Quartz 
    #quartz.jobStore.driverDelegateType = Quartz.Impl.AdoJobStore.SqlServerDelegate, Quartz 
    #quartz.dataSource.default.provider = SqlServerCe-351
    #quartz.jobStore.useProperties = true
    

  7. Create Simple Job like this

    public class SimpleJob : IJob
        {
            private static ILog logging = LogManager.GetLogger(typeof(SimpleJob));
    
            public void Execute(IJobExecutionContext context)
            {
                logging.InfoFormat("Hello from job");
            }
        }
    

  8. Using the Job in Program like this

    ILog log = LogManager.GetLogger(typeof(Program));           
    
    // First we must get a reference to a scheduler
    ISchedulerFactory sf = new StdSchedulerFactory();
    IScheduler sched = sf.GetScheduler();
    
    try
    {
    	var startTime = DateTimeOffset.Now.AddSeconds(5);
    
    	var job = JobBuilder.Create<SimpleJob>()
    						.WithIdentity("job1", "group1")
    						.Build();
    
    	var trigger = TriggerBuilder.Create()
    		.WithIdentity("trigger1", "group1")
    		.StartAt(startTime)
    		.WithSimpleSchedule(x => x.WithIntervalInSeconds(10).RepeatForever())
    		.Build();
    
    	sched.ScheduleJob(job, trigger);       
    
    	sched.Start();
    	Thread.Sleep(TimeSpan.FromMinutes(2));
    }
    finally
    {
    	sched.Shutdown(true);
    }         
    

  9. Now run for testing ADO Job Store. The Output like this

    15:56:31:280 [INFO]  Quartz.Impl.StdSchedulerFactory - Quartz.NET properties loa
    ded from configuration file 'C:\Users\Quang\Documents\Visual Studio 2013\Project
    s\QuartzNETSolution\Lesson6\bin\Debug\quartz.config'
    15:56:31:372 [INFO]  Quartz.Util.DBConnectionManager - Registering datasource 'd
    efault' with db provider: 'Quartz.Impl.AdoJobStore.Common.DbProvider'
    15:56:31:372 [INFO]  Quartz.Impl.StdSchedulerFactory - Using default implementat
    ion for object serializer
    15:56:31:383 [INFO]  Quartz.Impl.StdSchedulerFactory - Using custom data access
    locking (synchronization): Quartz.Impl.AdoJobStore.SimpleSemaphore
    15:56:31:384 [INFO]  Quartz.Impl.StdSchedulerFactory - Using default implementat
    ion for ThreadExecutor
    15:56:31:460 [INFO]  Quartz.Core.SchedulerSignalerImpl - Initialized Scheduler S
    ignaller of type: Quartz.Core.SchedulerSignalerImpl
    15:56:31:461 [INFO]  Quartz.Core.QuartzScheduler - Quartz Scheduler v.2.2.4.400
    created.
    15:56:31:491 [WARN]  Quartz.Impl.AdoJobStore.JobStoreTX - Detected usage of SQL
    Server provider without SqlServerDelegate, SqlServerDelegate would provide bette
    r performance
    15:56:31:491 [INFO]  Quartz.Impl.AdoJobStore.JobStoreTX - JobStoreTX initialized
    .
    15:56:31:498 [INFO]  Quartz.Core.QuartzScheduler - Scheduler meta-data: Quartz S
    cheduler (v2.2.4.400) 'MyScheduler' with instanceId 'NON_CLUSTERED'
      Scheduler class: 'Quartz.Core.QuartzScheduler' - running locally.
      NOT STARTED.
      Currently in standby mode.
      Number of jobs executed: 0
      Using thread pool 'Quartz.Simpl.SimpleThreadPool' - with 10 threads.
      Using job-store 'Quartz.Impl.AdoJobStore.JobStoreTX' - which supports persiste
    nce. and is not clustered.
    
    15:56:31:500 [INFO]  Quartz.Impl.StdSchedulerFactory - Quartz scheduler 'MySched
    uler' initialized
    15:56:31:501 [INFO]  Quartz.Impl.StdSchedulerFactory - Quartz scheduler version:
     2.2.4.400
    15:56:31:800 [INFO]  Quartz.Impl.AdoJobStore.JobStoreTX - Freed 0 triggers from
    'acquired' / 'blocked' state.
    15:56:31:844 [INFO]  Quartz.Impl.AdoJobStore.JobStoreTX - Recovering 0 jobs that
     were in-progress at the time of the last shut-down.
    15:56:31:844 [INFO]  Quartz.Impl.AdoJobStore.JobStoreTX - Recovery complete.
    15:56:31:861 [INFO]  Quartz.Impl.AdoJobStore.JobStoreTX - Removed 0 'complete' t
    riggers.
    15:56:31:876 [INFO]  Quartz.Impl.AdoJobStore.JobStoreTX - Removed 0 stale fired
    job entries.
    15:56:31:888 [INFO]  Quartz.Core.QuartzScheduler - Scheduler MyScheduler_$_NON_C
    LUSTERED started.
    15:56:36:787 [INFO]  Lesson6.SimpleJob - Hello from job
    15:56:46:619 [INFO]  Lesson6.SimpleJob - Hello from job
    ...
    

     

    While program running Open QRTZ_FIRED_TRIGGERS table

     

    While program running Open QRTZ_JOB_DETAILS table

     

    While program running Open QRTZ_TRIGGERS table

     

    Hope this help!

Advertisements

4 Comments »

  1. Great post, thanks; this helped me figure out how to use SqlCe for my AdoJobStore. Suggestion: The configuration code in your Quartz.config step #6 is actually Quartz.properties syntax. I struggled with that and had to dig through the Quartz source and several StackOverflow posts for some time before I figured that out. An edit with a clarification will be very helpful for a beginner.

    Comment by Jeff Bowman — October 13, 2014 @ 11:46 AM | Reply

    • Hi Jeff, How did you resolved the issue configuring SQLCe….my application is running but it is not updating any table . …I beginner to Quartz.net.

      Regards,
      Shrikant

      Comment by Shrikant — September 14, 2015 @ 11:11 PM | Reply

      • I used this:

        Friend Class Utils
        Public Shared ReadOnly Property QuartzProperties As NameValueCollection
        Get
        QuartzProperties = New NameValueCollection

        ‘ Configure Scheduler
        QuartzProperties.Add(“quartz.scheduler.instanceName”, “Scheduler”)

        ‘ Configure Thread Pool
        QuartzProperties.Add(“quartz.threadPool.type”, “Quartz.Simpl.SimpleThreadPool, Quartz”)
        QuartzProperties.Add(“quartz.threadPool.threadCount”, “10”)
        QuartzProperties.Add(“quartz.threadPool.threadPriority”, “Normal”)

        ‘ Configure Job Store
        QuartzProperties.Add(“quartz.jobStore.misfireThreshold”, “60000”)
        QuartzProperties.Add(“quartz.jobStore.type”, “Quartz.Impl.AdoJobStore.JobStoreTX, Quartz”)
        QuartzProperties.Add(“quartz.jobStore.useProperties”, “false”)
        QuartzProperties.Add(“quartz.jobStore.dataSource”, “default”)
        QuartzProperties.Add(“quartz.jobStore.tablePrefix”, “QRTZ_”)
        QuartzProperties.Add(“quartz.jobStore.lockHandler.type”, “Quartz.Impl.AdoJobStore.UpdateLockRowSemaphore, Quartz”)

        ‘ Configure Data Source
        QuartzProperties.Add(“quartz.dataSource.default.connectionString”, Utils.QuartzDbConnectionString)
        QuartzProperties.Add(“quartz.dataSource.default.provider”, “SqlServerCe-400”)
        End Get
        End Property
        End Class

        And I called it with this:

        Public Scheduler As IScheduler = New StdSchedulerFactory(Utils.QuartzProperties).GetScheduler

        HTH

        Comment by intexx — September 15, 2015 @ 8:02 PM

  2. STEP 6 : In That please remove # before Quartz Properties…..

    Comment by Rahul Mane — May 2, 2015 @ 10:35 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: