Synchronize CM4D Dbs with Results Db

Keeping your CM4D databases synchronized with the Results database is critical. When your system is operating normally, this synchronization is done automatically as data is added or changed by CM4D. However, there are some cases where you may need to trigger the synchronize operation manually:

After upgrading a CM4D database from v23 up to v24 or higher.

If your Results database becomes corrupted and you need to recreate the database.

Any other reason you have to create a new Results database.

Do not start this export unless necessary!

Can take a LONG TIME.

Cannot use CM4D Dbs while export is in progress.

Export Nominal Data

Synchronizing your CM4D database(s) involves exporting all Nominal data from the CM4D database(s) to the Results database. The export function in Site Manager will sync all Nominal data. Once the upgrade and export are complete, you must create at least one Results Rule and assign the rule to your Sample data. When Results Rule assignments in DataUtility (see here for instructions) are committed to the database, those samples will also be sent to the Results database. Results are generated once all data is synced between the two database types (CM4D and Results).

The exported jobs are entered into a queue in the CM4D Site database (dbo.ResultExportQueue). Once the Results services are running, the jobs are picked up and processed into the Results database.

Even if you do not create Results Rules and generate Results, all Nominal data (Routines and Features) that goes into the CM4D databases will remain synchronized with the Results database (if the Results Datasource is enabled in SiteManager). However, CM4D does not generate Processes and Results for the Results database unless at least one Results Rule is assigned to the appropriate measurement (Sample) data.

Results Services Process the Export Job

The Results export jobs are processed (in order of entry) by the Results Service, Results Launcher and Results Loader services. The services running on the same machine as the Site Manager where the Export to Results Database is clicked.

If you have multiple CM4D databases, the export process may take a long time. One way to speed up this process is to use separate clients to export each CM4D database. Each CM4D client has its own Results services so you can use a single client to process a single CM4D database. This way, all the job processing is distributed.

For example, you would run Site Manager on server A, select database 1 and export it to the Results database. Then you would run Site Manager on server B, select database 2 and export it to the Results database. Each server will handle the export processing for its selected database.

Even with distributing the export processing, expect a significant amount of time needed to complete the synchronization!!

As each database completes synchronization, it will become available for use with other CM4D applications. You may want to begin the synchronization with smaller databases first so you can start to access data as soon as possible.

Synchronize CM4D Db with the Results Db

See here for information on the process when synchronizing a newly upgraded v23 CM4D database.

Configure Results Database Connection.
Run Site Manager.

Go to the Results DataSource tab. 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.
If Enable Results Datasource is not checked, select the box to turn on the Results database.
Enter the required database connection information.
1. Server Name - Enter the name of the database server. If using a named instance include the instance name with the server.
2. Database Name - Enter the name of the Results Database.
3. 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'.
4. 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.
5. Password - Enter the password for the SQL user.
6. Confirm Password - Re-enter the SQL user password.
7. 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.
8. Click Save.

Leave Site Manager running.
Create Results Database.

Browse to the CM4D install folder and go to the DbMigrator folder.
Edit the ATS.CM4D.Conestoga.DbMigrator.exe.config file.
Set the following information:
1. fiscalStartMonth - Set the month of the start of your fiscal year. Enter a value of 1-12. The default setting is "1" (January).
2. 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.

Run ATS.CM4D.Conestoga.DbMigrator.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".

Test Results DataSource connection. This step is CRUCIAL to running other applications or services.

Go back to Site Manager.

Go to the Results Datasource tab.

Click Test Connection.

Click OK to acknowledge the success message.

Click Save.

Export CM4D Databases to Results Database.

Go to the Managed Data Source tab.

Select a database.

Click Export to Results Database.

If this button is grayed out (disabled) then there is a problem with your Results database connection. This could be from incorrect connection information, if the Test Connection button was not clicked, or changes were not saved.

Click OK to acknowledge the caution message: "This will export the entire database! This may be a very long operation, and will render the datasource unusable until export is complete. Continue?".

Look at the Export Job Count field, located under the Export to Results Database button. As long as the Results services are running, this Count will begin to decrease as the export jobs are processed. Until this count is zero the synchronization is not complete.

Once the Export Job Count is zero, export all remaining CM4D Databases to the Results database.

You can close Site Manager it and reopen it while the export is still running; the synchronization will not be effected.

Start Results Services.

Open Control Panel > Administrative Tools > Services.

For Server or Client installations, start the following services (in this order):

Results Service

Results Launcher

Results Loader

DO NOT start any other CM4D Service until the synchronization is complete.

Go back to the Managed DataSource tab in Site Manager.

Once the Export Queue Count is zero, repeat the 'Export CM4D Databases to Results Database' step and export all remaining CM4D Databases to the Results Database.

If all databases have completed synchronization, start any other CM4D services as needed.