SQL Server – LogShipping Frequency for Backup, Copy, and Restore

Now that SQL Server 2012 has shipped, don't forget about AlwaysOn, which allows explicitly for reads scale out.

Mirroring works for what you describe, but the orchestration of database snapshots for reporting can get hairy (the mirror database is unreadable, you must report from database snapshots created periodically).

I'm not a fan of transactional replication, but it can be used for your scenarios and others more knowledgeable than me have came out In Defense of Transactional Replication.

Both of AlwaysOn and Transactional Replication offer what you want (scale out reads w/o disruption) and they both achieve near-real-time data latency. They differ though on price point: AlwaysOn is an SQL Server enterprise only feature and requires Windows Clustering which in turn requires Windows enterprise licensing, ie. it costs. Plus it requires an upgrade to SQL Server 2012. Transactional Replication on the other hand costs basically nothing in addition to what you already have (except if your whole setup is on Express Edition instances, which I doubt) but it comes at a cost because it will create friction with your development/deployment cycle, which now has to consider the replication impact (must add new articles to the publications, certain schema change operations require careful planning etc).

And for some out-of-the-box thinking consider how some have resorted to Using Service Broker instead of Replication.

If you don't include the transaction log, then it's not really log shipping, is it?

Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at that same point in time. I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups).

So if your intention is to do this backup / restore once a day, and you don't need to keep the copy up to date throughout the day, just do a simple backup and restore, and stop calling it log shipping.

If you want the copy to stay relatively up to date throughout the day, then yes, it will be mandatory to take periodic log backups on the primary server and restore them on the secondary server. Note that you have to weigh what's more important: keeping a copy of the data up to date, or letting users query stale data uninterrupted. If you restore a log to the secondary server every 15 minutes, you have to kick all the read-only users out every 15 minutes, because you need exclusive access to the database in order to restore the log.

You can read more information about log shipping here:

http://msdn.microsoft.com/en-us/library/ms187103(v=sql.90).aspx