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)
>
>

question regarding referenced views and replication order

You can update the view definition and hope that this updates the
dependencies correctly. If it doesn't you can use sp_addscriptexec to add
all the views in the correct order. Note that in SQL Server 2005 you can
specify the article order (@.processing_order) to get round this type of
issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Stefan,
replication of views and procs doesn't follow the usual paradigm in
replication. Changes to these programming objects aren't picked up by the
log-reader, so we have to resort to some sort of manual solution to pick up
changes to replicated objects and apply them which ever way we go about it.
The script to generate the view creation scripts could be based on the view
definition - in which case it'll always be upto date if you even need to run
the whole thing again. Ad-hoc changes can meanwhile be done using individual
views.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Question on SQL 2k transactional replication

I have a CRM package that uses SQL Server 2k as the back end. I've got
this same software package being used by two different offices (two
separate implementations of the package, the database structure is
identical). I need to get both offices up an running on the same set of
data.
I plan to merge the two databases into one, and mirror this database in
the two offices (over a dedicated T1) and use transactional replication
(using the publisher/updating subscriber model - since both offices will
be updating/inserting) to keep these databases synced. The rationale for
the two databases is simply that everything will occur on the database
that is local to the respective office, so there will be little to no
performance price. In this scenario, if a transaction doesn't go through
on both databases it is either dropped or queued, right?
A G,
You might want to post this in the .replication group and reside guru Hilary Cotter will give some sage advice.
Before you do that, check that the vendor will support SQL Server replication running underneath the application? You may also wish to consider using Merge replication as that has a conflict resolver built in.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Question on SQL 2k transactional replication

I have a CRM package that uses SQL Server 2k as the back end. I've got
this same software package being used by two different offices (two
separate implementations of the package, the database structure is
identical). I need to get both offices up an running on the same set of
data.
I plan to merge the two databases into one, and mirror this database in
the two offices (over a dedicated T1) and use transactional replication
(using the publisher/updating subscriber model - since both offices will
be updating/inserting) to keep these databases synced. The rationale for
the two databases is simply that everything will occur on the database
that is local to the respective office, so there will be little to no
performance price. In this scenario, if a transaction doesn't go through
on both databases it is either dropped or queued, right?A G,
You might want to post this in the .replication group and reside guru Hilary
Cotter will give some sage advice.
Before you do that, check that the vendor will support SQL Server replicatio
n running underneath the application? You may also wish to consider using Me
rge replication as that has a conflict resolver built in.
Mark Allison, SQL Server MVP
http://www.markallison.co.uksql

Question on Querys

Ok in Replication for sql 2000,'

what happens when a query such as 'update tableA set a=1 where id=5' affects 1 row. What will happen when it does not affect any row.

Aaron

hi

1) with one row

in case of a merge replication => the updated Data replicated to the subscriber

in case of a transaction replication => the statement replicated and affects 1 rows on the subscriber again

2) with no row

in case of a merge replication => NOTHING

in case of a transaction replication => the statement replicated and affects 0 rows on the subscriber again

In Case of only one row, there is not big difference

in case of 100000 rows there is a big difference, because in case of merge replication, all of 100000 rows replicated step by step, in case of transaction replication, the statement will block the replication a long time, because the statement (not the result) will be replicate to the subscriber

bye

Thorsten Ueberschaer

Question on merge replication

We are working on an application that is running data on a local sql
server 2000 database. There is a second sql server on a different
domain, connected via vpn. Database on main domain sql server is
replicated / merge / push published to the 2nd domain sql server
The goal is to be available 24/7 for 100% so the plan is when the main
server becomes unavailable users are rerouted and the 2nd server takes
over. When the 1st becomes available again the data on the 1st server
should be updated from the 2nd and is supposed to resum
We are wondering now if Merge replication is is the way to go AND how
to set it up.
TIA, Martin
Merge replication can be use for this the caveats are
1) latency. Latency can be 1 minute or longer depending on the volume of
your transactions. This means if your publisher goes belly up, and your
standby (subscriber) server comes on line, it may not have all the records
which your publisher has
2) replication will add a guid column to your tables which may break the
application which is using these tables
Bi-directional replication is ideal for this as
1) latency is typically much less
2) you don't have to add a guid column
3) bi-directional replication works best when the transactions originate at
one node at a time
To set up merge replication go to Tools, point to replication, and click
create and manage publications and follow the prompts
To set up bi-directional transactional replication you are best to get the
updated BOL, and search on Implementing Nonpartitioned, Bidirectional,
Transactional Replication.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:4thvi0d35qmir83nnri6ud7tpcclqu6b79@.4ax.com...
>
> We are working on an application that is running data on a local sql
> server 2000 database. There is a second sql server on a different
> domain, connected via vpn. Database on main domain sql server is
> replicated / merge / push published to the 2nd domain sql server
> The goal is to be available 24/7 for 100% so the plan is when the main
> server becomes unavailable users are rerouted and the 2nd server takes
> over. When the 1st becomes available again the data on the 1st server
> should be updated from the 2nd and is supposed to resum
> We are wondering now if Merge replication is is the way to go AND how
> to set it up.
> TIA, Martin
>

Question on Data out of sync

Greetings,
Here is the scenario: Server A is Prod, Server B is reporting or
history
Server A does Transactional replication to Server B. Server A gets
records purged at the given point of retention. So as you can tell
Server B will have much more data than Server A. Today, I get the
dreaded call of that data in server A does not seem to be at Server B.
We may have had some issues with replication a couple of weeks ago but
it appeared that they were corrected and replication has been working
fine since.
So , to my questions
Is there any way of pushing over all data on Server A to B to make
sure they are in sync without losing the older data as well?
Any good way of comparing the data in Server A to Server B without
having to go row by row through each table?
I am afraid that we may be out of synch and would like to recify it
without having to drop all replication, bcp out and then bcp back in
to get them back in sync. As you can imagine that is a major headache
and would be my last resort. Any and all ideas are welcome.
Thanks,
MM
You could also have a look at Innovartis DB Ghost at
http://www.innovartis.co.uk/ which can compare & synchronize your
database schema AND data (plus build, verify & version control). I have
found you can use SQL Enterprise Manager to generate scripts to quickly
drop & re-create replication and run DB Ghost to ship your missing rows.
John McGrath MCSE
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!