2

When using Transaction Replication, are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it?

i.e. can selects be made against tables to which updates are being applied?

Will performance of these selects suffer whilst transactions are being applied?

2
  • Is this a theoretical question? Or is "Transactional Replication" some process in some available DBMS that you are using? If the latter, please let us know which. May 8 '14 at 18:55
  • Transactional Replication is a process available in SQL Server 2012
    – S OM.
    May 8 '14 at 19:33
1

are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it?

There are no restrictions for selecting data on subscriber side.

Will performance of these selects suffer whilst transactions are being applied?

Yes, depending on the frequency of the log-reader agent there might be blocking when the replicated transactions are being applied to subscriber.

If your publication database is very active - generating lots of changes that needs to be applied to subscriber, I would suggest to change the database option to SET READ_COMMITTED_SNAPSHOT ON for publication and subscription databases. TEST IT before implementing in PROD !!

As a side note:

  • publish only subset of tables that are required rather than the entire database.
  • Use PULL subscription when there are large number of articles as the agent will run on subscriber side and this will alleviate load on distribution.

Refer to : Enhance General Replication Performance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.