1. if setting up servers for transactional replication, is it possible for the subscriber server to delay the publisher server?
For example, the publisher server is under heavy load and inserting/deleting a large amount of rows; I realize that the publishing server will be a little slower due to sending these updates to the subscriber server(s), but does the publishing server have to wait or stop processing while the subscribing servers are committing the same transactions?
Or are the sent transactions to the subscriber queued and processed at its own pace - not impacting/stopping the publishing server? I understand that a distributor server cab queue these commands to the subscribers, but does the action of queuing ever pause the production/publisher server in any way?
2. When doing queries on a subscriber database (since it's read only), does the database become unavailable while committing transactions sent from the distributor or publishing server(s)?
Thanks for any help. Just looking to gain some insight on replication.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
1. Technically No. Although if the distributor is in the same server, there'll be the inevitable resource waits. The publisher DB transactions themselves are not delayed per se, but the underlying resource contention can delay the transaction as a whole. Queuing is managed by the distributor. The publisher actually does nothing. The distributor will monitor the DB log of the publisher and replicate those changes to the subscribers. If the distributor is located in a separate physical server, there wouldn't be any waits.
The problem here is the scope in which the sql statement executes. The @temp table variable declared is different from the @temp variable used in the inline query as the scope of both the queries are different. To make the query work we need to run the query in same scope ie declare the table variable using the same SQL satements as one used for the query.