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