|
I work in a software company currently using Adaptive Server Anywhere 9, the previous database software for our version was SQL Anywhere 5. Our typical customer setup is a consolidated database and anything between 1 to 40-50 remote databases. One thing I have noticed is that the documentations always assumes that there is a Consolidated User receiving updates on each Remote Database. On our Remote Databases the subscriber is defined as a Remote User. This setup is working fine as is. The one deciding it should be this way left the company some years ago and didn't leave behind any documentation on the subject, nor can I find any documentation on the difference between these in the SA documentation. My questions are;
-- |
|
The non-existence of a CONSOLIDATE USER in a database involved in replication signals to SQL Remote that this is a consolidated database, and as such, conflict resolution triggers should fire, and default conflict resolution actions should occur if a conflict is detected and no trigger exists. Because you have not defined a CONSOLIDATE USER at your remote database, conflict resolution will occur at the remote databases as well as the consolidated, and as a result, conflict resolution will not work as designed. If two remote databases modify the same row, it's unlikely that SQL Remote will be able to resolve the conflict properly. To answer your questions : 1) You gain nothing from this setup that I can imagine, unless you have defined a very custom conflict resolution at both the remote and consolidated databases in RESOLVE UPDATE triggers. 2) You lose the ability for default conflict resolution to work properly. 3) No. This setup has not been considered. I'm not saying the upgrade won't work (in fact, I can't imagine why it wouldn't work), but I am saying it has never been tested. I would strongly advise trying to find out if this design decision was made on purpose when the system was first introduced, and if it was, why? If you can't find any reason why you haven't defined a CONSOLIDATED USER at the remote databases, I would suggest changing the definition, so that default conflict resolution can work properly, and you will have a setup that matches the setup used in our internal testing as well as every other SQL Remote installation in the world. I have attached a sample that shows conflict resolution not working properly when the consolidate user is not defined in the remote database. Simply unzip the contents of the zip file into an EMPTY directory, open a DOS prompt, CD into the directory where the files exist, make sure SA12 is in your path, and type "rep". The sample finishes by selecting the row at the remote and consolidate where there was a conflict, and it clearly shows that the values are different. If you then edit the rem.sql file and change the line that reads GRANT REMOTE TO "cons" TYPE "FILE" ADDRESS 'cons' to GRANT CONSOLIDATE TO "cons" TYPE "FILE" ADDRESS 'cons' and then re-run the sample, you will see that conflict resolution works. I took a quick look at the example of default conflict handling from the documentation, http://dcx.sybase.com/index.html#1201/en/sqlremote/sra-update-conflicts.html There, an example is listed where two users update the same row on two different remote databases. The doc says that the most recent update will "win", all changes made by the first update are lost and no error is recorded. This is the same as we have today. By "most recent", is the transaction timestamp taken into consideration or is "most recent" simply the last site the consolidated database reads messages from ? What I am curious about is, what happens if the same column is updated by two users on the consolidated and a remote site ? In our setup, the consolidated value will be replicated to the remote site and the remote value will be replicated to the "consolidated" (defined as a remote user in our setup) site, values will still be different between databases. Is there a conflict resolution option for these kind of actions ? EDIT: I see that your example proves as an answer to my questions. I also tried to switch those update statements like this: C:sa12dbisql -nogui -c "uid=cons;pwd=sql;eng=cons;dbn=rem1" update admin set textcol = 'rem1' where a_pkey = 1; C:sa12dbisql -nogui -c "uid=cons;pwd=sql;eng=cons;dbn=cons" update admin set textcol = 'cons' where a_pkey = 1; I notice that the content of textcol is still 'rem1' for both databases.So, with default conflict resolution in this case, is the remote database always the "winner" because it is a remote database or did the remote database "win" because the replication messages containing 'update admin set textcol = 'rem1' where a_pkey = 1;' was applied to the consolidated database after the local insert ? In addition, this was a setup in perfect condition. The conflict was detected at the consolidated database. This is not always the case, more than often our situation would be that the consolidated database replicates, messages are transferred, and conflict is detected on the remote database. From my testing, am I right if I assume that:
?? :)
(12 Dec '11, 08:17)
OskarEmil
Replies hidden
AFAIK, yes. As to the conflict resolution: AFAIK there's no difference between a conflict detected in the consolidated between
In both cases, a conflict resolution trigger can be used in the cons to compare the OLD, NEW and REMOTE values - where OLD is the cons's state so far and REMOTE the previous state in the remote. By default, a change done directly in the cons is always "earlier" than one coming from a remote, so by default the remote is the "winner". As to the rest of your questions, by default a remote can't notice a conflict so I can't comment on that - this seems to be a particular feature of your doubled "grant consolidated" setting... Custom conflict resolution using triggers
(12 Dec '11, 08:35)
Volker Barth
That's right. Remote didn't notice the conflict, even with a consolidated user setup. Result was that cons database now had 'rem1' as value and remote database had 'cons' as value. I think we will be able to stop 40%-60% of conflicting updates by implementing a standard setup with conflict resolution in consolidated databases, but conflicts happening on remote sites will still be the same as of today.
(12 Dec '11, 09:23)
OskarEmil
Sorry, ignore my latest comment. It seems like a conflict was detected at the consolidated database and send 'rem1' back to the remote database again when I ran a new full synchronization.
(12 Dec '11, 09:26)
OskarEmil
Replies hidden
Well, with a sound setup, you should not have to live with conflicts at the remotes - otherwise folks wouldn't have had success with SQL Remote for years. Of course, what makes a conflict may be different from the system's and your users's point of view - e.g. if one user updates a row and another user on another remote deletes it then in the end the system may be "clean" (i.e. the row is deleted) but it may not be what the first user has expected:)
(12 Dec '11, 09:45)
Volker Barth
1
It's not clear to me if there are still un-answered questions here. For conflict resolution, I'm not concerned about whether there is a particular time when data is in synch between all the databases. The entire purpose of conflict resolution is to ensure that after all databases are done passing changes back and forth, that all databases in the replicating system have the same data. I do not believe this is possible if you have not defined a CONSOLIDATE USER at your remote sites.
(12 Dec '11, 16:16)
Reg Domaratzki
1
That seems reasonable to me and I think I got an answer that I may work with and do some further testing on. You are right that it is not possible, it is in fact a major problem that users change the same column data at two different sites where the result is that the data is not synchronized, it is just swapped (consolidated db gets remote data, remote db gets consolidated data), and I am pretty sure this is because we run a setup with REMOTE USER in both ends. Thank you
(13 Dec '11, 02:23)
OskarEmil
|
