We have a 2012 standard edition instance that is doing log-shipping of 2 related databases every 15 minutes to a secondary 2012 standard edition instance for DR purposes. Although it provides read-only access, users are automatically disconnected every 15 minutes when the restore job runs (it takes up to 2 minutes to complete the restores).
Without interfering with that process, we would like to set up a secondary instance for reporting and developer troubleshooting purposes that would also be kept up to date at least every 15 minutes (the reports need near real-time data), but would not involve periodic disconnections, enable different security than the primary (so that devs can read from the secondary but not the primary), and preferably allow indexes to be created that do not exist on the primary (to improve reporting performance).
1) How can we achieve this?
2) How can we achieve this without having to upgrade to Enterprise Edition?
Best Answer
Transactional Replication is typically used to off-load reporting to another server/instance and can be near real-time in a best case scenario. The benefit of Transactional Replication is that you can place different indexes on the subscriber(s) to optimize reporting. You can also choose to replicate only a portion of the data if only a subset is needed for reporting.
With Transactional Replication you will need Standard Edition or higher for the Publisher and Subscribers can be Express Edition or higher.
Have a look at Transactional Replication to get started.
If you have any questions, let me know. I hope this helps.