Vb.net mssql server 2008 database sync

[maccinelyro]

Hi. I was creating an enrollment system for my Thesis, and my adviser told me that I must have a Code in my program that Synchronize database from client and server so that if the terminal connection is "Up" the data will go to the server and if the connection is "Down" the data will go to the installed server on the the local machine. and every 20 mins or 30 mins will sync the database. can somebody help me. Any Idea how to do this Sync thing. thank you. :) have a nice day.

[lolafuertes]

As this is a project for a tesis, i will suggest the following:
Tables on both databases must have a global unique identifier field and also a synchro time field, and a row status (syncronized, updated, inserted or deleted).
In order to optimize the access to this info this can be included in a Index using both fields.

On the client side, you must have a database selector to connect to the server database, the client one or both depending on the connection status of the client machine.

You can determine if the server is connected (or accesible) trying the connection to the server each time you need to do a transaction (select, insert, update or delete). It would be appropiate to have a data layer class that manages all this stuff.

When you first connect to the remote server, you set the connector for transactions to the remote server.

At this time, you can start a bacgroud worker that, for each row in each table of the server database, will compare if the corresponding GUID exists on the local one, and if exist and the synch time in the local is lower than in the server, then update the row to the server values, and update both to sync status and to time now [Just get the time once and write to both]. If not exist in the client and the server status is not deleted then insert the row in the client and updat both to synch status and to time now.

When finished this step, do the same but from client to server, comparing if the local GUID exists on the server. If exists and the synch time in the local is greater that the server, then update the server row to the local values, and update both to sync status and time now. If not exists in the server and the local status is not deleted, then insert the row in the client and update both to synch status and time now.

You need to do it using a distributed transacion for each row, to be sure that the changes are committed on both machines. Also you need to catch any error to rollback the distributed transaction in case of problems, and go back to close and reopen the connection to the remote server.

Once the background worker finishes, you can start a timer to wait for 20 (or 30 or whatelse value) minutes to start it again.

If the connection to the server is not possible, you need to switch all the transactions (select, insert, update and delete to the local server.

The delete should only update the status and time for the synch info.

You can try to develop it by your self and enrich this approach with new suggestions.

Hope this helps.

[ChrisPadgham]

the biggest problem you will have is establishing a unique primary key. They cannot be set from the server because if the server is down you will not be able to add new rows. Possibly the most practical solution is for each client machine to have a unique id. the primary key can then be a compound of the clientId & a record id. This would be allocated on the client database and a background process synching the server databse.

[lolafuertes]

@ChrisPadgham: using a GUID column accomplishes this according to this MSDN article.

Hope this helps

[maccinelyro]

@loldafuertes. Thanks for your suggestion. it really helps me. i did create a simple SYnch program and I can now Synch the two database. but I was confused on what should I do. My program is only Enrollment System. and the database on my Client is Only for Student because the client is only used for registration. Do I have to have a same database on the client as the server has? ex. Server Database[Enrollment]: Student, Section, Subject, Faculty. Client Database[Enrollment]: Student, Section, Subject, Faculty. than Server Database[Enrollment]: Student, Section, Subject, Faculty. Client Database[Enrollment]: Student only(same field with the Student in the Server).

[lolafuertes]

If you can work disconnected from the server, yes, ity is necessary to have the same info in the server than in the client to have access to the info for sections, subjects, etc. needed during the enrollment process.

But the synch option can be assimetric: From the server to the client all tables, but from the client to the server only Student.

Hope this helps