Hang is seen during multiple-session restore with the Data Protection for SQL client
For a Data Protection for SQL restore, all stripes must be available in order for SQL Server to proceed with the restore processing. If all of the SQL Server data to be restored is located on one tape, and multiple stripes were used for the backup, then the subsequent session(s) will be in a MediaWait for the same tape. This will cause the restore to fail as the subsequent session(s) are not ready for the restore processing to continue until the MediaWait is satisfied.
Diagnosing the problem
When performing a Data Protection for SQL restore command, the operation hangs.
If the Data Protection SQL command line is used, it indicates that the restore is waiting on the Tivoli Storage Manager Server.
The Tivoli Storage Manager Server Query Session for the node will show one session in MediaWait and the other Session in SendWait. For example:
6,760 Tcp/Ip MediaW 5.9 M 2.6 K 996 Node TDP TDP-SQL-Node
56,761 Tcp/Ip SendW 5.2 M 3.2 M 683 Node TDP TDP-SQL-Node
A Query Mount, on the Tivoli Storage Manager Server, for the MediaW volume will show the tape volume is In Use, it is being used by the session that is in SendW.
Resolving the problem
When performing a backup with the Data Protection for SQL, the backup can be specified to have multiple stripes. Each stripe has its own session with the Tivoli Storage Manager Server. If multiple stripes were used for the backup and the same number of stripes are used for the restore, but all the data is stored on the same tape volume on the Tivoli Storage Manager Server, the restore cannot be performed. The restore processing will appear to hang waiting on the Tivoli Storage Manager Server. The problem is that the data for all the stripes must be available before the restore can proceed. Since the first session has the tape which contains the data, the subsequent sessions for the number of stripes remain in a media wait and the restore processing cannot continue.
It is possible within the Tivoli Data Protection SQL to set the number of stripes for the restore. This can be a value up to the number of stripes that was specified for the backup. By default the number of stripes for the restore will be the same number stripes used for the backup. If all the data is on a single tape, then specify STRIPES=1 for the restore processing.
It is also possible to alleviate the problem by manually moving the backup objects to separate tapes with the MOVE DATA or MOVE NODEDATA command on the Tivoli Storage Manager Server, specifying a stgpool that is collocated by filespace. Or if there is sufficient space in the DISK stgpool to contain all the data on the tape, the move data could be directed to this DISK storage pool.
To avoid this problem for future SQL backups using the Data Protection client with multiple stripes, the StgPool for this data should have Collocation by Filespace enabled.