SQL Scanning – Wide/Deep Process
Actual Scanning of SQL DBs is done using the SCAN action, and works much like a filesystem scan. Database and table statuses are mapped to the Files table (if the allowed_statuses are enabled), and hits show up in the Hits table as usual.
The recommended process for scanning SQL databases is as follows. These steps are of course optional and you are free to mix & match how you like. Following this procedure will enable you to get a handle on scanning speed and timing estimates, while warning of any permissions/security blockers in advance of scanning.
1) topnrows 5
Set the topnrows setting to 5 in your scan job and run a scan.
This enables the scanner to work through all the tables and databases rapidly, showing up any table permission/accessibility problems, and giving you a rough idea of the number of tables, columns and databases involved. Problems encountered with table access (if any) can be corrected before running a more comprehensive scan.
2) topnrows 10,000
Clone the previous scan job, change the topnrows setting to 10000 in your job and re-run it. This enables you to get a ballpark idea of your SQL DB scanning performance. This ballpark can be then be combined with table size information from your DBAs for large databases, which can be useful when determining whether e.g. an overnight, weekend, or week-long scan window is needed, and what sampling policy if any, is sufficient to your needs.
3) topnrows 100,000
As above, this setting will give a better speed estimate ballpark, but be more time-consuming as a scan. “First 100k rows” is also often a sufficient & recommended table sampling policy for situations where database sampling is sufficient to meet PCI Compliance needs, or entire-table-scans are too impractical. Check with your QSA/ISA if you are unsure.
4) topnrows 0
This setting scans the entire table, no matter how large it is. It is recommended to only engage this setting once you have worked through a rough sizing/timing estimation as above and scheduled the appropriate job scan windows.
Note: Useful information relating to SQL Scans is logged to the job’s Messages table, for example table number counts, queries used, and whether a table has more rows than topnrows for that query.