2

I have a SQL Server 2012 Std cluster that has a lot of writes going on. We'd like to offload some of the reporting to a separate (2012 Std) VM. It can be around 24 hours out of date.

I think log shipping is probably a good approach but I'm concerned I won't be able to compress the tlogs (they'll be big). My question is, can I manually set up log-shipping, essentially leaving the primary untouched and just restore 24 hours worth of transactions on the secondary each day - whilst still being able to read from the secondary during the day?

EDIT: After doing some testing and with the help from users here, it looks like I can just "manually" restore 24 hours' worth of backups each day by going SINGLE_USER then restoring all the tlogs WITH STANDBY, essentially log shipping but without touching the primary at all.

  • You dont have to do it manually. Logshipping restore job will take care of that. – Kin Shah Nov 2 '15 at 16:46
1

I think log shipping is probably a good approach but I'm concerned I won't be able to compress the tlogs

Since you are using logshipping on SQL Server 2012, you can compress your transaction log backups

enter image description here

(they'll be big).

Take frequent log backup, so that the size of the T-logs is manageable and you are using backup compression (see above screenshot). The minimum is 1 min.

My question is, can I manually set up log-shipping, essentially leaving the primary untouched and just restore 24 hours worth of transactions on the secondary each day - whilst still being able to read from the secondary during the day?

Yes you can do what you are saying. You can delay the restoration of log backups on the secondary server. So, just have to copy job to copy the log backups from primary to secondary and depending on your need and business requirement, you can delay the log restoration.

Note that when T-logs are being restored, users will be disconnected.

enter image description here

EDIT:

You can put inplace of allowing only COPY_ONLY log backups so that it does not break your log backup chain. I have written it in my answer here. Also, remember that a full backup will never break a log chain. It can mess up a differential backup though. So, its a good practice to have COPY_ONLY backups!

  • Thanks for this, this is contrary to the documentation Microsoft SQL Server 2012 Enterprise supports backup compression. msdn.microsoft.com/en-us/library/ms190640(v=sql.110).aspx – dwjv Nov 2 '15 at 14:38
  • One other thing, will this interrupt the backup chain on the primary? I do daily full, hourly tlog. Apologies, I've not configured log shipping before. – dwjv Nov 2 '15 at 14:39
  • Once you configure logshipping, any adhoc backups will break log chain. So just stop hourly log backups. A full backup wont break the log chain. – Kin Shah Nov 2 '15 at 15:10
0

If the reporting requirement is during core business hours, and since you can live with the data being a day out of date, you could take a full or diff backup on the primary at the end of each day (hopefully you'll be doing something like this already), and restore the full/diff combination on your new secondary reporting server. And then bring the database online. It's good for reporting purposes the following day, and this process is repeated at the end of each day. And it validates backups. As the data can be up to 24 hours out of date, I wouldn't be inclined to restore the transaction log backups.

Please remember that offloading the reporting to this secondary server does mean you have to ensure that this SQL Server is correctly licensed as production.

If you do want to restore the transaction log backups, the problem is that any process connected to a standby database needs to be killed before the restore can continue. This is messy if you want to continually restore log backups.

An alternative technology available in Standard edition is SQL Server Replication, which can be used for reporting purposes. It's not a trivial technology to implement or support, so don't rush into it.

  • This was also a thought of mine but I wanted to avoid copying the full backup over each day. I appreciate the users would need to be disconnected, the plan would be to drop the connections out of hours and apply all the tlog backups in one go, ready for the next day... – dwjv Nov 2 '15 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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