back to FAQ
1b. preparing
your database
Q: what considerations must I
allow for to ready my databases for a SyncSmart implementation?
A: SyncSmart uses special columns in
each table and special tables added to each synchronized
database to perform synchronization operations. These columns
and tables must be populated with specific information when
synchronized data is changed by non- SyncSmart applications. The
information that goes into the SyncSmart columns and tables can
be created using application logic or database triggers.
Database triggers are strongly recommended if your dbms supports
triggers and provides sufficient trigger capabilities to support
SyncSmart requirements. If no applications except SyncSmart
operate against a particular database, then triggers are not
required in the database. This is sometimes the case for the
central server database. (For detailed information on the
database triggers or application logic required to support a
SyncSmart implementation, please refer to the SyncSmart
integration guide.)
SyncSmart requires that two columns, often referred to a
versioning fields, be added to each table to be synchronized.
The two columns must be defined as holding integer values, and
it is recommended that they be capable of holding values up to
approximately 2,000,000,000 (two billion). For MS SQL server,
this is the "int" data type, and for oracle, numeric (10,0) is
recommended.
In addition to the columns that must be added to synchronized
tables, SyncSmart requires additional tables in the synchronized
databases. The requirements are different depending on whether
the database is a server database or a client database. Any
database that will synchronize with more than one other database
is considered a server database. Any database that will be
synchronizing with only one other database is a client database.
For client databases, there is a single SyncSmart table to
define. The name of this table is "itadeletion" and its purpose
is to hold and uniquely identify deleted records from the client
database.
For server databases, there are two additional tables to define.
The "itacaq" table is used for realignment operations and holds
uniquely identifiable records whose territory definition
criteria have changed. For example, in a customer relationship
management (CRM) application, when a customer moves to a new
state their respective sales territory may change. Because the
record has changed, there is no way to determine which territory
the customer previously belonged. Therefore, in order to realign
this customer record with the appropriate sales representative,
SyncSmart queries the "itacaq" table and distributes the updated
record to all clients at their next synchronization. The "itasynchlevel"
table is used to uniquely identify each client database and the
exact point or "synch-level" the server database had attained
when the client database completed its last synchronization.
SyncSmart uses this sync-level information to determine which
records to extract from the server database and distribute to
the client at synchronization time. (For more information on the
special columns and tables required to support a SyncSmart
implementation, please refer to the SyncSmart integration
guide.)
