I’m a fan of SQL Server’s transaction log shipping. It works in Standard Edition, it’s relatively simple to set up, and you can even make your log shipping secondary readable using STANDBY mode.
I’ve worked with some pretty cool, complex log shipping environments over the years. In one case, we had multiple log shipping secondaries and a load balancer involved to support a full fledged reporting application. It worked pretty well– with a lot of careful scripting and support.
But there’s a few things you should know before you decide to implement this yourself.
“Readable” Log Shipping Secondaries Are Just a Point in Time
Although a log shipping secondary can be made readable using “STANDBY” mode, it’s just readable to a specific point in time. If I bring the secondary online at 2 pm, users can only read data through the last committed transaction in the last log file I restored. And…
Everyone’s Kicked Out When You Restore Transaction Logs
The database can’t be read when you’re refreshing data. You must kick out any users (or not refresh the data).
Logs Restore More Slowly if You Use STANDBY (Readable) Mode
If you’re reading from the secondary, you usually want those periods where it’s unavailable to be as short as possible. If you have a lot of log files to restore, you will probably find that performance of the restores is better if you’re in “NORECOVERY” mode while you’re doing the restores, and then switch back to “STANDBY” at the end of the process so the data can be read.
This can be done, it just can take some fancy scripting.
You will also need to add monitoring for slow restores if getting the database online is critical. If your log shipping secondaries get behind, it’s possible to use differential backups to catch them up.
You can’t use “STANDBY” Mode if your Secondary is a Higher Version
I was a little sad when I learned this one years ago. I had a SQL Server 2005 instance that was the log shipping primary. The log shipping secondary was made readable so that developers could check out production data if needed without having access to production.
Our upgrade plan was to get SQL Server 2008 on the least critical servers first — and that developer access server was one of them. But I couldn’t use STANDBY mode on the log shipping secondary with it as a higher version: it failed with an error like this…
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
When SQL Server brings a database online in a higher version, it needs to make some modifications to that database. This breaks with STANDBY mode because the database is read only.
An aside: unfortunately, the Enterprise Edition feature of a database snapshot doesn’t overcome this limitation if you try to use it against a database mirror to make it readable to a point in time. You get a different error, but it’s the same theme:
Msg 946, Level 14, State 1, Line 1
Cannot open database 'MirrorMePlease_snap' version 661. Upgrade the database to the latest version.
Msg 1823, Level 16, State 7, Line 1
A database snapshot cannot be created because it failed to start.
Auto-Create Statistics Doesn’t Work in the User Database in STANDBY mode
If you’re using your log shipping secondary for reporting, SQL Server can’t automatically create statistics in the database, regardless of your settings. (That read only thing keeps coming up.)
In SQL Server 2012 and higher, this isn’t a huge problem because temporary statistics can get created in tempdb. This new feature was added when Availability Groups came along, but it also works for log shipping secondaries, which is pretty awesome.
Security is a Hoot. And by that, I Mean a Problem. (Less So in SQL Server 2014)
In many scenarios, you only want to grant read access to a user on the log shipping secondary database. You do not want that user to be able to access the primary. This is tricky.
To read from a database, you need a login with an associated database user. To grant reads to a log shipping secondary database, you can create all the logins you want– but the log shipping secondary database is read only, so you can’t create a user in it.
Up through SQL Server 2012, here’s the two main options I’ve seen people use (there are a couple more in the comments):
- Grant access via stored procedures or views in another database. This will require enabling cross database ownership chaining, which can be a treacherous security road. And a lot of things could go wrong over time as tables are added, modified, and dropped.
- Create the login on the log shipping primary instance with the associated database user, and disable the login on the log shipping primary instance. If you’re using SQL authentication, you may have to use a special script to transfer the SID to the log shipping secondary to get it all to work.
Option 2 isn’t terrible, it’s just awkward to have a bunch of disabled logins. Someone can misunderstand and accidentally enable them or delete them, and then… oops.
Using CONNECT ANY DATABASE and SELECT ALL USER SECURABLES IN SQL SERVER 2014
As of SQL Server 2014, you have a new option because of a couple of new security features. You can do this:
CREATE SERVER ROLE readeverything;
GRANT CONNECT ANY DATABASE TO readeverything;
GRANT SELECT ALL USER SECURABLES TO readeverything;
When you add a member to this role, they can read anything. I tested and this worked for me against a logshipping secondary in standby mode just fine.
CONNECT ANY DATABASE is designed to allow access to connect to databases that exist now and databases that exist in the future, so this really IS permissions to read everything, including system databases– only use this if that’s appropriate.
It’s Complicated, but It’s Still Cool
If you’re clever and dedicated, you can work around these issues and use log shipping to provide read access to either applications or users who shouldn’t be reading from the live production copy of data.
Brent Says: Even with AlwaysOn AGs out, I think I’ve said the words “use log shipping” more in the last two years than I’ve said in the rest of my career. It’s useful, cheap, flexible, and nearly bulletproof.