Database Synchronization

CrimeStar is designed to permit multiple CrimeStar databases to be merged together or synchronized. This permits multiple remote jurisdictions that want to share criminal data with a method for periodically merging their CrimeStar databases without the need for expensive full-time network connections. This process can also be used between a system in the office and one or more laptop computers taken into the field. Additionally you may use the synchronization program as a means to protect your data by creating backup copies of your database.

* Be sure to read the Crimestar License Agreement! Each computer (Desktop or Laptop) that maintains and routinely accesses its own local database must be separately licensed. Synchronizing between multiple standalone computers (Desktop or Laptop) for the express purpose of avoiding, bypassing or otherwise circumventing the concurrent user license restrictions, is a violation of the license agreement. Such violations may result in civil action and/or criminal prosecution.

This section describes how CrimeStar database synchronization’s are performed and explains the various option and considerations concerning this process.

Databases vs. Files: There are several programs on the market to merge computer files and many of them are quite good. These tools however only address systems files from different computers by comparing the file date to determine which file is newer. The problem is, what happens when you copy a file to another computer, then you make changes to that file on each computer. Now you have two files where each file contains some information that the other one does not. If you simply choose to keep the file that was modified last, it is guaranteed that the information contained in the opposite file will be lost. These types of tools (such as Microsoft Briefcase) should rarely if ever be used on database files. Database files contain multiple records. You may have 100,000 citations, but that is not 100,000 files, it is 100,000 records in 1 file. Therefore in order to synchronize database from multiple machine a more specific and detailed process that has intimate knowledge about the data itself is required.

Database Identification: In order for several different databases to be merged together it is necessary for all the data records in any given database to have some kind of unique identification so that once merged into another database they can always be identified and there is never a conflict between any two similar records that originated from different databases. In order to achieve this goal Crimestar RMS utilized a Database Identification Number. The database identification ID can be changed using the "Transfer Data" page of the Crimestar RMS Configuration Module. When Crimestar RMS is installed on a computer and the database is built a random Database Identification code is assigned to the database. While it is likely that these randomly generated numbers will be unique, it is VERY IMPORTANT to make sure, that each computer that maintains it’s own Crimestar RMS database (laptops etc.), has a different Database Identification number that is unique to only that computer. No two computers whose data will ultimately be synchronized should have the same number!

Within CrimeStar (and most commercial Databases) each data record in the system is assigned a ‘Primary Key’. In CrimeStar the Primary Key is simply a computer generated number that is assigned to each data record. This number is generally never seen by the user and is always unique within a single database regardless of system module or data entered. It is however possible and likely that the same Primary Key value will be used within two different CrimeStar databases. This creates an obvious problem. If the CrimeStar databases are merged together it is possible that record conflicts could occur due to duplicate primary keys within a single table. To address this issue CrimeStar utilizes a constant database identification number. This number can range from 0 to 999. This number is then treated as a decimal value and is summed to a primary key integer to create a primary key value with a 3 digit decimal resolution. Example of Primary keys created when your database identification number is 5.are as follows;

100.005

1354324.005

8956.005

Any Primary Key value created will always have .005 as part of the value. By utilizing this strategy multiple databases can now be merged as long as each database’s Identification Number has always been unique from the number used by the other database.  Remember EACH LAPTOP has its own database so where multiple laptops are used to merge data into a single master database, each laptop database MUST have its own unique database ID #.  Where multiple agencies are sharing data in a regional system a DataBase Administrator (DBA) should assign the database ID values for each machine in each agency that is to participate in the regional synchronization of data.

The Synchronization Program

 The CrimeStar Synchronization Program is provided as part of Crimestar RMS product. You can locate it on your program menu, and you can find the program (SYNCHRONIZE.EXE) in the Crimestar installation directory.   The Synchronize program is run it will appear in a minimized view.

If all the necessary setting have been configured all you need to do at this point is click the "Synchronize" button to begin the synchronize process.  If the necessary setting have not yet been configured you can expand the synchronize program by clicking the small black down arrow located to the right of the Table Progress bar.  This exposes a variety of configuration option including the location of the LOCAL DATABASE, the location of the REMOTE DATABASE and the criteria for how to examine the database records looking for records to be synchronized.  Your option are a "Full Synchronize (All Records); Those Records modified after the defined Table Date which is configured on a different screen by clicking the "View /Set Tables" button; and those modified within X number of days, where x can be configured via the INI file.

The program synchronizes the local CrimeStar Database with a remote CrimeStar database that you define.  A "remote database" can be a database on a different computer or on a different disc on the same computer or in a different directory on the same disc within the same computer. You simply select the location of the "remote database" by clicking the ellipse button […] located to the right of the remote database display field. You can optionally select which specific database tables will be synchronized by clicking the [View Set Tables] button and selecting specific tables to be included or omitted from the synchronize process as well as indicating if the synchronize program should attempt to send and receive data from each respective table in the database.

The View/Set tables form also allows you to indicate if you wish to transfer certain database records that have been marked as Restricted.  RMS database records can be marked as restricted by a system administrator via the Record Access / Locking dialog form.

Before the synchronize program can transfer data to/from the remote database the laptop/computer running the Synchronize program must be registered with the remote database as an authorized machine via a MACHINE ID #.  Additionally the remote database must specify if the computer performing the synchronize process is allowed to Send/PUSH and/or Receive/PULL data as well as if the computer is allowed to transfer certain restricted records.  In order for data to be pushed from the local database to the remote database or pulled from the remote database down to the local database, or for restricted records to be transferred both the local computer that is running the Synchronize program and the remote database which is to receive or provide the data must specify and agree that the action is allowed.

This remote database security is configured using Crimestar RMS connected to the remote database.  As a system administrator select "System"->"Synchronize Security" to activate the Synchronize Security / Machine Serial # Identification form. Enter the machine Serial # as shown on the computer that is attempting to run the Synchronize program and set the security permission for that specific computer.

To start the Synchronization process click the large synchronize button labeled [Synchronize].  The Synchronize program then scans through all the database tables comparing the primary key and modification date of each record and performs the necessary adds, updates & deletes.

If you think about this Primary Key as a sort of data record "serial number" you can see that synchronization program uses these record serial numbers to transfer and compare data records across different machines, so data can be properly added, updated or deleted on opposing machines. It is important to understand that the Synchronize program uses this Primary Key to identify a data record. It does not use any user entered Identifier such as an Incident # or Citation # etc. As such, if you enter the same Incident or Citation (or Code in a code table) on two different computers each record will receive a unique primary key number. As such the next time those two computers are synchronized, the synchronize program will transfer them and each computer will contain the record that was created on the machine thus resulting in each database have what appears to be two copies of the same thing. While they may appear the same to you (the user), the synchronize program sees them as being different because they have different Primary Key identifiers.

Program Options: The synchronization program provides you with several options that will help you control the way the synchronizer works. Database synchronization is actually a very complex operation. Understanding the following options will help you ensure that synchronization process operates correctly.

Connecting Computers: In order for the database synchronizer to run it is necessary for the program to have a true network connection to each of the two (2:Local & Remote) databases. This physical connection can be either wired or wireless. The actual physical network connection is a function of computer hardware and Microsoft Windows networking rather than Crimestar or the Database Synchronizer. The Synchronizer simply reads, compares and writes database records in two distinct databases.

The Synchronization program has the ability to read certain attributes from the crimestar INI file. The following settings in the INI can be used to set the defaults for the options explained above.

[SYNCHRONIZATION]

REMOTE_PATH=

SHOW_OPTIONS=ON/OFF

SQL_SYNCH=OFF

AUTO_START=0

BUFFER_DATA=ON/OFF

LOW_PRIORITY_MODE=ON/OFF

SHUTDOWN_ON_COMPLETE=ON/OFF

Note that the ‘REMOTE_PATH’ entry in the ini file should point directly to the remote database file named ‘crmstr00.dbc’. Example:

\\REMOTE_COMPUTER\DISK_DRIVE\PATH_NAME\CRMSTR00.DBC

SHOW_OPTIONS can be set directly via the INI file and will determine if the user is permitted to select any options associated with performing the transfer. This option is helpful if you want to pre-set the transfer settings for a machine and not give the user the ability to change them.

SQL_SYNCH can be set to any of 3 different values depending upon the databases that you wish to synchronize.  

Synchronization Limitations: It is not possible to accurately synchronize everything associated with a database. One such example is the Auto Numbering feature. The auto numbering process simply gets the next number in sequence from the master number source. In order for this to work you must have only one master number source. When there are multiple copies of the database in existence then there are multiple master number sources. When this duplication occurs, the issuance of duplicate numbers is probable. To avoid this issue it is recommended that you either use the optional feature of the Auto Numbering process to append a pre-defined letter to the computer generated number or that Auto Numbering simply not be used with remote copies of the database.

The following Crimestar database tables are never synchronized:

CRMSTR01.DBF - INDECIES (Holds counter values for record id’s and auto numbering)

CRMSTR18.DBF - SYSCONF (System Configuration)

CRMSTR32.DBF - USER_LOCK (Internal Use)

CRMSTR35.DBF - CODE_TYPES (List of Code Type Categories)

CRMSTR57.DBF - SYSCONF2 (System Configuration #2)

CRMSTR75.DBF - SYSCONF3 (System Configuration #3)

CRMSTR98.DBF - CAD_ACTIVE_EVENTS (Working Table for Active CAD Events)

CRMSTR99.DBF - CAD_ACTIVE_UNITS ( Working Table for Active CAD Units)

CRMSTR110.DBF - E911

CRMSTR111.DBF - RESERVED

CRMSTR113.DBF - CAD_CALL_DEFAULTS

CRMSTR114.DBF - CAD_SCHEDULED_EVENTS

CRMSTR115.DBF - CAD_UNIT_ASSIGNMENTS

CRMSTR116.DBF - CAD_DISPATCH_LOG

Synchronization Speed: One of the most frequent questions asked about database synchronization is; how long does it take? The answer is, "it depends". It depends on several items, including, the total number of database records that must be processed, the speed of your computer and more importantly the speed of your network connection to the remote database. Assuming that you are performing a two-way synchronization (Local to Remote AND Remote to Local), the database synchronization process requires that that each record in the local database be checked against the remote database and each record in the remote database be checked against the local database. Under ideal circumstances the database synchronization engine can process approximately 20 records per second. If a single database table on your local database contains a total of 10,000 records that need to be scanned and checked for modifications, and you are not using the modify flags as described above, that process will take approximately (10,000 records / 20 records per second = 500 seconds) 8 minutes. Now consider that the Crimestar database contains over 90 data tables that can be synchronized. Depending on which or how many of these tables you choose to synchronize and how many data records are in the tables, the processing time can range from one or two minutes to several hours. Again it is all a matter of the quality and speed of your physical network connection and how much data there is to process given an approximate processing rate of 20 records per second.

Security of your Data: For those of you who plan on using the Synchronization utility to have laptops (or any other kind of field computer) out in the patrol car, security of that data can be a major consideration.  While access to the Crimestar database is password protected via the Crimestar application software, those with some computer programming skills could still access the data directly from the database without using the Crimestar program software.  The (Your Data)  data as stored in the Crimestar database is not encrypted.  In the event that your laptop or computer were to be stolen out of a patrol car or simply lost, the data contained within the Crimestar database could be at risk of disclosure to unauthorized persons.   One method to help protect the data on your computer is to Encrypt the hard disc or drive volume where the Crimestar software and database is installed. Newer editions of Microsoft Windows provide this capability, however this added level of protection is not turned-on by default.  You must expressly turn it on and activate it.