Zero Footprint DBACOCKPIT for remote SQL Server instances
Purpose of the tool
Do you manage a large number of SQL Server instances running many different applications? Do they encounter critical performance problems from time to time? If that’s the case then this new DBACOCKPIT feature may be helpful to you.
The transaction DBACOCKPIT in NetWeaver is used to monitor the database the system is running on. It can also be configured to monitor remote databases. The database system being monitored can be any of the database management systems supported by NetWeaver.
DBACOCKPIT for SQL Server provides a set of tools which are installed in the database. This includes SQL Agent jobs which are used to collect a history of performance data along with several stored procedures, tables and views which are used to store and manage the data. These objects are stored in a schema which is created in the database. We refer to this schema as the “object source” schema.
In some cases it might be problematic to create foreign objects in the SQL Server being monitored. Sometimes it’s only necessary to do a quick performance analysis without creating any objects that then would have to be removed afterwards. It would be nicer to have a more lightweight option. This is why we created this new zero footprint feature in DBACOCKPIT.
As a prerequisite a SQL Server login must exist on the remote SQL Server with sufficient privileges to monitor the database. We recommend to use a SQL Server login which is a member of the sysadmin server role.
Then enter DBACOCKPIT in your NetWeaver system (your Solution Manager system for example).
On the initial system configuration maintenance screen choose “Add”:
Enter a name for the new system and a name for the new database connection. They can be the same or different:
Pres <RET> to continue.
Now enter the SQL Server login name in the “User Name” field and fill out the password, server instance name and database name. Enter the special “Object Source Name” ZF :
The Object Source Name must be exactly ZF, nothing else. This indicates that no object source schema will be created or used for this connection.
Press the Save button and press it again for the system configuration entry. You should see the message “System entry ZFPSYS has been saved and activated” in the message window at the bottom of the screen.
Finally switch to the system using the system selection dropdown:
Using DBACOCKPIT in Zero Footprint mode
When you connect to the system which is set to zero footprint mode in DBACOCKPIT you will see a message in the bottom message window stating that for example:
“16 actions disabled. Reason: Action is not supported in a zero-footprint connection”.
But many critical features are still available and can be used to do urgent performance analysis.
Most of the major screens in DBACOCKPIT do work fine in zero footprint mode. The DB02 (Space Overview) screen works without limitations but the ST04 (Performance Overview) screen works with some items disabled. A warning message will appear when you enter the Performance Overview screen: “This action is operating in zero footprint mode. Some display items are disabled”. This means that some fields that contain information from the DB collector history data cannot be filled and do not appear on the screen.
Performance -> Database Processes does work fine and so does SQL Statements. All the related actions which are used in those screens also work. That includes Explain, Single Table Analysis and Index Analysis.
The I/O Performance action does not work in the same way as the normal DBACOCKPIT action. Instead there is only a simple list of files showing the content of the sys.dm_io_virtual_file_stats management view.
Many other important actions are fully enabled. This includes Diagnostics -> SQL Error Logs, Performance -> Locks and Memory and Space -> Largest Tables.
Any action that relies on history data cannot be implemented in zero footprint mode. This includes all the Performance -> History actions.
In addition none of the actions related to DBA calendar, backup and restore history and SQL Agent jobs can be used.
This option in DBACOCKPIT is intended for use in situations where serious performance problems exist in SQL Server instances which run non-NetWeaver software. DBACOCKPIT for SQL Server was designed originally for NetWeaver systems only, but many features are independent of the application which is running on the server. We expect this feature to be used primarily for remote SQL Server instances which do not run NetWeaver.
Here are some of the possible use cases:
- SAP BW data sources are frequently defined for remote SQL Server databases using DBCON database connections. These connections can be used directly in DBACOCKPIT if the ZF object source is set on the connection. Use transaction DBCO to edit the connection and modify the OBJECT_SOURCE field in the “Conn. info” field. Make sure it looks like this:
Then enter DBACOCKPIT and add a system which uses this DBCONN connection:
Save this and the system ZFPSYS can be used for monitoring.
- The SLT product from SAP can be configured to replicate tables in any SQL Server user database. The zero footprint option is ideal to quickly analyse performance problems in the SLT replication process. Again the actual connection used in SLT can be used directly in DBACOCKPIT with object source set to ZF.
- SAP Identity Management (IDM) runs on SQL Server. If there are performance problems in IDM SAP Support can help directly if a DBACOCKPIT with zero footprint is set up.
- Many customers have third party applications or customer written applications running on SQL Server. A zero footprint connection can be used by the customer or by SAP support to help with performance issues.
We recommend to set up the zero footprint connection temporarily to be able to quickly analyse problems on the server. When the problem is solved the DB connection and the DBACOCKPIT system can be deleted and the login on SQL Server which was used can be disabled or dropped.
The zero footprint feature is only available in recent support packages of NetWeaver 7.0 Ehp2 and higher. The feature cannot be applied via SNOTE.
The feature was provided with the following SAP_BASIS support packages:
Good job, thank you for sharing