SQL Backup Pro 6.5

Making the most of SQL Backup Pro

Introduction

If time is tight, this guide is an ideal way for you to find out how you can make the most of SQL Backup Pro. It helps you to quickly identify key areas where you can make improvements to the performance of SQL Backup Pro in your environment.

The main areas highlighted in this guide have been selected because they are easy to experiment with and control. They also have the potential to improve your workflow and even save you time where you might not have anticipated.

This guide contains links to the online Support Center for SQL Backup Pro, where you can find a wealth of material and guidance to help you troubleshoot any problems you may encounter. If you have questions at any point, please don't hesitate to contact us. A dedicated team is always happy to hear from you.

Guide contents

Improving the performance of backups and restores

• Fine-tuning compression and thread settings
• Using the Compression Analyzer
• Optimizing network resilience settings
• Using T-SQL scripts
• Using the SQL Backup Pro Command Line or an extended stored procedure
• Splitting the backup into multiple files

Optimizing your workflow with restores to save time

• Automatic information retrieval and intelligent restore
• Restoring a transaction log to a point in time
• Checking for orphaned users
• Killing existing connections

Making the most of the interactive timeline

• Setting different time zones and locations
• Editing scheduled jobs from the timeline
• Viewing activity properties from the timeline

Improving the performance of backups and restores

Fine-tuning compression and thread settings

There are a couple of small tweaks in SQL Backup Pro that will allow you to improve the performance of your backups and restores considerably. The key features that help you achieve a better performance are the different compression levels, multiple threads and splitting your backups into multiple files.

Let's look at choosing the right compression level for your database first. SQL Backup Pro offers you four high-performance compression levels. The fourth level compresses your backup the most, and, in general, the smaller the backup, the quicker it is to restore. The flip side of the coin is that highly compressed backups take longer to create. For detailed information about the individual compression levels that SQL Backup Pro offers, including which algorithms are used and an estimate of the differences in CPU cycle usage between each compression level, see Compression levels in the SQL Backup Pro Support Center.

Using the Compression Analyzer

If you are unsure which compression level to use, SQL Backup Pro's Compression Analyzer helps you determine which compression level is best suited for your database.

The Compression Analyzer works by sampling the database data for analysis. It produces an estimate of the compression ratio that would be achieved with each compression level if a full backup of that database was made.

In this example, level 4 would produce the highest compression. Level 1 results in the fastest compression, but also creates larger backup files. Use the Compression Analyzer to experiment with different compression levels to find the right balance between faster or smaller backups.

Optimizing network resilience settings

Network outages are a common problem when transferring data. They can interrupt backup operations and cause job failure. Network resilience in SQL Backup Pro is designed to minimize the disruption caused by network outages. If a backup job is interrupted by a transient network outage during the writing or copying of a backup across a network, network resilience means that SQL Backup Pro will resume the operation once the network connection is re-established. If the network connection during a backup operation is lost, SQL Backup Pro is set by default to retry connecting to the network after 30 seconds to complete the operation. If the network connection is still down, SQL Backup Pro will, by default, reattempt the operation up to 10 times. The default settings are appropriate for temporary network outages, but you can adjust the settings to suit the needs of your environment. For example, we recommend you experiment with different settings if your backup operation must complete within a short timeframe. In this case, you could try reducing the time SQL Backup Pro waits to re-attempt the operation and also the number of retry attempts. If the network connection remains down after the time SQL Backup Pro is set to wait and the number of retries have been exhausted, SQL Server will mark the operation as having failed. If you want to give a backup operation more time to complete successfully, you can increase one or both settings. To disable retries completely, specify "Retry up to 0 times".

Network resilience settings can also be optimized for log shipping operations. You can specify how long SQL Backup Pro should wait and how many times it should retry transferring an individual transaction log if a network outage occurs during a log shipping operation. For log shipping, an extra level of resilience is provided in a way that makes the process self-healing. If a prolonged network outage occurs while transaction log backup files are being copied, and the standard number of retries is exceeded, SQL Backup Pro v6 keeps a record of the files that failed to copy, and defers the log file transfer for a set period (two minutes by default). The transfer becomes a pending task in the Log Copy Queue tab, and SQL Backup Pro will keep attempting to complete the transfer at regular intervals for a maximum of 24 hours before marking it as failed. Once the network outage is resolved, SQL Backup Pro v6 will transfer all the affected transaction log files, starting with the earliest. Log shipping is then brought right up-to-date, without the need for your intervention.

Using T-SQL scripts

You can download a T-SQL script from the Red Gate SQL Backup Pro Evaluation Center that enables you to compare performance when creating backups with both SQL Backup Pro and native SQL Server. The script collates the necessary statistics and returns them in the form of a report at the end of the test.

The script performs up to 15 backups, depending on the SQL Server version being used. It first performs native backups for a benchmark, followed by eight SQL Backup tasks (from the non-compressed level 0, up to the highest compression level, 4). It can optionally perform up to three user-defined tasks (for example, specially-configured backup tasks). Read through the script step by step prior to running the tests, so you can assess what will happen and make any necessary tweaks. Please also take care not to run the tests in a production environment!

In addition, an article by Dan McClain in the SQL Backup Pro Evaluation Center contains a script that can be used to look at performance statistics once a set of backups with different compression ratios has been created.

Using the SQL Backup Pro Command Line or extended stored procedure

You can use the SQL Backup Pro Command Line or extended stored procedure to optimize your backups step-by-step with different compression levels and different numbers of threads (up to a maximum of 32).

The backup process can be separated into three distinct stages, and each stage gives an opportunity for you to optimize performance through compression and the use of multiple threads:

• Stage 1: SQL Server backup engine reads the data and log files. This incurs disk Input/Output (I/O) reads.
• Stage 2: SQL Backup Pro compresses, and optionally encrypts, the data. This uses CPU cycles.
• Stage 3: SQL Backup Pro writes the resulting compressed data files to disk. This incurs disk I/O writes.

You can run a series of tests using the Command Line or extended stored procedure to perfect each of these stages in turn. For more information about how to run these tests, see Optimizing backup speed in the SQL Backup Pro Support Center.

Splitting the backup into multiple files

SQL Backup Pro enables you to split a backup into multiple files. This can speed up the backup process if backing up to a single file does not fully use the I/O capacity of your disks. Note that all backup files must be available when you restore the backup.

You can split the backup into multiple files, either when using SQL Backup Pro's Back Up wizard, or by using the relevant Command Line parameters or extended stored procedure. For more information, see Scheduling backups: file settings and The BACKUP command in the SQL Backup Pro Support Center.

Optimizing your workflow with restores to save time

Even a few minutes saved here and there each day can easily add up each week. Over a year, that's potential for a lot of time saved for you to put to other uses. There are several features in SQL Backup Pro that remove the complexity from different areas of backup-related tasks, saving you both time and effort. Take a look at the different areas highlighted below to see which are most useful for you.

Automatic information retrieval and intelligent restore

When you are restoring a backup, SQL Backup Pro lists only those files that are relevant for the restore operation. When working with differential or transaction log backups, SQL Backup Pro's intelligent restore finds all files needed for a successful restore.

For example, if you select a transaction log backup, SQL Backup Pro retrieves all transaction log backups since the corresponding full backup, and the full backup itself.

SQL Backup will also check during the restore that the backup file can be restored. If there are any problems, a red cross will appear next to the file details.

Restoring transaction logs to a point in time

If you are restoring transaction logs, you can quickly and easily specify the exact point in time to restore to.

SQL Backup Pro automatically populates a list with appropriate transaction logs. When you select a transaction log, a slider in step 3 of the Restore wizard shows the exact time period covered. Simply move the slider to select the exact time to which you want to restore the transactions.

 

Checking for orphaned users

In step three of the Restore wizard, you can request a check for orphaned users: If you are restoring a database onto a different SQL Server instance, it is possible that some of the restored database user accounts will not have an associated server login on that instance. These database user accounts are known as orphaned users and will be unable to access the database unless you create their associated logins. If you select Check for orphaned users and list in log file in step three of the Restore wizard, SQL Backup Pro checks for orphaned users automatically. If any orphaned users are found, a warning is included in the SQL Backup Pro log file along with a list of the orphaned users, so you know as soon as possible which logins you need to create. This avoids a tedious manual search and makes for a smoother restore process.

Killing existing connections

If you decide to overwrite an existing database when you restore a backup, and there are existing connections to the destination database, the restore process will fail. This usually means you have to end the connections manually before reattempting the job. SQL Backup Pro prevents this problem by giving you the option to kill existing connections automatically. This means the restore process can be completed successfully without further intervention and a lengthy manual process can be avoided. If you would prefer to kill specific connections one at a time, you can uncheck this option before continuing.

Making the most of the interactive timeline

These tips assume you are already familiar with the color-coding in the timeline, the use of differently sized color blocks to indicate backup, restore and transaction log activities, and ways to select different points on the timeline. The following tips highlight ways for you to make the most of the timeline's interactivity.

Setting different time zones and locations

SQL Backup sets up an initial location for you to register your SQL Server instances, using the time set on the client computer. The time zone is reflected in the tab at the top of the timeline.

The use of time zones and multiple tabs for grouping servers can be particularly useful if you need to manage backup activities across a large number of SQL Servers or instances. If you have additional SQL Servers or instances in a different location on a different time zone, you can register those SQL Servers under a different tab by simply clicking a blank tab at the top of the timeline and selecting the appropriate settings.

Editing scheduled jobs from the timeline

The interactive timeline can be a quick and simple way for you to view past, current and future backup activities at a glance.

It is easy to spot a clash in future backup activities, as these are indicated by a red block or a group of red blocks on the timeline (depending on the number of clashing activities). You can resolve the clash by selecting the activity directly from the timeline and editing the setting. This is much quicker than scrolling through a jobs list to find the relevant activity or activities. To see a list of clashing activities, simply right-click a red block indicating a future activity clash. Clicking any activity in the list then highlights the relevant activity in the Jobs tab. You can right-click an activity in the list to edit the job. For more information about editing a scheduled job, see The Jobs tab in the SQL Backup Pro Support Center.

Viewing activity properties from the timeline

Clicking on an activity in the timeline that is shown as completed (to the left of the orange line marking the current server time) selects the corresponding entry in the Activity History, so you can view further details about the activity.

Double-clicking on a past activity in the timeline enables you to view its properties, such as file name, file size and the compressed size after backup, in addition to any associated errors or warnings.

Clicking on an activity scheduled for the future (to the right of the orange line marking the current server time) selects it in the Jobs tab. A number of activities on the timeline can correspond to a single job in the Jobs tab if you have scheduled a series of activities as a single job.

Double-clicking on a future activity also enables you to edit the job in the Edit Backup Jobs wizard. For more information, see Time Line in the SQL Backup Pro Support Center .