Moving the Database to a Standalone SQL Server

This topic explains how to move the adTempus database from the SQL Server Express instance installed with adTempus to a standalone SQL Server instance. The steps are:

  1. Back up the database.
  2. Restore the database on the new server.
  3. Configure adTempus to use the new database.

Requirements

  • You must have a licensed SQL Server instance already installed. (SQL Server Express is provided with adTempus and does not require a separate license.)
  • These instructions assume that you are familiar with basic SQL Server management, or are working with a database administrator who is. They focus on the steps specific to adTempus.
  • The instructions use SQL Server Management Studio (SSMS), which is available free from Microsoft. If you run SSMS on a different computer than SQL Server Express, you must first enable remote connections to the SQL Server Express instance.
  • adTempus requires a constant, reliable connection to the database server. Interruptions may cause missed job executions, job failures, and loss of history. Make sure the network connection to your database server is reliable, and that the adTempus service is stopped before maintenance that makes the database unavailable.

Database Security

Decide how adTempus will authenticate with the new server (see Using a Standalone SQL Server and SQL Server Authentication):

  • Windows integrated security. adTempus connects using the computer account of the adTempus computer (e.g., "mydomain\myserver$"). The computer must be able to authenticate with SQL Server (for example, by being in the same or a trusted domain).
  • Microsoft Entra ID, if supported by your database server.
  • SQL Server security (user ID and password). The database server must be configured to allow SQL Server authentication.

Procedure

  1. Stop the adTempus service so that the data is in a consistent state. (You can copy the database while the service is running, but any activity after you start the backup will not be in the new database.)

  2. Back up the database. In SSMS, connect to the SQL Server Express instance (".\ADTEMPUS" on the same computer, or "computername\ADTEMPUS" from another computer). Expand the Databases folder, right-click the adTempus database (its name begins with "adTempus_"), and select Tasks > Back Up. Add a destination file and click OK. The backup file is created on the computer where SQL Server Express is running.

  3. Copy the backup file to the computer hosting the new SQL Server instance.

  4. Restore the database. In SSMS, connect to the new SQL Server instance, right-click the Databases folder, and select Restore Database. Set the source to Device and add the backup file. On the Files page, change the location of the database files if necessary.

  5. Configure security. On the new server, create the login that adTempus will use if it does not already exist (for integrated security, see SQL Server Computer Account). Map the login to the adTempus database with the db_datareader, db_datawriter, and db_backupoperator roles.

  6. Remove the service dependency. The adTempus service depends on the SQL Server Express service. Remove the "MSSQL$ADTEMPUS" line from the service's DependOnService setting as described in Service Dependencies.

  7. Back up the database settings. In the Registry Editor, export the key HKEY_LOCAL_MACHINE\SOFTWARE\Arcana Development\adTempus\Instances\Default\Database to a file, so that you can revert to the old database if necessary.

  8. Configure adTempus to use the new database. Run the Database Configuration Wizard and enter the information for the new server and database. On the Select Actions page, check only the options to configure adTempus to use the database and to start the service. If the service fails to start, see Service Startup Troubleshooting.

  9. Configure database backups. adTempus backs up the database automatically only when it uses SQL Server Express. Include the database in your existing SQL Server backups, or configure adTempus to back it up on the Data Backup page of the Server Options. See Database Backups.

  10. Remove SQL Server Express (optional). Once you have confirmed that adTempus is working with the new database, you can uninstall the "ADTEMPUS" instance of SQL Server using Add/Remove Programs. Other applications may have their own SQL Server instances; be sure not to remove them.

Rolling Back the Change

If you have problems with the new database, you can return to the old database by importing the Registry file you exported in step 7 (you may wish to export the new settings first), then restarting the adTempus service. If you removed the service dependency, add it back as well.

Related Topics

Backing Up and Restoring Data

Database Connection Settings

Database Ownership

Database Size and Performance

Using a Standalone SQL Server Instance

Moving or Upgrading the adTempus Database