Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merging schema changes from server db to client db Expand / Collapse
Author
Message
Posted Thursday, May 28, 2009 6:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 1, 2009 5:15 AM
Points: 5, Visits: 13
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 ?

Post #724964
Posted Thursday, May 28, 2009 6:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 166, Visits: 268
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
Post #724981
Posted Thursday, May 28, 2009 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 1, 2009 5:15 AM
Points: 5, Visits: 13
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
Post #724992
Posted Thursday, May 28, 2009 7:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 11, 2009 2:35 AM
Points: 50, Visits: 14
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.

Post #725047
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse