Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Replication / April 2006

Tip: Looking for answers? Try searching our database.

non-updateable merge subscription

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Leila - 28 Apr 2006 09:36 GMT
Hi,
In a particular scenario, the customer has a Win XP with SQL Server 2000
Personal Edition. I want to establish replication environment. Obviously
Merge and Snapshot replication is available. Snapshot is not suitable, the
only option is Merge. Some tables must not reflect the changes to publisher.
Is it possible to do this?
Thanks in advance,
Leila
Paul Ibison - 28 Apr 2006 11:10 GMT
Leila,
in SQL Server 2005 you can use -EXCHANGETYPE = 2 parameter to limit the data
flow to the publisher, and subscriber database permissions to prevent data
alterations if this is what you require. Actually in this case the merge
triggers still fire and record all the changes on the subscriber into
metadata tables. In SQL Server 2005 this is more elegantly implemented using
sp_addmergearticle @subscriber_upload_options = 1 or 2 depending on your
requirements. Note that in SQL Server 2005 this is on a per article basis,
which is what you request. In SQL Server 2000 the -EXCHANGETYPE parameter is
on the subscription itself, so you will need to create 2 publications and
have it work that way.
 Cheers,
          Paul Ibison SQL Server MVP, www.replicationanswers.com
            (recommended sql server 2000 replication book:
            http://www.nwsu.com/0974973602p.html)
Leila - 28 Apr 2006 14:13 GMT
Great! What about the changes those are being stored on the subscriber
because of
the triggers on the table? It will be too much after long time. Can I create
job to truncate them regularly?
How about dropping the replication triggers of table on the subscriber?

> Leila,
> in SQL Server 2005 you can use -EXCHANGETYPE = 2 parameter to limit the
[quoted text clipped - 11 lines]
>             (recommended sql server 2000 replication book:
>             http://www.nwsu.com/0974973602p.html)
Paul Ibison - 28 Apr 2006 15:50 GMT
Leila,
you'd have to ensure that the changes made to the subscriber tables don't
violate the changes coming through from the publisher. Assuming that you can
ensure that then disabling the merge triggers on the subscriber sounds like
a good move. However this sort of alteration would leave your setup in an
unsupported state if you raise a PSS call, so it's your decision. It might
be worth moving to SQL Server 2005 where this is supported out of the box.
 Cheers,
          Paul Ibison SQL Server MVP, www.replicationanswers.com
            (recommended sql server 2000 replication book:
            http://www.nwsu.com/0974973602p.html)
Hilary Cotter - 28 Apr 2006 12:55 GMT
With SQL 2000 you can only select this level of bidirectionality on the
publication/subscription level. So if you want to be able to toggle certain
articles as read only on the subscriber or publisher what you have to do is

a) create a separate publication for them.
b) carefully apply permission to the account you are using for each
individual subscriber. For example lets say you use the account Leila. You
want table1 to be full, but table2 to be read only, or lets say update only.
You grant permission to Leila in the publication db for update only.

Then when you create your publication and get to the specify articles
dialog, click on the browse  button to the right of your table name, and
select the Merging Changes Tab, and then select which DML type activity you
want to be enforced. What will happen is that if you select all three lets
say, and the permission for the Leila account does not have rights to do any
DML on the authors table (lets say), all DML originating at the subscriber
will be kicked back as a conflict.

Signature

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

> Hi,
> In a particular scenario, the customer has a Win XP with SQL Server 2000
[quoted text clipped - 4 lines]
> Thanks in advance,
> Leila
Leila - 28 Apr 2006 14:13 GMT
Thanks Hilary,
Will this solution keep the changes recorded on the subscriber in related
tables? I mean if by this manner I prevent the changes from being replicated
to
publisher, the changes will fill system tables too much?
How about dropping the replication triggers of table on the subscriber?

> With SQL 2000 you can only select this level of bidirectionality on the
> publication/subscription level. So if you want to be able to toggle
[quoted text clipped - 23 lines]
>> Thanks in advance,
>> Leila
Hilary Cotter - 28 Apr 2006 15:49 GMT
With compensate_for_errors set to false it will keep changes in the
subscriber. The system tables should be pruned on the subscriber time each
time the merge agent runs, the publisher tables will be the same size as if
you did not have this option.

You can use the option to selectively make tables read only for all dml or
only certain types of dml, and even change it on the fly.

While other people (not Paul) may recommend dropping or disabling the
replication triggers I do not recommend this, as it may lead to problems
down the line.

Signature

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

> Thanks Hilary,
> Will this solution keep the changes recorded on the subscriber in related
[quoted text clipped - 34 lines]
>>> Thanks in advance,
>>> Leila
Leila - 28 Apr 2006 20:50 GMT
Thanks indeed Paul and Hilary!
I tried "Download Only Conflict Resolver" for my article and it produces
exactly the result that I want (changes go to subscriber but do not come
back to publisher).
I could not find any documentation for this resolver. Can I rely on this?

> With compensate_for_errors set to false it will keep changes in the
> subscriber. The system tables should be pruned on the subscriber time each
[quoted text clipped - 49 lines]
>>>> Thanks in advance,
>>>> Leila
Hilary Cotter - 29 Apr 2006 02:32 GMT
What this does is say that if there is a conflict download the publishers
row from the publisher and send it to the subscriber. If there is no
conflict, subscriber changes still make it from the subscriber to the
publisher. This may or may not be what you want - in fact I don't think it
is what you want at all.

Signature

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

> Thanks indeed Paul and Hilary!
> I tried "Download Only Conflict Resolver" for my article and it produces
[quoted text clipped - 57 lines]
>>>>> Thanks in advance,
>>>>> Leila
Leila - 29 Apr 2006 06:44 GMT
As you might have experienced, if a change is made at the subscriber and
there is no conflict, the change does not go to publisher! I wish I could
find a document that describes behavior of this resolver!..

> What this does is say that if there is a conflict download the publishers
> row from the publisher and send it to the subscriber. If there is no
[quoted text clipped - 66 lines]
>>>>>> Thanks in advance,
>>>>>> Leila
Hilary Cotter - 29 Apr 2006 11:02 GMT
I'm trying to find out if this is by design or a bug.

Signature

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

> As you might have experienced, if a change is made at the subscriber and
> there is no conflict, the change does not go to publisher! I wish I could
[quoted text clipped - 75 lines]
>>>>>>> Thanks in advance,
>>>>>>> Leila
Hilary Cotter - 29 Apr 2006 03:48 GMT
Actually I am not so sure of this now. I have tried to repro it and I find
that it does exhibit the behavior you describe. This doesn't jive with my
understanding on how it is supposed to work.

Hilary

Signature

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

> Thanks indeed Paul and Hilary!
> I tried "Download Only Conflict Resolver" for my article and it produces
[quoted text clipped - 57 lines]
>>>>> Thanks in advance,
>>>>> Leila
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.