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 the Creating a Maintenance Window in JAMS article. 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. The permissions may need to be removed and re-added. This includes the Login from the instance, and also the User from the database.
- 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 regardless of failover configuration.
5. Restart the JAMS Scheduler service and the JAMS Server service from the Windows control panel services applet.
6. If running JAMS V6.4.X or older, 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.
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>