To improve the performance of IBM Tivoli Data Protection (TDP) for SQL Backup & Restore, you have to use trail and error method to choose the correct value for the TDP SQL performance tuning parameters. Many factors can affect the backup and restore performance of Data Protection for SQL, such as hardware configuration, network type, and capacity. Some parameters that are related to Data Protection for SQL can be tuned for optimum performance. Similarly, to troubleshoot the TDP SQL Backup & Restore problems, you need to first know the root cause of the problem and then try to troubleshoot it. In this post we will see some of the performance tuning parameters and troubleshooting tips for TDP SQL.
IBM TDP SQL Performance tuning parameters
For TDP SQL other than the parameters in dsm.opt file, some parameters in tdpsql.opt file also plays an important role. The number of threads & sessions used to read/write data from SQL database and TSM server can be changed to improve the backup and restore performance. The default path for tdpsql.opt file is C:Program Files/tivoli/tsm/tdpsql.
Also Read: 3 TDPO utilities to troubleshoot Oracle DB issues
Also Read: 3 TDPO utilities to troubleshoot Oracle DB issues
1) Buffering (Legacy only)
2) Data Striping (Legacy only)
In addition to multi-threading to maximize throughput on a single session, Data Protection for SQL uses separate threads to support SQL data striping, which allows use of multiple parallel sessions to backup and restore a single database. This is another method to maximize data throughput. If a single session cannot fully exploit available bandwidth, multiple parallel sessions can yield improved data throughput, especially if the database is spread across multiple physical volumes.
You can specify the number of stripes to use with the /STRIPes parameter on the command-line interface. You can also specify the number of stripes to use from the MMC GUI, by changing the number in the Stripes field in the Backup options or Restore options panel.
Additional striping does not necessarily improve performance and may even decrease performance if system constraints involving real and paged memory, processors, network interface cards, networks, device reads and writes, and RAID become saturated or exceed capacity.
If you use striping in conjunction with SQL buffers, be certain that the number of SQL buffers specified is equal to or greater than the number of stripes.
3) LAN-free environment (Legacy and VSS)
Troubleshooting IBM TDP SQL Backup and Restore issues
If an error condition occurs during a Data Protection for SQL event, you should first determine the cause of the problem. There are several sources of information you can view to help determine the problem.
Also Read: Increase TSM server performance by following these guidelines
Also Read: Increase TSM server performance by following these guidelines
- Data Protection for SQL logs information, by default, to the tdpsql.log file in the directory where Data Protection for SQL is installed. This file indicates the date and time of a backup, data backed up, and any error messages or completion codes. This file is very important and should be monitored daily.
- The Tivoli Storage Manager API logs API error information, by default, to the dsierror.log file in the directory where Data Protection for SQL is installed. No backup statistics are kept in this log. The dsierror.log file cannot be marked as read-only.
- The SQL Server logs information to the SQL Server error log. SQL Server error log information can be viewed using the SQL Server administration tools.
- The Tivoli Storage Manager scheduler logs information to both the dsmsched.log and the dsmerror.log files. By default, these files are located in the directory where the Tivoli Storage Manager Backup-Archive Client is installed.
- Windows Event Log.
- For VSS operations, view the dsmerror.log file in the backup-archive client installation directory.
- Finally, you can also get some information on TSM Server activity log.
Determining if the problem is with the Tivoli Storage Manager or SQL issue
For Legacy operations
- Use the Backup or Restore utility provided in the SQL Server administrator program to see if the problem can be recreated. If the error message "ACO5350E An unknown SQL API error has occurred" is displayed, the SQL server encountered an unexpected situation. Microsoft assistance may be needed if the problem continues.
- Data Protection for SQL error messages occasionally contain an HRESULT code. Use this code to search Microsoft documentation and the Microsoft Knowledge Base for resolution information.
- For Windows Server 2008 and later, try recreating the problem with the Microsoft DISKSHADOW application. This application is shipped with Windows Server 2008 and later.
For VSS operations
- Try recreating the problem with the Microsoft VSHADOW application. This application can run backups using the Microsoft SQL VSS APIs. If the problem is recreatable with VSHADOW, then the problem most likely exists within the VSS provider or the SQL server.
- Microsoft ships VSHADOW with the Volume Shadow Copy Services (VSS) Software Developer's Kit (SDK). IBM Service can provide a copy of VSHADOW if you encounter problems obtaining or building this application.
- Retry the operation that failed, If the problem still exists, close other applications, especially those applications that interact with SQL such as antivirus applications,and retry. If the problem still exists, restart the SQL server, including the SQL server VSS Writer service retry the operation that failed. If the problem still exists, restart the computer and Run the operation that failed.
Where are the log files for Tivoli Data Protection for SQL ?
Each TDP SQL component is located in its own directory along with its respective troubleshooting log and trace files.
1) Log and trace files for Tivoli Storage FlashCopy Manager
Default Installation directory is c:\program files\tivoli\flashcopymanager
Trace and Log files are
dsierror.log,
fcm.log,
TraceFm.trc,
TraceUx.trc,
TraceManagedCapacityHistory.trc,
TraceSchedLaunch.trc,
VssProvisioning.log
2) Log and trace files for Tivoli Data Protection for SQL
Default Installation directory is C:\Program Files\Tivoli\TSM\TDPSql
Trace and Log files are
dsierror.log
tdpsql.log
TraceFileSql.trc
VSS Requestor
3) TSM Backup Archive Client
Default Installation directory is C:\Program Files\Tivoli\TSM\baclient and the Log file dsmerror.log
4) IBM Volume Shadowcopy Services
IBMVDS.log
IBMVss.log
If the above applications are installed in a path other than the default c:\program files\tivoli the reports will not include task completion, type of data protection activity, and amount of data protection activity for scheduled backup and restore operations.
0 Comment to "Use these troubleshooting tips to fix IBM TDP for SQL backup and restore problems and improve its performance"
Post a Comment