DBA:M and the SQL Backup 6.0 pre-release

Rodney Landrum, DBA manager in Pensacola, Florida, puts the pain of DBA:M into context as we learn about how SQL Backup can evolve to keep pace. Take a look at the changes we've got planned to help time-pressed DBAs in the forthcoming pre-release of version 6.0, including a new compression level and network resilience.

DBA:M and the SQL Backup 6.0 pre-release

729-network_reliability-may09.gif

One of the new features in the forthcoming SQL Backup Pro v6.0 that all time-pressed and sleep-deprived DBAs will appreciate is its added resilience to network outages. Judging by the responses we received to a recent SQL Server Central poll, a shaky network is a common issue. Over half of the poll’s participants admitted that their networks were only ‘normally ok’ or suffered from ‘occasional outages’.

A couple of seconds of network outage may not sound like much at first, but if your backup files are being transferred across a network, whether they are being written, copied or restored, the consequence is usually time-consuming forensics for the DBA to work out how to resolve the interruption to the activity.

Rodney Landrum, a manager of database administration in Pensacola, Florida has to oversee the health and well-being of over 100 SQL Servers. He has given a name to the time in the morning at which a DBA typically staggers in to work, bleary eyed, having spent most of the previous night dealing with failed backups, disk space problems or any one of the numerous problems that can keep a DBA from his sleep. That name is DBA:M (pronounced D:BAM), and it’s usually around 9.30AM. He describes one cause of that D:BAM feeling:

“For various reasons, we do all backups to central share rather than local disk so we’re backing up across a network. We have a SQL agent job that sequentially backs up every database, one at a time, using Red Gate SQL Backup 5. Mainly it works well but we have one large database that fails regularly with the error message, “network name no longer exists”. The on-call DBA receives an alert to retry the backup manually, through the GUI, at which point it generally succeeds.

We’ve run integrity checks on the database, inspected transfer sizes and other properties of SQL Backup that allow the backup process to  use more or less bandwidth, or a greater or fewer number of threads, we’ve tweaked the backup schedule in case there was contention at that particular time, of which we were unaware. All of this was to no avail. It’s possible that the failure is caused by small, unpredictable network glitches, exacerbated by the fact that the database is large and the backup takes a long time.”

It is to prevent such sources of D:BAM that network resilience has been added to SQL Backup Pro version 6.0. Earlier versions of the tool will attempt to start a backup a configurable number of times but if a backup fails while in progress, SQL Backup will report a failure and not attempt to complete the operation. SQL Backup Pro v6.0 adds an extra level of resilience to temporary network outages. It will handle interruptions to data-transfer operations during a network outage by:

  • Waiting for a given number of seconds before retrying the operation.
  • Retrying the operation a given number of times. If the operation fails on the final retry, the activity is marked by SQL Backup Pro as having failed.

You will be able to specify both how long SQL Backup Pro waits before it retries an operation, and the number of times that SQL Backup Pro should retry the operation. Although SQL Backup Pro v6.0 is not yet available in its full version, a pre-release program is open for registration, where you can sign up to hear news and how to download a pre-release build to try out the network resilience for yourself. We have given default settings in the pre-release for the waiting and retrying stages, and we would value your feedback on whether the defaults are right for you: 30 seconds’ waiting time during a network outage before SQL Backup Pro retries the operation; then 10 attempts at retrying the operation before the process is marked as having failed. Note that if you have a very strict timeframe to complete an operation and you would prefer the operation to fail immediately following a network outage, you can disable network resilience by setting the number of retries SQL Backup Pro makes to ‘0’.


SQL Backup Pro v6.0
will provide network
resilience to log
backups, and it also
adds features to
improve the resilience
of log shipping;
so-called ‘self-healing’
log shipping.
                   “

By the same mechanism, SQL Backup Pro v6.0 will provide network resilience to log backups, and it also adds features to improve the resilience of log shipping; so-called ‘self-healing’ log shipping. We hope this will provide some relief for many DBAs, including Rodney Landrum. The agony of network outages when attempting log backups and log shipping is something he knows all too well:

“When log backups fail due to unexpected SAN or network issues, it can cause us a lot of pain. Our standard process is to take a full backup of a given database, restore it on the target system, and then apply logs to it. The logs coming from the source and those being applied to target have to maintain the Log Sequence Number coming from the source. If the log backup fails, then LSNs get skewed and you have to do a full restore on every single server.

Problems with our log shipping are rare, as the log backups are smaller sets. Nevertheless, the logs are shipped across a slow LAN link and we have experienced problems in the past. Each time, we’ve had to do a full backup to get log shipping working again. When you bear in mind that this could be a 300 GB remote database, which we then have to restore with correct recovery mode and apply logs, you can see that this can be a very time-consuming process.”

In the case of log shipping, the network resilience feature in SQL Backup Pro v6.0 extends the behaviour of the COPYTO keyword. If a network outage occurs while transaction log backup files are being copied (resulting in failure to copy the files), then SQL Backup Pro will keep a record of the affected files. Once the network outage has been resolved, SQL Backup Pro will transfer and reprocess each affected transaction log file, in the right order, bringing log shipping right up to date, without you having to intervene.

In addition to added network resilience, SQL Backup 6.0 incorporates several user-requested , as follows:

Improved GUI Performance

The timeline information for the databases registered under each SQL Server is now loaded only when the SQL Server in the left-hand pane is expanded. This should mean that the timeline information loads more quickly on opening the user interface.

As well as improved performance, we’ve also added a few new features that we hope will ease the task of managing your backup and restores operations, such as improved editing of scripted jobs, and support for purging remote files (either by age or quantity).

Additional Compression Level

When storage space is at a premium, SQL Backup Pro v6’s new compression level (level 4) might prove interesting. It uses an advanced compressions algorithm, and initial tests here at Red Gate indicate that the compression ratio of the 2.330 GB backup of our Website_20 database, at compression level 4, approaches 90%, using almost 40% less space compared to the compression achieved with level 1, as shown in the Compression Analyzer below:

729-compression_analyser_2.gif

Level 4 aims for maximum compression, so backups may take longer to complete than at compression levels 1, 2 or 3. However, it’s a useful option if saving storage space is a top priority. Put the new compression level to the test – we’d be interested to hear what results you achieve with your own databases.

Backup wizard

When setting up log shipping, you can now choose to skip initialization of the destination database. This offers you greater flexibility if you have already set up the target database with a full backup of the source database, because you can set SQL Backup Pro v6.0 to skip the step of performing a full seed backup prior to beginning log shipping.

Another new feature is the ‘Kill existing connections’ option for restores, which will prevent a restore from failing due to a user connecting to the database while the operation is in progress. If you forget to set this option, you can now step back into the wizard following an error, and adjust the settings without having to start over again. We’ve added this ‘retry’ functionality to all the SQL Backup wizards.

SQL Backup 6.0 Pre-release Program

If you’re keen to try out the SQL Backup pre-release, you can find out more on the SQL Backup pre-release pages, including further information about the features, changes and how to get your hands on the v6.0 pre-release.

Give it a trial run and let us know what you think. We’ll respond to all requests for of existing features, and will consider all “new feature” requests for inclusion in post-v6.0 releases.

Looking forward to hearing from you!