|
Home > Archive > SQL Anywhere database replication > July 2005 > How to design a publication for flexible table schema?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to design a publication for flexible table schema?
|
|
| Volker Barth 2005-06-28, 8:23 pm |
| Hi all,
we are administrating an ASA 8 replication system with about
200 remotes.
ASA consolidated is on ASA 8.0.3.5251, remotes are on
8.0.1.3023 (yes, quite old...).
We are quite experienced in making schema changes:
In the last years, we frequently had to alter publications
in order to add tables / columns.
We do know, that - in general - dropping tables, renaming
columns and the like are no-no's if you aren't willing to
reextract...
There are - as usual - two groups of data that are
replicated so far:
a) System-specific data which is centrally administrated and
is shared among all remotes
b) User-specific data which is - primarily - manually added
by our users, and which is replicated with "subscribe by".
Therefore we use two different publications for these two
groups.
But now there's a special problem:
We are going to add a few tables to our replication system
which will take data generated by application programs. This
will primarily taking place in the cons.
Of course, we try to design those tables as deliberatly as
possible, but we expect that there might be reasons to
change the schema of those tables later on when applications
will change, and that these changes could enforce "stronger"
schema changes than just adding tables or columns.
Therefore we would like to be able to make as much schema
changes in future for those tables as possible, and to do
this without interferring with the "old" tables that are
already replicated.
As the data is generated by applications (and as such "in
our administrative access"), we expect that the problem will
not be to transform the data to a different schema but to
apply schema changes to remotes.
The question is:
Would there be any advantage if we use a separate
publication for those tables?
I.e. would it be easier to drop and recreate those tables if
we could drop this separate publication before?
We guess that dropping tables from a "living" publication
won't be a realistic option.
If there is no advantage than adding those tables to one of
the existing publications would be less overhead.
Any ideas about this are highly appreciated.
TIA
Volker
P.S. I know, I could do a lot of tests to find out about
dealing with upcoming changes but unfortunately I don't have
time...
| |
| Rob Waywell 2005-06-29, 9:23 am |
| One benefit of a separate publication is that SYNCHRONIZE SUBSCRIPTION
operates at a publication level. You will still need to use PASSTHROUGH to
create the new tables and to clean up/drop the old tables, but SYNCHRONIZE
SUBSCRIPTION will let you push out the new data for the new tables in the
new publication.
Of course all of this will need to be coordinated with application changes.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
<Volker Barth> wrote in message news:42c1a07d.6b5a.1681692777@sybase.com...
> Hi all,
>
> we are administrating an ASA 8 replication system with about
> 200 remotes.
> ASA consolidated is on ASA 8.0.3.5251, remotes are on
> 8.0.1.3023 (yes, quite old...).
>
> We are quite experienced in making schema changes:
> In the last years, we frequently had to alter publications
> in order to add tables / columns.
> We do know, that - in general - dropping tables, renaming
> columns and the like are no-no's if you aren't willing to
> reextract...
>
> There are - as usual - two groups of data that are
> replicated so far:
> a) System-specific data which is centrally administrated and
> is shared among all remotes
> b) User-specific data which is - primarily - manually added
> by our users, and which is replicated with "subscribe by".
>
> Therefore we use two different publications for these two
> groups.
>
> But now there's a special problem:
> We are going to add a few tables to our replication system
> which will take data generated by application programs. This
> will primarily taking place in the cons.
> Of course, we try to design those tables as deliberatly as
> possible, but we expect that there might be reasons to
> change the schema of those tables later on when applications
> will change, and that these changes could enforce "stronger"
> schema changes than just adding tables or columns.
>
> Therefore we would like to be able to make as much schema
> changes in future for those tables as possible, and to do
> this without interferring with the "old" tables that are
> already replicated.
> As the data is generated by applications (and as such "in
> our administrative access"), we expect that the problem will
> not be to transform the data to a different schema but to
> apply schema changes to remotes.
>
> The question is:
> Would there be any advantage if we use a separate
> publication for those tables?
> I.e. would it be easier to drop and recreate those tables if
> we could drop this separate publication before?
>
>
> We guess that dropping tables from a "living" publication
> won't be a realistic option.
>
> If there is no advantage than adding those tables to one of
> the existing publications would be less overhead.
>
>
> Any ideas about this are highly appreciated.
>
>
> TIA
>
> Volker
>
>
> P.S. I know, I could do a lot of tests to find out about
> dealing with upcoming changes but unfortunately I don't have
> time...
| |
| Volker Barth 2005-06-29, 11:23 am |
| Hi Robert,
thanks for your response!
I guess the advantage to use SYNCHRONIZE SUBSCRIPTION would
not be the main point. I expect we would be able to "refill"
the corresponding data into a new table schema in the cons
in a way that should get the data replicated without a
explicit SYNCHRONIZE SUBSCRIPTION.
Maybe I should spell my question as:
Is there a chance to drop a table which belongs to a
publication without having to drop the corresponding
publication?
I.e. would something like the following work (done in
passthrough mode):
alter publication P drop table X;
drop table X;
create table X; -- with new schema
alter publication P add table X;
Or could their be "old" DML statements corresponding to
table X that could still be waiting in the log to be
replicated and that would stop replication when confronted
with the new schema?
I'm not sure how SQL Remote works: Will it replicate data
which has belonged to a publication at the time it was
changed but does not belong to a publication at the time SQL
Remote scans the log?
I would be glad to get any hints.
Volker
Robert Waywell wrote:
> One benefit of a separate publication is that SYNCHRONIZE
> SUBSCRIPTION operates at a publication level. You will
> still need to use PASSTHROUGH to create the new tables
> and to clean up/drop the old tables, but SYNCHRONIZE
> SUBSCRIPTION will let you push out the new data for the
> new tables in the new publication.
>
> Of course all of this will need to be coordinated with
> application changes.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
>
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug[color=darkred]
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available
> through the iAnywhere Developer Community at
> www.ianywhere.com/developer <Volker Barth> wrote in
> all, >
> "subscribe by". >
> tables or columns. >
> before? >
| |
| Rob Waywell 2005-06-30, 9:23 am |
| Executing the schema change through passthrough forces it to be synchronized
with the flow of DML statements in the SQL Remote messages. So yes this is
an effective approach.
Note that any old operations in the remote log waiting to be uploaded are
read directly from the log file by dbremote so they don't rely on the
current table schema to be replicated. On the other hand, these operations
do require a compatible table definition at the consolidated in order to be
applied there. This likely means using new table names when you are creating
new tables that are not backwards compatible.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
<Volker Barth> wrote in message news:42c2bebb.75cd.1681692777@sybase.com...[color=darkred]
> Hi Robert,
>
> thanks for your response!
>
> I guess the advantage to use SYNCHRONIZE SUBSCRIPTION would
> not be the main point. I expect we would be able to "refill"
> the corresponding data into a new table schema in the cons
> in a way that should get the data replicated without a
> explicit SYNCHRONIZE SUBSCRIPTION.
>
> Maybe I should spell my question as:
> Is there a chance to drop a table which belongs to a
> publication without having to drop the corresponding
> publication?
>
> I.e. would something like the following work (done in
> passthrough mode):
>
> alter publication P drop table X;
> drop table X;
> create table X; -- with new schema
> alter publication P add table X;
>
> Or could their be "old" DML statements corresponding to
> table X that could still be waiting in the log to be
> replicated and that would stop replication when confronted
> with the new schema?
>
> I'm not sure how SQL Remote works: Will it replicate data
> which has belonged to a publication at the time it was
> changed but does not belong to a publication at the time SQL
> Remote scans the log?
>
>
> I would be glad to get any hints.
>
> Volker
>
>
>
> Robert Waywell wrote:
>
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
| |
| Volker Barth 2005-06-30, 11:23 am |
| Hi Robert,
my mind seems to clear up step by step...
As I'm basically still wondering if I should use a separate
publication for those tables - let's call them "result
tables" - or not, I guess the decision will be "not". This
is mainly because I prefer a lesser number of publications
if suitable.
If I get your points right, there would be no special
advantage for a separate publication.
Using the existing publications, I still could - if there's
ever need to do so:
1. Drop existing tables from the existing publications.
2. Drop tables that are no longer used by publications.
On course, like any replication topic, this should be done
with enough planning and testing and the like.
The main thing to care about would be to enforce that these
tables exist (and are part of the corresponding
publications) as long as any remote side could send data for
those tables.
As our data model has its own "version management", I think
we could reliably know the time when no "unwanted data" will
stuck around, and then could start to drop those tables.
This would be "flexible enough" from my point of view.
Robert, please correct me if the above assumptions are wrong
- and thanks for your support!
Volker
Robert Waywell wrote:
> Executing the schema change through passthrough forces it
> to be synchronized with the flow of DML statements in the
> SQL Remote messages. So yes this is an effective
> approach.
>
> Note that any old operations in the remote log waiting to
> be uploaded are read directly from the log file by
> dbremote so they don't rely on the current table schema
> to be replicated. On the other hand, these operations do
> require a compatible table definition at the consolidated
> in order to be applied there. This likely means using new
> table names when you are creating new tables that are not
> backwards compatible.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
>
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug[color=darkred]
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available
> through the iAnywhere Developer Community at
> www.ianywhere.com/developer <Volker Barth> wrote in
> Robert, >
| |
| Rob Waywell 2005-07-01, 11:23 am |
| Sounds like you understand it correctly.
One possible clarification - the tables that are being deprecated don't have
to stay in the publication on the receiving(consolidat
ed) end at that time.
They need to exist so that insert/update/deletes can be applied, but you
probably want to remove them from the publication at the point you deprecate
them.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
<Volker Barth> wrote in message news:42c4136d.6d3.1681692777@sybase.com...[color=darkred]
> Hi Robert,
>
> my mind seems to clear up step by step...
>
> As I'm basically still wondering if I should use a separate
> publication for those tables - let's call them "result
> tables" - or not, I guess the decision will be "not". This
> is mainly because I prefer a lesser number of publications
> if suitable.
>
> If I get your points right, there would be no special
> advantage for a separate publication.
> Using the existing publications, I still could - if there's
> ever need to do so:
>
> 1. Drop existing tables from the existing publications.
> 2. Drop tables that are no longer used by publications.
>
> On course, like any replication topic, this should be done
> with enough planning and testing and the like.
>
> The main thing to care about would be to enforce that these
> tables exist (and are part of the corresponding
> publications) as long as any remote side could send data for
> those tables.
>
> As our data model has its own "version management", I think
> we could reliably know the time when no "unwanted data" will
> stuck around, and then could start to drop those tables.
>
> This would be "flexible enough" from my point of view.
>
> Robert, please correct me if the above assumptions are wrong
> - and thanks for your support!
>
> Volker
>
>
> Robert Waywell wrote:
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
| |
| Volker Barth 2005-07-04, 7:23 am |
| Hi Robert,
thanks for your response - especially as your last point was
not yet clear to me.
Guess now I'm ready to fix the design of our next DM
version...
....and that feels so much better with such valuable support
behind!
Thanks again,
Volker
Robert Waywell wrote:
> Sounds like you understand it correctly.
>
> One possible clarification - the tables that are being
> deprecated don't have to stay in the publication on the
> receiving(consolidat
ed) end at that time. They need to
> exist so that insert/update/deletes can be applied, but
> you probably want to remove them from the publication at
> the point you deprecate them.
>
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
>
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available
> through the iAnywhere Developer Community at
> www.ianywhere.com/developer <Volker Barth> wrote in
> Robert, >
> those tables. >
> it >> to be synchronized with the flow of DML statements
> in the >> SQL Remote messages. So yes this is an
> effective >> approach.
> to >> be uploaded are read directly from the log file by
> schema >> to be replicated. On the other hand, these
> operations do >> require a compatible table definition at
> the consolidated >> in order to be applied there. This
> likely means using new >> table names when you are
> creating new tables that are not >> backwards compatible.
> all >>
>
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
> Status >> http://my.sybase.com/detail?id=1002288
> Hi >> Robert, >
> able >> > to "refill" the corresponding data into a new
> table >> > schema in the cons in a way that should get the
> data >> > replicated without a explicit SYNCHRONIZE
> SUBSCRIPTION. >> >
> to >> > table X that could still be waiting in the log to
> be >> > replicated and that would stop replication when
> it >> > was changed but does not belong to a publication
> at the >> > time SQL Remote scans the log?
>
>
|
|
|
|
|