Skip to main content

[DSP / Collect] Problems Connecting to the DBMoto Database

Caroline St.John
  • Updated

Issue

DSP® cannot connect to the DBMoto database.

The 32-bit version of DBMoto is still packaged with the DSP 6.6.x installation files. If you are running the 64-bit version of DSP, you will need to uninstall the 32-bit DBMoto program and replace it with the 64-bit version.

Solution 

Complete the following steps to replace the current installation of DBMoto:

  1. Completely remove the 32-bit DBMoto program currently installed on the DSP application server.
  2. Request the 64-bit DBMoto version via support ticket at https://support.syniti.com.
  3. Install the 64-bit version of DBMoto.
  4. Configure additional DBMoto settings in DSP.

Install and Set Up DBMoto for DSP

DBMoto for DSP and the DSP must be installed on the same server.

  1. In the DBMoto InstallShield Wizard, select the language and click Next.
  2. Accept the license agreement and click Next.
  3. Select the Destination Folder and click Next.
  4. Click the Standard Installation option in the Setup Type window and click Next.
  5. Click Import in the Customer Information and Setup Options window.
  6. Supply the path to the license and click Next.

    NOTE: The DBMoto for DSP license is included in the installation files and may be found at the following location: <installation folder>\BOA\DSP\Packaging\Packages\DSP.DBMoto-<version>\DBMoto\dbmotolicense.txt Example: C:\Program Files (x86)\BOA\DSP\Packaging\Packages\DSP.DBMoto-6.5.0+9029\DBMoto\dbmotolicense.txt

  7. Check Enable digital signing verification and click Next.
  8. Click Next on the Start Copying Files window.
  9. Continue with all defaults until you reach the Service Dependencies selection.

    NOTE: Use the DBMoto Service Dependencies window to specify services needed by DBMoto prior to the DBMoto Data Replicator Service startup. 

    • If the metadata database is not local (on the same system where DBMoto is installed), dependencies do not need to be set. Click Finish to close the window.
    • If the database that is storing the DBMoto metadata is local (installed on the same system as DBMoto), the database must be running before DBMoto to avoid errors when DBMoto checks the metadata.

    NOTE: To add a dependency, select the service in the list, then click Add. The DBMoto Data Replicator Service will not start until the selected service has started. For example, if the SQL Server service is selected and Add is clicked, SQL Server is added as a dependency; the DBMoto service does not start until the SQL Server service starts. Click Install.

    At the end of the installation, the DBMServiceMonitor program is available in the Windows Notification area (System Tray). Use this program to start the DBReplicator service after creating metadata tables in the DBMoto Management Center (see below).

  10. Click Finish; the DBMoto Management Center displays.

NOTE: The following steps remove the default DBMoto metadata and add a specialized DBMoto for DSP metadata so that connections and replications can be set up automatically from the DSP.

Next, in the Metadata Explorer:

  1. Expand the tree, right–click Metadata.
  2. Select Remove Metadata. A confirmation message displays.
  3. Check the Drop the metadata tables from the database check box.
  4. Click Yes.
  5. Right-click the entry local, and then select Add New Metadata; the Metadata Connection Wizard window displays.
  6. Click Next.
  7. Enter DBMoto_Client in the Name field.
  8. Select Microsoft SQL Server from the Database list box.

    NOTE: Ensure you select Microsoft SQL Server and do not select Microsoft SQL Server CE.

  9. Click Next to display the connection parameters.
  10. Enter the IP address or server name for the installation of SQL Server, which is running the Collect database (a component of the DSP) in the Data Source field.
  11. Enter the username that BackOffice Solutions uses in SQL Server (for example, CranSoft) in the User ID field.
  12. Enter the password for that database user in the Password field.
  13. Click Test to verify the connection settings.
  14. Click OK to close the message.
  15. Click Next.
  16. Select DBMoto_Client from the Database Name list box.
  17. Select dbo from the Owner Name list box.
  18. Click Next to set the metadata.
  19. Select the Create a new metadata database option.
  20. Click Next.
  21. Click Yes on the pop-up message.
  22. Click Finish.
  23. Exit the DBMoto Management Center.

Next, in the BackOffice Solutions installation directory:

  1. Go to Database\Apps\DataGarage\Components\
    Objects\PostInstallSteps.
  2. Right-click the file DBmoto_in_DataGarage_reference.sql and select Open to execute the contents of the file in the DataGarage database in the SQL Server Management Studio.
  3. In the BackOffice Solutions installation directory, under the Web\UserArea\4f3346ca-235a-46bd-84d8-1d234b873a0c\Processes folder.
  4. Locate and open the txt file.
  5. Select all text (Ctrl+A) and copy the text (Ctrl+C).
  6. Close the DBMoto_Global_Script.txt file.
  7. Open the DBMoto Management Center, right-click the new metadata DBMoto_Client, and select Global Script.
  8. In the Global Script window select all of the text (Ctrl+A), delete the text and then paste the text from the txt file into the Global Script window.
  9. In the Script Language drop-down, select VB; a warning message displays.
  10. Click Yes.
  11. Click OK to run the script and close the Global Script window.
  12. Exit the DBMoto Management Center.
  13. In Windows, go to Start >Administrative Tools >Internet Information Services (IIS) Manager.

    NOTE: There are two types of IIS Managers:

    • Internet Information Services (IIS)
    • Internet Information Services (IIS) 6.0 Manager

Go to Internet Information Services (IIS), NOT Internet Information Services (IIS) 6.0 Manager.

  1. Expand the tree to the left and find the CranSoft application pool under Application Pools.
  2. Right-click the CranSoft application pool and select Advanced Settings.
  3. Change the Identity field to LocalSystem, and then click OK.
  4. Click OK, and then close the manager.

Configure Additional DBMoto Settings

  1. Navigate to Common > Configuration > Modules > Parameters – Collect > DBMoto Settings
  2. Populate all of the DBMoto Settings fields. Refer to the following table for a description of each field:

 

DBMoto Settings

Description

Server Name

Server name defined in the DBMoto Management Center, from the Metadata Explorer panel under the DBMoto node (for example, local).

Group Size

Number of tables in each mirroring group. Groups are used to check system logs for changes. It is more efficient to check once for a group of tables than to log in for each table individually. Group Size must be determined by the client. Each group runs in single thread mode with one table refreshing at a time. The default value is 50.

Maximum Connections

Limit of simultaneous connections to the source system. The default value is 8.

Mirror Interval

For the Mirroring Package type (changed data only), the number of seconds between checking the source database log for changes. As a recommendation, do not use a value less than 60 seconds. The value could be set to 600 so the log can be checked every 10 minutes.  The default value is 900.

Start Replicator

If checked, the DBMoto Replicator is started after each Build Package process. If unchecked, the DBMoto Replicator must be manually started in the DBMoto Management Center on the application server. This feature is disabled by default.

Enable Group

If checked, allows the group to mirror after each Build Package process. If unchecked, the Enable Group must be manually restarted in the DBMoto Management Center on the application server. The DBMoto Replicator must be running before the data download process can start.  This feature is disabled by default.

Metric Time

Controls when all tables without rules will have the boaDBMotoInd set to zero and the daily change count put in the Metrics process. The default value is the date and time that the BackOffice Solution was installed.
  1. In Collect, add Target and Target Source.
  2. In the DBMoto Metadata Explorer, add connections for source databases as needed. Note that the source name in DBMoto must match the target source name in the Collect component of the DSP, including case.
  3. In the Metadata Explorer of DBMoto, add connections for target databases as needed. However, do not enable Transactional Replication for target databases. The target name in DBMoto must match the target name in the Collect component of the DSP, including case.

NOTE: Refer to the following documents available on https://support.syniti.com for further details on setting up DBMoto connections:

 

DBMoto Connection Documentation

Oracle

Create source connections in the DBMoto Management Center with names that match the Sources created in the Collect component of the DSP.  Refer to the Create DBMoto Source Connections section. Review the document DBMoto-DSP-SetupOracle for information on required database permissions and settings for Oracle database.

Microsoft SQL Server

Create source connections in the DBMoto Management Center with names that match the Sources created in the Collect component of the DSP.  Refer to the Create DBMoto Source Connections section. Review the document DBMoto-DSP-SetupSQLServer for information on required database permissions and settings for Microsoft SQL Server.

IBM DB2 LUW

Create source connections in the DBMoto Management Center with names that match the Sources created in the Collect component of the DSP. Refer to the Create DBMoto Source Connections section. Review the document DBMoto-DSP-SetupIBMDB2LUW for information on required database permissions and settings for IBM DB2 LUW.

Other databases with ODBC, OLE DB or .NET Provider access

Contact BackOffice Associates Support at https://support.syniti.com to submit an issue to verify support for your database. Refer to How to Create DBMoto Source Connections section.
  1. Build the DBMoto package in the Collect component of the DSP.
    1. Log in to Collect.
    2. Click Targets.
    3. Select the desired target and click the Sources icon.
    4. Click the Table icon for the Target Source.
    5. Verify that the target and source in the Collect component of the DSP have the same names as in DBMoto (case-sensitive).
    6. Click Build Package.

If DBMoto for the DSP was installed correctly and the default settings for the DBMoto package are used, the data replication process runs automatically.  For further assistance with installing and setting up DBMoto for DSP, submit an issue to BackOffice Associates Support at https://support.syniti.com.

 

Configure Source Connections

Microsoft SQL Server, Oracle, and IBM DB2 LUW are supported as source databases. If using another database, check that the database and version is supported by submitting an issue to BackOffice Associates Support at https://support.syniti.com.

To create DBMoto source connections for databases:

  1. In the Metadata Explorer, expand the DBMoto_Client.metadata.
  2. Right-click Sources and select Add New Connection.
  3. In the Source Connection wizard, set values that match the connection specified in the Collect component of the DSP on the Target Source (Setup). Refer to the documents listed in the DBMoto Documentation table.
  4. Complete the wizard and check that the connection has been created in the Metadata Explorer.
  5. Create additional connections as needed.
  6. Create the Target Connection.

DBMoto Troubleshooting

If problems arise when building or refreshing the DBMotoDownload or DBMotoMirror packages in the Collect component of the DSP, verify the following items:

  • The DBMoto metadata is stored in a SQLServer database, with the following name for the database object: DBMoto_Client.
  • Connection names defined in the DBMoto metadata (using the Management Center) are the same for the Data Source used in the Collect component of the DSP.
  • To use the mirroring package, the transactional log settings are configured in the source connection properties, from the Management Center. The configuration depends on the database type of the connection. Refer to the specific setup guide for privileges and database settings.
  • DBMoto Data Replicator and DBMoto Agent services are installed.
  • DBMoto Agent is running.
  • The identity in the DSP Application Pool is Local System.
  • DSP service and IIS were reset before trying to use DBMoto for DSP.
  • The DBMoto setting defined in the Collect component of the DSP has the following entry to refer to the dbmoto sever: ServerName=<NameOfTheServerDefinedInManagementCenter>.

 

Related articles