Hello, dear blog reader.
This week’s blog is coming to you from the home office. Finally, my son is back in school, and the house has some peace.
For this blog, I thought I would write about a situation that I ran across recently.
Problem
Did you know that log shipped databases do not have to sit there collecting dust and not being used? However, there are certain situations where replication or Availability Groups are not the answer to the question of separating your reporting traffic.
You don’t need to build an actual data warehouse, you don’t want the complexity of Windows Cluster for Availability Groups, and you cannot use replication because your tables do not have primary keys.
We can use the long trusted Log Shipping technology and tweak a few settings and get a read-only copy of a database.
Solution
I will not demo the building of Log Shipping for you; I will cover the setting to allow you to perform this action.
There are three main settings for building Log Shipping. One is you take a Transaction Log backup on the primary server, and the second is to copy the Transaction Log backup to a location the secondary server can read, and finally, you restore the Transaction Log backup on the secondary server.
We will focus on the restore settings to allow you the ready-only copy of the log shipped database.
You will look at these settings on the Secondary Database Settings screen and the Restore Transaction Log tab.
There are two options to choose from for restoring the transaction log. First, no Recovery mode keeps the database in a “Restoring” state and thus does not allow access to the database. The second option, Standby mode, brings the database into a read-only state.
The Standby option gives you a window where the database(s) is in an online state and can be used for reporting, large queries, or other needs. The only different setting to be aware of is your restore schedule. While you still need to take the transaction log backup based on your RPO/RTO, you will only want to perform the restore based on the schedule that will not impact your user. For instance, if your users will be querying data during business hours, you would not want to restore the transaction log during those hours as that will disconnect sessions. My situation allowed me only to have to perform transaction log restores every 24 hours. So I scheduled the restore schedule for 23:59 each day.
Conclusion
Now you have a read-only database copy for use in different scenarios. Sure, this will not be for everyone, as your database will be up to 24 hours behind the source, but if that does not impact your output needs, this is an easy button method to get what you need.
More Information
