SQL Anywhere database replication - "Promote" a table that replicates subsets of rows to making it replicate all rows.

Author

"Promote" a table that replicates subsets of rows to making it replicate all rows.

Jorge Quintana

2005-07-06, 8:23 pm

I have the need to replicate all records from a set of tables to every
remote database, however this set of tables are already part of an existing
publication and remote databases subscribe to this table (via the
publication) using a Subscribe By clause meaning that remote users receive
only a subset of records.

What would be the best approach to "promote" a table that replicate subsets
of rows to making it replicate all the rows.

Has anybody experience similar need? I know that I can use a "mirror
technique" by creating new tables and copy the contet of the existing tables
into the new tables, making sure the two tables are always in-synch (when a
update is done in Original table then an update has to be done in the new
table) and then have users subscribe to the new table without any subscribe
or where clause. But I want to hear from people that gone thorugh this same
issue and how was resolved or if there is any recommended aproach or
guidelines to resolve a situation like this

Thanks in advance

Jorge

Rob Waywell

2005-07-07, 1:23 pm

This should be fairly straight forward. You will need to:

1) Update the publication to remove the SUBSCRIBE BY clause for this table
2) Execute a SYNCHRONIZE SUBSCRIPTION for this publication. Depending on how
many other tables are in the publication and the volume of data, this
synchronization can be a pretty expensive operation, however it will
accomplish what you are trying to do.

--
-----------------------------------------------
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
"Jorge Quintana" <jorge. quintana@laborfinder
s.com> wrote in message
news:42cc3f4e$1@foru
ms-2-dub...
>I have the need to replicate all records from a set of tables to every
>remote database, however this set of tables are already part of an existing
>publication and remote databases subscribe to this table (via the
>publication) using a Subscribe By clause meaning that remote users receive
>only a subset of records.
>
> What would be the best approach to "promote" a table that replicate
> subsets of rows to making it replicate all the rows.
>
> Has anybody experience similar need? I know that I can use a "mirror
> technique" by creating new tables and copy the contet of the existing
> tables into the new tables, making sure the two tables are always in-synch
> (when a update is done in Original table then an update has to be done in
> the new table) and then have users subscribe to the new table without any
> subscribe or where clause. But I want to hear from people that gone
> thorugh this same issue and how was resolved or if there is any
> recommended aproach or guidelines to resolve a situation like this
>
> Thanks in advance
>
> Jorge
>
>

Jorge Quintana

2005-07-07, 8:24 pm

Rob,

Well this publication has many other tables and aprox 300 remote users are
subscribedy to this publication so I am assuming this would generate a lot
of messages back and for between remote users and consolidated database and
there is a potential of clogging the message pipeline.

Another concern is that publication changes need to be done in both places
(Consolidated and Remote), I don't know if I can execute an Alter
Publication as part of a pass-through statement. Is there any other
alternative?

Thanks

Jorge

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:42cd77fb$1@foru
ms-2-dub...
> This should be fairly straight forward. You will need to:
>
> 1) Update the publication to remove the SUBSCRIBE BY clause for this table
> 2) Execute a SYNCHRONIZE SUBSCRIPTION for this publication. Depending on
> how many other tables are in the publication and the volume of data, this
> synchronization can be a pretty expensive operation, however it will
> accomplish what you are trying to do.
>
> --
> -----------------------------------------------
> 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
> "Jorge Quintana" <jorge. quintana@laborfinder
s.com> wrote in message
> news:42cc3f4e$1@foru
ms-2-dub...
>
>

Rob Waywell

2005-07-08, 11:23 am

This is a design decision that you will have to make. Yes I could automate
the ALTER PUBLICATION operation on the remotes, either through PASSTHROUGH
or through a request queue. Since you are partitioning data on the remotes
already (are you sure of this? the default extraction would not include the
SUBSCRIBE BY clauses in the publications on the remote databases), you may
have data on the remotes which is not on the consolidated and which you will
have to reconcile.

The decision you need to make is to choose the approach that you feel is
most effective. You may decide that you are better off making the changes at
the consolidated and re-extracting all the users.

How would you like to implement this change? If you can tell us your
proposed approach then we can provide feedback.

--
-----------------------------------------------
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
"Jorge Quintana" <jorge. quintana@laborfinder
s.com> wrote in message
news:42cd9482$1@foru
ms-1-dub...
> Rob,
>
> Well this publication has many other tables and aprox 300 remote users are
> subscribedy to this publication so I am assuming this would generate a lot
> of messages back and for between remote users and consolidated database
> and there is a potential of clogging the message pipeline.
>
> Another concern is that publication changes need to be done in both places
> (Consolidated and Remote), I don't know if I can execute an Alter
> Publication as part of a pass-through statement. Is there any other
> alternative?
>
> Thanks
>
> Jorge
>
>
> "Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
> news:42cd77fb$1@foru
ms-2-dub...
>
>

Jorge Quintana

2005-07-08, 1:24 pm

Rob,

Want to implement is a White/Yellow Pages type of functionality across all
my stores, so that users can easily look up people or store information for
any store. We are a franchise system and stores are owned by different
corporations (Franchisees), each corporation owns more than one store,
currently the records are replicated across stores from the same
corporation, I want to replicate records across all corporations to be able
to implement the search.

OPTION 1

My initial idea to avoid messing with the current production environment was
to create a set of new tables that will be the "Public" tables and the
information that want to provide with the new search functionality will be
"programmatically" copied to these tables every time a change is made in
original tables. The new tables can be part of an already existing
Publication for system wide data (replicates to every remote database). This
option it has the less impact in the current production environment and will
be coded in the application itself (copy data from original table to public
table) but will have some redundancy in the data model.

OPTION 2

After thinking in option 1, I said, well I already have the tables and the
information if I create a new set of tables I will have redundancy in my
Data Model (although this would be a conscious decision). So if I can
modify the replication rules and avoid creating new tables then "voila" I
only need to implement the search capabilities and do not have to worry to
programmatically copy data from private tables to public tables. The problem
with this option is that is a high-risk approach that can disrupt a
production environment of more than 300 remote databases and that would not
be fun also if there are changes in the original tables I will need to
change the public tables and modify my application to programmatically copy
the data to the new tables.

NOTES

- It is worth to mention that these tables have very low volume of
data modifications (mainly when a new store is open and when a new person is
hired) it is not by any means a transactional table.

- The option of re-extracting all remote databases is not acceptable
since we are talking about my customers (Franchisees) and I can't enforce
such a thing to satisfy a design requirement besides the fact that will
require a lot of coordination, time and labor to re-extract, push and
install all databases.

Can I synchronize only an Article and not the whole subscription?

Jorge

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:42ceade5@forums
-1-dub...
> This is a design decision that you will have to make. Yes I could automate
> the ALTER PUBLICATION operation on the remotes, either through PASSTHROUGH
> or through a request queue. Since you are partitioning data on the remotes
> already (are you sure of this? the default extraction would not include
> the SUBSCRIBE BY clauses in the publications on the remote databases), you
> may have data on the remotes which is not on the consolidated and which
> you will have to reconcile.
>
> The decision you need to make is to choose the approach that you feel is
> most effective. You may decide that you are better off making the changes
> at the consolidated and re-extracting all the users.
>
> How would you like to implement this change? If you can tell us your
> proposed approach then we can provide feedback.
>
> --
> -----------------------------------------------
> 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
> "Jorge Quintana" <jorge. quintana@laborfinder
s.com> wrote in message
> news:42cd9482$1@foru
ms-1-dub...
>
>

Rob Waywell

2005-07-11, 9:23 am

The SYNCHRONIZE SUBSCRIPTION statement affects all the tables in the given
publication.

SYNCHRONIZE SUBSCRIPTION
TO publication-name [ ( subscription-value ) ]
FOR remote-user, ...

You can limit the # of users you synchronize at a time, or filter by
SUBSCRIBE BY value, but it needs to work at the publication level in order
to handle foreign key relationships between tables in the publication.

You could put these tables in a separate publication.

--
-----------------------------------------------
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
"Jorge Quintana" <jorge. quintana@laborfinder
s.com> wrote in message
news:42cecb7e@forums
-2-dub...
> Rob,
>
>
>
> Want to implement is a White/Yellow Pages type of functionality across all
> my stores, so that users can easily look up people or store information
> for any store. We are a franchise system and stores are owned by different
> corporations (Franchisees), each corporation owns more than one store,
> currently the records are replicated across stores from the same
> corporation, I want to replicate records across all corporations to be
> able to implement the search.
>
>
>
> OPTION 1
>
> My initial idea to avoid messing with the current production environment
> was to create a set of new tables that will be the "Public" tables and the
> information that want to provide with the new search functionality will be
> "programmatically" copied to these tables every time a change is made in
> original tables. The new tables can be part of an already existing
> Publication for system wide data (replicates to every remote database).
> This option it has the less impact in the current production environment
> and will be coded in the application itself (copy data from original table
> to public table) but will have some redundancy in the data model.
>
>
>
> OPTION 2
>
> After thinking in option 1, I said, well I already have the tables and the
> information if I create a new set of tables I will have redundancy in my
> Data Model (although this would be a conscious decision). So if I can
> modify the replication rules and avoid creating new tables then "voila" I
> only need to implement the search capabilities and do not have to worry to
> programmatically copy data from private tables to public tables. The
> problem with this option is that is a high-risk approach that can disrupt
> a production environment of more than 300 remote databases and that would
> not be fun also if there are changes in the original tables I will need to
> change the public tables and modify my application to programmatically
> copy the data to the new tables.
>
>
>
> NOTES
>
> - It is worth to mention that these tables have very low volume of
> data modifications (mainly when a new store is open and when a new person
> is hired) it is not by any means a transactional table.
>
> - The option of re-extracting all remote databases is not
> acceptable since we are talking about my customers (Franchisees) and I
> can't enforce such a thing to satisfy a design requirement besides the
> fact that will require a lot of coordination, time and labor to
> re-extract, push and install all databases.
>
>
>
> Can I synchronize only an Article and not the whole subscription?
>
> Jorge
>
>
>
> "Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
> news:42ceade5@forums
-1-dub...
>
>

Jorge Quintana

2005-07-11, 9:23 am

Rob,

Thanks for all the information and ideas, I will setup a testing environment
and start testing my different scenarios to see which one works best

Jorge

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:42d27918@forums
-2-dub...
> The SYNCHRONIZE SUBSCRIPTION statement affects all the tables in the given
> publication.
>
> SYNCHRONIZE SUBSCRIPTION
> TO publication-name [ ( subscription-value ) ]
> FOR remote-user, ...
>
> You can limit the # of users you synchronize at a time, or filter by
> SUBSCRIBE BY value, but it needs to work at the publication level in order
> to handle foreign key relationships between tables in the publication.
>
> You could put these tables in a separate publication.
>
>
> --
> -----------------------------------------------
> 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
> "Jorge Quintana" <jorge. quintana@laborfinder
s.com> wrote in message
> news:42cecb7e@forums
-2-dub...
>
>

Sponsored Links