What is the impact on primary (in terms of locking) and restore process when secondary database is being read by large query?

Question

I am exploring couple of solutions:

1. Log shipping - say a large query is running and meanwhile the log shipping process is triggered. Will this stop the query or delay the log shipping restore? Clearly this will have no impact on the primary db.

2. AG - when a large query is running and meanwhile the mirroring process sends data that needs to be written into the db. Or maybe the mode is async so the primary is waiting for the commit on secondary. How does this affect the primary database and restore process?

3. Transactional replication

I am trying to understand what is the impact on the primary database (in terms of locking) and on the restore process when the secondary db is being read by a large query?

Answer 1

Will this stop the query or delay the log shipping restore? Clearly this will have no impact on the primary db.

You mean transaction log is triggered, that is the correct word to use. No this will have no impact on transactions running on primary DB. Transaction log backups does not hampers already running transactions. But in few cases, assuming you have big transaction log and your storage is slow( basically something outside SQL Server) this can hamper transactions on primary DB.

AG - when a large query is running and meanwhile the mirroring process sends data that needs to be written into the db

AG is not mirroring but advanced for of it.

Or maybe the mode is async so the primary is waiting for the commit on secondary.

In Async mode primary does not wait for commit on secondary.

I am trying to understand what is the impact on the primary database (in terms of locking) and on the restore process when the secondary db is being read by a large query?

Well the question is bit vague, but restore on secondary in case of LS is not impacted by query or anything on primary. For AG large read query can be affected by queries running on primary but that is whole different scenario.

Answer 2

Log Shipping:

If the restore process start while there is a big query running on the server (I presume this query is running in a different database as you can't restore a database while reading from it), the query will not stop but depending on the hardware ressources, it may be slow down a bit.

If the query was run against the database that is the replica, then depending on your script, either the log restore will fail with an error like "Exclusive access could not be obtained because the database is in use" or the query will be kill.

This has no chance to impact the primary

Availability group:

When your secondary replica is readable, SQL is using read committed snapshot behind the scene. This mean that even if new transaction are being written, this will not impact your actual query (at least not at a locking level).

Same as for the Log shipping, if the hardware is not adequate, this may slow down your query. The important thing to considerer with AG is that this specific situation can also impact the primary node if you are running in "sync" mode. This is because if the secondary node hardware is not able to keep up, this can cause the secondary to take longer to acknowledge the reception of the new data and as the primary is waiting for this acknowledgment to commit the transaction, it can make the transaction slower on the primary node.