Technical | SQL Mirror
      Back to home
      1. EBMS Knowledge Base
      2. EBMS Main Documentation
      3. Technical | SQL Mirror

      Upload Files

      Complete Upload

      After configuration, a bulk upload of data to the SQL Server is required. Turn on the Complete Upload option and close the dialog to initiate. EBMS will make copies of the tables selected for mirroring, convert them to .txt files, transfer those files to the shared folder on the SQL Server, and then convert them into SQL tables. Depending on data size this process may take several hours.

      It is recommended that a Complete Upload be completed each time data structure changes, such as when adding or deleting a table or changing field exclusions.

      Upload Individual Tables

      The user may wish to upload a specific table for the following reasons:

      1. When a static configuration table is added to the sync list, such as "ZIP", containing Postal Code information.
             SQL Mirror will never otherwise sync this table, because will it never detect any data changes

      2. When full upload skips an individual table due to a temporary failure (e.g. timeout, network disconnection)

      3. Propagate untracked data changes, such as data fixes performed by EBMS Support 

      Complete the following steps to upload individual tables:

      1. Select File > System Options > SQL Mirror from the main EBMS menu.

      2. Click Table List to open the complete Tables to export to SQL Server.

      3. Enable Reload for tables that need to be uploaded again.  This option will refresh the table data on the SQL Mirror.  

      4. Click Filters to configure optional DBase expression limiting the records to be uploaded and synchronized to the SQL Mirror.
        For example, in a large dataset with many years of history, only G/L transactions within the last 5 years will be uploaded.  Filtering or a query will reduce the time to initially load (or reload) a table, as well as reduce the database size - useful to satisfy the limitations of SQL Server Express edition.  Reducing table size in SQL Mirror can potentially improve reporting performance as well.  Review Set Query Options for configuration details.

      5. Click TSQL to add T-SQL statements which will execute after initially creating or re-creating the SQL table.  This optional is used by the SQL server database administrator.  

      6. This gives the DBA opportunity to add indexes, triggers, full-text search and other DDL statements appropriate to the anticipated reporting workload.

      7. Click OK to save settings.  The timed synchronization process will notice this change at its next synchronization interval and reload the table, creating it on the Mirror as needed. After the reload has been completed, the check box will be cleared.

      If these tables are replaced in the future from an external source, the operation should be repeated.  This will refresh the table data on SQL Mirror, replacing the previously uploaded data.

      Enable Export

      EBMS will push any database changes to the SQL database every few seconds. Turn on the Enable Export option to start this process. It can be left on always for those companies for which an SQL database is configured and a mirror is desired.

      Notes:

      • After changing the table list or the fields list, a Complete Upload is recommended.

      • Performing a Complete Upload will delete the SQL database tables and re-add them in SQL

      • If the system finds fields have been added or dropped in EBMS, it will drop and re-add the table in the SQL Mirror

      • If the system finds data problems in the mirror process, it will drop and re-add the table in the SQL mirror

      • Enable export will work as soon as it is checked and saved. It is advisable to double check the setup before turning it on.

      Technical Information: Administrators may access the DBEXCHG.dbf EBMS database table to observe the SQL export files waiting to be exported. The SQL Mirror keeps track of the last record successfully sent and checks every few seconds to see if any new ones have been added.

      Verbose Logging

      Only use the Log option when troubleshooting. This will write a log to the Logs.dbf EBMS database table every time the export event is triggered, as well as increase the detail that is written to the Diagnostic logs accessible from the EBMS Server Manager.

      Every time EBMS sends information to SQL a log of the event is written to a text file. Administrators may monitor this detail by opening the EBMS Server Manager program and choosing the View Logs option. The log files found here are very helpful in determining the status of complete uploads and export status.