IBM Tivoli Data Protection for Microsoft SQL Database is one of the IBM TSM family products which can be used to backup & restore all SQL databases including log files to Tivoli Storage Manager (TSM) server storage. It can be used to backup either individual or group of databases. Data Protection for SQL provides several methods of backing up small sized or large sized SQL Server data.
IBM Tivoli Data Protection for Microsoft SQL Database (TDPSql) Introduction
There are two types of backups available in IBM Tivoli Data Protection for MS SQL, they are LEGACY TDP SQL and VSS TDP SQL backups. Legacy backups are a stream of bytes that Data Protection for SQL stores on the Tivoli Storage Manager server. VSS Backups differ since they are at the volume and file-level. When a SQL Server database is not fully allocated, a Legacy backup might transfer a smaller amount of data for a Tivoli Storage Manager backup than for a VSS Backup because a VSS Backup transfers the entire file, regardless of its allocation.
TDP SQL Legacy Backup
A Legacy backup creates a copy of all or part of a SQL database or logs on Tivoli Storage Manager storage media. Data Protection for SQL provides selection mechanisms and the logic that are required to back up and restore SQL data. When a backup is performed, Tivoli Storage Manager server retains information about the SQL Server and database. This information is available for query and restore operations after the backup is completed. Data Protection for SQL software can either compress or instruct the SQL Server to compress the SQL data before sending it to the Tivoli Storage Manager server. When you initiate a Legacy backup operation, Data Protection for SQL completes the following actions
TDP SQL Legacy Backup
A Legacy backup creates a copy of all or part of a SQL database or logs on Tivoli Storage Manager storage media. Data Protection for SQL provides selection mechanisms and the logic that are required to back up and restore SQL data. When a backup is performed, Tivoli Storage Manager server retains information about the SQL Server and database. This information is available for query and restore operations after the backup is completed. Data Protection for SQL software can either compress or instruct the SQL Server to compress the SQL data before sending it to the Tivoli Storage Manager server. When you initiate a Legacy backup operation, Data Protection for SQL completes the following actions
1. First TDP SQL begins a session with a Tivoli Storage Manager server using the Tivoli Storage Manager API and information contained in a client options file (dsm.opt).
2. Starts a session with the SQL Server using the SQL-SMO interface.
3. Instructs the SQL Server using the SQL VDI interface to begin a backup of the
selected or all database objects.
4. Receives data from the SQL Server and sends it to the Tivoli Storage Manager server.
5. Informs the SQL Server that the backup is complete.
6. Ends the Tivoli Storage Manager server and SQL Server sessions.
Also Read: How TDPO works with RMAN
TDP SQL VSS Backup
A VSS Backup uses Microsoft Volume Shadow Copy Service technology to produce an online snapshot (point-in-time consistent copy) of SQL data. A VSS Backup means that the SQL Server is not in backup mode for an extended period of time. The length of time to perform the snapshot is usually measured in seconds, not hours. Ideally, it reduces the pressure/load on the production system where database resides by taking a snapshot of production database and place the snapshot on other server or volumes and then start the backup from the secondary volumes to reduce the offload, these secondary volumes are often referred as backup servers or VSS shadow volumes. In addition, a VSS Backup allows a snapshot of large amounts of data at one time because the snapshot works at the volume level. 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.
These types of local VSS Backups are faster for a couple of reasons. They are faster because the way the snapshots are managed and because the data is not placed into Tivoli Storage Manager server storage. Restoring these backups is also fast because the SQL data is not transferred from Tivoli Storage Manager server storage over the network. 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.
Backing up Microsoft SQL Server 2012 Databases
- Full VSS backups of the primary availability replica
- VSS copy-only full backups of availability replicas
The following types of legacy backup operations are supported:
- On the primary replica, legacy full, differential, file, set, group, and log backups are supported.
- On the secondary replica, legacy full, file, set, group, and log backups are supported.
- VSS and legacy copy-only full backups, legacy copy-only file, set, or group backups, and legacy copy-only and normal log backups are supported.
- For all backup operations of secondary availability replicas, the secondary replicas must be in the synchronized or synchronizing state.
Restoring Microsoft SQL Server 2012 availability Databases
You can use Legacy restore or VSS restore operations to restore the MS SQL 2012 availability database but with some restrictions and limitations.
Also Read: Steps to take MS SQL DB backup and Restore using TDP for SQL
You can use Legacy restore or VSS restore operations to restore the MS SQL 2012 availability database but with some restrictions and limitations.
Also Read: Steps to take MS SQL DB backup and Restore using TDP for SQL
Legacy restore
You can restore an availability database on either a primary or secondary replica. During the restore process, the restored database is removed from the availability group. When a database is removed from the availability group, the database becomes a local database on that replica. The database is restored as a local database. After this restore is complete, manually add the database back to the availability group. However, before adding the database to the availability group, verify that the data on all replicas is transactionally consistent. For example, the database and any log files must be restored so the files are all at the same level. After verifying the data is transactionally consistent, the database can be added to the availability group.
Because of a SQL Server limitation, you cannot restore a VSS backup to an alternative SQL server instance. Therefore, VSS backups must be restored to the same SQL server instance where the snapshot was taken.
Tivoli Data Protection for MS SQL Database Backup Types
Data Protection for SQL offers an expanded range of backup types that allows flexibility for your environment and processing needs. With TDP SQL you can take
Full database backup (Legacy and VSS)
Data Protection for SQL backs up an entire SQL Server database and the portion of the transaction log necessary to provide a consistent database state. With both full and differential backups, the copy includes enough information from any associated transaction logs to make a backup consistent with itself. The portion of the log included contains only the transactions that occur from the beginning of the backup until its completion.
Copy-only full backup (Legacy and VSS)
A copy-only full backup is a type of backup that is independent of the sequence of conventional SQL Server backups. The copy-only full backup does not disturb the sequence for a differential backup. The differential backup is not associated with the copy-full backup, but is associated with the prior full backup that was completed. This type of backup can be used for special purpose backups that do not affect existing backup and restore procedures. In addition, when compared to conventional backups, this type of backup can be used for longer term retention. An example of a special purpose backup is a backup of a log before an online file restore. In this scenario, the copy-only full backup is used one time. After the backup is used, it is deleted.
Differential backup (Legacy only)
Data Protection for SQL backs up only the data pages in a SQL Server database instance that changed after the last full backup and a portion of the transaction log. Differential backup is associated with the last full backup that was performed. The last full backup might be completed by Data Protection for SQL or another tool or product. For example, if you run a full backup with SQL Server to disk backup, and run a differential backup with Data Protection for SQL, the differential backup is associated with the SQL Server disk backup. (Microsoft SQL Server 2012 only) Differential backup is not supported for databases on the secondary replica.
Log backup (Legacy only)
Data Protection for SQL backs up only the contents of a SQL Server database transaction log since the last successful log backup. Before the first log backup, complete either a full backup or an equivalent type of backup. Log backups normally follow full backups. The portion of the log included in full and differential backups is not equivalent to a log backup. Additionally, in full and differential backups, the log is not truncated as it is during a log backup. However, a log backup following a full or differential backup includes the same transactions as a full or differential backup. Log backups are not cumulative as are differential; they must be applied against a base backup and in the correct order. A log backup in SQL Server terms is not equivalent to an incremental backup in Tivoli Storage Manager terms.
File backup (Legacy only)
Data Protection for SQL backs up only the contents of a specified SQL Server logical file. This type of backup can ease the scheduling for backing up large databases. You can back up different sets of files during different scheduled backups. File, group, and set backups must be followed by a log backup, but a full is not required.
Group backup (Legacy only)
Data Protection for SQL backs up only the contents of a specified SQL Server file group. This backup allows you to back up the set of database tables and indexes within a specific group of files. The "group" is specified as part of the setup within the SQL Server when you define the database files. If no group is specified and all the database files are part of the "primary" group, it is not possible to back up or restore just part of the database by using the group.
Set backup (Legacy only)
Data Protection for SQL backs up the contents of specified SQL Server file groups and files as a unit.
Tivoli Data Protection for MS SQL Database Restore Types
Data Protection for SQL provides several methods of restoring SQL Server data. You can restore the SQL server data both with Legacy and VSS methods but with some limitations and restrictions.
Legacy restore
A legacy restore obtains backup copies of SQL databases from Tivoli Storage Manager server storage and restores them to their original location. Like a Legacy backup, it uses a specialized API restore that functions with the SQL Server. A complete restore of a database involves restoring a full backup or the equivalent thereof (from group, file, or set backups) and restoring all transaction logs since the last full backup.
Also Read: Integrating TDPO with RMAN to configure Oracle DB backups
VSS Restore
A VSS Restore restores VSS Backups (SQL database files and log files) that reside on Tivoli Storage Manager server storage to their original location. The limitations and restrictions of VSS Restores are
- You can only restore SQL Server VSS Backups to the same SQL Server instance.
- Full and copy-only full backup types can be restored. Differential, individual file groups, individual files, and set backups are not supported by VSS and therefore, cannot be restored. However, Legacy differential and Legacy log backups can be applied after a full VSS Backup has been restored.
- VSS Restore granularity is at the database level.
- Supports restoring one or more databases from a VSS snapshot backup located on Tivoli Storage Manager server storage.
- Restores can be performed in a Microsoft Windows Failover Clustering or Veritas Cluster Server (VCS) environment.
- Supports restoring a VSS Backup (directly from Tivoli Storage Manager server storage) to an alternate location using the /relocatedir option.
VSS Fast Restore
A VSS Fast Restore restores data from a local snapshot. The snapshot is the VSS backup that resides on a local shadow volume. The restore operation retrieves the data by using a file-level copy method. The following characteristics are true of VSS Fast Restores
A VSS Fast Restore restores data from a local snapshot. The snapshot is the VSS backup that resides on a local shadow volume. The restore operation retrieves the data by using a file-level copy method. The following characteristics are true of VSS Fast Restores
- Full and copy-only full backup types can be restored. Differential, individual file groups, individual files, and set backups are not supported by VSS and therefore, cannot be restored. However, Legacy differential and Legacy log backups can be applied after a full VSS Backup has been restored.
- You can only restore SQL Server VSS Backups to the same SQL Server instance. v VSS Backups can be restored to an alternate location by using the /relocatedir option.
- Restore granularity is at the database level.
- Restores can be performed in a Microsoft Windows Failover Clustering or Veritas Cluster Server environment.
VSS Instant Restore
A VSS Instant Restore restores data from a local snapshot. The snapshot is the VSS backup that resides on a local shadow volume. The restore operation retrieves the data by using a hardware assisted restore method (for example, a FlashCopy operation). A VSS Instant Restore is only possible when all of the data from the storage group or database that is specified for restore resides on storage subsystems that are supported by the VSS Instant Restore. If part of the data being restored, including the log files and full-text index files, resides on a local disk, a VSS Fast Restore is performed.
A VSS Instant Restore restores data from a local snapshot. The snapshot is the VSS backup that resides on a local shadow volume. The restore operation retrieves the data by using a hardware assisted restore method (for example, a FlashCopy operation). A VSS Instant Restore is only possible when all of the data from the storage group or database that is specified for restore resides on storage subsystems that are supported by the VSS Instant Restore. If part of the data being restored, including the log files and full-text index files, resides on a local disk, a VSS Fast Restore is performed.
When performing VSS Instant Restores, a best practice is to make sure that any previous background copies (that involve the volumes being restored) are completed prior to initiating the VSS Instant Restore. However, this check is not necessary for XIV, SAN Volume Controller, or Storwize V7000 with space-efficient target volumes. Check TDP SQL document for configuration information on how to perform VSS Instant restores using DS8000, SAN Volume Controller, Storwize V7000 storage systems.
General Backup Scenarios
Different backup strategies are available depending on specific requirements regarding network traffic, backup window and acceptable restore times. Strategies defined by backup type. Some commonly used strategies (based upon backup type) are described as follows
Full backup only (Legacy and VSS)
This approach is best for SQL databases that are relatively small because it implies that the entire database is backed up each time. Each full backup takes longer to perform, but the restore process is most efficient because only the most recent (or other appropriate) full backup need be restored. This is the appropriate strategy for system databases such as master, model, and msdb due to their normally small size.
Full plus log backup (Legacy and VSS)
A full plus transaction log backup strategy is commonly used when the normal backup window or network capacity cannot support a full backup each time. In such cases, a periodic full backup followed by a series of log backups allows the backup window and network traffic to be minimized. For example, you can perform full backups on the weekend and log backups during the week. The restore process becomes more complex, however, because a full backup, as well as subsequent log backups, must be restored. It is also possible to do a point-in-time restore to restore a transaction log to a specified date and time.
Full plus differential backup (Legacy and VSS)
This strategy can be used between full backups. A differential database backup can save both time and space. Space is saved because the backup consists of only the changed portions of a database since the last full backup (it is cumulative). Time is saved because you can avoid applying all individual log backups within that time to the operation. This applies to restore operations as well; only the last differential backup (latest version) need be restored. Although VSS supports full backups only, Legacy differential backups can be applied to the VSS full backup.
Full plus differential plus log backup (Legacy and VSS)
This strategy allows for a faster restore scenario by reducing the number of transactions that may need to be restored and applied. If, for example, a full Legacy or VSS backup is done weekly, a differential nightly, and a log backup every four hours, the restore would involve the full backup, a differential, and at most five log backups.
File or group backups (Legacy only)
When a group is created on the SQL Server, database files are identified with that group. The group used for the group backup is dependent on the group to which the database files are defined. Use a file backup strategy when it is impractical to backup an entire database because of size and accompanying time and performance issues. When performing restore operations for a file or file group, provide a separate backup of the transaction log.
0 Comment to "How IBM Tivoli Data Protection for MS SQL (TDPSQL) works and types of DB backups it offers"
Post a Comment