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;
asked 09 Dec '11, 09:10
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.
answered 09 Dec '11, 09:49