Friday, April 16, 2010

WebInspect's scan repository and full SQL Server

HP WebInspect defaults to using Microsoft SQL Express as its scan repository and reporting workspace, but one could instead use a full SQL Server.

WebInspect 8 SP2 (January 2010) added support for SQL 2008, not just the existing SQL 2005 allowed. The benefits of using a full SQL Server is that there is theoretically no limit on the individual scan size, compared to the 4 GB limit native to SQL Express. It can also be managed and backed-up via the corporate database administration instead of being at risk stored on one local hard drive.

One additional item is that the HP support team indicates a possible scan performance increase of up to 15% when the full SQL Server component is not installed on the same machine as WebInspect. This has more to do with Microsoft SQL than with WebInspect. I have no such statistics on the comparison between using SQL or SQL Express on the local machine, this fact is just for the case of full SQL version.

To make use of a full SQL Server, you will need the following guidelines.

* Do not install SQL Express, only install WebInspect. If you are already using SQL Express, that is fine, keep it around until after you transfer your saved scans.

* Configure the WebInspect scan repository connection manually by opening the Edit menu > Default Scan Settings > Data Connectivity panel.

Fill in the server name, credentials, and the database name, e.g. "WebInspectScanDB". The database utilized should not be used by any other applications, but it may reside on a SQL Server or cluster alongside other databases without any issue. The dialogs on this screen allow the user to create the new database on-the-fly, assuming that the current Windows user or supplied SQL credentials have DB Owner rights to the target SQL Server.

After the creation of the database, the connection credentials can be downgraded to simply DB Administrator privileges if the SQL Server administrators prefer. Alternatively, the administrators could generate the blank database ahead of time for the WebInspect user, leaving them with only DBA rights to this blank database.

* Configure the WebInspect reporting database connection by manually opening the Edit menu > Application Settings > Reports panel.

Just as before, fill in the server, credentials, and a different database name, e.g. "WebInspectReportingWorkspace". This cannot be the same as the one used to store the scans! This database can be created on-the-fly and has the same DB privileges requirements as the scan repository created in the prior step. This will be a workspace used by WebInspect when generating reports.

* Return to WebInspect's Start Page tab and open its "Manage Scans" section. Configure the Connections button to ensure this screen is now showing the contents of the scan repository database configured previously. If you happen to also use SQL Express for WebInspect, this screen allows you to list the contents from both databases at the same time. Bear in mind that "local" means "SQL Express" and "remote" indicates full SQL Server (whether on localhost or not). If this is not clear, customize the available columns to include the Location and/or Database column.

* Two or more WebInspect may share the same SQL database for scans, but it is not optimal. There could be conflicts if multiple users open the same saved scan simultaneously and begin modifying it. Since the reporting workspace is temporary, it is probably fine to share among several users.

* Lastly, keep the SQL Server logically close to the WebInspect machine. Do not try to store your real-time data across the English Channel or some such enormous distance and then complain of the latency or product instability. I am not making this up.

AMP Sensor

If you happen to be a customer using HP AMP (Assessment Management Platform), all the aspects of installing WebInspect apply equally to the AMP Sensor, those remote workstations that actually perform the AMP assessments. These two products use the same install file, although with slightly differing install folders. AMP Sensors also default to using SQL Express, or can be configured to use full SQL Server. They also benefit from using a remote rather than local SQL Server. And unlike WebInspect, they can share a single scan repository database as they only store their current scan there. Once completed, the scan is uploaded to the AMP Manager server's database and the Sensor's copy is wiped.

1 comment:

  1. This particular posting predates the WebInspect 9.0 release. Prior to 9.0, WebInspect required two databases, one for storing scans and one as a workspace for generating reports. After the 9.0 release, the reporting handles itself quietly (perhaps inside the remaining database?) and the user is only required to configure and use a single SQL database for storing scans.

    Also, with that 9.0 release, the database configuration moved from WebInspect's Default Scan Settings > Data Connectivity panel to now reside under the Application Settings > Database panel.