| Groups | Blog | Home |
sql server replication : non-updateable merge subscription
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
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)
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. -- 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 [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... > 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 >
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. -- 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 [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:O%23T0E4saGHA.3720@TK2MSFTNGP03.phx.gbl... > 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? > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:e8eW4zraGHA.1196@TK2MSFTNGP03.phx.gbl... >> 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. >> >> -- >> 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 >> >> >> >> "Leila" <Leilas@hotpop.com> wrote in message >> news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... >>> 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 >>> >> >> > > >
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)
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? [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:e8eW4zraGHA.1196@TK2MSFTNGP03.phx.gbl... > 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. > > -- > 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 > > > > "Leila" <Leilas@hotpop.com> wrote in message > news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... >> 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 >> > >
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? [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:eWhFtvqaGHA.1192@TK2MSFTNGP03.phx.gbl... > 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) > > > >
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. -- 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 [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:eu6y5VwaGHA.3328@TK2MSFTNGP02.phx.gbl... > 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? > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23Zp8dLtaGHA.3304@TK2MSFTNGP04.phx.gbl... >> 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. >> >> -- >> 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 >> >> >> >> "Leila" <Leilas@hotpop.com> wrote in message >> news:O%23T0E4saGHA.3720@TK2MSFTNGP03.phx.gbl... >>> 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? >>> >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:e8eW4zraGHA.1196@TK2MSFTNGP03.phx.gbl... >>>> 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. >>>> >>>> -- >>>> 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 >>>> >>>> >>>> >>>> "Leila" <Leilas@hotpop.com> wrote in message >>>> news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... >>>>> 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 >>>>> >>>> >>>> >>> >>> >>> >> >> > >
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 -- 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 [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:eu6y5VwaGHA.3328@TK2MSFTNGP02.phx.gbl... > 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? > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23Zp8dLtaGHA.3304@TK2MSFTNGP04.phx.gbl... >> 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. >> >> -- >> 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 >> >> >> >> "Leila" <Leilas@hotpop.com> wrote in message >> news:O%23T0E4saGHA.3720@TK2MSFTNGP03.phx.gbl... >>> 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? >>> >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:e8eW4zraGHA.1196@TK2MSFTNGP03.phx.gbl... >>>> 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. >>>> >>>> -- >>>> 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 >>>> >>>> >>>> >>>> "Leila" <Leilas@hotpop.com> wrote in message >>>> news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... >>>>> 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 >>>>> >>>> >>>> >>> >>> >>> >> >> > >
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!.. [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:u8lRyyyaGHA.4060@TK2MSFTNGP02.phx.gbl... > 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. > > -- > 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 > > > > "Leila" <Leilas@hotpop.com> wrote in message > news:eu6y5VwaGHA.3328@TK2MSFTNGP02.phx.gbl... >> 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? >> >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:%23Zp8dLtaGHA.3304@TK2MSFTNGP04.phx.gbl... >>> 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. >>> >>> -- >>> 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 >>> >>> >>> >>> "Leila" <Leilas@hotpop.com> wrote in message >>> news:O%23T0E4saGHA.3720@TK2MSFTNGP03.phx.gbl... >>>> 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? >>>> >>>> >>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>> news:e8eW4zraGHA.1196@TK2MSFTNGP03.phx.gbl... >>>>> 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. >>>>> >>>>> -- >>>>> 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 >>>>> >>>>> >>>>> >>>>> "Leila" <Leilas@hotpop.com> wrote in message >>>>> news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... >>>>>> 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 >>>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >> >> > >
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? [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23Zp8dLtaGHA.3304@TK2MSFTNGP04.phx.gbl... > 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. > > -- > 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 > > > > "Leila" <Leilas@hotpop.com> wrote in message > news:O%23T0E4saGHA.3720@TK2MSFTNGP03.phx.gbl... >> 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? >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:e8eW4zraGHA.1196@TK2MSFTNGP03.phx.gbl... >>> 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. >>> >>> -- >>> 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 >>> >>> >>> >>> "Leila" <Leilas@hotpop.com> wrote in message >>> news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... >>>> 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 >>>> >>> >>> >> >> >> > >
I'm trying to find out if this is by design or a bug.
-- 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 [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:%23Hp2dh1aGHA.3524@TK2MSFTNGP04.phx.gbl... > 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!.. > > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:u8lRyyyaGHA.4060@TK2MSFTNGP02.phx.gbl... >> 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. >> >> -- >> 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 >> >> >> >> "Leila" <Leilas@hotpop.com> wrote in message >> news:eu6y5VwaGHA.3328@TK2MSFTNGP02.phx.gbl... >>> 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? >>> >>> >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>> news:%23Zp8dLtaGHA.3304@TK2MSFTNGP04.phx.gbl... >>>> 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. >>>> >>>> -- >>>> 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 >>>> >>>> >>>> >>>> "Leila" <Leilas@hotpop.com> wrote in message >>>> news:O%23T0E4saGHA.3720@TK2MSFTNGP03.phx.gbl... >>>>> 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? >>>>> >>>>> >>>>> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >>>>> news:e8eW4zraGHA.1196@TK2MSFTNGP03.phx.gbl... >>>>>> 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. >>>>>> >>>>>> -- >>>>>> 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 >>>>>> >>>>>> >>>>>> >>>>>> "Leila" <Leilas@hotpop.com> wrote in message >>>>>> news:eZ1j9jqaGHA.4196@TK2MSFTNGP03.phx.gbl... >>>>>>> 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 >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |