Transaction Log Backups Serial or Parallel?

Question

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance.

This question isn't so much about Ola's scripts as they are about a best practice. I realize the ultimate answer is "it depends on your company's requirements". But I am trying to seek the community's advice on how best to fulfill what I understand of our company's requirements.

I wish to set up transaction log backups for every 15 minutes. This way we hopefully lose no more than 15 minutes of data. Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I ask, because I have the feeling based on how I see Ola's script functioning that the backups are kicked off in serial. The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes). Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case. I would want the others to continue backing up.

So is it true that Ola's scripts execute in serial and also a failure stops successive backups?

And is it better to have a job for each database? or a single job that does all? My inclination is toward separate jobs, but I wish to understand what SQL Server DBA's in general tend to do.

Answer 1

Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel?

I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time.

What could be possible drawbacks with running in parallel

1. Suppose you have 50 databases and you schedule transaction log backup of all the databases and they all start running in parallel this is definitely going to utilize lot of I/O. And if disk on which it is backing up files happen to have other data files you would see slowness. I have seen backup becoming slow when a poor query requesting lot of I/O runs along with backup job.

2. Again suppose you have 50 database would it not be difficult to manage 50 jobs in SQL Server agent and what would be condition if you have 100-200 databases I would just not like it when you open the SQL Server agent and see lots of job, just keep it simple. I am sure the same case would be with you.

The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes).

Transaction log backups are mostly small and if you have a busy database producing lot of log records you might need to change the backup frequency. Mostly I have seen transaction log backup completing fine when frequency is 15 mins. I don't think should be matter of concern for you.

Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case

I would say just don't worry about it. Transaction log backups just cannot fail unless you made some mistake. The mistakes can be

1. The owner running the job is removed from AD

2. Someone changed the recovery model of database.

3. Insufficient disk space

Apart from above I have not seen any reason for transaction log backup to fail. Its very robust you can rely on it.

Answer 2

In general, always run your T-log backups in serial; many of my instances have a couple of dozen databases, and several that are very active, and the transaction log backups only take a few seconds total; up to half a minute or so when it's particularly busy.

Running backups in parallel only really would be beneficial if all of the following conditions are true:

• Your databases and log files are all on unique independent spindles (or are on solid state disks in any combination)

  • For just T-log backups, only the log files would need to fulfill this requirement.

• Your backup targets for each database are on separate spindles.

• You aren't using shared SAN HBA or iSCSI or other bandwidth between the SQL Server instance and the media.

• i.e. the IOPS from reading Database A and writing Backup A DO NOT use the same disks as reading Database B and writing Backup B.

If all of these are true, then it's possible that some degree of parallelism will decrease the amount of total calendar time. If all of these are not true, more than likely you'll cause one or more sets of disks to thrash, and your parallel backups will actually both take more calendar time than serial, but also may cause OS filesystem or storage level fragmentation, because you're writing Backup A and Backup B at the same time!

Don't worry about one backup failing and the rest succeeding - if any fail, you need to check everything anyway, and the only times I've seen backups fail are due to:

• Disk failure

• Hyperbac/Litespeed/third party compression software failure (if you have software between SQL and the disk that fails)

  • As a warning, the failure may take the form of a backup job that never finishes, so having some check for "jobs that run longer than expected" that sends alerts is valuable.

• Encryption product failure (if you have software between SQL and the disk that fails)

• Network failure (if the database files, or more likely the backup files, are on the network)

• Permissions

  • most common with brand new installs

  • or brand new backup locations

  • changing the SQL Server Service user (which is what needs the permissions for normal backups)

  • locking out the SQL Server service user because it's used by more than just one SQL Server instance

• Configuration errors

• Power failure

• OS crash

Most of which won't affect one and not the others unless the above conditions are also met.

Answer 3

Just to add, Ola designs his scripts where if one database backup fails to backup for whatever reason, the next one(s) are attempted. As earlier stated, you could have an alert set up to inform you of the job failure as the backup job would still fail, even if only one database backup failed out of all of the user databases - assuming you are backing up all databases (one job for all).