on 2012 Sep 26 11:58 AM
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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.
Those tables then would be synchronized with another database.
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?
That way, we could completely isolate the changes between both databases.
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...
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).
Three remarks:
As to the bad experience with proxy tables in v5: I can't comment on that, other that my experience with proxy tables under v5.5.05 has been fine - both with other SA databases or MS SQL Server remotes. And v5 is soooo old - I'd really give this a try with a current version. In particular v12 has seen remarkable performance optimizations w.r.t. proxy tables.
Synchronizing "just some records" from a huge table is no problem both for SQL Remote and MobiLink, as you can specify filters on what to synchronize when defining the publications.
If you need to somehow "control" when existing data is ready to be synchronized (say, by explicitly marking it as "ready") and that control logic cannot be expressed as a logical condition, then I would think that you really will have to use separate tables and can't use views. (One might also think to use manually-refreshened materialized views but I'm quite sure those cannot be part neither of a ML nor a SQL Remote setup.)
Just my 2 cents, apparently:)
User | Count |
---|---|
67 | |
10 | |
10 | |
10 | |
10 | |
8 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.