The IBM Tivoli Data Protection for Microsoft SQL also known as TDP SQL can be used to take two kinds of SQL database backups - Legacy Backups & Volume Shadow copy Services (VSS) Backup. In this post we will see what is TDP SQL VSS backup and its advantages. VSS backup uses Microsoft Volume Shadow Copy Service technology to produce an online snapshot (point-in-time consistent copy) of SQL data.
TDP SQL VSS Backup Introduction
VSS is a set of Microsoft COM APIs that implement a storage management architecture that enables volume-level snapshots to be performed while the applications that contain data on those volumes remain online and continue to write. This architecture can be used for backing up your applications by creating point-in-time snapshots of your data. VSS provides the backup infrastructure as well as the mechanism for creating consistent point-in-time copies of data known as shadow copies. VSS produces consistent shadow copies by coordinating with business applications, file-system services, backup applications, fast-recovery solutions, and storage hardware. VSS Backups can be stored on Tivoli Storage Manager server storage or local VSS shadow volumes. Both of these storage destinations require that sufficient space be available for the snapshot. VSS Backups stored locally on VSS shadow volumes are directly accessible by the SQL system as long as sufficient space is available for the snapshot.
Requirements for TDP SQL VSS Backups
For local VSS Backups you must have a licensed version of IBM Tivoli Storage FlashCopy Manager or IBM Tivoli Storage Manager for Copy Services installed on your system.
When performing VSS Backups and moving data to Tivoli Storage Manager server storage, sufficient space on local snapshot volumes is still temporarily required to hold the snapshot. For SQL data backed up to Tivoli Storage Manager server storage, the SQL data on the snapshot volume is sent to the Tivoli Storage Manager server. After the data transfer to the server is complete, the snapshot volume is made available for reuse. If you are storing VSS Backups locally and the maximum number of local backup versions to be maintained (as specified by the Tivoli Storage Manager policy) is reached, the oldest backup version is expired to create the snapshot for the backup to Tivoli Storage Manager server storage.
Also Read: Integrating TDPO with RMAN to configure Oracle DB backups
Also Read: Integrating TDPO with RMAN to configure Oracle DB backups
The Tivoli Storage Manager backup-archive client serves as the VSS requestor that communicates with VSS to access the SQL data to create shadow copies of SQL databases. Data Protection for SQL serves as a front end for VSS Backup operations. Because of the role that the backup-archive client performs as the VSS requestor, features such as LAN-free backup, client-side deduplication, data encryption, and data compression require that options related to these features be specified in the backup-archive client options file for VSS operations. With the IBM Tivoli Storage Manager for Copy Services Microsoft SQL VSS Integration Module, you can manage local persistent snapshots and perform offloaded VSS backups.
Volume Shadow copy Services Overview
The VSS service manages and directs three VSS software applications that are used during VSS operations. These are the VSS software applications that the VSS Service manages and directs
The VSS writer for the Microsoft SQL Server is the SqlServerWriter. The SqlServerWriter is provided by the SQL Server VSS Writer service.
VSS requestor
This application initiates a snapshot operation. The application sends a command to the VSS service to create a shadow copy of a specified volume. The VSS requestor is the Tivoli Storage Manager backup-archive client.
This application produces the shadow copy and also manages the volumes where the SQL data resides. A provider can be a system provider (such as the one included with the Microsoft Windows operating system), a software provider, or a hardware provider (such as one that is included with a storage system). VSS hardware providers require installation and configuration, including the installation of all required fix packages.
VSS system provider overview
A VSS system provider assists with creating and maintaining copies on local shadow volumes. The VSS system provider refers to the default VSS provider that is available with Windows Server. If you are using the Windows VSS system provider, no configuration is required. However, you can make some configuration changes using the VSSADMIN commands.
A software or hardware provider acts as an interface during VSS processing at the software or hardware level, respectively. If you use a software or hardware provider, consider the following requirements when planning for VSS Backups
- Place database files for each database or group of databases that are to be backed up and restored together as a unit on a separate and dedicated logical volume.
- Place logs for each database on a separate logical volume.
- Do not place non-SQL data on storage volumes that are dedicated to SQL data.
- When using hardware snapshot providers, do not share storage group LUNs with other databases or applications.
- Make sure to read and follow specific installation and configuration instructions in the documentation provided by your VSS provider vendor.
- If you use XIV, you must install and configure IBM XIV Provider for Microsoft Windows Volume Shadow Copy Service (xProv) Version 2.3.0 or later.
- If a hardware provider is used, configure the disks that store SQL data and log files as basic disks.
Features of TDP SQL VSS Backup
Some VSS Backup characteristics are different from Legacy backup characteristics. Examples of these differences are the backup characteristics for types supported, the backup granularity, and the backup storage location options. The following characteristics are true of VSS Backups
- Full backups are supported. The copy-only full backup type is supported for VSS and Legacy backups. Log, differential, file, group, and set backup types are not supported. Legacy differential and Legacy log backups can be applied after a full VSS Backup is restored.
- Backup granularity is at the database level only.
- Backups are managed through Tivoli Storage Manager server policy.
- Backups can be stored on local shadow volumes, Tivoli Storage Manager server storage, or both locations.
- Different policy settings can be defined for each storage location, backup method, and backup type (FULL or COPY).
- Backups to Tivoli Storage Manager server storage can be offloaded to an alternate machine as resource relief for production servers.
- Backups can be run in a Microsoft Windows Failover Clustering or Veritas Cluster Server (VCS) environments.
VSS Backup Planning Requirements
Plan a VSS Backup strategy to optimize your backup operations performance and avoid potential problems. Consider the following requirements when planning for VSS Backups
- When running VSS operations, make sure you have at least 200 MB of free disk space on your Windows System Drive. This space is used to hold the metadata files for Data Protection for SQL.
- Continue to schedule and perform Legacy backups in your strategy.
- Make sure you have a well-defined and tested recovery plan that meets your service level objectives.
- Use basic disks.
- If you plan to keep some VSS snapshot backups on local shadow volumes only, make sure to consider the VSS provider-specific implementation and configuration options when setting up your strategy. For example, if your VSS hardware provider supports a full-copy snapshot versus a copy-on-write (COW) snapshot mechanism, be aware that full-copy type implementations have greater disk storage requirements but are less risky because they do not rely on the original volume to restore the data. COW implementations require much less disk storage but rely completely on the original volume to perform a restore. Since these implementations are entirely controlled by the VSS provider and not Data Protection for SQL, make sure to consult your VSS provider documentation for a complete understanding of your VSS implementation.
- If you perform parallel VSS Backups, stagger the start of the backups by at least ten minutes. This interval ensures the snapshot operations do not overlap. If you do not stagger the snapshots, errors can occur. In addition, configure the parallel instance backups so they do not take snapshots of the same volumes. Make sure that parallel backups do not snapshot the same LUN.
- Do not place multiple volumes on the same LUN. Microsoft recommends that you configure a single volume, single partition, and single LUN as 1 to 1 to 1.
- Do not set the ASNODENAME option in the dsm.opt file when using Data Protection for SQL. Setting ASNODENAME can cause VSS Backups and VSS Restores to fail.
- Specific database, log, file, and LUN settings are required for IBM System Storage. The DS8000®, SAN Volume Controller, Storwize V7000, and XIV storage subsystems require special settings when planning for VSS Backups
Offloaded VSS Backups
An offloaded backup uses another machine to move the data to the Tivoli Storage Manager server. This type of backup shifts the backup load from the production machine to another machine. An offloaded VSS Backup requires a VSS hardware provider that supports transportable shadow copy volumes is installed on the production and secondary machines.
Offloaded VSS Backups require a Tivoli Storage FlashCopy Manager license.
Best Practices for Configuring TDP SQL VSS Backup and Restore
Choose a suitable backup and restore strategy based on your predefined service level requirements. Consider these general factors as they can affect strategy
- When creating policy for VSS Backups to be stored on local volumes, use the VEREXISTS and VERDELETED management class settings. That is because the number of versions you keep should be based on the numbers of volumes your subsystem has available for shadow copies. The general rule is that if you want to keep N local backups, you need to specify VEREXISTS=N+1. You need to specify N+1 because VSS requires pre-allocated space for taking a snapshot.
- Many service level agreements base the retention of application server backups stored on the Tivoli Storage Manager server according to specific time periods, not versioning. If that is true in your environment, use the RETEXTRA and RETONLY management class settings instead of VEREXISTS and VERDELETED when setting policy for the backups stored on the Tivoli Storage Manager server.
- Use different management classes if each storage group has its own retention requirements.
- Legacy backup management policy remains the same as before. However, adding VSS Backups to your backup strategy might influence you to re-evaluate the frequency and retention of the legacy backups in order to best exploit both legacy and VSS Backup capabilities to meet your service level requirements.
- Make sure to provision enough local storage on your disk subsystem to meet the management class settings.
- Use Tivoli Storage Manager policy management for the different VSS Backup types. You can assign separate policy management classes for the different VSS Backup destination types and different storage groups or databases. Set these to meet your short-term and long term recovery objectives.
- Automated (scheduled) backups: You can use the Tivoli Storage Manager scheduler to back up your application data automatically. Define different client schedules for VSS and legacy backups. Make sure backup schedules do not overlap.
Legacy backup versus VSS Backup
Each backup method may provide different types of functions and features. Evaluate the functions and features available with both methods and determine how they might affect service level requirements for your overall backup and restore strategy.
VSS Backup to TSM versus VSS Backup to local
Compared to VSS Backup to local, VSS Backup to TSM requires a longer processing time for backup and recovery; however, VSS Backup to TSM stores the data on Tivoli Storage Manager server tape or disk media for long-term retention (e.g. for regulatory requirements). After a VSS Backup to TSM is complete, the target volume is released for use by future snapshots. On the other hand, VSS Backup to local might be a better choice for recovering large databases in a shorter time frame because VSS Fast Restore or VSS Instant Restore can be used. These two restore methods typically complete much quicker than when restoring a VSS snapshot from Tivoli Storage Manager storage. However, these two restore methods require more target volume space for snapshots.
Tivoli Storage Manager VSS instant restore considerations
VSS Instant Restore is a hardware-assisted, volume level restore which performs a very quick copy of the target volumes back to the original source volumes. This is the quickest restore method in most cases. Consider these recommendations when using VSS Instant Restore
- VSS Instant Restore is not part of the generic VSS infrastructure provided by Microsoft. It requires specialized code to directly communicate with the disk subsystem. As of the publication date of this document, VSS Instant Restore is supported on IBM DS6000, IBM DS8000, and IBM SAN Volume Controller subsystems.
- Carefully plan the layout of the application data files. A VSS Instant Restore will overwrite all data on the production source volumes. If multiple objects that can be restored individually are placed on a single volume, those objects must be restored as a unit if a VSS Instant Restore is performed. Be aware that Tivoli Storage Manager does provide an option to disable VSS Instant Restore should it be necessary.
- If you want to preserve any data on the original source volumes, you must manually copy the desired data to a temporary location and replace it after the VSS Instant Restore operation is complete.
Tivoli Storage Manager VSS offloaded backup considerations
Setting up your environment for VSS offloaded backups requires the following components:
- A secondary machine that has access to the storage area network (SAN).
- A VSS hardware provider that support transportable shadow copies.
- The Tivoli Storage Manager nodes are configured in a proxy relationship.
IBM TDP SQL Performance Tuning
There are several performance considerations to evaluate when performing VSS Backups and VSS Restores for large databases
- The amount of overhead involved for VSS snapshots depends on the VSS Provider implementation for the disk subsystem. For example, if the VSS Provider uses copy-on-write, it may be able to create snapshots almost instantaneously; however, this method relies heavily on the original volumes to restore the data. If the VSS Provider uses full volume copy, it may not require the original volumes to restore data but some operations might take a longer time to complete.
- During a VSS backup, some application servers might require additional processing that can extend the time required for the VSS Backup to complete. For example, Exchange Server VSS Backups require an integrity check that must scan each page of the database and log files.
- When performing a VSS Backup to local volumes, there is low overhead since there are no resources needed to send the application data to Tivoli Storage Manager Server storage. In addition, the application server will be in backup mode for a very short period of time.
- VSS offloaded backups may provide better overall performance of the production application server machine since the movement of data to Tivoli Storage Manager Server storage is offloaded to the second machine.
For more detailed instructions follow this link
0 Comment to "How IBM TDPSQL uses Microsoft Volume Shadow copy Services to take MSSQL online DB backups"
Post a Comment