Skip to main content
Version: 3.2

Microsoft SQL Server

Overview

Microsoft SQL Server is a production-capable database server that can be installed locally or on a network server. Microsoft SQL Server is also available as a fully-managed cloud service via Amazon Aurora, Azure Database, and Google Cloud SQL.

Integration Manager is compatible with Microsoft SQL Server version 13 (aka SQL Server 2016) or later.

Step 1: Install MS SQL Server

Step 2: Verify MS SQL Server Service

  1. Go to WindowsServices.
  2. Confirm SQL Server (MSSQLSERVER) service is registered and running.
  3. Open ProgramsMicrosoft SQL Server ToolsMicrosoft SQL Server Management Studio to confirm your connection information.
  4. If you run into problems, see https://docs.microsoft.com/en-us/sql/database-engine/install-windows/repair-a-failed-sql-server-installation?view=sql-server-2016.

Step 3: Integration Manager Configuration

Integration Manager uses the "spring.datasource" prefix properties in the ../conf/application.properties file to create a database connection.

note

Any change to application.properties requires a restart of the Integration Manager Service.

Integration Manager will initialize all required database tables at the initial startup.

Here are some example properties to connect Integration Manager to a MS SQL Server database:

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://DB_HOSTNAME;databaseName=datacloud_db;integratedSecurity=true
spring.datasource.initialize=false
spring.datasource.continue-on-error=false
spring.jpa.properties.eclipselink.cache.shared.default=false
spring.liquibase.change-log=classpath:db.changelog-master.xml

Step 4: Create SQL Server Compatible Quartz Properties file

The default Quartz configuration is not compatible with SQL Server, so you will need to create a custom properties file as follows:

  1. Create a file named quartz.properties in the <ProgramDataDirectory>\Actian\IntegrationManager\conf folder.

    note

    Any change to quartz.properties requires a restart of the Integration Manager Service.

  2. Add the following contents to properly initialize the Quartz subsystem in SQL Server:

org.quartz.scheduler.instanceName=ServerScheduler
org.quartz.scheduler.instanceId=AUTO
org.quartz.scheduler.skipUpdateCheck=true
org.quartz.scheduler.jobFactory.class=org.quartz.simpl.SimpleJobFactory
org.quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.makeThreadsDaemons=true
org.quartz.threadPool.threadCount=20
org.quartz.threadPool.threadPriority=5
org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
org.quartz.jobStore.useProperties=true
org.quartz.jobStore.misfireThreshold=6000000
org.quartz.jobStore.tablePrefix=QRTZ_
org.quartz.jobStore.isClustered=true
org.quartz.jobStore.clusterCheckinInterval=30000
org.quartz.jobStore.txIsolationLevelSerializable=true
org.quartz.jobStore.acquireTriggersWithinLock=true
org.quartz.jobStore.lockHandler.class=org.quartz.impl.jdbcjobstore.UpdateLockRowSemaphore

Step 5: Configure the Integration Manager Service for Domain login

  1. Go to WindowsServicesActian Integration Manager.
  2. Right-click Actian Integration Manager and select Properties.
  3. Select the Log On tab.
  4. Select This account: and enter a Windows Domain User with read/write access to MS SQL Server.
  5. Click OK.
  6. Select the General tab.
  7. Right-click Actian Integration Manager, select Start or Restart (depending on whether the service is currently running).