TSM4VE – Protecting MS SQL
This (lengthy) article will only focus on MS SQL, but the majority is also applicable to MS Exchange.
During most of my recent TSM for Virtual Environments (in this case Data Protection for VMware) implementations, the following question always comes up:
“Is TSM4VE is capable of backing up an installation of MS SQL that is running inside a virtual machine?” Which can be translated to: “How to protect MS SQL databases hosted in VMware”?
There are a few basic challenges with this:
- Application consistent backups;
- Recover individual SQL databases from the backup;
- Roll-forward SQL logs to a specific point-in-time (PIT).
The chosen method or level of protection required depends on the recovery point objective (RPO):
- Is a PIT restore to the last full backup sufficient?
- If not, there is a need to also backup SQL logging. This to be able to roll-forward the database transactions since the last backup after the restore.
TSM4VE before v6.4 (<v6.4)
- DP for VMware used standard VMware functions to quiesce applications that run in the VM guest;
- Applications were not notified that a backup completed successfully and as a result application logs were not truncated.
Sample output looked like this:
TSM4VE v6.4 introduced “application awareness” or “self-contained application protection”:
- DP for VMware uses its own functions to ensure guest level application consistency and log truncation;
- Freezes and thaws VSS writers;
TSM interacts with MS SQL (running inside the TSM, uses FULL VSS backup type) that a backup is about to occur, and:
- Allows applications to truncate transactions logs;
- Commit transactions;
- Now the application can resume from a consistent state when the backup completes.
To implement all this good stuff, simply specify in v6.4 dsm.opt on the vStorage Backup Server (vBS):
You can use a single INCLUDE.VMTSMVSS statement per VM, or use wildcards. For example, you can use…:
…if you are indeed that standardized and you use SQL in the naming of all your VM’s running SQL. You’ll also need to specify a VMware account used for TSM4VE backups that have permissions to create VSS copies and truncate SQL logs.
If protected by TSM, sample output looks like this:
An existing backup can also be checked at a later time with this command:
query vm <vmname> –detail
But there are some limitations with this application-aware setup:
- It is not supported in an SQL cluster;
- It does not support roll-forward restore with SQL logs;
- It is not possible to restore an individual SQL database.
Please note that SQL logs simply cannot be backed up by VSS – that is a limitation beyond the scope of TSM4VE.
Because of those limitations, many implementations choose to use a hybrid-style approach. They will protect the Windows environment and SystemState (The OS disk + registry) with TSM4VE, but will exclude the SQL DATABASE and LOG disks (EXCLUDE.VMDISK) and run DP for SQL to protect their contents. If the only RPO requirement is a restore to the last full SQL database backup, v6.4 application-awareness is sufficient. This method was called the “Hybrid” method, until now.
TSM4VE v7.1 – Cooperative Hybrid Method
Meet v7.1; all of the previous limitations are now gone and v7.1 also introduces the ability to restore a single SQL database from a TSM4VE Full VM backup. That above described method is now referred to as the “Partitioned Hybrid Method”, which of course can still be used in v7.1.
But by choosing for a so-called “Cooperative Hybrid Method” one can use the in-guest data protection software (TSM for Databases – Data Protection for Microsoft SQL) to backup just the logs and one can use TSM4VE to create full backups of the SQL databases. This method:
- Will leverage the advantages of changed block tracking (CBT) and TSM’s incremental forever to have all the benefits of backing up on the VM level;
- Provides a more sophisticated integration. The term “coordinated” refers to the fact that there is awareness between the individual software components.
This remainder of this article will outline how to configure the Cooperative Hybrid Method. All the information provided here and much more, including verification of the setup, can be found in great detail in the document “IBM Tivoli Storage Manager – Protecting Microsoft SQL Databases Hosted in VMware“.
Based upon DP for VMware v7.1 and DP for SQL v7.1, this is a fully IBM supported method. See paragraph 1.3 of the mentioned document for details on this.
Let’s start off with an existing TSM4VE implementation, where 3 machines are involved:
- Virtual machine running MS SQL (VM_SQL01);
- vBS, Windows-based, running 1 datamover with nodename DC1 (FACE);
- TSM server (HANNIBAL).
On the vBS (FACE):
Specify in dsm.opt of the datamover:
INCLUDE.VMTSMVSS VM_SQL01 options=keepsqllog
This will ensure that the state of the SQL server of the time of the VM snapshot is collected through VSS and preserved. This VSS metadata is also called “manifest data” and will be used later during restore by DP for SQL. The option keepsqllog is needed to not interfere with the SQL logging at this point.
Dsmc set password –type=vmguest VM_SQL01 <administrator> <password>
This specifies the account used for TSM4VE backups that have permissions to create VSS copies and truncate SQL logs. The password is stored locally on the vBS, and it is of course encrypted. The required SQL server permission can be found in this IBM Technote.
Now create a TSM4VE backup which will include a full SQL backup and the required VSS manifest data. That can be done through CLI or one of the GUI’s, just as you’re familiar with.
Check this backup for “database-level recovery” and make sure that all virtual disks hosting SQL databases or logs have a VMDK Status of “Protected”:
query vm vm_sql01 –detail
On the VM machine running SQL (VM_SQL01):
- Install the Data Protection for VMware Recovery Agent and License. These components can be selected when running a Custom installation of the Data Protection for VMware package;
- When running the DP for SQL configuration wizard, specify the datacenter node name (DC1):
Verify that the “Configuring Recovery Agent rule” status reports “Passed”.
Back to the vBS (FACE):
The TSM B/A client that is running here is configured with a VSS requestor node (VSS_REQ):
Give this VSS requestor node access to the VM backups. In this case, these are the filespaces for VM_SQL01 on the TSM server. Note that if your vBS setup is using ASNODE in its dsm.opt, refer to the document.
- Copy the existing dsm.opt to dsm.setaccess.opt
Replace the line NODENAME:
- NODENAME DC1
- Dsmc set access backup –type=vm vm_sql01 vss_req –optfile=dsm.setaccess.opt
As stated before, the full SQL backups (as part of the TSM4VE backup stream) are stored as a filespace under the DC node. The SQL log backups are stored as we’re used to: directly under the DP for SQL node name.
Because both components might be needed during a restore, it is critical that the retention of the DATABASE and LOGS are aligned. Normally everything is stored under the DP for SQL node name, and logs are associated with their corresponding full backup to ensure everything is available during restore. But that mechanism does not work in this hybrid setup.
In this case the log backups must be explicitly inactivated, because they are not “attached” to a full backup. This inactivation is done through the tdpsqlc inactivate command with the /OLDERTHAN= parameter.
A sample setup to keep around SQL backups for 60 days and delete the log backups 1 day after being inactivated:
TSM4VE / DC node management class:
- RETEXTRA=60, RETONLY=60, VEREXIST=NOLIMIT, VERDEL=NOLIMIT
You can update the backup copy group of the policy domain, or define a new management class and use the VMMC option in the .opt file of the vBS.
DP for SQL node management class:
- RETEXTRA=0, RETONLY=1, VEREXIST=NOLIMIT, VERDEL=NOLIMIT
RETEXTRA is just ignored, it does not applies to SQL log backups.
If needed, use the correct INCLUDE statements in the .opt file of DP for SQL.
For roll-forward log restore recovery, schedule TSM4VE full backups and schedule subsequent DP for SQL log backups after that, which use the log truncation option. Also schedule the inactivation of your SQL log files (see “MANAGING VERSIONS”). How many in-guest SQL log backups you’ll need depends on your RPO. Again, refer to the document for how to setup these backup schedules. This can be done locally or more centralized.
Open the recover tab in the DP for SQL GUI:
- The full SQL backups performed by TSM4VE are listed as “Backup Method”: VMVSS;
- The regular DP for SQL logs are listed as LEGACY.
This is based on the RPO. If a PIT restore of the Full VM hosting the SQL databases is good enough:
- Full VM restore of VM_SQL01 from TSM4VE.
In that case, the entire VM including all SQL database is restored. If that is sufficient, all the additional configuration steps are not needed.
If a single database restore is needed:
- Restore a full backup of a specific database (taken by TSM4VE) through the DP for SQL GUI.
If roll-forward is needed:
Restore a full backup of a specific database (taken by TSM4VE) through the DP for SQL GUI.
BE SURE to use the RunRecovery=FALSE (command line: /recovery=no) option;
Restore and apply SQL logs to recover the database to the needed PIT, with RunRecovery=TRUE (command line: /recovery=yes).
How each of these scenarios are carried out is listed in the document. Appendix A also shows the command line version for each scenario. Appendix D describes how to handle restore situations that involves relocated database or log files, as there are some to be aware off.
If only backing up full SQL databases through TSM4VE (hence, no SQL logging), there is no need to install anything inside the guest machine. At a later time, or during restore, the DP for SQL GUI can always be installed and configured to provide the SQL DBA an interface to restore all or an individual SQL database.
One of IBM’s Technical Evangelists, Cyrus Niltchian, is very active on his Youtube channel. This particular video, named “IBM TSM for VE – Individual SQL Database restore from a VM Backup” is very much related to the subject of this article.
Cyrus only uses TSM4VE to restore an individual SQL database. He creates the TSM4VE full VM backup and then restores just a single SQL database from the TSM4VE backup with the DP for SQL software installed inside the VM. Hence, the GUI is ONLY used for restore, not for backup in this case. For this to work the VSS manifest data is needed (INCLUDE.VMTSMVSS). This video shows the expected outcome, not how to set it up, but is still very useful. The pre-requisites are listed in the top comment below the video.
I hope the information provided here was helpful in getting to learn and deciding upon which method to use to protect your virtualized SQL environment. As always, feel free to comment!
—- Tommy Hueber