Database Utilities

Creating and updating databases for CM4D can be a lengthy process when done manually, so we have provided some simple command line tools with the CM4D Server and CM4D Results Services installers to make database maintenance easier.

• Create or Upgrade a Site Database - Run for new installations or version upgrades.
• Create or Upgrade a CM4D Database - Run for new installations, version upgrades, or creating additional databases.
• Results Database Utility - Run for new installations or version upgrades.
• Replace Database GUIDs - Run after copying a database or restoring a database from a backup.
• Generate User Roles - Extract user/group information from the CM4D Site database for transfer into ATS Security.
• Data Command Generator - Migration tool; generates missing DataSource IDs in the Results database (upgrade from 25.2 to 25.3).

The CM4D Site, CM4D and Results databases can be created/updated manually by running scripts in SSMS or automatically via using the utilities provided in the CM4D install folder.

To ensure you have sufficient system privileges to run the database utilities, it is recommended executing the tool using 'Run as administrator'.

Information for DBAs

Some organizations may have a dedicated Database Administrator outside of CM4D users that creates and maintains all databases, or may need to run Database Utilities separately from CM4D for another reason. In such cases, the information in this topic can be provided to a DBA or other qualified user that will manage the various CM4D databases.

The Database Utilities folder are included with the CM4D installations. After installation, the Database Utilities folder can be copied directly from the installation folder and delivered separately to your DBA. The DBA needs to use either a command line argument or the launch settings file to connect to CM4D databases before running Database Utilities.

Site Database Utility

The Site Database Utility is a simple command prompt tool that executes all the scripts necessary to create a new CM4D Site database or upgrade a v24 or v25 Site database to the latest site database schema. Connection information can be obtained from the CM4D.4ds file (via DbConnect) or entered manually. This utility is included with CM4D Server installer and should only be run by users with a clear knowledge of CM4D and its databases.

See the topic Site Database Utility for details.

Datasource Database Utility

The Datasource Database Utility is a simple command prompt tool that executes all the scripts necessary to create a new CM4D Datasource database or upgrade a v24 or v25 CM4D Datasource to the latest database schema. This utility is included with CM4D Server installer and should only be run by users with a clear knowledge of CM4D and its databases.

See the topic Datasource Database Utility for details.

Results Database Utility

Results Database Utility is simple tool that create an entirely new Results database, create the tables for an empty database or upgrade an existing Results database from a previous version. In cases of upgrading, the tool automatically detects the existing database tables and adds or changes tables as needed. This application is included with the CM4D Results Services installation and should only be run by DBA users or someone with a clear knowledge of CM4D and its databases.

See the topic Results Database Utility for details.

GUID Replacer

Database identifiers for Routines, Features and Samples in a CM4D Database must be unique within the Site database. When a CM4D database is copied (i.e., backup/restore) and added as a new Managed Datasource to a different Site database, problems may occur if there are GUID conflicts with other CM4D databases managed by that Site database. The GUID Replacer is a utility that reassigns Routine, Feature and Sample GUIDs in a CM4D Database. The GUIDs can replaced all at once or for only Routines/Features/Samples individually, and each option can be done in batches. This tool can be used on any CM4D database with errors related to GUID conflicts. This utility is included with CM4D Server installer and should only be run by users with a clear knowledge of CM4D and its databases.

See the topic GUID Replacer for details.

User Role Generator

The User Roles Generator Utility is a simple tool which allows you to pull the User and Group information from the CM4D Site database into a file that can be transferred into ATS Security Manager as User and Roles. This utility is included with CM4D Server installer and should only be run by users with a clear knowledge of CM4D configuration.

See the topic User Roles Generator for details.

Data Command Generator

The Data Command Generator Utility is a simple command line tool that is used to resolve specific database discrepancies between the CM4D and Results databases (e.g., new db columns) following a version upgrade. This utility is installed with CM4D Server (.\CM4D\Database Utilities folder) and should only be run as directed by ATS support by users with a clear knowledge of CM4D databases and configuration.

See the topic Data Command Generator for details.

Utility Command Arguments

All of the CM4D Database utilities listed above use the same line arguments for connecting the utility to a database along with commands to access help and version information.

In the command prompt, navigate to the utility executable and enter one or more of the following options:

Command	Option	Description
Catalog (Database Name)	-c, --catalog <Database Name>	Enter the name of the CM4D Database. For example: -c:cm4dsitedb1
Connection String	-cs, --connstring <Connection String>	Enter the connection string using the following format: -cs:"Server=ServerName;Database=DatabaseName;user id=UserName;Password=pass;"
DataSource (Server Name)	-ds, --datasource <Server Name>	Enter the name of the server hosting the databases.
Help	-?, -h, --help	Show help and usage information.
Password	-p, --password <Password>	Enter the password for the SQL user accessing the database.
User Name	-u, --user <User Name>	Enter the user name for SQL Authentication to the database.
Version	--version	Show the version of the application utility.

Run Utility via Command Prompt Line Arguments

You can start the utility from a Command Prompt by navigating to the executable and entering the database connection string as shown below:

Individual line arguments may be inserted one after another, separated by a blank space as shown below:

Run Utility as Executable

Running the utility local to your CM4D installation along with your method of executing the program (run as administrator) influences the startup behavior of the application.

• Run Executable as Administrator - Start the utility by running the executable as Administrator from the file browser and connect to the 4DS file. The database connection can only be obtained from the Cm4d.4ds file if CM4D is installed local to where the utility is run. If the application cannot locate/access the 4ds file, you must enter the connection information manually.

If your Results Database configuration uses Windows Authentication, running the Results Database Utility (as Administrator) does not recognize the 4ds connection string as valid. Since the 4ds connection fails, you are prompted to enter the connection information manually. When entered manually, Windows Authentication is valid. Workarounds include using SQL Authentication for your Results database credentials or simply enter the connection information manually when running the Results Database Utility.

• Run Executable - Start the utility by running the executable from the file browser and entering connection information as prompted by the utility.

Can we improve this topic?