Microsoft SQL secondary read-only DB for SSRS?

Question

I know that Microsoft SQL has AlwaysOn with a read-only secondary replica. However the cost of enterprise edition for all of our SQL servers makes this not possible. We have SSRS configured to run reports against our OLTP databases but it puts high loads on them.

Are there any other options of getting a secondary copy that could be used to point SSRS at?

Answer 1

Transactional replication

In theory Transactional Replication would be your go to here, but in reality it can be tricky to set up and maintain. This is very opinionated, but I don't think you would find many DBA's who really enjoy (setting up) transactional replication.

I am not saying you shouldn't, but it can get frustrating.

Log shipping

Another option could be Log shipping while keeping the databases in Standby Mode, but then you would have to choose between:

A) Getting your log restores behind.

B) Getting your connections kicked out when logs are restored.

It is not ideal, but it could work. There are other things you would need to work around, listed here.

Backup Restore

You could restore from backup, either by using your full (+ Diff / Log) backups, and simply restoring up until your last backup.
The main advantage being that it is simple to set up and does not put a lot of strain on the server.

You could also set up side by side restores to refresh your data during reporting hours.

Check out Restore-DbaDatabase by Dbatools if you haven't yet.

The main disadvantage being that you would work on stale data. If this disadvantage is a no-go or not depends on your requirements, hardware, database size,....