Sql-server – Looking for a better way with Reporting Db server

reportingsql server

I have a "reporting" db server which is basically a clone of production using backup and restore in a stored proc to handle restore , user , permission etc.
Backups/restores happen 1x per day at a scheduled time.

Now they have a new db that they want on the report server and it is a pure OLTP db (right now I backup and restore it daily takes about 45 mins). The would like something a little more up-to-date just not realtime aka approx. with 2-4hours. I am in middle of converting from 2008 r2 to sqlserver 2014. 2008 r2 would give me replication or log shipping anybody have a better idea? Would really like a better idea as I am a single dba shop on call 24/7 365 and enjoy ability to sleep thru the night.
Thanks
kris

Best Answer

I would suggest 2 options ...

If you are using

  • Enterprise edition then

    • SQL server 2014 Enterprise edition will give you AlwaysON Availablity groups .. which you can use to offload reporting on secondary.

  • Standard edition then

    • You can use logshipping with delaying on transaction logs on secondary in STANDBY mode. The only caveat will be that when the transaction logs are being restored, all the users will be kicked out of the secondary server.

(I am not mentioning database mirroring, but you can use that (even though it is marked as deprecated. You can create database snapshot on secondary and every 2-3 hrs, you can generate a new one since your requirement is - data can be stale 2-4hours)

When using backup/restore method, make sure that you have enabled Instant file initialization and you are using backup compression. That will cut down the backup/restore time.

Related Question