Last week we covered five reasons why log shipping should be used.  I got a great question that I thought should be answered here in a short blog post.  The question is “how do you configure transactional log shipping to include compression when taking the transactional log backups?”

The Missing Question

First, a better question is “should we be compressing our SQL Server Backups?” In a second we will address log shipping, but first I wanted to just focus on compressing backups. The answer is “it depends” (typical DBA response).  There is CPU overhead for utilizing compression. We will not focus too much on this here as you can enable resource governor to limit CPU usage for your backups.

Today, we will focus on capacity and misleading storage saving results due to enabling backup compression.  Looking at a traditional DBA role, you might have exposure to view your server, drives, and the size of the backups. Therefore, taking a compressed backup leads you to likely see less storage being used for the backup file compared to other backups of the same database.  This is typically the goal for backup compression to reduce storage space consumed by backups.  

Another question you should be asking yourself is, “What storage system is being used for your SQL Server backups?” For example, storage access networks (SAN) might have its own compression and native SQL Server backup compression might hurt the impact of the SAN’s compression, which could cause more RAW storage to be used. Therefore, there really isn’t a silver bullet to always use SQL Server backup compression in every environment.  You need to understand if there is any compression built into the storage used for your backups, and understand how backup compression impacts the storage system before deciding to utilize backup compression.

Compress Log Shipping Backup

Okay, now that we got our disclaimer common real-world mistake out of the way.  Here is how you would enable backup compression for transactional log shipping.

You can access Log Shipping Settings from Database Properties

Log Shipping Backup Settings on Primary

Log Shipping Backup Settings on Primary

Here are Log Shipping backup compression options.

Here are Log Shipping backup compression options.

Enabling Compression with T-SQL Backups

Enabling backup compression with T-SQL is as simple as just adding the compression option.  The following is a common example of backing up a database with compression.

[code language=”sql”] BACKUP DATABASE [LSDemo] TO DISK = N’c:\somewhere\LSDemo.bak’ WITH COMPRESSION [/code]

Enable Compression by Default

If all of your backups for an instance of SQL Server go to storage where utilizing native SQL Server compression provides improvement, then you can consider enabling compression by default. Therefore, all backups taken on this instance of SQL Server would then use backup compression by default unless another option was given during the T-SQL execution of the backup command.

[code lang=”sql”]
EXEC sys.sp_configure N’backup compression default’, N’1′
GO
RECONFIGURE WITH OVERRIDE
GO
[/code]

SQL Server comes with several options to keep your data highly available. Today we are going to cover five different reasons why Transactional Log Shipping should be one of your best tools in your SQL Server DBA Toolbelt. 

Validates Transactional Log Backups

Your last line of defense in any SQL Server Disaster Plan is recovering backups.  To be able to recover to a point of failure you have to leverage transactional log backups while using the full or bulk-logged recovery model.  Every transactional log backup has to be restored one by one in-sync after your last full and differential backups to recover to the point of failure.  Wouldn’t it be nice if you could automate restoring every log backup so you knew you had good backups? Transactional log shipping is the easiest way to complete this task. It also gives you a copy you can easily bring online without having to restore all of your backups.

Secondary Copy of Transactional Log Backups

We already mentioned that the last line of defense in any disaster recovery plan is restoring backups.  Wouldn’t it be nice if you had copies of your transactional log backups on multiple servers? Maybe even multiple data centers?  Well, transactional log shipping also helps automate this as well. The second step in transactional log shipping is copying your transactional log backups. Now you just have to make sure your full and differential backups are copied.

Recover Data Quickly for Very Large Tables

Have you ever forgotten to add or highlight the where condition while you are running a delete or update statement? If you haven’t, do not be surprised if it happens in the future by you or someone else in your company.  This can be an extremely painful recovery process if it happens against tables with terabytes of data.  With all other SQL Server High Availability features like mirroring, availability groups, and failover cluster instances, the data changed almost instantly so you couldn’t use this solution to recover.  You now have bad data that is highly available.

With transactional log shipping, you can delay the restore time so if you discover the changed data by mistake you could disable the SQL Agent Jobs and pull the missing data in parallel of implementing your plan to get the rest of the data from the last restore.  This can bring most of your data online quickly while you are restoring a very large backup.  The best part is you don’t have to recover your log shipping copy.  You can put it in standby mode and read data while it’s still in a state where you can continue to restore transactional backups (more on this below).

Poor Person’s Reporting Copy

Would it be nice to have a readable copy of your database that you could use for reporting? Even if you have Web Edition. Transactional log shipping gives you this ability even though you might not know that it exists.  When you are restoring the transactional log backups with transactional log shipping you have two options.  The transactional log backups can be restored in standby mode which allows you to read data in-between transactional log backup restores. You can also use the default, which is no recovery.  This doesn’t allow any access to read the backups. This is just like your mirror copy in database mirroring (without snapshot copy on enterprise edition).

There is one big catch with using standby as your recovery option.  You have to specify if you want to delay restores if an active session exists or pick to terminate the connections to force the transactional backup restore.

Improve Downtime for Migrations and Upgrades

When you migrate a database or upgrade using the side by side option you have to have another copy of the database.  Ideally, the source and destination would be identical at the point of implementing the migration.  With very large databases this could require a very large downtime so backup and restore, or detach and attach, are good options unless you have a very large maintenance window. Seeding the data so that only a small amount of changed data needs to be synced is ideal.  Log shipping is an excellent way to do this especially if you have multiple copies that need to be synced for implementing availability groups.  Here is how we made a 60TB database highly available for an Availability Group implementation with zero downtime. Example

My New Log Shipping Feature Request 

I know, you are already thinking that Log Shipping has been around forever and it doesn’t need any new features, but I got one.  In SQL Server 2016 Direct Seeding was added to streamline how the initial sync occurs for Availability Groups. I would like to see this same feature extended for log shipping so we have an easy automated option to perform the initial sync for transactional log shipping.

Curious about the basics of SQL Server High Availability?  Please join our founder and MVP, John Sterrett, where he will be presenting SQL Server High Availability 101 on Tuesday, July 18th, 2017 at the North Austin SQL Server User Group.  

[Update – Sept 5th]

You can now watch the full presentation online.  You can see also see some reference links below as well. If you have any questions feel free to contact us!

Links:

High Availability Reference Material

Abstract:

Here is a quick synopsis of what will be discussed:

Have you ever wondered how companies keep their databases online through unplanned disasters? Have you wondered which high availability features might be best for our shop? In this session, we will go over a quick look at log shipping, database mirroring, transactional replication, failover cluster instances, and availability groups. John will identify pros and cons for each feature and share some tips from the field.

You are also invited to meet afterward for networking and cocktails!  It is a great opportunity to learn, connect with fellow SQL Server professionals, and build solid relationships for your future.  We cannot wait to see you there!