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.

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.

You must be a member of the Administrators group on the computer where adTempus is installed.

Procedure

Note: If you want to be able to roll back the change, review the Rollback section below before proceeding.

1. Stop the adTempus service

Before beginning the transfer process, stop the adTempus service using the Windows service control tool.

2. Detach the database

a. On the adTempus server, run the Database Utility tool from the adTempus Server Tools group on the Start menu. When the tool starts it will connect to the adTempus database.

b. In the query window, enter the following commands, using the F5 key to execute each command:

use master F5

select name,filename from sysdatabases F5

Locate the adTempus database in the returned list of databases. It should have a name that begins with "adtempus_". Make note of both the name and the filename.

c. Enter the following command, using the F5 key to execute it (replace databasename with the name of the adTempus database.):

sp_detach_db 'databasename' F5

3. Copy the database files

Locate the adTempus database file that you noted in step 2a above. In addition to the ".mdf" file, there will be another file with the same name and extension ".ldf".

Copy (or move) these two files to the SQL Server data directory on the database server where you want to host the adTempus database.

4. Attach the database on the new server

On the target database server, use SQL Server Management Studio to attach the database files you copied in step 3, to add the adTempus database to the new server.

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 user guide. The following models can be used:

  • SQL Server security (user ID and password).
  • Integrated (Windows) security, with adTempus running under Local System account.

To use SQL Server security, this security model must be enabled on the SQL Server database server. Check with the database administrator to see if this option is available.

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:

  1. Run the Registry Editor and go to key "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\adTempus$default"
  2. Delete the "DependOnService" value from this key

7. 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.

8. Remove SQL Server Express

If you no longer plan to use SQL Server Express you can remove it from the adTempus computer. 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.

Rolling Back the Change

Preparation

If you want to be able to revert to the original configuration in case of problems with the new configuration, be sure to copy (not move) the database files to the new server in step 3, leaving the originals on the adTempus computer.

In step 6, make an export of the Registry key "HKEY_LOCAL_MACHINE\Software\Arcana Development\adTempus\Instances\Default\Database" before you run the database configuration wizard to make the database configuration changes.

Do not uninstall SQL Server Express in step 8.

Rolling Back

To roll back, you need to reattach the adTempus database and restore the database configuration.

1. First stop the adTempus service if it is running.

2. If you closed the Database Utility tool after detaching the database in step 2 above, re-launch it (if you left it running after step 2 above, go on to step 3 below), then use the Set Connection command from the File menu. Under the Database Type, select "SQL Server" and enter the following values:

  • Server: "."
  • Instance: "adTempus"
  • Database: "master"
  • UserID: (blank)
  • Password: (blank)

Click OK to close the connection window.

3. In the Database Utility command window, enter the following command and press the F5 key to execute it (replace databasename and filename with the database name and file name you noted in step 2b):

sp_attach_db 'databasename', 'filename' F5

4. After you have reattached the database, restore the adTempus "Database" key in the Registry to its original value from step 6.

5. Now restart the adTempus service.