Moving the JAMS SQL Database

Follow

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

 

1. Make sure that any jobs are not running or scheduled to run any time soon. It may be worthwhile to put all jobs on hold manually using the Monitor view, or using a Maintanence Resource as described in this document. http://support.jamsscheduler.com/hc/en-us/articles/219436108-Creating-a-Maintenance-Window-in-JAMS. The reason for this is that when restoring the database and activating the JAMS Server, you may not want jobs to run at that time.

2. Perform a normal SQL database backup and restore to target SQL server. A backup can be performed utilizing the JAMSBackup job within your JAMS Folder.

3. Edit the connection string property of the Common.config file found in C:\Program Files\MVPSI\JAMS\Scheduler\ by default. Update the server location and the database name if the name of the database is being changed.

4. Permissions may need to be re-added to the JAMS Scheduler server to connect to the restored DB.

    • Replace 'YourDomain\YourMachineName' with your own name in both lines of the query:
exec sp_grantlogin @loginame='YourDomain\YourMachineName$'

exec sp_grantdbaccess @loginame='YourDomain\YourMachineName$', @name_in_db='JAMSMachine2'

exec sp_addrolemember @rolename='JAMSApp', @membername='JAMSMachine2'

 

    • If you have a failover server, change the 2 to a 1 in the query (IE: JAMSMachine2 to JAMSMachine1), then execute the query again.
    • You will need to truncate the Failover table of the JAMS database if not in a failover configuration. 
 
5. Restart the JAMS Scheduler service from the Windows control panel services applet.
6. Run "JAMS_DB_ERRORS.sql" on the master database within the same SQL instance the JAMS DB resides; this script is found in the JAMS Scheduler directory.
NOTE: If using JAMS V6.5.X or newer, users no longer need to run JAMS_DB_ERRORS.sql.

 


The Common.config file contains the connection string used by JAMS services to connect to the SQL database. Below are two examples of connection strings, one for Windows Authentication, one for SQL Server Authentication.


For Windows Authentication:

 

<?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"/>
</appSettings>

 

For SQL Account Authentication:

 

<?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" />
</appSettings>

 

 

Have more questions? Submit a request

Comments