Re: Many pubs or single pub with dynamic filters ???

Tech-Archive recommends: Speed Up your PC by fixing your registry

Here are the issues that you need to consider when moving to a single
publisher model:


1. Concurrency: syncing multiple subscribers at the same time is not
optimized in SQL2000, which will end up serializing the merge process. In
SQL2005, performance is greatly improved by allowing multiple subscribers to
sync at the same time.



2. Partitioning: When a subscriber with a given ID syncs, SQL2000 will scan
the publisher's articles for changes pertaining to that subscriber. This
process has its performance implications for large publication with large
number of changes. In SQL2005, this issue was also addressed by the use of
partition groups.


Regards,
Rafik

--
This posting is provided "AS IS" with no warranties, and confers no rights.


"dw" <dw@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7730CDFB-81A8-437B-80A5-C5B0131814B9@xxxxxxxxxxxxxxxx
> Hello...
>
> Some background: We have merge replication set up between SQL 2000 and
> mobile devices running SQL Mobile. Each of our customers has a unique
> customer id and currently we have a separate publication for each
> one...that
> has a static filter such as "WHERE CustomerID = 12345". And on each
> device,
> it subscribes to its own publication....based on the Customer ID.
>
> What I am thinking about....in the near future, as our customer base
> grows,
> is that we'll be faced with maintaining lots of Publications on the
> server -
> one for each Customer ID.
>
> So, would it be better to have a single publication that has dynamic
> filters...based on the HostName property...which I'd set in code on the
> device (based on the Customer ID) or keep things the way we have them now?
>
> I am mostly concerned about replication/synchronization performance
> between
> the server and the mobile devices...some of which use GPRS for
> connectivity.
> I don't want to sacrifce any performance gains, if there are any, by
> having
> lots of publications.
>
> thanks for any thoughts.
> - will


.

Relevant Pages

  • Re: Many pubs or single pub with dynamic filters ???
    ... we cannot move to SQL 2005 for ... Customer ID in the filtering of the data, or is it better to have multiple ... syncing multiple subscribers at the same time is not ... > process has its performance implications for large publication with large ...
    (microsoft.public.sqlserver.replication)
  • Merge pull across VPN
    ... - Each customer has one or more MSDE databases on their own sitewhich each pull a subscription (one publication per customer) from the central SQL Server. ...
    (microsoft.public.sqlserver.replication)
  • Customizing users from address
    ... I have a customer who emails their publications to their customers. ... They would like the From line to look like Publication Subscription@xxxxxxxx ... system and this extra user would cost extra $$$ each month. ...
    (microsoft.public.exchange.admin)
  • Re: SQL2000 Publisher/SQL7.0 Distributor?
    ... Also, if you have multiple subscribers to a particular publication, they'll ... all use the same distributor, so only one of them is configured to hold the ... distribution database, per publication. ...
    (microsoft.public.sqlserver.replication)