|
Is the best way to syncronize two tables in the same database creating triggers in the origin table or there is another way to do it? We have about 50 tables that need to be syncronized to another set of tables but the destination set is a simplified version of the tables. We are using SQL Anywhere 12. |
|
SQL Anywhere synchronization is done either via MobiLink (synchronous replication) or SQL Remote (asynchronous replication). Both of these only work with tables in different databases. Doing anything with the same database will involve trigges and such. I'll see if this question can be tagged a better way. My first thought is that what you want is views.
I agree with Russ that views sound like what you want.
(26 Sep '12, 11:26)
Reg Domaratzki
Comment Text Removed
We designed the synchronization is two steps: 1) The tables is the hot database would be synchronized with another set of tables in the same database. 2) Those tables then would be synchronized with another database. That way, we could completely isolate the changes between both databases. Can a view be used with Mobilink in a synchronization?
(27 Sep '12, 08:52)
MarcosCunhaLima
Replies hidden
1
Views can be used in a MobiLink environment in the consolidated database, as long as the view is updated-able. The view you describe that uses a subset of columns from a single table should be update-able. Views cannot be used at a remote site, where dbmlsync runs. Why? The MobiLink Server relies on an ODBC connection to execute queries you write (or you let our product write for you) to keep data in synch, but the on the remote side, dbnlsync scans the transaction log for changes, and only changes to base tables are written to the log, so views can't be used at the remote.
(27 Sep '12, 09:13)
Reg Domaratzki
Are you relating to MobiLink/SQL Remote, or is this "just a export data to another database", as doable with remote data access/proxy tables?
Could you tell why you like to somehow "separate" to access of the "hot tables" and the "synchronized ones"? - FWIW, IMHO views would not help here as then the synchronization would still need to query the underlying base tables. But possibly I have just completely missed the point of your reuqirements...
(27 Sep '12, 10:05)
Volker Barth
Views can be used for scripted uploads, I believe, but that can get quite complex to do robustly.
(27 Sep '12, 16:27)
RussC_FromSAP
Volker It's just an export data to another database. It could be done through proxy tables but I had a terrible experience with it (with SQL Anywhere 5). The option of isolated tables is because we need to control how and when the data would be synchronized between both databases. Hence, we should control when and how the about-to-be synchronized tables (in the same database) will be updated. Afterwards, through Mobilink/SQL Remote, the other database would be automaticaly synchronized. Moreover, the hot tables should keep all the records of each table but the about-to-be synchronized tables need just a small set of records (just the last-month records).
(28 Sep '12, 15:09)
MarcosCunhaLima
Three remarks:
Just my 2 cents, apparently:)
(30 Sep '12, 11:33)
Volker Barth
|