Note: This article applies to adTempus 4 and later. For adTempus 3 and earlier, see article K00000420.
Summary
This article describes how to move the adTempus database from the default SQL Server Express database to a standalone SQL Server database server.
This involves the following steps:
- Backing up the adTempus database
- Restoring the database on the new server
- Configuring adTempus to use the new database
Note: Previous versions of this article were written for users with limited access to SQL Server database tools, and used a different approach to detach, copy, and reattach the database. The article has been updated to use newer tools and a backup/restore approach.
Background, Requirements, and Assumptions
- SQL Server Express is provided with adTempus and does not require a separate license. If you want to move the adTempus database to a full SQL Server database server, you must have a licensed instance of SQL Server already installed.
- This article assumes that you are familiar with basic SQL Server database management procedures, or that you will be working with a database administrator who is.
- This article does not provide detailed instructions for all steps of the process: it focuses mainly on adTempus-specific tasks.
- This article assumes you are working with the default adTempus instance. If you are working with a different instance, use the appropriate instance name instead of "Default" in the instructions.
Prerequisites
SQL Server Management Studio
These instructions use SQL Server Management Studio (SSMS), which is freely available from Microsoft (download here). You can install SSMS on the adTempus server in order to manage the SQL Server Express instance, or you can use an installation of SSMS on another computer to connect to the instance.
If you are running SSMS on a different computer than where SQL Server Express is located, you must enable remote access to the SQL Server Express instance.
Database Connectivity
adTempus requires a constant, stable connection to the database server. Interruptions in connectivity may result in missed job executions, job failures, and loss of job history information.
- Ensure that the network connection between adTempus and the database server is reliable
- Ensure that procedures are in place to shut down the adTempus service before performing maintenance operations that make the database unavailable
Database Security
If your database server allows only Windows integrated security, then the adTempus computer must be able to authenticate with SQL Server (e.g., the computer must be in the same domain or a domain with trust or delegation configured) machine account of the adTempus computer. adTempus will connect to the database under the computer account of the computer where it is running; that account must have read and write permission for the adTempus database. For example, if the computer is "mydomain\myserver" the computer account in Active Directory will be "mydomain\myserver$" (with a trailing $).
Beginning with adTempus 5.1, adTempus can use Microsoft Entra ID to authenticate with the database server (more information). This is not supported for earlier adTempus versions.
If you are not using Windows integrated security or Microsoft Entra ID, your database server must be configured to allow SQL Server (user ID and password) security.
Procedure
1. Stop the adTempus service (optional)
Before beginning the transfer process, if possible you should stop the adTempus service using the Windows service control tool so that the adTempus data is in a consistent state.
You can still copy the database if adTempus is running, but any activity that happens in adTempus after you start the database backup will not be reflected when you switch to the new database.
2. Back up the adTempus database
Connect SSMS to your SQL Server Express instance. adTempus installs SQL Server with the instance name "ADTEMPUS"
- If SSMS is on the same computer, connect to the server name ".\adtempus"
- If SSMS is on a different computer, connect to the server name "computername\adtempus" (where ccomputername is the name or IP address of the computer where adTempus and SQL Server Express are installed.
Expand the Databases folder in SSMS and locate the adTempus database. This database will have a name starting with "adtempus_".
Right-click the database name and select Tasks>Back Up.
In the backup window, leave all settings at their default. In the Destination section, click Add and specify the folder and filename where you want to save the backup. Note: The backup location must be on the computer where SQL Server is installed, even if you are running SSMS remotely.
Click OK to create the backup.
3. Copy the database files
Locate the adTempus database backup file that you created in the previous step. Copy (or move) this file to the computer hosting the destination SQL Server instance.
4. Restore the database on the new server
Connect SSMS to the new SQL Server instance.
Right-click the Databases folder in SSMS and select Restore Database.
In the Restore Database window, change the Source to Device. Click the browse button (...). In the Select backup devices window click Add add the backup file you copied from the old server. Click OK to return to the Restore Database window.
Select the Files page and adjust the Restore As location if you need to change the location where the database files will be stored. Consult your DBA if you are unsure.
Click OK to restore the database.
5. Configure security for the database
Determine which security model you will use to allow adTempus to connect to the database. For more information see the Database Security section above and the user guide. The following models can be used:
On the target database server, create the database login that adTempus will use, if it does not already exist.
On the User Mapping page of the settings for the login, map the user to the adTempus database. Assign the login the db_datareader and db_datawriter roles for the database.
6. Remove SQL Server service dependency
The adTempus service is configured to depend on SQL Server Express, and will not start once SQL Server Express is removed. To remove the service dependency:
- Run the Registry Editor and go to key "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\adTempus$default" (replace "default" with the appropriate instance name if necessary).
- Edit the "DependOnService" value and remove the line with "MSSQL$adTempus".
7. Back up the adTempus database settings
Run the Registry Editor and go to key "HKEY_LOCAL_MACHINE\SOFTWARE\Arcana Development\adTempus\Instances\Default\Database". Select File>Export and export this key to a file. This will allow you to revert the change if there is a problem with the new database.
8. Change adTempus database settings
Run the Database Configuration Wizard from the adTempus Server Tools group on the Start menu. Proceed through the configuration wizard as described in the Database Installation and Configuration topic in the installation guide, entering the information for the new database server and database name.
When you reach the Select Actions page, check only the Configure adTempus to use the database and Start the adTempus service options.
Click Next to have the wizard apply the configuration changes and start the adTempus service.
If the service fails to start, check the Windows Event Viewer for error messages from adTempus and refer to article K00000222 for additional troubleshooting information. If you want to revert to the old database, see Rolling Back the Change below.
9. Remove SQL Server Express
Once you have confirmed that adTempus is working correctly with the new database, you can remove SQL Server Express if you no longer plan to use it. To do so, locate "Microsoft SQL Server" in the Add/Remove Programs tool, then select the option to remove it. When prompted, select the "ADTEMPUS" instance to remove.
Important: Other applications may also be using SQL Server Express; each application will have its own instance of SQL Server. If there are other instances listed when you go through the uninstall process, be sure you do not remove them.
10. Configure database backup
By default adTempus creates a daily backup of the database when you are using SQL Server Express. When you use a standalone SQL Server instance it does not create the backup by default, and you must configure the backup explicitly using one of these approaches:- Manage the backup outside of adTempus.You can back up the adTempus database as part of your existing backup strategy for the database server, or schedule a new backup using SQL Server or a third-party backup tool.
- Configure the backup in adTempus from the Data Backup settings
Rolling Back the Change
If you encounter problems with the new database, you can revert to the old database by simply restoring the adTempus database settings in the Registry, using the export file you created above. You may wish to export the new database settings first so you can switch back to them without running the Database Configuration Wizard again. Alternatively you can rename the "Database" key to a new name, such as "New Database".
After you restore the database settings, you must restart the adTempus service (if it is running) for the changed settings to take effect.
