This topic includes information for a DBA or other qualified user to configure and run DbMigrator outside of a CM4D installation. DbMigrator 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 DbMigrator folder can be copied to and run from any location:
To run DbMigrator, the following requirements must be met:
· .NET Framework 4.7.2 (or higher)
· SQL Server Role 'dbcreator'
There are three available methods for configuring the Results database information for DbMigrator. At runtime DbMigrator 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 DbMigrator will fail.
Tip
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.
1. Command Line
a. Single connect string, or
b. Individual parameters (preferred)
2. Application Configuration File
ATS.CM4D.Conestoga.DbMigrator.exe.config
Connection String line is not included in the default config file provided with install
Manually enter string
3. CM4D Site Database - requires the Results Db information is saved in Site Manager, and has a user assigned that has a sufficient privilege level.
Cm4d.4ds
Site Manager
Default DbMigrator configuration for fiscal start month/day
Order of execution:
1. Command line at runtime:
a. Connection string supplied as argument,
it uses this as connection string
b. If data source and catalog are supplied
it tries to create a connection string
2. If connection string in DbMigrator config is present, that
one is used
3. If none of the above applies, it tries to retrieve it from
CM4D Site database
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 | Usage |
| -silentrun: | -sr: | Used to silence verbose logs in the console when doing the migration. | n/a |
| -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=cm4ddbmigratorservice;Password=123!@#;multipleactiveresultsets=True;" |
| -user: | -u: | Supplies the database user. If using Windows Authentication, do not provide this. | -user:cm4ddbmigratorservice |
| -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 |
With full connection string:
C:\Program Files\CM4D\Db Migrator\ATS.CM4D.Conestoga.DbMigrator.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\Db Migrator\ATS.CM4D.Conestoga.DbMigrator.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\Db Migrator\ATS.CM4D.Conestoga.DbMigrator.exe -sr -user:cm4d -password:cm4dpass datasource:SERVER98 -catalog:Results
With deconstructed connection string and windows authentication:
C:\Program Files\CM4D\Db Migrator\ATS.CM4D.Conestoga.DbMigrator.exe -sr datasource:SERVER98 -catalog:Results
Without connection string in arguments:
C:\Program Files\CM4D\Db Migrator\ATS.CM4D.Conestoga.DbMigrator.exe -silentrun
With silent run omitted:
C:\Program Files\CM4D\Db Migrator\ATS.CM4D.Conestoga.DbMigrator.exe
If no connection information is provided via the Command Line, DbMigrator will pull the information from the Configuration file. By default, the Database connection string is not included with the DbMigrator config file. You need to paste the string into the file manually and configure the information.
1. Copy one of the following strings, according to your database authentication type:
a. SQL Authentication:
<connectionStrings>
<add name="NEwT_Results" connectionString="Data Source=(local);Initial Catalog=Results;User Id=user;Password=password;multipleactiveresultsets=True;" providerName="System.Data.EntityClient"/>
</connectionStrings>
b. Windows Authentication:
<connectionStrings>
<add name="NEwT_Results" connectionString="Data Source=(local);Initial Catalog=Results;Integrated Security=SSPI;multipleactiveresultsets=True;" providerName="System.Data.EntityClient"/>
</connectionStrings>
2. Edit the file ATS.CM4D.Conestoga.DbMigrator.exe.config.
3. Paste the copied string between the </startup> and <appSettings> tags:
4. Set the following:
a. Data Source - The machine name of the Database Server. If applicable, include the instance name.
b. Initial Catalog - The name of the Results Database.
c. 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.
d. 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.
5. If you are using SQL Authentication, also set the following:
a. User Id - 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.
b. Password - The password for the SQL user.
6. Save the file.
7. Run the DbMigrator application.
If no other connection information is provided (via Command Line or Configuration file) DbMigrator pull the information from the CM4D Site database, as configured in Site Manager.
If you are running DbMigrator 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.
1. Run Site Manager to set the Results database connection in Site Manager.
a. Go to the Results DataSource tab and enter the required information.
Notice
The
Results database object may not exist at the time of initial configuration
if this is a brand new installation (Test Connection will FAIL - this
is ok). The information still needs to be entered here since it will be
used by DbMigrator to create the database object when the tool is run.
b. If Enable Results Datasource is not checked, select the box to turn on the Results database.
c. Enter the required database information.
i. Server Name - Enter the name of the database server. If using a named instance include the instance name with the server.
ii. Database Name - Enter the name of the Results Database.
iii. Windows Authentication - Select one of the two available methods of authentication for connecting to the databases:
(1) Windows Authentication - Set the dropdown to 'True'.
(2) SQL Authentication - Set the dropdown to 'False'.
(a) 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.
(b) Password - Enter the password for the SQL user.
(c) Confirm Password - Re-enter the SQL user password.
iv. Click Test Connection.
(1) If successful, click OK and continue to the next step.
(2) If the connection fails on an existing Results database, check your settings and then test again.
(3) If the connection fails in a new installation (before Results db is created), this is ok and you can continue to the next step.
d. Click Save.
2. Edit the file ATS.CM4D.Conestoga.DbMigrator.exe.config.
a. Set the following:
i. 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.
ii. 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.
b. Save the file.
3. Run the DbMigrator application.
1. Run ATS.CM4D.Conestoga.DbMigrator.exe.
2. When prompted, hit Y to continue.
3. Wait for the process to complete. When it is finished running, the Command Prompt will read ''Successful migration. Press any key to exit application".
The DbMigrator tool creates the db object with the correct properties. However, if you created the Results database object in SSMS before running the DbMigrator (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.
· Recovery Model - Simple
· Allow Snapshot Isolation - True
· Is Read Committed Snapshot On - True
After running the DbMigrator tool, open SQL Server to confirm that the database object was created with the correct properties.
1. Run SSMS.
2. Right-click on the Results database created by DbMigrator.
3. Modify the Properties.
4. Go to the Options page and check the following options are set:
a. Recovery Model - Simple
b. Allow Snapshot Isolation - True
c. Is Read Committed Snapshot On - True
5. Click OK.
The update process for the Results Database is very simple - obtain, connect and run the new version of DbMigrator.
1. Obtain the updated version of DbMigrator (provided with the updated version of CM4D Server). The version of the DbMigrator must correspond to the version of CM4D.
2. Connect DbMigrator the same way as you did for the new instance (command line, configuration 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.
3. Run DbMigrator. The message before you hit 'Y' to proceed will show any pending changes.
