Is this scenario possible?

Transactional Replication.
Box1: Publisher
Box2: Reporting only Subscriber
Box3: Updateable Subscriber
If Box1 modifies data, it obviously goes to both Subscribers. But if box 3
modifies data, will it show up on Box2? If memory serves correctly, it will
not by design. Is there a way to change this?
SQL2K SP3
TIA, ChrisR
it will. I just tried it.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:OlbFqjZKFHA.2812@.TK2MSFTNGP15.phx.gbl...
> Transactional Replication.
> Box1: Publisher
> Box2: Reporting only Subscriber
> Box3: Updateable Subscriber
> If Box1 modifies data, it obviously goes to both Subscribers. But if box 3
> modifies data, will it show up on Box2? If memory serves correctly, it
will
> not by design. Is there a way to change this?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||I've tried it with no luck. :-( Box2 will not recieve new rows inserted
from Box3.
Any ideas?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#dSrqZkKFHA.3340@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> it will. I just tried it.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:OlbFqjZKFHA.2812@.TK2MSFTNGP15.phx.gbl...
3
> will
>
|||it does work for me however. I am not sure why it is not working for you.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:%23wE1dPlKFHA.2852@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I've tried it with no luck. :-( Box2 will not recieve new rows inserted
> from Box3.
> Any ideas?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#dSrqZkKFHA.3340@.TK2MSFTNGP14.phx.gbl...
box
> 3
>
|||Is your Box2 a Subscriber of both Box1 and Box3? I only have it set up as a
Subscriber of Box1.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OjssPelKFHA.508@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> it does work for me however. I am not sure why it is not working for you.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23wE1dPlKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> box
it
>
|||Hillary I just got it. I don't know why, but I set up 2 different
Publications for this. One for the Immediate Updating Subscription, and one
for the Non Immediate Updating Subscription. Still not sure why I went that
route.
Once I used only 1 Publication, and configured Immediate Updating in the
Subscription options only, all went as expected.
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OjssPelKFHA.508@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> it does work for me however. I am not sure why it is not working for you.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23wE1dPlKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> box
it
>

Is there transactional consistency across multiple publications?

Hi,

Currently, I have Server A which has Publication P1. Server B is subscribed to P1. Let's use 'T1' as the name for the set of tables/articles included in P1. Now I need to add serveral new tables to Server A. Let's call the new set of tables 'T2'. There is a Server C that needs to sync with the data in both T1 and T2. But Server B cannot have T2's data for privacy reasons.

One of the solutions I'm thinking about is to create another publication in Server A, called P2, which would publish the data in T2. Then have Server C subscribe to both P1 and P2. There would be no changes to Server B, who still subscribes to P1. My concern with this solution is: there are times where one transaction on Server A affects tables in both T1 and T2; since this type of transaction is split into 2 publications, will transactional consistency be maintained in the replication?

More specifically, suppose a statement (S1) in a transaction inserts a row in a T1 table, the next statement (S2) inserts a row in a T2 table using the result of S1. So S1 will be included in P1 and S2 in P2. Does SQL Server 2005 Replication guarantee that, by the time S2 is executed in Server C, S1 is already executed?

If anyone could explain what would happen in the above scenario, I'd really appreciated.

Thanks,
Dandan

Hi Dandan,

Transactional consistency for multiple subscriptions to the same subscriber database from multiple transactional publications of the same publisher database will be preserved if all publications involved are configured to share the same distribution agent (syspublications.independent_agent = 0). Otherwise, multiple distribution agents servicing the subscriptions can deliver parts of a transaction (spanning multiple tables) at different speed.

Hope that helps.

-Raymond

|||

Thank you very much for this information, Raymond.

Your rely mentioned "same subscriber database". In my scenario, different subscriber databases are subscribed to the same publisher. That is, Server B subscribes to P1, Server C subscribes to P1 and P2. P1 and P2 are publications from the same publisher database. In this case, will transactional consistency be preserved if the publications share the same distribution agent?

Thanks you very much,

Dandan

|||

I am not sure I understand the question correctly but different subscriber databases are serviced by different distribution agents at varying speeds so while transactional consistency is preserved at each subscriber database, there really is no guarantee that a replicated transaction will arrive at all subscribers at the same time.

-Raymond

|||

Sorry that I wasn't more clear with the question. But I think you have answered my question. Let me summarize: transactional consistency is preserved at each subscriber database, even if the subscriber database is subscribed to multiple publications, given that these publications are using the same distribution agent. In other words, if I have a stored procedure that performs many operations in one transaction, and this transaction affects multiple publications, then the order of execution for this transaction will stay the same when it is replicated to the subscriber database.

Did I get it right?

Thanks much,
Dandan

|||

I think I have probably over interpreted your situation and yes, the order is preserved in your scenario.

-Raymond

|||

Raymond,

I just need one more clarificaiton. For the subscriber that is subscribed to multiple publications, can the syncs from the 2 publications happen at different times? (We plan to have the subscriber sync with the publisher every hour) If so, could it break the intergrity between the tables in the 2 publications if the tables have dependencies on each other?

I would think the answer is no, because the above thread states "transactional consistency is preserved in the same subscriber syncing with multiple publications." I'd just like to confirm this with you.

Thanks much,
Dandan

|||

For a subscriber subscribing to multiple publications (from the same publisher database), transactional consistency across these multiple subscriptions\publications is preserved if and only if they are all serviced by the same distribution agent. That is, syspublications.independent_agent = 0 for all the publications involved. Note that this is *not* the default if you configure replication through SQL2005 Management Studio. You should be able to change this particular setting through the publication property page as long as you haven't created any subscriptions yet.

Hope that helps.

-Raymond

|||

Thank you very much for all of your help, Raymond. I will make sure to set publication's independent_agent property to false.

Dandan

Is there another way out?

Guys,
We have a transactional replication set up with a single publisher and
a single subscriber.
Recently, replication failed due to the following reason:
Data, erroneously, was deleted from subscriber. Then, to make the
publisher and subscriber synch, the same data was deleted from
publisher.
At that point, the publisher, as it should, attempted to delete the
data in the subscriber. But since the data is not there, the attempt
failed, and replication failed.
I tried to restart the agent, but replication kept failing. Since
nothing worked, I just deleted the subscription and recreated it. The
replication then started, but since it had to go through all the steps
again (i.e. Snapshot ...), it took us 2 hours to get the data back in
synch.
Luckily this happened in the testing environment. If it would be
production, we would be in trouble, as availability is our #1 concern.
Would anyone know of any other (i.e. more elegant) way I could've
solved this problem?
The only other idea I had was the following: It is possible that the
data which the publisher plans to delete is temporarily stored in some
file and if that file is located and deleted, and then the agent is
restarted, it could possibly work - but, I could not locate any such
files - and this is a completely hypothetical idea.
Any suggestions would be appreciated.
Thanks
have a look at the continue on data consistency error profile.
Hilary Cotter
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
"sql_er" <sql_er@.yahoo.com> wrote in message
news:1165954222.427226.20530@.j44g2000cwa.googlegro ups.com...
> Guys,
> We have a transactional replication set up with a single publisher and
> a single subscriber.
> Recently, replication failed due to the following reason:
> Data, erroneously, was deleted from subscriber. Then, to make the
> publisher and subscriber synch, the same data was deleted from
> publisher.
> At that point, the publisher, as it should, attempted to delete the
> data in the subscriber. But since the data is not there, the attempt
> failed, and replication failed.
> I tried to restart the agent, but replication kept failing. Since
> nothing worked, I just deleted the subscription and recreated it. The
> replication then started, but since it had to go through all the steps
> again (i.e. Snapshot ...), it took us 2 hours to get the data back in
> synch.
> Luckily this happened in the testing environment. If it would be
> production, we would be in trouble, as availability is our #1 concern.
> Would anyone know of any other (i.e. more elegant) way I could've
> solved this problem?
> The only other idea I had was the following: It is possible that the
> data which the publisher plans to delete is temporarily stored in some
> file and if that file is located and deleted, and then the agent is
> restarted, it could possibly work - but, I could not locate any such
> files - and this is a completely hypothetical idea.
>
> Any suggestions would be appreciated.
>
> Thanks
>