question with type of replication

Hello,
I have a question on the subscriptions and type of replication to use.
We have about 5 subscriptions to a published db setup as merge replication
via the websync on sql2k5. We would actually like the subscriptions (all
running sql express) to just download changes to the publisher. We do not
need the publisher to pass any data up to the subscribers. Is there a way to
do this? We need to use the functionality of web synchronization, if at all
possible. Thanks in advance.
Jake
Use the exchangetype of uploadonly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jake" <noreply@.nowhere.com> wrote in message
news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a question on the subscriptions and type of replication to use.
> We have about 5 subscriptions to a published db setup as merge replication
> via the websync on sql2k5. We would actually like the subscriptions (all
> running sql express) to just download changes to the publisher. We do not
> need the publisher to pass any data up to the subscribers. Is there a way
> to do this? We need to use the functionality of web synchronization, if at
> all possible. Thanks in advance.
> Jake
>
|||Hilary,
Where would I add this script setting in SQL2k5? Since the clients are
sql express there really isn't a scheduled agent to run the replication,
we're using a .net app to start the replication process. Prior to this we
are running the following scripts to setup replication. Any further
clarification would be appreciated. Thanks in advance.
--BEGIN: Script to be run at Subscriber FOR
WAN--
sp_addmergepullsubscription
@.publication = 'pub_subscriber_db', --the name given to the publication
during the creation of the publication via the wizard
@.publisher_db = 'subscriber_db', --the name of the actual database running
on the server instance
@.publisher = 'computer-db', --the name of the server instance
@.subscriber_type = 'global',
@.sync_type='automatic'
--BEGIN: Script to be run at Publisher FOR
WAN--
use [subscriber_db]
exec sp_addmergesubscription @.publication = N'pub_wcpc_arizona',
@.subscriber = N'subscriber-01\subscriber',
@.subscriber_db = N'subscriber_db',
@.subscription_type = N'pull',
@.subscriber_type = N'global',
@.subscription_priority = 0,
@.sync_type = N'Automatic'
GO
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23PnLtc3RGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Use the exchangetype of uploadonly.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Jake" <noreply@.nowhere.com> wrote in message
> news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
>
|||Jake,
in SQL Server 2005 there is the @.subscriber_upload_options parameter of
sp_addmergearticle which takes the following values:
0 : No restrictions. Changes made at the Subscriber are uploaded to the
Publisher.
1 : Changes are allowed at the Subscriber, but they are not uploaded to the
Publisher.
2 : Changes are not allowed at the Subscriber
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Is there a way to have changes made on the subscriber downloaded the the
publisher, but additions on the publisher are not uploaded to the
subscriber?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
Let me re-phrase.
Scenario:
We have two (actually more but there shouldn't be a difference between two
or more) subscribers and want their data to download to the publisher, but
we do not want subscriber 1 data to be uploaded to subscriber 2 if
subscriber 2 replicates after subscriber 1 has.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jake,
you could possibly modift the exchangetype parameter on the commandline
dynamically when initiating a pull subscription. However I'd be afraid that
the data will soon become corrupt this way. Merge is really designed for
data to be fully partitioned amongst all the nodes involved. Partitioning is
ok, and not replicating deletes to a subscriber is also ok - but what you
are suggesting could lead to a complete spaghetti of data. Perhaps you could
explain further the business requirements and we might arrive at a different
way of realising them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the responses so far. I think we have it. We're going to add
a column for the workstation and use filter rows with the host_name. If I am
correct this will only replicate the items for that subscriber to the
publisher, back and forth. Is this correct?
Also is there a way to limit the amount of RAM that replication can use? We
have a couple of subscribers, using sql express with little RAM and are
running out of VM. My thought is if we can limit the RAM being used it will
avoid this issue, but increase the amount of time it takes to replicate.
What are your thoughts?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSj%23pL4RGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Jake,
> you could possibly modift the exchangetype parameter on the commandline
> dynamically when initiating a pull subscription. However I'd be afraid
> that the data will soon become corrupt this way. Merge is really designed
> for data to be fully partitioned amongst all the nodes involved.
> Partitioning is ok, and not replicating deletes to a subscriber is also
> ok - but what you are suggesting could lead to a complete spaghetti of
> data. Perhaps you could explain further the business requirements and we
> might arrive at a different way of realising them.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Jake,
for the first part, you could use dynamic filtering, but if you only have a
few subscribers, a separate publication for each subscriber works better.
Limiting the RAM for the replication exe - interesting. To be honest, I
don't know how this can be done. I'll look into it though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the help so far. If you find a way to limit the Ram I'd be
very appreciative.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23mgw854RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Jake,
> for the first part, you could use dynamic filtering, but if you only have
> a few subscribers, a separate publication for each subscriber works
> better. Limiting the RAM for the replication exe - interesting. To be
> honest, I don't know how this can be done. I'll look into it though.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>