Note: This article applies to adTempus 3 only. For adTempus 4 and later, see article K00000421.  

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 "adtdbutil" tool found in the adTempus program directory. 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:

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

a. SQL Server Security

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. If so, create a new login on SQL Server that adTempus will use, and ensure that it has the following roles for the adTempus database:

  • db_datareader
  • db_datawriter
  • db_backupoperator

Make note of the user ID and password used when creating the login.

b. Integrated (Windows) security, with adTempus running under Local System account

Follow the procedure described in article K00000397 to grant the adTempus machine account access to the SQL Server database.

c. Integrated (Windows) security, with adTempus running under user account  (not recommend for adTempus 4 or later).

Note: This configuration is not fully supported in adTempus 4 and later. Use option b instead for maximum future compatibility.

Follow the procedures under Change the account under which the adTempus service runs to change the service account for adTempus.

In SQL Server, create (if necessary) a login for the Windows user account, and make sure it has the following roles for the adTempus database:

  • db_datareader
  • db_datawriter
  • db_backupoperator

6. Change adTempus database settings

You must now update the adTempus database settings in the Registry to point to the new database location.

In the following examples, make the following replacements:

  • "dbserver" is the name of the new database server
  • "databasename" is the name of the adTempus database (from step 2b)
  • "userid" is the SQL Server login created in step 5a (if applicable)
  • "password" is the SQL Server password created in step 5a (if applicable)

On the adTempus server, run the Registry Editor and go to key "HKEY_LOCAL_MACHINE\SOFTWARE\Arcana Development\adTempus\Database". Make the Registry value changes described here based on the security model you selected in step 5.

a. SQL Server Security

Set the ConnectionString value to

Provider=SQLOLEDB;server=dbserver;database=databasename

Set the UserID value to the userid (create the value if necessary, as a REG_SZ value)

Set the Password value to the password (if there is already a binary password value, delete it and recreate it as a REG_SZ value)

b, c. Integrated (Windows) security, with adTempus running under Local System account or under user account

Set the ConnectionString value to

Provider=SQLOLEDB;server=dbserver;database=databasename; Integrated Security=SSPI

If there are UserID and Password values present under the Registry key, delete them.

7. Start the adTempus service

Start the adTempus service using the Windows service control tool.

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 before you make any 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 adtdbutil tool after detaching the database in step 2 above, re-launch it as follows (if you left it running after step 2 above, go on to step 3 below): Run the adtdbutil tool and 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 adtdbutil 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.