Skip to content

SQL Agent Configuration

If you'll be using the SQL Agent, it must be configured using the SQLAgent property in MasterConfig.json.

SQL Agent Configuration
"SQLAgent": {
  "AdminLoginDurationMinutes": 60,
  "ConsumerWaitTime": 1,
  "DropAndRestoreOnInvalidReplayState": true,
  "EnableHostTerminal": true,
  "EnableSwaggerUI": true,
  "HelperAssembly": "C:/PATH/TO/SQLTableHelpers.dll",
  "KafkaClientId": "DEFAULT_REPLICATOR1",
  "KafkaConsumerGroup": "DEFAULT_REPLICATOR",
    "KafkaClientAuthentication": {
      "Enabled": false,
      "Certificate": "C:/PATH/TO/CLIENT-CERTIFICATE.PEM",
      "KeyFile": "C:/PATH/TO/CLIENT-PRIVATE-KEY.PEM",
      "KeyPasswordFile": "C:/PATH/TO/CLIENT-PRIVATE-KEY-PASSWORD.TXT"
    },
  "LogCacheMaxRows": 1000,
  "LogFilePath": "C:/PATH/TO/LOGDIR",
  "LogKeyValues": false,
  "LogToFile": true,
  "LogToHost": true,
  "SleepTime": 1,
  "SnapshotProvider": "RemoteS3",
  "SnapshotTimeoutMinutes": 60,
  "StopOnError": false,
  "SystemLogging": false,
  "TableCheckOnStartup": true,
  "TempStoragePath": "C:/PATH/TO/TMPDIR",
  "VerboseLogging": false,
  "Database": {
    "AdminUser": "adminuser",
    "AdminPassword": "adminpassword",
    "BatchSize": 10000,
    "BatchTimeoutSeconds": 0,
    "CompressionMode": "None",
    "Database": "DatabaseName",
    "LoadTimeoutSeconds": 1800,
    "MasterKeyDecryptionPassword": "password",
    "Password": "userpassword",
    "RetryDelaySeconds": 30,
    "RetryMax": 10,
    "S3CredentialName": "S3Credential",
    "S3DataSourceName": "S3DataSource",
    "Server": "localhost",
    "Sql2022TimeProcessing": false,
    "TimeoutSeconds": 60,
    "User": "databaseuser"
  },
  "EnvironmentVariables": [],
  "PeriodicServices": {
    "Entries": [
      {
        "Name": "PeriodicService1",
        "Program": "C:/PATH/TO/PROGRAM",
        "Arguments": "--arg1 --arg2 value2",
        "WorkingDirectory": "",
        "CronExpression": "* * * * *",
        "Priority": 1,
        "Enabled": true,
          "PauseEnabled": false
      }
    ]
  }
}

Configuration Properties

Property Type Description
AdminLoginDurationMinutes Integer Once logged in, how many minutes an admin session lasts before the user must log in again (default 60)
ConsumerWaitTime Integer When the Kafka consumer returns with no message, how many seconds the agent should wait before retrying (default 1)
DropAndRestoreOnInvalidReplayState Boolean If startup cannot locate or validate the persisted CDC replay position, enabling this option allows the agent to recover automatically by dropping and rebuilding all replicated tables from a snapshot.
EnableHostTerminal Boolean Within the admin UI, should the host terminal option be available?
EnableSwaggerUI Boolean Should the agent expose API information via SwaggerUI?
HelperAssembly String The path to the .NET assembly containing the code generated SQL Helper classes for the environment
KafkaClientId String The client ID to use when subscribing to Kafka
KafkaConsumerGroup String The consumer group to use when subscribing to Kafka
KafkaClientAuthentication Object If the Kafka server is configured for MutualTls transport security, this object defines the client certificate and associated password to present to the server.
.. Certificate String The path to the client certificate PEM file. Authentication is not used when this value is empty.
.. Enabled String When enabled and a certificate file is specified, Kafka clients use mutual TLS client certificate authentication.
.. KeyFile String The path to the private key PEM file associated with the client certificate.
.. KeyPasswordFile String The path to a file containing the private key password.
LogCacheMaxRows Integer The maximum number of log entries that can be cached in the dashboard app (1 to 5000, default 1000)
LogFilePath String If file logging is enabled, the full path where the agent should create log files
LogKeyValues Boolean Should the agent log key values when processing changes? CAUTION: Only use this for development/debugging
LogToFile Boolean Should the agent log messages to a log file?
LogToHost Boolean Should the agent log messages to that ASP.NET application log?
SleepTime Integer If the agent runs out of work (changes to process), how many seconds to wait before checking for new changes
SnapshotProvider Enumeration The type of the Snapshot storage provider (RemoteS3 or LocalS3)
SnapshotTimeoutMinutes Integer The maximum number of minutes to wait for a requested snapshot to become available
StopOnError Boolean Should the agent stop processing if an error is encountered? Not stopping results in an out-of-sync database
SystemLogging Boolean Should the agent log messages to the system log (Windows event log/Linux syslog)?
TableCheckOnStartup Boolean Should the agent check for and attempt to load missing database tables during startup?
TempStoragePath String The full path where the agent should store temporary files
VerboseLogging Boolean Should verbose logging be enabled? CAUTION: Only use this for development/debugging
Database Object Database configuration information
Database.AdminUser String The username of a SQL Server login that has admin rights
Database.AdminPassword String The password to the SQL Server admin account
Database.BatchSize Integer The number of CDC operations that should be grouped together into a batch for commit purposes (default 1000)
Database.BatchTimeoutSeconds Integer The number of seconds of inactivity allowed before committing a partial batch (default 0, no timed commit)
Database.CompressionMode Enumeration The type of database compression to use (None (default), Page or Row)
Database.Database String The name of the SQL Server database
Database.LoadTimeoutSeconds Integer How many seconds to wait before a bulk load operation times out (default 21600 = 6 hours)
Database.MasterKeyDecryptionPassword String The password used to decrypt the master key for the database. Used to encrypt and decrypt the password for the S3 credentials
Database.Password String The password for the main database user account used by the agent (required)
Database.RetryDelaySeconds Integer How many seconds to wait before retrying a failed database connection (default 30)
Database.RetryMax Integer The maximum number of retry attempts to make after a failed database connection (default 10)
Database.S3CredentialName String The name of the S3 credential to use for accessing S3 resources (required)
Database.S3DataSourceName String The name of the S3 data source to use for accessing S3 resources. This is used to access files in S3 buckets
Database.Server String The DNS name or IP address of the database server (required)
Database.Sql2022TimeProcessing Boolean Work around a SQL Server 2022 bug related to the processing of time fields (default false)
Database.TimeoutSeconds Integer The number of seconds to wait for normal operations before timing out (default 60)
Database.User String The database user name to connect as. The user should be a SQL Server account and should be the owner of the database
EnvironmentVariables Object array An array of environment variables to set in the agent environment
EnvironmentVariables.Name String The name of an environment variable
EnvironmentVariables.Value String The value of an environment variable
PeriodicServices Object Hosted commands that run on a cron schedule
PeriodicServices.Entries Object[] An array of periodic service entries
...Entry.Name String Job name
...Entry.Program String The command to run
...Entry.Arguments String Command line arguments
...Entry.WorkingDirectory String The working directory to be set for the running program
...Entry.CronExpression String A valid cron expression that defines when the job should run
...Entry.Priority Integer Job priority (1 is the highest, then 2, etc.) (default 1)
...Entry.Enabled Boolean Is the job enabled? (default false)
...Entry.PauseEnabled Boolean Can the service be paused by an administrator? (default false)