Merging schema changes from server db to client db

Question

Merging schema changes from server db to client db

Pratibha_Pillai

SSC Enthusiast

Points: 131
More actions
May 28, 2009 at 6:08 am

#384672

My application is used for synchronization of database (client-sql ce and server-sql server 2005). As my application is of type synchronization the client can perform changes on the client db...now once its done ...there can be places where we can find conflicts when we try to execute the downloaded scripts(like if I am imposing primary key constraint I can get an error in the client db incase if the client has entered any duplicate rows).
1) how can I prevent such issues?Any strategy?
2)Also in my client db I have 2 additional columns other than the columns in the server(added after the first download of tables from server).And now if i try to insert any columns and then try to insert data there will be conflict becuase there is change in the ordinal position of the new column in the server and the new column in the client.
Any idea as to how can I resolve this issue ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Satish Nagaraja SSCrazy Points: 2632 More actions · Answer 1

Looks like a bad design in first place , points below explain why....

First schema on Pocket PC SQL CE should have same constraints or rules and identical objects as in sever database, it need not have all the objects of server database which are not required for offline replication.

Second if you are planning to have add columns to tables in cleint which changes the table structure , then best it use XML data types instead of adding columns you append the data in XML column. Yes this is tricky in case you want to run lot of select for reproting purpose on XML column.

Though second option is good which was suggested on workshop by my mentor Praveen(MVP), but in practice there should not be DDL changes to schema as far as possible for synchronization or replication purpose.

-Sat

Cheer Satish 🙂

Pratibha_Pillai SSC Enthusiast Points: 131 More actions · Answer 2

Hi,

Thanks for the reply

But there can be instances wherein I need to add more tables on the server or any such schema changes.As it is a new requirement for me...I need to find a way to this problems.Please do provide me the solutions which can fit the needs of my current architecture.

Thanks

dhulme Mr or Mrs. 500 Points: 570 More actions · Answer 3

If I understand this right, both you and your clients are free to modify the schema of your respective databases, and you need to synchronise data between client and server. The changes will not be managed, scheduled, controlled or cross-notified.

Sorry to be a downer, but that facts are:

1. Chance of success? 1%

2. Risk of total system breakdown? 99%

3. Chance of your profitable client relationship turning into ruinous legal action? 25%

You will need to control the changes on both sides. Without change control, you are doomed to failure.

You can specify columns for each of the update / insert statements:

insert into serverAddress (urn, add1, add2, town)

select id,address1, area, city

from clientTable

where not exists (select top 1 1 from serverAddress where urn = clientTable.id)

update a

set add1 = b.address1

from serverAddress a

inner join clientTable b

on a.urn = b.id

but you will struggle to programatically understand the significance of new, missing, modified columns. Column order should not be an issue unless you are running "select *" sort of nonsense.

If duplicates are being created, you need to revisit the workflow and start again! Are you both recording the same data? Will you both record different interpretations of the same data? Are identity columns used?

Sorry, but you're doomed.