Managing the JAMS Database

The JAMS Database provides critical features to the JAMS Scheduler and stores JAMS definitions. The following section contains information on managing the JAMS SQL database back end.

Understanding the JAMS Database Connection

The JAMS Database connection is defined by a file or set of files called "common.config". You can modify the connection string in these locations as needed with a text editor. Depending on your configuration, the default location of these files will be one or both of the following:

  • JAMS Scheduler and JAMS Supporting applications: C:\Program Files\MVPSI\JAMS\Scheduler\
  • JAMS Web Client: C:\Program Files\MVPSI\JAMS\WebClient\

It may be necessary to restart the JAMS Services to implement changes to the database connection settings. A service interruption should be planned and communicated when making modifications to these files and restarting the JAMS Services.

NOTE: It is recommended to make a backup of this file before making any changes, as improperly modifying this file could lead to services not functioning properly.

Configuring the JAMS Database Connection

NOTE: Improperly modifying the JAMS Database connection may result in JAMS behaving unexpectedly or unable to run and schedule Jobs. Any modifications should be made under advisement of your JAMS Database Administrator, as some level of JAMS service outage will occur when modifications to database connections are made.

As of versions 18 and 19 of the OLE DB and ODBC Microsoft SQL Server drivers, the default behavior of connecting to the JAMS Database may have changed. The default behavior of the Microsoft SQL Drivers has been improved to enhance the security of SQL Database Connections.

Starting with JAMS 7.7.2, JAMS uses updated drivers affected by these new behaviors, and adjustments may be necessary to the JAMS database connection in the common.config files to preserve JAMS functionality.

When you upgrade to a version of JAMS affected by these changes, the JAMS installer will display a message that the certificate chain was issued by an authority that is not trusted. This is displayed when JAMS cannot connect to the database due to using an unencrypted connection or requiring a certificate.

There are several solutions to this problem detailed in this document. It is recommended you work with your JAMS Database Administrator to implement a solution. You may need to modify the common.config file for the JAMS Scheduler, JAMS Web Client, and/or the SQL Connection in the JAMS Connection Store. See Connections to Database Servers for more information on modifying the SQL Connection.

If no adjustments are made to the JAMS connection parameters to address this updated default behavior, the JAMS installer will not complete the installation.

Manually Creating the JAMS Database

Typically, the JAMS Database is created with the JAMS Scheduler during the installation process. However, in some cases, when installing the JAMS Scheduler you may prefer not to create the JAMS Database. See Creating the JAMS Database for more information.

NOTE: The JAMS Database should always be created using the JAMS Installer or JAMSDBA utility. Manually creating the JAMS Database outside of these tools will cause various issues in JAMS.

Security

JAMS supports either Windows Integrated Authentication or SQL Server Authentication. This can be modified after installation by changing the connection string found in the Common.config file. The default installation requires the JAMS Services to be running under the LocalSystem account. To facilitate this, the installation executes the following SQL commands:

Copy 

exec sp_grantlogin @loginame='BUILTIN\Administrators' 
exec sp_grantdbaccess @loginame='BUILTIN\Administrators', @name_in_db='JAMSService' 
exec sp_addrolemember @rolename='JAMSApp', @membername='JAMSService'

The effect of using Windows Integrated authentication is that anyone in the administrators group can map the JAMSService Database user and become a part of the JAMSApp Database role.

NOTE: You can modify the security to fit your needs, but the JAMS Services must be included in the JAMSApp Database role.

Backing up the JAMS Database

It is critical to back up the JAMS Database. Since the JAMS Database is a standard SQL Server database, simply add it to your existing SQL Server backup procedures. You can also use the JAMS Job named JAMSBackup, located in the JAMS folder, to back up the JAMS Database.

Choosing the Recovery Model

Before implementing a backup plan, select the recovery model to use with the JAMS database. The default installation uses the Simple Recovery Model. The other option is the Full Recovery Model. The main difference between the two is in the amount of journaling performed by the database.

Simple Recovery Model

The Simple Recovery Model is the easiest to use. You periodically backup the JAMS database only, not the journal file. The downside to this model is a lack of recovery options. If the JAMS Database becomes corrupted, you can recover it from a backup but all database changes since the last backup will be lost. This includes job execution history, so Job dependencies may not work as expected.

Full Recovery Model

This model contains more robust recovery features. However, the disadvantage of this model is that you must manage the journal file for the JAMS Database. Each time a change is made to the JAMS Database it is also written to the journal file.

For example, if a hardware failure occurs or you have otherwise corrupted the JAMS Database, you can restore a backup and then recover it using the journal files up to a specific point in time.

Restoring the JAMS Database from a Backup

Restoring a JAMS Database from a backup requires a standard SQL Server restore operation, but there are some details to consider before attempting the restoration process.

The JAMS Database includes the current schedule. Often, you may not want the current schedule to be restored. For example, if the database was backed up on Monday and you restore it on Friday, you probably don’t want to start running Monday’s Jobs along with the rest of the week’s processing.

You can use JAMSDBA.exe to reset the Schedule.

Copy 
CD C:\Program Files\MVPSI\JAMS\Scheduler " JAMSDBA RESET SCHEDULE
CD C:\Program Files\MVPSI\JAMS\Scheduler" JAMSDBA UPDATE/LOG

Moving or Restoring the JAMS SQL Database

The process for moving the SQL database used by JAMS does not require reinstalling JAMS.

  1. Ensure there are no Jobs currently running or are scheduled to run in the near future. You can manually put all Jobs on hold by using the Monitor view or by using a Maintenance Resource as described in the Creating a Maintenance Window  article. When restoring the Database and activating the JAMS Server, you may not want jobs to run at that time.
  2. Use the JAMSBackup Job in the JAMS folder to back up the SQL database and restore to the target server.
  3. Edit the connection string property of the Common.config file located at C:\Program Files\MVPSI\JAMS\Scheduler\ by default.
  4. Update the server location and the database name, if the name of the database is being changed.
  5. Update the permissions to the JAMS Scheduler Server to connect to the restored JAMS Database.
  6. Replace YourDomain\YourMachineName with your own name in both lines of the query:
    Copy

    exec_sp_grantlogin @loginame='YourDomain\YourMachineName$'
    exec_spgrantdbaccess @loginame='YourDomain\YourMachineName$', @name_in_db='JAMSMachine2'
    exec_sp_addrolemember @rolename='JAMSApp', @membername='JAMSMachine2' 
         
    NOTE: If you have a failover server, change the JAMSMachine2 to JAMSMachine1 in the query and run the query again. You need to truncate the Failover table of the JAMS Database regardless of the failover configuration.
  7. Restart the JAMS Scheduler Service from the Windows Control Panel.
  8. If you are running JAMS V6.4X or older, run JAMS_DB_ERRORs.sql, located in the JAMS Scheduler directory, on the master database within the same SQL instance where the JAMS Database resides.
NOTE: Moving a SQL/JAMS database does not require reinstalling JAMS.

The Common.config file contains the connection string used by JAMS services to link to the SQL database. Below are two examples of connection strings: one for Windows Authentication and another for SQL Authentication.

Windows Authentication

Copy 

<?xml version="1.0" encoding="utf-8" ?>
<appSettings>
<add key="ConnectionString" value="Server=SQLA\INST1; Failover Partner=SQLB\INST1; Database=JAMS; Application
Name=JAMS; Connect Timeout=600; Integrated Security=SSPI; TrustServerCertificate=true" />
</appSettings>

SQL Account Authentication

Copy 

<?xml version="1.0" encoding="utf-8" ?>
<appSettings>
<add key="ConnectionString" value="Server=SQLA\INST1; Failover Partner=SQLB\INST1; Database=JAMS; Application
Name=JAMS; Connect Timeout=600; Trusted_Connection=False; uid=YOURSQLACCOUNT; pwd=YOURPASSWORD; TrustServerCertificate=true;" />
</appSettings>