This topic includes information for a DBA or other qualified user to configure and run the Results Database Utility outside of a CM4D installation. Results Database Utility is a simple tool that creates and updates the CM4D Results database. The tool is included with the CM4D Server installation and can be obtained from any user with access to the CM4D Server installation folder:
The Results Database Utility folder can be copied to and run from a system other than the CM4D install machine, provided you can use one of the configuration connections described below.
Requirements
To run Results Database Utility, the following requirements must be met:
.NET Framework 4.7.2 (or higher)
SQL Server Role - 'dbcreator'
SQL Server Feature - 'Full-Text'
Enable SQL Server Feature - Full Text
The Results database requires the Full-Text and Semantic Extractions for Search feature in SQL Server. If this feature is not already enabled, you must re-run the SQL Server setup to add the required feature.
To enable this feature:
Run the SQL Server Installation.
Go to 'Installation'.
Click 'New SQL Server stand-alone installation or add features to an existing installation'.
Go to 'Installation Type'.
Select the SQL Server instance for which you want to add the feature.
Go to 'Feature Selection'.
Expand the Features tree Instance Features > Database Engine Services.
Select the check box 'Full-Text and Semantic Extractions for Search'.
Complete the installation to apply the changes.
Once the Full-Text feature is enabled, continue with the Results database steps below.
Connection Configuration
There are three available methods for obtaining the Results database connection information for the Results Database Utility. At runtime the Results Database Utility will check for each of these methods (in this order) and use the first successful connection it finds. If the database information is not provided by any of the three methods, the Results Database Utility will fail.
Make a record of the method used for creating the Results database, as you will likely use the same method to update the Results database in future releases.
- Command Line
- Single connect string, or
- Individual parameters (preferred)
- SQL Authentication only
- JSON File
- Properties\launchSettings.json
- SQL Authentication only
- Manually enter string
- CM4D Site Database - requires the Results Database information is saved in Site Manager, and has a user assigned that has a sufficient privilege level.
- Cm4d.4ds
- Site Manager
- Default app settings for fiscal start month/day
- SQL or Windows Authentication
Order of execution:
- Command line at runtime:
- Connection string supplied as argument, it uses this as connection string
- If data source and catalog are supplied it tries to create a connection string
- If connection string in Results Database Utility launchSettings.json is present, that one is used
- If none of the above applies, it tries to retrieve it from CM4D Site database
Command Line Arguments
Intended way of use -arg1:value1 -arg2:value2 -arg3 (arg3 would be ones that are used without value, -silentrun for example).
| Argument | Abbrev. | Value Description | Example |
|
-silentrun: |
-sr: |
Used to silence verbose logs in the console when doing the migration. | |
|
-connstring: |
-cs: |
Fully qualified connection string. Takes precedence over all other arguments. If connString is not supplied, it checks for individual arguments (-u, -p, -d, -c) for connection. |
-connstring:"Data Source=atsserver001;Initial Catalog=ConestogaResultsDb;User Id=cm4dservice;Password=123!@#;multipleactiveresultsets=True;" |
|
-user: |
-u: |
Supplies the database user. If using Windows Authentication, do not provide this. |
-user:cm4dservice |
|
-password: |
-p: |
Password for the database user. If using Windows Authentication, do not provide this. |
-password:123!@# |
|
-datasource: |
-d: |
The machine name of the Database Server. If applicable, include the instance name. |
-datasource:atsserver001 |
|
-catalog: |
-c: |
The name of the Results Database. |
-catalog:ConestogaResultsDb |
Command Line Examples
With full connection string:
C:\Program Files\CM4D\Database Utilities\Results Database Utility\ATS.CM4D.ResultsDatabaseUtility.Migrator.exe -silentrun -connstring:"Data Source=SERVER98;Initial Catalog=Results;User Id=cm4duser;Password=pwtemp;multipleactiveresultsets=True;"
With full connection string and short names for arguments:
C:\Program Files\CM4D\Database Utilities\Results Database Utility\ATS.CM4D.ResultsDatabaseUtility.Migrator.exe -sr -cs:"Data Source=SERVER98;Initial Catalog=Results;User Id=cm4duser;Password=pwtemp;multipleactiveresultsets=True;"
With deconstructed connection string and SQL Server Authentication:
C:\Program Files\CM4D\Database Utilities\Results Database Utility\ATS.CM4D.ResultsDatabaseUtility.Migrator.exe -sr -user:cm4d -password:cm4dpass datasource:SERVER98 -catalog:Results
With deconstructed connection string and windows authentication:
C:\Program Files\CM4D\Database Utilities\Results Database Utility\ATS.CM4D.ResultsDatabaseUtility.Migrator.exe -sr datasource:SERVER98 -catalog:Results
Without connection string in arguments:
C:\Program Files\CM4D\Database Utilities\Results Database Utility\ATS.CM4D.ResultsDatabaseUtility.Migrator.exe -silentrun
With silent run omitted:
C:\Program Files\CM4D\Database Utilities\Results Database Utility\ATS.CM4D.ResultsDatabaseUtility.Migrator.exe
Launch Settings File - Database Connection
If no connection information is provided via the Command Line, Results Database Utility pulls the information from the launchSettings.json file. By default, this is located in the .\Properties folder in the Results Database Utility folder.
Open the file launchSettings.json and modify the connection information:
" -user:TestAdmin -password:Password2022! -catalog:CM4DDBResults25 -datasource:(local)"
- - user: The name of the SQL user that will be used to create/modify the database object. This user must have a sufficient privilege level in SSMS to perform this task.
- - password: The password for the SQL user.
- - catalog: The name of the Results Database.
- - datasource: The machine name of the Database Server. If applicable, include the instance name.
Application Settings File - Use Fiscal Year
By default, the Results database is configured to go by Calendar Year (Jan1-Dec31). However, if your organization uses a fiscal year you can customize your Results database to use a specific Start Month/Day. This allows search parameters which include the year parameter to adhere to the set fiscal year.
To set these dates, open the appsettings.json file from the .\CM4D\Database Utilities\Results Database Utility directory and modify the following information:
- FiscalStartMonth: Set the month of the start of your fiscal year. Enter a value of 1-12. The default setting is "1" (January).
- FiscalStartDay: Set the day of the month as the start of your fiscal year. Enter a value of 1-31. The default setting is "1" (first day of the month). Must be a valid day for the set month.
CM4D Site Database
If no other connection information is provided (via Command Line or launchSettings.json file) Results Database Utility pulls the information from the CM4D Site database, as configured in Site Manager.
If you are running Results Database Utility on a system that has CM4D Server installed locally, this is the recommended method to use. This is also the only method that allows you to use Windows Authentication.
Run Site Manager to set the Results database connection in Site Manager.
- Go to the Results DataSource tab.
- If Enable Results Datasource is not checked, select the box to turn on the Results database.
- Enter the required database connection information.
- Server Name - Enter the name of the database server. If using a named instance include the instance name with the server.
- Database Name - Enter the name of the Results Database.
- Windows Authentication - Select one of the two available methods of authentication for connecting to the databases:
- Windows Authentication - Set the dropdown to 'True'.
- SQL Authentication - Set the dropdown to 'False'.
- DataBase UserName - Enter the user name of the SQL user login; must have the 'dbowner' privilege level in SSMS. This is the login used by DbMigrator to create/modify the Results database in SQL Server.
- Password - Enter the password for the SQL user.
- Confirm Password - Re-enter the SQL user password.
- Click Test Connection.
- If successful, click OK and continue to the next step.
- If the connection fails on an existing Results database, check your settings and then test again.
- If the connection fails in a new installation (before Results db is created), this is ok and you can continue to the next step.
- Click Save.
Edit the file ATS.CM4D.ResultsDatabaseUtility.Migrator.exe.config.
Set the following:
fiscalStartMonth - Set the month of the start of your fiscal year. Enter a value of 1-12. By default, this is set to "1" for January.
fiscalStartDay - Set the day of the month as the start of your fiscal year. Enter a value of 1-31. By default, this is set to "1" for the first day of the month. Make sure to use a day that is valid for the set month.
Save the file.
Run the Results Database Utility application.
Run DbMigrator
Run ATS.CM4D.ResultsDatabaseUtility.Migrator.exe.
When prompted, hit Y to continue.
Wait for the process to complete. When it is finished running, the Command Prompt will read ''Successful migration. Press any key to exit application".
Database Properties (Manual)
The Results Database Utility creates the db object with the correct properties. However, if you created the Results database object in SSMS before running the Results Database Utility (this is not recommended), there are some settings that must be set manually in SQL server. Your SQL Server may already use some of these settings by default, but you should confirm these settings are correct or you may have issues with the Results database.
Options
- Recovery Model: Simple
If you decide to use the Full recovery model, be aware that your disk space may be consumed rapidly due to the way the Results database functions. Unless you have a very specific reason to use Full, the Simple option is sufficient and recommended since CM4D has built-in methods of recovery available for this database.
Other Options: Miscellaneous
- Allow Snapshot Isolation - True
- Is Read Committed Snapshot On - True
Check the Results Db Properties
After running the Results Database Utility tool, open SQL Server to confirm that the database object was created with the correct properties.
Run SSMS.
Right-click on the Results database created by Results Database Utility.
Modify the Properties.
Go to the Options page and check the following options are set:
Recovery Model - Simple
Allow Snapshot Isolation - True
Is Read Committed Snapshot On - True
Click OK.
Update the Results Database
The update process for the Results Database is very simple - obtain, connect and run the new version of Results Database Utility.
Obtain the updated version of Results Database Utility (provided with the updated version of CM4D Server). The version of the Results Database Utility must correspond to the version of CM4D.
Connect Results Database Utility the same way as you did for the new instance (command line, json file or CM4D Site db). The only difference is the Db object will already exist, so just confirm that you are using the correct connection method/information.
Run Results Database Utility. The message before you hit 'Y' to proceed will show any pending changes.