Tivoli Storage FlashCopy Manager Internals

Creating SQL Server database clones with IBM Spectrum Protect

4R2P_Lu_Nguyen ‎ 1,665 Views

IBM Spectrum Protect Snapshot (formerly called Tivoli Storage FlashCopy Manager) can clone Microsoft SQL Server databases much faster, and with much less disruption to the production database, than traditional replication-based cloning techniques. Using the advanced copy capabilities of storage hardware, clones can be created in just minutes and with minimal CPU usage.

To start, you must be familiar with Microsoft SQL Server, IBM Spectrum Protect, and Microsoft Windows (including Volume Shadow Copy Services (VSS), DISKSHADOW, and DISKPART). Know your storage hardware and its capabilities and limitations. In the examples outlined, I use a mix of GUI and command-line commands. If you want to automate the cloning, the entire process is scriptable. In my example, I create a clone on the same SQL Server machine. It is also possible to create a clone on a different SQL Server. Let me know in the comments section if you’d like to see that scenario, or any other scenario.

Overview

At a high level, the steps are:

Back up the SQL Server database to be cloned using IBM Spectrum Protect Snapshot
MOUNT the backup on the local machine with IBM Spectrum Protect Snapshot
(Optional) MASK the backup from the local machine using DISKSHADOW
(Optional) Delete the backup to remove it from IBM Spectrum Protect’s database
(Optional) IMPORT the shadow copies back to the local machine with VSHADOW
(Optional) EXPOSE the shadow copies and assign drive letters or mount points with DISKSHADOW
CLEAR the READONLY bit on the imported volumes with DISKPART
Use the SQL Server ATTACH command to create the clone database using the imported data and log files
When you are finished, use the SQL Server DETACH command to remove the clone database from SQL Server
DELETE the shadow copies with DISKSHADOW

Theoretically, it is possible to perform the cloning operation using snapshots without using IBM Spectrum Protect Snapshot. However, when dealing with dozens or hundreds of databases, each of which might use half a dozen file systems or more, it becomes very unwieldy. Furthermore, as the databases grow and change, adding, removing, and relocating files, the odds of making a mistake are very high. IBM Spectrum Protect Snapshot eliminates the complexity involved. Combined with VSS, it is no longer necessary to keep track of which databases are located on which file systems, and in turn which file systems are located on which LUNs.

Process

1. Create a backup of the desired database. The backup type should be Copy-Only Full, so that it does not affect the regular backup sequence. The backup destination should be LOCAL, or BOTH, which instructs IBM Spectrum Protect Snapshot to keep the shadow copies on local disk. The backup method must be VSS.

Be sure to set the “Import VSS Snapshots only when needed” flag. Under the covers, this causes the VSS transportable flag to be set, which is necessary to transport the VSS snapshot to another server. In version 4.1.3 and later, this setting is enabled by default, but in prior versions the default was disabled.

2. Import the shadow copies into the local machine (or remote machine) using the MOUNT command. This action makes the shadow copy available to the system.

At this point, if you are sure that 1) you or any other administrators will not be confused into thinking that this backup is available for restore and 2) your CopyOnly-Full backups use a different management class than your regular backups, so that your backup retention policy is not affected, you can actually skip steps 3-6 and go directly to 7.

Always be aware of the limitations of your storage hardware. In some cases, you storage hardware may also impose limits on the order that your snapshots can be deleted. Whether you perform steps 3-6 or not, it is important to make note of these limitations or else some of your backups may disappear unexpectedly. In particular, you should not use this procedure with storage systems that have snapshot dependency chains, unless you fully understand both the dependency chain limitations and the ramifications of this procedure.

Steps 3 to 6 are required to remove the clone shadow copy from IBM Spectrum Protect Snapshot’s control (and awareness). Since the cloning operation modifies the files located on the shadow copy, it can no longer be used for restore. We remove it from IBM Spectrum Protect Snapshot so that an administrator does not think that it’s available for restore, and so that it does not disrupt any retention policies.

3. In order to remove it from IBM Spectrum Protect Snapshot, we “hide” the shadow copies, delete the corresponding entry in the internal database, and then “unhide” the shadow copies. The “hide” step can be performed using the DISKSHADOW MASK command. You can determine which shadow copy to mask by checking the time stamp. The “Creation time” will be shortly after the backup date/time.

DISKSHADOW> mask {eb5b1f79-d543-42b0-a431-6d0790560bea}

The shadow copy has been removed from the system.

If you look in the baclient dsmerror.log file, you will notice this error:

10/13/2015 16:50:35 FMV1449W A non-critical, unexpected error was encountered.

Deletion of a snapshot(Set/Volume) failed. Probable cause, snapshot does not exists anymore. Continuing Process...

FCM snapShot ID : {ffc61c9d-b10b-4f9c-a2ed-9646a30880c0}

FCM function name : vssDeleteSnapshot()

FCM function : Deletion of SnapshotSet failed, Continuing...

FCM return code : 131

FCM file : ..\..\common\winnt\vssback.cpp (7134)

Of course, this error is expected, as IBM Spectrum Protect Snapshot could not find the snapshot it was trying to delete. It is safe to ignore this error. If you plan to mount the shadow copies back to a different location, then you may also wish to clean up the mount points as well.

4. Delete the CopyOnly Full backup in IBM Spectrum Protect Snapshot. This removes the entry from IBM Spectrum Protect Snapshot’s internal database, but does not touch the shadow copy LUNs. This is because we masked the LUNs from the local machine, and they are on the SAN at the moment.

Before you delete the backup, though, you need to save the backup documents file. This file is called TSM_Backup.xml, and is located in C:\Program Files\Tivoli\tsm\baclient\adsm.sys\vss_staging\<SQL nodename>\<IBM Spectrum Protect server name>\<backup timestamp>. See Step 5 below for more details, where this file is used.

At this point, the backup (and corresponding shadow copy) has been completely removed from IBM Spectrum Protect Snapshot. The remaining steps are strictly Windows and SQL Server commands. It is important to delete the backup from IBM Spectrum Protect Snapshot; otherwise, an administrator might think that a backup is available for restore, when in fact it is not.

Now that IBM Spectrum Protect Snapshot is no longer aware of the shadow copy, it won’t list the backup as available for restore, and it won’t interrupt your retention policies.

5. Make the shadow copy available again to the server. The VSS operation for this step is the IMPORT command. Unfortunately, the DISKSHADOW tool requires a metadata file that uses its custom format for this step. Instead, we revert to Microsoft’s older VSS tool, called vshadow. On my Windows Server 2012 R2 server, this file is installed by default in "c:\Program Files (x86)\Windows Kits\8.0\bin\x64\vshadow.exe", but your mileage may vary. You may need to download it from the Microsoft web site. It is part of the VSS SDK 7.2.

The vshadow tool takes as input the Backup Components xml file created at backup time. By default, IBM Spectrum Protect Snapshot stores this file in the C:\Program Files\Tivoli\tsm\baclient\adsm.sys\vss_staging\<SQL nodename>\<IBM Spectrum Protect server name>\<backup timestamp>. In some older versions, the file was stored in c:\adsm.sys\.

In order to import the shadow copy, run the following command:

Vshadow –i=TSM_Backup.xml

VSHADOW.EXE 3.0 - Volume Shadow Copy sample client.

(Option: Import shadow copy set from file 'TSM_Backup.xml')

Reading the file 'TSM_Backup.xml' ...

- Setting the VSS context to: 0xffffffff

Importing the transportable snapshot set ...

(Waiting for the asynchronous operation to finish...)

Shadow copy set succesfully imported.

6. Now the LUNs are mapped to the local machine, but we need to assign drive letters or mount points to access the data. Previously, in step 2, I mounted the shadow copy volumes as c:\mnt1. This time around, I will expose the shadow copy volumes as H:\ and I:\.

DISKSHADOW> expose {8b8835d3-6e3c-4570-b080-484bfbafcf2e} H:

The shadow copy was successfully exposed as H:\.

DISKSHADOW> expose {7fcbe688-07ab-434c-adaf-652b85ead0ea} i:

The shadow copy was successfully exposed as i:\.

7. The shadow copies are exposed as H: and I:, but by default the volumes are read-only. We need to disable the read-only bit in order to create the clone. We use the DISKPART ATTRIB command to accomplish this.

If you get the attributes of the volume, you’ll see that the read-only bit is set:

DISKPART> attrib volume

Read-only : Yes

Hidden : No

No Default Drive Letter: Yes

Shadow Copy : Yes

You can clear this attribute using the CLEAR command:

DISKPART> list volume

Volume ### Ltr Label Fs Type Size Status Info

---------- --- ----------- ----- ---------- ------- --------- --------

Volume 0 D DVD-ROM 0 B No Media

Volume 1 System Rese NTFS Partition 350 MB Healthy System

Volume 2 C NTFS Partition 99 GB Healthy Boot

Volume 3 E Data NTFS Partition 99 GB Healthy

Volume 4 F DATA ReFS Partition 6110 MB Healthy

Volume 5 G LOG ReFS Partition 6110 MB Healthy

Volume 6 H DATA ReFS Partition 6110 MB Healthy

C:\mnt1\FCM-vol-2\

Volume 7 I LOG ReFS Partition 6110 MB Healthy

C:\mnt1\FCM-vol-1\

DISKPART> select volume 6

Volume 6 is the selected volume.

DISKPART> attrib volume clear readonly

Volume attributes cleared successfully.

DISKPART> select volume 7

Volume 7 is the selected volume.

DISKPART> attrib volume clear readonly

Volume attributes cleared successfully.

DISKPART> attrib volume

Read-only : No

Hidden : No

No Default Drive Letter: Yes

Shadow Copy : Yes

8. The shadow copy is successfully imported, exposed, and writeable. We can now create a clone database using the database files on the shadow copy.

Note that Microsoft does not support cross-server restores with VSS backups. Instead, you should use the ATTACH command to make the clone in SQL Server as shown:

9. Once you are done with the clone, use SQL Server to DETACH the clone.

10. Now that SQL Server is no longer using the shadow copy files, use DISKSHADOW to delete the shadow copies. In my example, the shadow copies are the only ones on the system, so I can use DELETE SHADOWS ALL. If you have other shadow copies on your system, you should delete only the relevant shadow copy set.

DISKSHADOW> delete shadows all

Deleting shadow copy {8b8835d3-6e3c-4570-b080-484bfbafcf2e} on volume \\?\Volume

{b8b77ad1-9cf5-43c4-b5b8-d3c200b1b349}\ from provider {d90dd826-87cf-42ce-a88d-b

32caa82025b} [Attributes: 0x00190029]...

Deleting shadow copy {7fcbe688-07ab-434c-adaf-652b85ead0ea} on volume \\?\Volume

{8b127714-f202-4574-896c-263ff610993d}\ from provider {d90dd826-87cf-42ce-a88d-b

32caa82025b} [Attributes: 0x00190029]...

Number of shadow copies deleted: 2

If you skipped steps 3-6 and left the shadow copies under IBM Spectrum Protect Snapshot’s control, you can use IBM Spectrum Protect Snapshot to perform this step instead. Simply delete the CopyOnly Full backup.

Conclusion

At this point, I’ve demonstrated how to quickly and easily clone a SQL Server database using IBM Spectrum Protect Snapshot. Using the techniques outlined here, you can also create clones in fully automated, multi-database, and multi-server configurations.

This information is presented as-is, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED. If you need support, please contact IBM, Microsoft, or your storage vendor as appropriate.

References:

How to: Clone an Existing Database

https://msdn.microsoft.com/en-us/library/hh272696%28v=vs.103%29.aspx?f=255&MSPPError=-2147217396

Diskpart command reference

https://technet.microsoft.com/en-us/library/cc766465%28v=ws.10%29.aspx?f=255&MSPPError=-2147217396

Diskshadow command reference

https://technet.microsoft.com/en-us/library/cc772172.aspx

Software Versions Used:

IBM Spectrum Protect Snapshot 4.1.3

IBM SAN Volume Controller 7.5.0.2

IBM VSS Provider 4.10.0.0001

Microsoft SQL Server 2014

Microsoft Windows 2012 R2

Modified on by 4R2P_Lu_Nguyen

Come join us at IBM Edge in Las Vegas May 11-15

4R2P_Lu_Nguyen ‎ 782 Views

I will be at IBM Edge in Las Vegas May 11-15, 2015. Many of our business partners, such as Rocket Software, will be there also. Come find out what's new with FlashCopy Manager and IBM Spectrum Protect, and bring your technical questions. I look forward to meeting everyone.

http://www-03.ibm.com/systems/edge/

FlashCopy Manager videos

4R2P_Lu_Nguyen ‎ 1,147 Views

Here are a few new videos about FlashCopy Manager and related topics. These videos show how to do some common (and not so common) tasks, and are intended to be from one technical expert to another, without undue product promotion.

If you'd like to see a video on a certain task, please let us know in the comments. I will update this post with new videos periodically.

Tivoli Storage Manager for Virtual Environment 7.1.1 Exchange Integration

IBM Tivoli Storage Manager for SAP HANA

IBM Tivoli Storage FlashCopy Manager for VMware - Remote Mirroring with VSC

Finally, here is a link to all of the Data Protection videos: http://tinyurl.com/TSMVIDEOS

Modified on by 4R2P_Lu_Nguyen

FlashCopy Manager for Windows support for Windows Server 2012 R2

4R2P_Lu_Nguyen ‎ 1,031 Views

In case you were wondering, FlashCopy Manager for Windows (as well as Data Protection for Exchange and Data Protection for SQL Server) does support Windows Server 2012 R2. See the hardware and software requirements tech notes for details:

FCM Exchange / DP Exchange:

http://www.ibm.com/support/docview.wss?uid=swg21654310

FCM SQL / DP SQL:

http://www.ibm.com/support/docview.wss?uid=swg21654311

FCM for File Systems and Custom Applications:

http://www.ibm.com/support/docview.wss?uid=swg21654309

FCM VSS Requestor component:

http://www.ibm.com/support/docview.wss?uid=swg21654308

Mailbox restore with Exchange 2013

4R2P_Lu_Nguyen ‎ 3,164 Views

Microsoft made a number of changes in Exchange Server 2013. With regards to TSM mailbox restore, perhaps the most significant change was the requirement for MAPI clients to use RPC over HTTPS (also known a Outlook Anywhere). RPC over HTTP is no longer supported. When using any new or changed technology, you may encounter some bumps in the road. I've been working with Exchange 2013 for quite some time now, and I would like to share with you a few tips for using mailbox restore with Exchange 2013.

Before you start:

Use FlashCopy Manager 3.2.1 (or later) or Data Protection for Exchange 6.4.1 (or later).
Use Exchange 2013 CU2 or later
Use MAPI download 6.5.8320.0 or later
Verify your Exchange environment
- Open the current administrator's mailbox in Outlook or OWA
- Open the target mailbox in Outlook or OWA
- (optional) Use MFCMapi to verify your MAPI configuration. This Microsoft blog lists the steps to perform this verification. If MFCMapi cannot connect to Exchange, then TSM will not be able to either.

If your environment is configured correctly, mailbox restore will work as with previous versions Exchange. However, if you run into trouble, I suggest the following troubleshooting steps:

Grant "full access" permission to the target mailbox. Especially when using the "Administrator" mailbox, typically Exchange 2013 blocks full access permission for this administrator.
Log in as an Exchange administrator with a mailbox on an Exchange 2013 database
Use an Exchange 2013 CAS server (i.e. set the CLIENTACCESSServer parameter to an Ex13 CAS server)
If using a load balancer, for troubleshooting purposes set the CLIENTACCESSServer to an actual server instead of the load balancer
Try using Mailbox Restore Browser with "Open Exchange Mailbox" to open the administrator mailbox and the target mailbox. This is the simplest case.
Check your MAPI registry key. It is located at HKEY_CURRENT_USER\Software\Microsoft\Windows NT\Current Version\Windows Messaging Subsystem, and the name of the key is RpcHttpProxyMap_TSM. You may have to change the http to https, or the authentication method, or the domain name to *. The MAPI download (linked above) contains the Microsoft documentation for setting this registry key.

Beta program for an upcoming release of FlashCopy Manager

4R2P_Lu_Nguyen Tags: flashcopy-manager eap beta ‎ 1,627 Views

I don't typically use this space to announce things, since they are already announced through other channels. However, I wanted to let you know about the Early Access and Beta programs for a future release of FlashCopy Manager. This is your chance to not only get a preview of the new features of the future version, but also to make suggestions on how you'd like for future versions to work. Our clients, business partners, and internal IBMers are invited to apply:

https://www.ibm.com/developerworks/mydeveloperworks/blogs/869bac74-5fc2-4b94-81a2-6153890e029a/entry/current_flashcopy_manager_early_access_program_and_beta_progarm_for_future_release_looking_for_participants5?lang=en

More FCM performance tips

4R2P_Lu_Nguyen Tags: tsm flashcopy-manager performance dp-sql fcm tivoli-storage-manager tivoli dp-exchange ‎ 2,663 Views

A couple more FCM performance tips for you. We have released the fix for APAR IC86558 for the TSM Server. It is included in 6.2.5 and 6.3.4. This change allows the TSM Server to manage lots of small files more efficiently in certain situations. For those using Exchange 2010 (which has thousands of 1MB transaction logs), I highly recommend getting this fix. For those running Exchange 2007 and backing up using VSS, I would also recommend this fix. This fix may also help those using FCM to backup their file systems and custom applications, depending on the type of files being backed up.

Secondly, I would like to discuss the BUFFers and BUFFERSIze parameters. For legacy backups, the Data Protection process performs the backups, so these parameters are used. However, for VSS backups, the DSM Agent performs the majority of the transfer to the TSM Server, so you will want to tune the baclient parameters instead. (Be sure to restart the DSM Agent for the changes to take effect). In both cases, though, the default values are the optimal values for most environments.

Troubleshooting FCM failures

4R2P_Lu_Nguyen Tags: fcm tivoli flashcopy-manager dp-sql tivoli-storage-manager dp-exchange tsm ‎ 3,242 Views

FlashCopy Manager contains many components, and writes its data to many different log and trace files. While this helps separate the components so that the logs are easier to read, it can be confusing deciding which logs you need to check in case of a failure. For example, in a typical FlashCopy Manager backup, there are a few distinct phases:

Prepare for backup
VSS Snapshot
Integrity Check (FCM for Exchange only)
Transfer to TSM Server

The first step of any problem diagnosis is to determine the phase in which the failure occurred. The most useful log for this step is the tdpexc.log, tdpsql.log, or fcm.log file. Once you know which phase is failing, you will be able to review the appropriate logs to continue your problem diagnosis. The following table contains a high level explanation of the different log files that FCM uses.

Problem Category ---> Log file

-------------------------------------------------------------------------------------

Initial Diagnosis ---> tdpexc.log, tdpsql.log, or fcm.log

VSS backup / restore ---> Agent trace (TraceFileAgent.trc)

VSS hardware / system problems ---> VSS Hardware Provider logs, Windows Event logs

Legacy backup / restore ---> DP Trace (TraceFileXXX.trc)

MMC GUI ---> Fm trace (TraceFm.trc)

Exchange

Exchange server errors ---> Application Event log
Mailbox Restore errors ---> DP trace (TraceFileExc.trc)
MAPI errors ---> DP trace (TraceFileExc.trc)
Client Access Server (CAS) errors ---> CAS logs (C:\Program Files\Microsoft\Exchange Server\V14\Logging\Rpc Client Access)
Integrity check errors ---> C:\adsm.sys\vss_staging\<Nodename>\<TSM Server Name>\<Backup Timestamp>\<Backup GUID>_Audit.txt.log

SQL ---> Application Event log

Problems during transfer to TSM ---> tdpexc.log, tdpsql.log, or fcm.log, and potentially API trace (TraceFileXXXAPI.trc)

A couple of quick tips for improving backup performance

4R2P_Lu_Nguyen Tags: flashcopy-manager dp-exchange tivoli-storage-manager dp-sql tsm fcm tivoli performance ‎ 3,639 Views

When backing up databases using the VSS backup method, you can improve the backup performance by telling FlashCopy Manager to use multiple sessions to communicate with the TSM Server. (This tip assumes you are using multiple disks or LUNs for your database, for example, separate LUNs for your data files and your log files). To do this, set the RESOURCEUTLIZATION parameter in the backup-archive client dsm.opt file. The TSM Performance Tuning Guide (PDF) suggests a value of 5 or 10.

Secondly, when backing up Microsoft Exchange databases, it may be possible to improve performance by skipping the integrity check of the databases. Typically, Microsoft requires an integrity check before a backup is considered "good." However, in Exchange 2010 Database Availability Group (DAG) environments, Microsoft states: "For databases in a DAG that has two or more healthy copies, the database consistency checking step can even be skipped." You should evaluate whether skipping the integrity check is appropriate for your environment. If so, you can tell FlashCopy Manager to skip the integrity check with the SKIPINTEGRITYCHECK parameter.

MAPI limits during FlashCopy Manager mailbox restore operation

4R2P_Lu_Nguyen Tags: problem-of-the-month fcm tsm tivoli flashcopy-manager dp-sql tivoli-storage-manager dp-exchange ‎ 1,794 Views

A couple of our clients reported recently that their mailbox restore operations failed intermittently while using Data Protection for Exchange or FlashCopy Manager. Looking at their diagnostic trace (specifically the TraceFileExc.trc file), we noticed FCM was getting a MAPI_E_CALL_FAILED error when it tried to communicate with Microsoft Exchange using MAPI. Long story short, it turns out the mailboxes they were trying to restore contained many folders, and FCM exceeded the MAPI limits in Exchange.

FlashCopy Manager uses Exchange MAPI to perform mailbox restore operations, and it may fail if it has exceeded the limits set by Exchange. To check whether this situation applies to you, look in the event log for Event ID 9646. This Microsoft article briefly discusses this problem for Exchange 2003, but it applies to other versions too. This other Microsoft article details the existing limits and how to increase these limits. objtFolder and objtMessage are the limits that you are most likely to hit, but other limits are possible too. The Windows Application event log will specify which limit you need to increase. After increasing these limits, our clients were able to restore these large mailboxes successfully.

Welcome to the Tivoli Storage FlashCopy Manager Internals blog

4R2P_Lu_Nguyen Tags: tsm tivoli-storage-manager dp-exchange tivoli flashcopy-manager dp-sql fcm ‎ 1,921 Views

I am a software developer at IBM, working on Tivoli Storage Manager and Tivoli Storage FlashCopy Manager. In the course of my work, I encounter many interesting problems and their innovative solutions. Periodically, I would like to share some of these experiences with you. This blog is intended to be a technical blog, and I hope that it may be useful to others who may encounter similar problems. The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.

If you have any topics that you would like for me to discuss in a future blog entry, feel free to let me know in the comments section.

Feed for Blog Entries Feed for Blog Comments

Blogs