Selecting the SQL Server Backup Type to Use with NetVault: Backup – Part 2

Mike Daniels, Product Manager

In my previous blog we talked about the importance of defining a SQL Server backup strategy that included defining the types of backups that you will perform. To determine which backup types you use, you must first understand the difference between the various SQL Server VDI-based backup types. To recap, NetVault: Backup APM for SQL Server offers the following type of VDI-based backups:

• Full database
• Differential database
• Copy-only
• Incremental transaction log
• Tail-log
• Full file and filegroup
• Differential file and filegroup
• Partial database
• Differential partial database

In this blog, we will discuss Full File and Filegroup Backups, Differential File and Filegroup Backups, and Partial Database Backups.

Full File and Filegroup Backups are designed for SQL Server databases that contain multiple files or filegroups. These backups, like the Full Database Backups back up all the data in one or more Files or Filegroups. A complete set of Full File backups is equivalent to a Full Database backup. The benefit that the Full File Backup has over the Full Database Backup is that with the Full File Backup, you can recover an individual file without requiring the recovery of the entire database. This will speed up the recovery process for recovery scenarios where you only need to recover the damaged files and not the entire database.

While Full File and Filegroup Backups are advantageous for larger databases they also work well for databases that contain different update characteristics. For example if you have a large database where some of the database files are modified frequently, some are modified less often, and maybe even a read only file, using a standard backup strategy of Full Database and Differential Database Backups which backups up the entire database including the files which are rarely modified or even marked read-only. Having the ability to choose exactly which files and/or filegroups to backup and when to backup those files up is a more efficient solution. With these advantages does come additional complexity in that you must understand that a media failure can make a complete database unrecoverable if the damaged file(s) can not be recovered because it wasn’t backed up.

Calculating NetVault: SmartDisk Licensed Capacity

Dawn renee Campbell

You have chosen which data you want to deduplicate with NetVault: SmartDisk (NVSD), and now you are tasked with calculating how much capacity to license.

NVSD is licensed based on the logical capacity (or total size) before deduplication of ALL backups that are stored in NVSD.

NVSD provides the ability to deploy multiple instances that can be targeted for backup from multiple NVBU Servers. When calculating the licensed capacity for NVSD, the capacity across all deployed NVSD instances must be included. For example, if two NVBU Servers are targeting backups to one or more NVSD instances, the capacity of the backups from both NVBU Servers must be included in the calculations.

Understand that NVSD licensed capacity is NOT based upon any of the following:

• Actual size of the storage pool, staging store, or chunk store
• Actual size of the backups after deduplication

Calculating NVSD capacity requires the collection of the following values for each NVBU Server that will target backups to NVSD. When inserting these values into the calculations, be sure to include the sum of the values across ALL the NVBU Servers that will target backups to NVSD. For example, if NVBU Server 1’s size of weekly backups is 40GB and NVBU Server 2’s is 60GB, use 100GB as the size of the weekly full backups.

If separate NVSD instances for non-deduplicated and deduplicated data will be used, perform the calculations below separately for the backups that will not be deduplicated versus the backups that will be deduplicated. This will enable you to know exactly how much NVSD non-deduplicated capacity needs to be licensed and how much capacity needs to be licensed for the NVSD deduplication option.

If a single NVSD instance will be used for both non-deduplicated and deduplicated data, include both the values for non-deduplicated and deduplicated data in the single calculation.

Size of Weekly Full Backups

Size of all the full backups that will be targeted to NVSD. For example, if  SQL Server, Exchange, Oracle and File System backups will be targeted for NVSD, calculate the total size of ALL the full backups for SQL Server, Exchange, Oracle and File System.

Weekly Full Backup Growth Rate

Average weekly growth rate of the full backups, which are included in the size of weekly full backup calculation. Weekly full backup growth rate is a critical factor in accurately determining NVSD sizing. For example, the average of all of the full backups is growing 10% each week. Another convenient method to calculate this is to base it on annual data growth.  For example, an annual growth rate of 100%, or an annual doubling of data, would represent a weekly growth rate of 1.333%.

Weekly Full Backup Retention Period in Weeks

Number of weeks that full backups will be retained in NVSD. Restores are faster when being performed from disk-based media than when being performed from tape-based media. Increasing the amount of protected data that is available on disk-based media increases the number of restore scenarios that can be performed from disk-based media.  Requiring tapes to be located, retrieved and loaded in order to perform a restore slows down the restore speed and increases downtime.

Additionally when performing deduplication, the longer the data is retained in NVSD, the better the deduplication ratios will be because more duplicate chunks are found, thereby enabling the ability to pack more data into the same storage footprint. This enables even more protected data to be available via disk-based media.

To obtain the most ideal deduplication ratios, we recommend a retention period of 12 weeks or more.

Size of Daily Backups

Size of all the daily backups that will be targeted to NVSD.  For example, if SQL Server, Exchange, Oracle and File System backups will be targeted for NVSD, calculate the total size of ALL the daily backups for SQL Server, Exchange, Oracle and File System.

Daily backups are interim backups between the weekly full backups. These are anticipated to be incremental or differential backups, and as such, will generally be much smaller than the weekly full backups.

Daily Backup Growth Rate

Average daily growth rate of the daily backups that are included in the size of daily backups.  For example, the average of all the daily backups is growing 1% each week.

Number of Daily Backups between Full Backups

Number of daily backups that are performed between full backups. For example, if full backups are performed on Sunday and daily backups are performed each day, Monday thru Saturday, the number of daily backups would be six.

Daily Backup Retention Period in Weeks

Number of weeks that daily backups will be retained in NVSD. Daily Backups provide the ability to perform fixed-point-in-time restores, thereby reducing the recovery time objective. Daily backups can be retained for the same period of time as full backups, which enables fixed-point-in-time restores for the entire weekly full backup retention period.  Fixed-point-in-time restores are typically performed using backups taken within the last four weeks. As such, it is possible to retain weekly full backups for one retention period and daily backups for a shorter retention period. This would allow you access to daily backups for a set period of time, such as four weeks, while providing you with access to full backups to a longer period of time, such as 12 weeks.

NVSD Sizing Calculator

With the NetVault: SmartDisk Sizing Calculator, you simply enter all the above values.  It will calculate not only the amount of NVSD capacity to license but also tell you the number of NVSD instances and the amount of memory for each NVSD instance. The NVSD Sizing Calculator can be obtained from your BakBone representative.

In my next NVSD blog, we will learn how to calculate how much physical capacity is required.

Selecting the SQL Server Backup Type to use with NetVault: Backup – Part 1

Mike Daniels, Product Manager

Before deploying the NetVault: Backup APM for SQL Server, you must first define your SQL Server backup strategy. The purpose of creating a SQL Server backup strategy is to be able to recover a database that has been damaged or become corrupted. When creating a backup strategy, you define the type and frequency of backups to meet the data protection requirements of the database or application.

First, it is important to understand the VDI-based backup types that can be performed with SQL Server in order to give you an idea of what each one does. The NetVault: Backup APM for SQL Server offers several types of backups including

• Full database
• Differential database
• Copy-only
• Incremental transaction log
• Tail-log
• Full file and filegroup
• Differential file and filegroup
• Partial database
• Differential partial database

With full database backups, you are backing up the entire database. This type of backup also includes part of the transaction log so that the full database backup can be completely recovered. A full database backup represents the database at the time that the backup was completed. These backups are typically easy to use because they contain all the data in the database. For smaller databases that can be backed up quickly, this is the recommended method for backups due to the simplicity of the recovery process. As databases become larger and the full database backups take longer, you probably want to consider supplementing full database backups with differential database backups.

Differential database backups only record the data that has changed since the last full database backup. These backups are smaller and faster than a full database backup, which saves time but does start to increase the complexity of the backup scheme. For large databases, it is Microsoft’s best practice to follow full database backups with a series of differential database backups. Since each differential will get larger than the previous, it is also recommended that you schedule a new full database backup on an appropriate schedule.      There are special situations where you might want to have a database or a backup of the database, but do not want to impact the regular backup rotation or scheme. A typical SQL Server backup will change the database by updating the differential bitmap. This change affects how future backups and restores are done. By using copy-only backup, you can create a backup that is independent of the regular SQL Server backup sequence. This means that the differential bitmaps are not changed and your regular backup sequence will not be affected. Typically these backups are ideal for circumstances where you require the database, but do not want to affect you production system, such as creating a test environment, or for standby databases.

Transaction logs contain the data for all of the actual transaction that have occurred on the database. In SQL Server, taking routine transaction log backups are essential to be able to fully recover the data. With transaction log backups, you have the ability to recover a database to a specific point in time or to the point of a database failure. With incremental transaction log backups, you capture all of the transaction logs including transaction logs that have been generated since the last full database/file or filegroup, differential database/file or filegroup, or incremental transaction kog backup. These backups are also the means by with the SQL Server logs are typically truncated in order to reduce the amount of space required by the SQL Server logs.

Tail-log backups are backups that capture the logs that have not been backed up yet. These are typically the last backup restored in a SQL Server recovery sequence because it completes the log chain. With SQL Server 2005 and 2008, it is required that you run a tail-log backup prior to restoring any database that is currently attached to the SQL Server Instance. Because of this, the tail-log backup is typically the first step in any recovery process.

While restores are the primary consideration when creating a backup strategy, you have to understand what the different types of backups are actually doing so that you can make a conscious and informed decision.

In my next Blog we will continue the discussion on different types of SQL Server backups and their purpose.