0

I need a read-only copy of my production SQL Database so our reporting department can query it and not bring down production because of it being so resource intensive. Would replication still be the way to go (Transactional or snapshot) - or is a read-only availbility group (Which I have no experience with) be the better solution?

3
  • 2
    Which version & which edition are you using? What kind of latency is acceptable? Commented Nov 29, 2023 at 16:35
  • 2
    Are you currently using an availability group?
    – Dan Guzman
    Commented Nov 29, 2023 at 16:42
  • 1
    How many tables would need to be replicated? How big is the database?
    – J.D.
    Commented Nov 29, 2023 at 17:12

1 Answer 1

0

Log Shipping is ideal for your task.

Using Log Shipping, you can leave the secondary database in standby \ readonly mode.

On your primary server, regular Transaction Log backups will be performed, which place virtually no load on the server.

The only thing to take into account is that the data on the secondary server to which transaction log backups are restored during the log shipping process will lag in relevance for a fixed period of time. This is typically acceptable for reporting purposes.

By the way, in documentation it is directly recommended by SQL Server to use Log Shipping for reporting:

You can use a secondary database for reporting purposes

Log shipping can be configured via SSMS by writing backup/restoration scripts, or via third-party utilities.

Here are a couple of articles on how to set it up:

What is SQL Server log shipping?

How to Set Up Log Shipping

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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