Quartz on SQL Server

Status: official. The JDBC job store, the MSSQLDelegate and the DDL script are part of Quartz itself and are maintained with it. Nothing about the job store is community code, unlike the MongoDB and Redis stores.

Source: Spring Boot: Quartz Scheduler and Testcontainers: Microsoft SQL Server Module. All versions except the two Testcontainers ones are managed by Spring Boot 4.1.1 (checked with mvn dependency:tree: mssql-jdbc 13.4.0.jre11, flyway-sqlserver 12.4.0, Quartz 2.5.2). Maven Central lists mssql-jdbc 13.6.0.jre11 as newer; the page was verified with the Boot-managed 13.4.0.

Three details of that list matter. Flyway ships database support as separate modules since version 10, so the project that was tested has flyway-sqlserver next to the Boot starter. The Testcontainers 2.x artifact is testcontainers-mssqlserver and the container class is org.testcontainers.mssqlserver.MSSQLServerContainer (the 1.x class org.testcontainers.containers.MSSQLServerContainer still exists as a deprecated alias). And the Testcontainers module needs an explicit acceptLicense() call, because the image is covered by Microsoft’s end-user licence agreement.

A driver delegate is the class that issues the SQL for one database. The default, StdJDBCDelegate, uses plain JDBC and works on several databases (MySQL and MariaDB among them); engines with a different dialect or a different way of handling binary columns need their own subclass. org.quartz.impl.jdbcjobstore.MSSQLDelegate is the one for SQL Server. Reading its 2.5.2 source, it extends StdJDBCDelegate and overrides exactly three methods:

Source: MSSQLDelegate.java (v2.5.2) and MSSQLDelegate Javadoc.

The delegate does not change the lock statement. That belongs to the job store: JobStoreSupport.initialize looks at the configured driverDelegateClass, and if its name contains the text MSSQLDelegate and selectWithLockSQL is not set, it installs an SQL Server specific one (see Locking: selectWithLockSQL, UPDLOCK and ROWLOCK). The consequence is that the delegate is mandatory, not an optimisation. Spring Boot does not set a delegate (the property is not part of spring.quartz.*), so the effective delegate is StdJDBCDelegate unless you name another one in spring.quartz.properties. The measured behaviour on SQL Server 2022 with the schema in place:

The failure message is worth recognising, because it does not mention Quartz, a delegate or SQL Server’s locking: it comes from the default lock statement, SELECT * FROM QRTZ_LOCKS WHERE …​ FOR UPDATE, which is valid on PostgreSQL, MySQL and Oracle but not in T-SQL, where FOR UPDATE exists only for cursors.

Quartz ships org/quartz/impl/jdbcjobstore/tables_sqlServer.sql in the quartz-2.5.2.jar. Reading it from the jar gives this anatomy:

Source: tables_sqlServer.sql (v2.5.2) and the Spring Boot reference, which says that "the SQL Server, Azure SQL and Sybase scripts may need editing" without saying how (Spring Boot: Quartz Scheduler).

In production the schema should be created by a migration tool, run once and recorded, not by the application’s initializer. With Flyway the edit is one line: delete the USE line, keep everything else, and name the file by Flyway’s convention.

Source: Spring Boot: Database Initialization. The result was run through Flyway 12.4.0 on SQL Server 2022 and created the 11 tables.

Source: Spring Boot: Quartz Scheduler. The keys were run with these values, with the container’s URL and credentials in place of the placeholders.

Boot orders the scheduler after the database initializers, so on startup Flyway runs before Quartz reads its tables; that was visible in the tests, where Quartz started on a database that Flyway had just migrated. Three remarks on the migration itself:

Liquibase handles the same script through sqlFile. All three variants in the measured table worked; the shortest one keeps the GO lines and sets no attribute at all.

Source: Liquibase: SQL changelogs and Spring Boot: Database Initialization. The quartz/tables_sqlServer.sql file is the jar script with the USE line removed. This exact changelog, with the file next to it, created the 11 tables on SQL Server 2022 under Liquibase 5.0.3 and the application then ran the Bookshelf jobs.

Liquibase’s documentation says that statements are split on ; or on a go at the end of a line by default, and the measurement agrees: the variant without splitStatements and endDelimiter, and the variant with the GO lines replaced by semicolons, both worked too. splitStatements="true" with endDelimiter="\nGO" is shown above because it states the intent, and it behaves the same. Setting spring.liquibase.change-log is only needed when the changelog is not at Boot’s default classpath:/db/changelog/db.changelog-master.yaml; with the XML name above, set it to classpath:db/changelog/db.changelog-master.xml. The general Liquibase workflow is on Liquibase: Getting Started, and the choice between the tools is on Choosing a Migration Tool.

Like the PostgreSQL page, this one recommends that Quartz’s tables are kept apart from the business tables and that the runtime user can do less than the migration user. On SQL Server there are two levels of separation: a database of its own (selected by databaseName in the URL, no script change) or a schema of its own in the application’s database. The second needs one more edit, because the script hard-codes [dbo].

Source: Microsoft: CREATE USER. The passwords in the test were random values generated per run; use secrets from your vault.

Source: Microsoft: GRANT Schema Permissions. The three files ran under Flyway 12.4.0; the replacement touches only the bracketed [dbo], so the dbo.sysobjects catalogue view in the drop guards stays as it is.

Source: Quartz: Configure JDBC JobStoreTX and Spring Boot: Database Initialization. The same keys, with the test’s values, ran with the Bookshelf jobs on SQL Server 2022: the jobs fired and survived a restart with a runtime user that holds only SELECT, INSERT, UPDATE, DELETE on the schema.

tablePrefix is the property that carries the schema: Quartz substitutes it into every statement, so quartz.QRTZ_ produces quartz.QRTZ_JOB_DETAILS and so on, and with the user’s DEFAULT_SCHEMA set to quartz as well, the unqualified names used by monitoring queries resolve too. The runtime user needs no DDL rights. It does need INSERT as well as SELECT, UPDATE and DELETE, because the QRTZ_LOCKS rows are inserted by Quartz itself on first use.

Quartz uses a lock table, QRTZ_LOCKS, with two rows per scheduler name (TRIGGER_ACCESS and STATE_ACCESS), and takes a lock by selecting one of them with a row lock inside a transaction. The statement is selectWithLockSQL, and the code default in StdRowLockSemaphore is SELECT * FROM {0}LOCKS WHERE SCHED_NAME = {1} AND LOCK_NAME = ? FOR UPDATE, in which {0} is the table prefix and {1} the scheduler name. SQL Server rejects FOR UPDATE, which is the failure of the delegate table above. Instead, JobStoreSupport.initialize contains this block (at line 671 of the 2.5.2 source):

Source: JobStoreSupport.java (v2.5.2). The code is quoted from the 2.5.2 sources.

The block runs only when the job store uses database locks, which Spring Boot’s LocalDataSourceJobStore (a JobStoreCMT subclass) always does, and it also runs when the scheduler is not clustered. The INFO message is logged by the job store class, so its logger is org.springframework.scheduling.quartz.LocalDataSourceJobStore, not a org.quartz logger. Setting logging.level.org.springframework.scheduling.quartz=INFO produced exactly this line in the test log:

Source: Quartz: Configure Clustering with JDBC-JobStore. The two lines are copied from the log of the test run.

The hints do what their names say: UPDLOCK takes an update lock that other UPDLOCK readers wait for (and keeps it until the end of the transaction), and ROWLOCK asks for a row-level lock rather than a page or table lock, so that the two lock rows do not block each other at a coarser level. Because the default is installed only when selectWithLockSQL is null, a property of your own wins. The hints were observed with two plain JDBC connections against the real server:

Source: Microsoft: Table hints (Transact-SQL). The test read the first row of each result, because the driver returns from executeQuery before the row lock is requested. It is worth knowing when writing such a probe yourself: a statement that is blocked on the lock reports it when the result is read, not when it is sent.

By default, trigger acquisition takes no row lock: JobStoreSupport.acquireNextTriggers claims each trigger with a conditional UPDATE from WAITING to ACQUIRED, and a node whose UPDATE affects 0 rows simply moves on. The WITH (UPDLOCK,ROWLOCK) statement on the TRIGGER_ACCESS row is taken afterwards, in triggersFired and triggeredJobComplete, and that is where a second node waits. Acquisition runs inside the same lock only when org.quartz.jobStore.acquireTriggersWithinLock=true or org.quartz.scheduler.batchTriggerAcquisitionMaxCount is greater than 1.

Source: JobStoreSupport.java (v2.5.2), methods acquireNextTriggers, triggersFired and triggeredJobComplete. The statements are drawn from the source; the behaviour of the lock row itself is the measurement above. The same shape, with FOR UPDATE in place of the hints, is drawn on Quartz on PostgreSQL and Quartz on Oracle.

Azure SQL Database was not available for the tests, so this section is derived from Microsoft’s documentation and from what the script and the delegate do; it is not a measurement.

Source: Microsoft: USE (Transact-SQL) and Microsoft: T-SQL differences between SQL Server and Azure SQL Database. These statements were not run against Azure SQL.

Version 10.2 of the Microsoft driver changed the default of encrypt to true (and since 11.2 the property is a string that also accepts strict), and trustServerCertificate defaults to false. A server with a valid certificate therefore needs no option at all. The Docker image generates a self-signed certificate, so a container needs either encrypt=false or trustServerCertificate=true.

Source: Microsoft: Setting the connection properties. The container URL is the one Testcontainers 2.0.5 returned in the run; the port is random per run.

Do not copy trustServerCertificate=true or encrypt=false into a deployed configuration; they turn off the server authentication that TLS exists for. Two tips in the script’s old header were not needed in any test: SelectMethod=cursor was advice for the SQL Server 2000 driver, and the jTDS driver it mentions is not the Microsoft driver used here. sendStringParametersAsUnicode is true by default; the Microsoft documentation recommends false for VARCHAR columns, but Quartz’s columns are NVARCHAR, so leave it alone unless you have measured a reason (it is also the one setting connected to #1514 above).

Clustering needs nothing SQL Server specific beyond what is on this page: the MSSQLDelegate, the lock statement above, and the usual properties on every node.

Source: Quartz: Configure Clustering with JDBC-JobStore. The clustered runs below used these keys with a 2000 ms check-in interval.

The measurements, all on the SQL Server 2022 container with the edited script, MSSQLDelegate, a three-thread pool per node and the fast Bookshelf schedule (cache refresh every second, reminders every two seconds):

Source: Quartz: Configure Clustering with JDBC-JobStore. "Duplicates" means a scheduled fire time executed more than once; the check is made by recording every execution in the test.

Three honest limits apply. The nodes were schedulers in one JVM, so the test shows the locking, not network behaviour. The runs lasted seconds, so they say nothing about the low-probability deadlocks of #1514, which was reported under production load and is not reproduced here: absence of a deadlock in a 20-second probe is not evidence that the report is wrong. And the uneven split of executions between nodes (31 executions of one job: 8, 6 and 17) is normal; Quartz does not load-balance, the first node to take the lock fires. How fail-over and requestsRecovery work is measured on PostgreSQL and described on Clustering.

The test starts SQL Server, lets Flyway create the tables from V1__quartz_tables.sql (the script with the USE line removed), starts the Bookshelf service with the JDBC job store and checks that the jobs were stored and that one of them fires. The Bookshelf schedule is the one from Getting Started with Spring Boot, with a property that makes the cache refresh fire every second.

Source: Testcontainers: Microsoft SQL Server Module and Spring Boot: Testcontainers. The test was run on 5 October 2026 and passed (1 test, 0 failures, about 50 seconds including the image start).

What the run established, stated plainly because the page is the only place it is written down:

The wider Testcontainers setup (reuse, @ServiceConnection, CI runners) is on Spring Boot Integration Tests with Testcontainers.

If the business data is also on SQL Server, the simplest layout is one Hikari data source, which Boot shares with Quartz: a single DataSource bean is picked up for the job store automatically. With a second data source you need @QuartzDataSource (and @QuartzTransactionManager when the Quartz data source has its own transaction manager), exactly as on the Job Stores & Persistence page, and the pitfall measured there applies here too: two data sources with no @Primary make Boot silently fall back to the RAM store, and @QuartzDataSource does not rescue that (mark the application’s data source @Primary and Quartz’s @QuartzDataSource). Entities and repositories are covered on Spring Data JPA; the general SQL material of this site is under SQL.

Collation can matter on SQL Server, and it was tested: a database created with the case-sensitive collation Latin1_General_100_CS_AS ran the Bookshelf jobs and the restart check without any change, because Quartz addresses its tables and columns in upper case, as the script creates them. The pitfall is on your side: monitoring queries and any code that writes qrtz_triggers in lower case would fail on such a database.

Source: the measurements and sources cited in the sections above.

The complete list of version changes and documentation discrepancies of the section is on Migration & What’s Changed.

Source: Quartz releases, Quartz pull request #1515 and commit b9e92c33.