SQL Server activity monitoring (Guardium): pre-deployment questions
Checklist of questions to ask the vendor before deploying a database audit and monitoring tool on SQL Server.
This checklist gathers the questions to ask a vendor before deploying a database activity monitoring tool (for example IBM Guardium and its STAP agent) on a mixed SQL Server estate. It gives no answers: it is a preparation base for the discussion with the vendor.
Estate compatibility
- Is the tool compatible with every SQL Server version and edition in use (older versions included, Standard and Enterprise editions)?
- Are the Windows Server versions involved (including the oldest ones) supported?
- Are servers running another DBMS (for example an old Sybase ASE) supported?
- Cumulative updates and service packs are applied regularly: should the tool be part of the deployment process? Changes are validated through several environments (development, test, acceptance, production): must the tool be present at each stage?
Data collection
- How does collection work: through the SQL API (like SQL Profiler), through Extended Events, or by network or memory interception?
- Are inbound queries and results (messages, data) collected? The SQL text sent, data modifications, the result of a query?
- Does collection work with an encrypted connection (SSL/TLS) between the application and the database?
- How are indirect modifications tracked:
- schema-bound view or indexed view;
- stored procedure that runs SELECT and UPDATE at the same time;
- SQL replication (specific protocol);
- linked server between instances;
- cross-database ownership chaining (a procedure in database A modifies database B) and SQL Agent jobs?
- Some applications work directly in
tempdb: is this covered? - Can a tool that monitors resources and high availability (for example Spotlight) disturb collection?
- Are XA transactions supported?
Database options
- The recovery model (
SIMPLE,BULK_LOGGED,FULL) changes what is kept in the transaction log: can it affect collection? - Does snapshot isolation (
SNAPSHOT ISOLATION) have any effect on the tool?
Recovery
- After a disaster or a bad deployment, does the restore procedure need to change? Are the existing backup tools (for example TSM or the built-in SQL Server tools) compatible with the agent?
- Are database snapshots (used for a quick rollback during a deployment, not to be confused with snapshot isolation) compatible?
Resources and performance
- What is the impact on server CPU, memory and storage? Should the configuration be adapted (RAM, CPU affinity)?
- Resource Governor is configured: is there any interaction?
- For multi-instance servers, is one agent installation needed per instance?
- Is collection synchronous or asynchronous? Can it increase server response time?
- With several thousand queries per second at peak, how does the tool cope with the load?
High availability (Always On)
Example topology to document before deployment: four nodes across two sites (one active read/write node and one passive backup node on site 1; one passive read-only reporting node and one passive spare on site 2), each node connected to three networks (management, heartbeat, application).
- Since data is reachable through several nodes and several networks, how does the tool handle this multiplicity?
- Some application deployments (for example SharePoint) require disabling high availability, removing the link with the replicas and rebuilding them: what is the impact on the tool?
Data masking
- How does masking work: network traffic interception or another mechanism?
- How should access be managed, given that masking depends on the user or group type?
- Is masking compatible with Kerberos multi-hop delegation (single sign-on between user, reporting server and SQL Server)?
Auditing
- How can an application query be correlated with the end user when the application (for example Java) connects with a generic account?
- Does the tool add data to the SQL packets?
- How does capture work at several thousand queries per second?