Synchronization Techniques
Synchronization process depends on the functionality required by the disconnected clients, when they are not connected to the Central Database.
There must be strict business rules on both Client Side and Server Side for deletion and updation of records.
Triggers can play a vital role in tracking changes made to either side of the database.
Triggers should maintain a log files on the table basic which should contain in formation about (Type of operation Performed, Table Name, Row Unique Value, Column name, timestamp of operation). These must be maintained at both server and client side.
In the process of Merge replication, Automatic generated primary keys can not serve our purpose, so there needs some unique value that exist at both (Client, Server) which can be used for conflict detection.
All the Tables in the database must follow the cascade rules for updation and deletion.
Business Rules for Database SynchronizationDefine the time when synchronization process should occur for total disconnected clients, they must synchronize themselves at least twice a day, so that both Client and Server have latest data.
Client applications are not allowed to delete any records.
If any situation arises that clients are required to delete any record, then at that time they should only be allowed to mark those records as deleted, the actual process of deletion must occur at Server Side.
There must be only one Administrator, who is allowed to delete any record.
Any deletion operations are allowed after server synchronization itself with all the available clients.
Structure of Log Files that will be maintained by Triggers
<?xml version="1.0" encoding="UTF-8"? >
<transactions>
< table name ="””" rowuniquevalue="””" operationperformed="””"
columnname="””" 'oldvalue="””" newvalue="””" timestampofoperation="””" priority="”0”/" >
< /transactions >
Explanation:
Name: Name of the table in which update was made.
RowUniqueValue: it is Unique Row ID which will uniquely identify the Row it can be Primary key, if it is an auto generated primary key then it will not work.
OperationPerformed: Operation Performed on the table (Insert, Update, Delete);
ColumnName: Name of the Column that was affected by the operation.
OldValue: Old value of the Column before the change was applied; it will be blank for New Record.
NewValue: New value for column after update operation, it will be blank for new and delete operation.
TimestampofOperation: timestamp when the operation was performed, it client and server are in same time zone.
Priority: Set the priority of the operation performed, this is usually for the clients that are allowed to deleted and update the records. This can help when two or more clients make updates on same value.
The above structure of XML file will be easy to maintain and can easily be loaded into a Dataset and can easily be queried.
One important point that needs to clear in mind at creation of transaction Log Files, is the use of Timestamp of operation. It is possible that the client and server could be in different time zones. At that time synchronization can create problems in order to maintain the order of transactions. We can not consider that client and server would be same time zones. If it is case, than no problem it will work fine.
One solution could be to synchronize with the internet time, but this is not a permanent solution. For this to happen, the machine is required to be connected to internet.
Let us consider that the Client and Server are both in different time zones. Then we have to maintain the synchronization process in a specific order on each table that is altered. In this situation we can simply ignore the timestamp data for synchronization.
If Addition, Deletion and Update process has been performed on a table. Then we can maintain an order for changes i.e.
First add all the records to the Client and Server Table.
Then perform the delete operation and reflect to both (Client and Server Database)
And at last perform the Update operation.
Why update operation at last? Because, if some record has been updated by any client and administrator decided to delete that record, then deletion of record after an update operation will be wastage of time.
Using the Above XML File we can generate an SQL Script File (For Insert, Update and Delete operation in database) which will be executed on the database to effect the changes.
I.e. Client Script File will be executed on the Server and vice versa.
In such a situation, server will check how many clients wants to synchronize itself with the server database, and will maintain a queue for all the clients and will accept all the changes from each client one by one and will generate new transaction Log File that will executed on each client.
Conflict detection Technique
Conflict arises when more than one client or client and server both have made updates on same column value. In such a situation there must be some provision to define which update to accept. One way could be a setting the priority of the update that has been made to the record. When any update has been made to the table, both server and clients will set the priority for the update. Now, if server and clients set the same priority for the update, then in that condition, updates from the server will be accepted.
In most of the situation in a client and server based architecture usually server are not an active participant in updating the records, they are in most case act as an central storage medium and very less often act as working medium. It is the client with whom conflicts arises, as they do more client centric work and are usually interacting with the end user. When two or more clients raise a conflict situation, it’s a server task to resolve a conflict and result in a consistent database. For the clients who continuously update the database and require synchronization for such clients it should be provided that they can not process any records in the database. A process records should be maintained for each transactions. When the clients try to synchronize themselves then at that time their request will be sent to the server and server will decide which process to continue and how. This situation depends on the type of application and requirement.
Conflict is a situation when:
One client updates one record and other client deletes that record.
More than one client updates same column value.
One client updates one column value, which other client wants to use in updating other column.
Solutions:
Delete is an operation, which should be given more priority than the update. As I have stated early that, clients are not allowed to delete any record, they can only mark them as deleted, it is the server who will decided whether to delete a record or not.
If more than one client updated the same column value, then it should follow the priority rule, if both the clients have set the same priority value, then server must follow First-Come-First-Serve policy for updating.
The third one needs more attention.
No clients should be allowed to alter master data, on which other values depend.
Even if clients wants to make update, they will maintain a process log for their transaction, that will be passed to sever, when they will synchronize them self.
Usually, Total disconnected clients are not allowed to do any transaction, even if transactions are allowed, they should only be allowed to Add Record.
Totally disconnected clients do not come under the Client Server Architecture. They are just independent applications that want’s their data to be stored at a single place, so that the server has the latest data from all the independent applications. And later all the independent application can copy that data to make them updated.
More to come...
