diff and update when a user is actually migrating, let server1 run until all users have migrated, if migrated prevents to ever connect to server1
simple. best On Thu, Jun 19, 2014 at 1:59 PM, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: > Simon Slavin wrote... > > > >> On 19 Jun 2014, at 3:55pm, jose isaias cabrera <cabr...@wrc.xerox.com> >> wrote: >> >> These servers will be in two different servers and in two different >>> parts of the world, so network access will be very slow. What I am >>> thinking in doing is to copy the data on Server1 to Server2 and set the >>> starting id at a higher point than the last id on Server1. For example, >>> Server1 highest project id is 134000, so, I would set the start id for >>> Server2 to 135000. The Server two will continue to add new projects, so the >>> next project id will be 135001 and so forth. >>> >> >> Is your whole database really just in a single table ? If not, you have >> to figure out how to spot related records in other tables. Once you've >> done that you need to have a 'LastUpdateDate' column on every table. And >> then you have to worry about spotting rows which have been deleted. Since >> the row has been deleted, there are no 'LastUpdateDate' values which let >> you spot it ! >> > > Thanks for helping Simon. Yes, there are a bunch of more tables and yes, > each of them have a LastUpdateDate and they all will be based on ProjID, > which is id in the LSOpenProjects table. This is only temporarery until we > close all the records in Server1.db and then, only one db, Server2.db will > be used. > > > >> Synchronising two different databases is ridiculously difficult to get >> right. Especially when both of them continue to be modified and when you >> continue to make use of AUTOINCREMENT columns to number new rows. There is >> no good general solution. There's not even really a good book on >> approaches to the problem. >> > > Yes, I understand. For Server1, no record will be added, so the last > record opened, in Server1, will continue to be the last one until it is > closed. For Server2, I will insert a dummy record to be 135001, which will > guide the rest of the new records that will be open in Server2. So the > next autoincrement will be 135002, etc. There last record for Server1 > should be, at most, at 134500, so there is a space of 500 empty records. > We want to do this so that there is an specific number that will guide the > original record for Server2 as well as for Server1. > > >> From the information you posted I would recommend that you keep the two >> database files separate and explain to your users when to use one and when >> to use the other. You can have two different apps, one of which uses the >> 'old server' data, and the other accesses the 'new server' data. Or you >> can have one app which can flip between database files. If this is at all >> practical in your business it will make things much simpler and your users >> will understand how their system works far better. >> > > Yes, this will happen, and they know, but in a few months, Server1 will no > longer be, so we need to continue to update until we close that server. > > >> However if you have a reason to need both sets of data in the same >> database, then consider what would happen if instead of introducing a >> 'LastUpdateDate' field everywhere and the clever programming you'd need to >> do to use it correctly, do the following: >> >> * Create a new table called 'SQLCommandLog' in a new attached database. >> * Table has the usual AUTOINCREMENT rowid and one text column called >> 'command'. >> * All commands which might change the data (INSERT/UPDATE/DELETE) get >> saved in the table. >> * To update another database to reflect the changes to this one, simply >> issue those commands, then empty the table again ready for new commands. >> >> That way you don't have to copy a big database file of your data to the >> other server, you just copy a small database of SQL commands. >> > > Hmmmm... > > >> There are the usual problems with choosing different AUTOINCREMENT row >> numbers on different servers but this system may be simpler to implement >> than anything clever with deep understanding of your business logic. >> >> Simon. >> > > I appreciate your input, Simon. > > josé > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
