DBpulse Getting Started - Windows

DBpulse: Getting Started - Windows

Thanks for trying out DBpulse. As with any software package, user feedback is very important. Please contact us on the Web at: www.dbpulse.com or by e-mail at: info@dbpulse.com.

This README file is divided into these sections:

  1. General Concepts
  2. Installation Guidelines
  3. Running the Software
  4. Additional Examples
  5. Uninstall Guidelines

Have fun!

DBpulse General Concepts

DBpulse is designed to serve three main purposes:

  1. diagnose SQL performance problems
  2. audit SQL transactions
  3. benchmark SQL applications

To accomplish this, it captures network packets, rebuilds sessions, decodes TDS, and writes output. The result of all this processing is simply two file types: RATING and THRESH.

The RATING files are used to benchmark applications, whereas the THRESH files are used to diagnose performance problems and audit SQL transactions. Both types of files are rotated on a 15 minute basis (i.e. the default RUN interval is 15 minutes but can be changed). Thus, every 15 minutes two new files are created and the contents of a particular file pertain to 15 minutes of activity.

The RATING file stores data for benchmarking purposes. It contains:

  1. Query type ([A]ttention, [L]ogin, [M]arker, [Q]uery, [R]PC)
  2. Query end time
  3. Query elapsed time
  4. Query status
  5. Source IP address
  6. Servername (Instance Name)
  7. Internal Session ID
  8. Server.Database.Username
  9. For type 'M': Marker name
  10. For type 'M': Marker start description
  11. For type 'M': Marker end description

Note that the RATING file does NOT store the actual query itself. The most important items in the RUN file are Query elapsed time and Query status. Those are used to calculate Rating and Load values. To find out more about Rating and Load values, see the white paper about performance management using DBpulse.

The THRESH file stores all the items in the RATING file and more. It contains:

  1. Query type ([A]ttention, [L]ogin, [M]arker, [Q]uery, [R]PC)
  2. Query end time
  3. Query elapsed time
  4. Query status
  5. Source IP address
  6. Servername (Instance Name)
  7. Internal Session ID
  8. Server.Database.Username
  9. Query threshold value
  10. Result set row count
  11. Packets to server
  12. Bytes to server
  13. Packets to client
  14. Bytes to client
  15. For type 'A': ATTENTION
    For type 'L': LOGIN
    For type 'Q': Actual query string
    For type 'R': Actual query string
    For type 'M': Marker name
  16. For type 'M': Marker start description
  17. For type 'M': Marker end description

The THRESH file is used to identify the most pathological queries (those queries that take the longest time to run). The default behavior of DBpulse is to log ALL (threshold = 0.0) queries, thus the THRESH file becomes an audit file of every query sent to the database server.

In its normal mode of operation, DBpulse only outputs to the THRESH file. To limit the queries that are written to the THRESH file, use the -t option.

For example: -t 5.0 sets the threshold at 5.0 seconds.

Again, for audit purposes, don't use the -t option (which sets the threshold to 0.0). To completely turn off the query output to the THRESH file, use the -no_thresh option. With this option set, no output will be sent to the THRESH file.

You can turn on the Rating feature by setting the -rating option.

NOTE: Rating is a premium feature of DBpulse and has an associated cost. This option tells DBpulse to write benchmarking data to the RATING files. It also keeps track of the interval and creates a new RATING file when the interval is up.

DBpulse provides real-time interaction through a TCP connection. Currently, the information it provides is read-only. That is, it reports on internal data structures such as packet capture statistics, TDS session values, and RAM buffer allocation. It does not allow a user to change the behavior of DBpulse. Currently, there is no user authentication so the data is viewable by any user with access to the appropriate port.

For example: -l 2500 sets the TCP listening port to 2500

To connect to DBpulse: telnet {hostname} 2500

There is a special case of the -l option, and that is to use -l by itself. This tells DBpulse to use the default TCP listening port of 21107.

For additional information about DBpulse and its configuration options, please review the man page (/foresight/man/man1/dbpulse.1) or html page (/foresight/docs/dbpulse.html).

Installation Guidelines for Windows systems

NOTE: VPN client software such as CheckPoint's Secure Remote wreck havoc with DBpulse's packet capturing technology. We strongly recommend that you uninstall such products before installing DBpulse.

There is a slight chance that you may need to reboot the computer that this software is installed on, so please perform the installation during non-peak hours.

The installation process generally takes less than 10 minutes. We recommend you become familiar with entire process before starting. Note that DBpulse requires a license file in order to run. An evaluation license file is provided free of charge but has some limiting functionality.

Where should you install DBpulse?

The simplest place is on the database server itself. That way, all traffic can be monitored. For test purposes, you can install DBpulse on a client machine (Web/App server or PC); but you will only see queries made from that client machine. Or, if you can get access to a span/mirror port on a switch or your network uses a hub (that can see all traffic going in to and out of the database server) and you can connect a computer to it, that will work as well.
  1. Log in as 'Administrator' or a user with Administrator rights. DBpulse needs administrator rights because it opens the network port and sets it to promiscuous mode.
  2. Run the installer (assumes you have the software bundle in C:\temp):
        C:\> C:\temp\DBpulse-3.1.6174.exe
            
  3. Click 'Next' on the splash screen.
  4. Read the license agreement; click 'I Agree' if you agree with the terms.

    DBpulse depends on WinPCAP to read network packets; WinPCAP is bundled with this distribution. It contains a kernel-level packet filter and various DLLs to do the packet capturing. If you already have WinPCAP 4.0 or newer installed on your computer, uncheck the WinPCAP box.

  5. Click 'Next'

    We recommend C:\foresight as the install directory

  6. Click 'Install'
  7. Click 'Close'
The installation process will perform the following steps. This documentation assumes you have installed the software in C:\foresight.
  1. Install DBpulse executables and documentation into the C:\foresight tree.
  2. Create standard Uninstall entries in the Registry
  3. Add a K_HOME environment variable to the current user.
  4. Append %K_HOME%\bin to the PATH of the current user
  5. Install the 'dbpulse' service in 'Manual' mode.

Running DBpulse for Windows systems

DBpulse requires a license in order to run. An evaluation license comes in the distribution tree, however, it limits the analysis to 2,000,000 packets. After 2,000,000 packets, DBpulse shuts down. If you wish to analyze the data stream further, you must re-start DBpulse (or purchase a registered license at the DBpulse Web Site).

Here's how to use the evaluation license:

  1. Log in as 'Administrator' or a user with Administrator rights.
  2. Copy the evaluation license file into the real license file:
        C:\> cd \foresight\etc
        C:\foresight\etc> rename license_dbpulse license_dbpulse.old (Optional)
        C:\foresight\etc> copy eval_dbpulse.lic license_dbpulse
              
NOTE: if you already have a valid license in license_dbpulse, DO NOT overwrite it with the evaluation license without making a copy!

DBpulse can be run in two different ways:

  1. a standalone executable used to troubleshoot a specific problem (i.e. run for a short period of time)
  2. a service running 24x7 used to analyze all sessions for service assurance. The dbpulse service is set up during the install, however, it is set to 'Manual' mode and must be started.

Before running DBpulse, you must edit the C:\foresight\etc\hosts_dbpulse file. This is the default filename for hosts. It can be changed using the -H option.

For example:

    #                       Host            TCP     Net             Rating
    # Servername            IP_address      Port    Proto   Thresh  Flag
    # --------------------  --------------  ------  ------  ------  ------
      dbms2                 192.168.1.52    1433    mssql   1.0     1
    #
    

This entry indicates you want to capture queries going to the 'dbms2' server. The IP address is 192.168.1.52 and it is using the standard SQL Server port of 1433. The network protocol is MS SQL Server. The long running query threshold time is one second and the rating flag is turned on.

To run DBpulse in standalone mode, you must give the device name of the ethernet port; however, Windows device names are non-intuitive. To find the proper name, use the program 'find_all_devices.exe'. For example:

    C:\foresight\bin> find_all_devices

      1.  Device: \Device\NPF_GenericDialupAdapter
         Comment: Adapter for generic dialup and VPN capture
      2.  Device: \Device\NPF_{8A71110E-38F4-4A4F-8410-AA020526B348}
         Comment: FETND5BV1 VIA Rhine II Fast Ethernet Adapter                          
          Family: 2
         Address: 192.168.1.52
    

The name of this device is: \Device\NPF_{8A71110E-38F4-4A4F-8410-AA020526B348}

Note: some PCs have multiple network interfaces. Make sure you select the proper device name, otherwise DBpulse will be sniffing on the wrong interface.

Most of the command line options can be put into a DBpulse configuration file. The default filename is C:\foresight\etc\dbpulse.cfg. The configuration file can be changed using the -f option. In addition, memory buffer size parameters are put into the configuration file.

Here is a sample DBpulse configuration file:

    Pcap_Device=\Device\NPF_{8A71110E-38F4-4A4F-8410-AA020526B348}
    Query_Threshold=5.0
    

This configuration file sets the packet capture device name and a default query threshold of 5.0 seconds. There is also a query threshold setting in the hosts file which overrides this default setting.

With this configuration file, you can run dbpulse very easily:

    C:\foresight\bin> dbpulse
    

Without a configuration file, the command line to do the same operation looks like this:

    C:\foresight\bin> dbpulse -d \Device\NPF_{8A71110E-38F4-4A4F-8410-AA020526B348} -t 5.0
    

For a more complete list of options, please review the DBpulse html documentation.

Once DBpulse is running, you can use any client application (from a remote PC because DBpulse does not see traffic on the loopback interface) to test it. As soon as DBpulse sees database traffic, it processes the packets. Try a few queries and then review the RATING and THRESH files. To stop DBpulse in standalone mode, simply press Ctrl-C.

If you wish to interact with DBpulse when it is running, you must use the -l option (TCP listen port). When this option is set, DBpulse listens to a TCP port (limited to 32 concurrent sessions) and outputs information about its internal data structures. These internal structures contain information such as command line parameters, uptime, software version, RAM utilization, packet capture statistics, and query information.

To run DBpulse in listening mode, type:

    C:\foresight\bin> dbpulse -l
    
The default TCP listening port is 21107. To change this, enter a port number immediately behind the -l option. For example: -l 25000

Alternatively, you could put the listening port parameter into the configuration file rather than using the -l option. The configuration file would look like this:

    Listen_Port=25000
    Pcap_Device=\Device\NPF_{8A71110E-38F4-4A4F-8410-AA020526B348}
    Query_Threshold=5.0
    

To connect to DBpulse, use telnet.

    C:\foresight\bin> telnet localhost 25000
    

Once connected, type 'help' to see the available options. It's a simple interface. The main command is 'get'. Try: get info. This outputs the start-up parameters. Try: get perf. This outputs DBpulse performance data. Try: get stats all. This outputs all statistical data including packet capture numbers, RAM allocation, TDS session values, thread processing data, and string buffer usage. When done reviewing DBpulse internal data structures, type: quit to disconnect.

To run DBpulse in service mode, you need to start the service. You can either use the Services GUI and click 'Start', or from the command line, type net start dbpulse. If you want this service to run automatically (every time the machine is rebooted), then use the Services GUI and edit its properties to start automatically.

To stop DBpulse in service mode, type net stop dbpulse. For additional ways to stop DBpulse, see the DBpulse html doc.

Additional EXAMPLES of DBpulse parameters

    dbpulse -h fsa01 -p 5500 -S SQL_PROD -x 255
    

This example turns on FULL debugging. In most cases, this is way too much information and slows down the packet capture tremendously. We recommend setting the debugging level above 3 ONLY for a short period of time or on a desktop PC (which doesn't affect SQL Server's performance). This example also performs a full audit. Every query sent to the database will be captured in the THRESH file.

NOTE: In general, you SHOULD NOT run in debugging mode. Outputing debug information can reduce efficiency by several orders of magnitude.
    dbpulse -h fsa01 -p 5500 -S SQL_PROD -t 10.0
    

This example tracks long running queries. That is, any query that takes longer than 10.0 seconds is written to the THRESH file.

    dbpulse -h fsa01 -p 5500 -S SQL_PROD -t 10.0 -rating
    

This example is a refinement of the one above. It still tracks long running queries, but also writes benchmarking data to the RATING files.

    dbpulse -h fsa01 -p 5500 -S SQL_PROD -o /tmp/fsa01.pcap
    

This example captures all packets coming in to and going out of fsa01 on port 5500 and writes them to a file named /tmp/fsa01.pcap. It performs no processing of the packet stream (other than writing it to a file). This file can be used in the future to playback a sequence of packets. Neither the RATING nor the THRESH files are updated.

NOTE: because all of these examples are in standalone mode, you will need to type Ctrl-C at the console to shut the program down.
    dbpulse -h fsa01 -p 5500 -S SQL_PROD -o /tmp/fsa01.pcap -c 100000
    

This is a refinement of the previous example where the program stops after capturing 100,000 packets.

    dbpulse -i /tmp/fsa01.pcap
    

This example opens a file named /tmp/fsa01.pcap and uses it rather than a network device for packet input. It processes the file as if the packets were coming from the network.

Uninstall Guidelines for Windows systems

DBpulse has an uninstaller program. The DBpulse uninstaller program will allow you to uninstall both DBpulse as well as WinPCAP. There are two ways you can run the uninstaller. Both require that you be logged in as 'Administrator' or a user with Administrator rights.

Control Panel

  • Use the Control Panel to uninstall DBpulse and/or WinPCAP. From the Control Panel:
    1. Choose Add/Remove Programs
    2. Find the DBpulse entry and click on it.
    3. In the highlighted box, click on 'Change/Remove'. The uninstall program should appear.
    4. This uninstall program will indicate which directory it thinks is the DBpulse distribution. If this is the proper directory, click 'Uninstall'.
    5. Click 'Close'.

Start Menu

  • Use the 'Uninstall' link in the Start Menu. From the desktop:
    1. Click the 'Start' menu
    2. Choose 'Programs'
    3. Choose 'DBpulse {VERSION}'
    4. Choose 'Uninstall DBpulse {VERSION}'
The DBpulse installer will perform the following steps:
  1. Remove the 'dbpulse' service.
  2. Remove files installed by the installer
  3. Remove EMPTY directories created by the installer
  4. Remove registry entries created by the installer
  5. Remove the 'Start Menu' links (if they were installed)
If you have other Foresight Automation products installed, the C:\foresight directory will not be empty. Also, any configuration files and data files generated will also remain. To remove the DBpulse data files, you should examine the directories in C:\foresight\var\dbpulse. This entire directory can be removed without affecting any other Foresight Automation software. If you have no other Foresight Automation software, you can be safe in removing the entire C:\foresight directory.