A Database Testing and QA Roadmap

1
A Database Testing and QA Roadmap

• SASQAG
• July 18, 2002

Ron Talmage
Prospice, LLC
2
Speaker Bio

• Ron Talmage
• Microsoft SQL Server MVP
• President, Pacific NW SQL Server Users Group
• Developed and currently teach SQL Server for
  Testers' for MSTE
• Contributor to SQL Server Magazine
• Columnist with SQL Server Professional
• Author, SQL Server 7.0 Administrator's Guide
  (Prima)
• Owner and operator, Prospice LLC
  (www.prospice.com)
• Rtalmage_at_prospice.com

3
Goals

• Develop a checklist of essential points for
  testing the back end database
• as a stand-alone module
• as a component of an application .
• Develop the checklist in a top-down fashion
• We assume that the tester or QA auditor has
  acquired specialized database knowledge

4
Why a checklist is needed

• A checklist is needed to help achieve coverage of
  the database component.
• A good checklist can help organize a testing and
  QA strategy.
• Achieving coverage is difficult because of
  database
• Independence and
• Complexity

5
Database Independence

• The database system represents an independent
  component of an application because it
• is accessed via both application and database
  utilities
• has its own security system
• requires independent maintenance procedures
• may be used by more than one application
• may have dependencies outside the application
  (data import and export)

6
Database Complexity

• Organizing a strategy for testing and QA can be
  difficult.
• A database is a feature-rich and complex software
  product in its own right
• Testing a database requires specialized knowledge
  of
• Configuration parameters
• Maintenance procedures for security, backup and
  recovery
• Memory and disk tuning
• Querying using SQL with vendor-dependent dialects

7
Checklist Overview

• Our Roadmap/Checklist is based on the following
  organizing concepts
• 1. Discovery
• 2. Test Environment
• 3. Design
• 4. Performance
• 5. Availability
• 6. Security
• 7. Database Code
• 8. Data Quality
• 9. Operations

8
Discovery
9
Notes on Discovery (1)

• Discovery means learning about the database and
  gaining an understanding
• You may have to reverse engineer each step if the
  documentation is inadequate
• Only the simplest databases can get by without
  documenting these features
• Out of date documentation is a common and serious
  problem

10
Notes on Discovery (2)

• What is the purpose of the database?
• The purpose of each database or schema
• What users must have access to it, and how
• What role the data plays in the application
• How the application accesses the data
• How and where data is imported from, and exported
  to
• How the database servers are named, organized,
  and connected

11
Notes on Discovery (3)

• What is the database required to do?
• The data elements that must be stored in the
  database
• The dependencies and relationships among these
  data elements
• How they are inserted, updated, and deleted
• What elements belong together
• How they must be collected, and timing issues

12
Notes on Discovery (4)

• What is the database design?
• Should include the central conceptual facts and
  assumptions used in the database system.
• A diagram of the logical entities and attributes
  (ER diagram or equivalent)
• A physical diagram/report of how the design is
  implemented

13
Notes on Discovery (5)

• What naming convention does the database follow?
• Objects in the database should follow a naming
  convention for
• Tables, columns, indexes, constraints, stored
  procedures, views, triggers, functions, etc.
• What coding standards are in place for database
  code?
• Stylistic standards for SQL statements (upper,
  lower, mixed case)
• Standards for indentation, commenting, and
  keyword case

14
2. Test Environment
15
Test Environment Notes

• Configuration can be extracted from the existing
  production server (or development server) and
  compared with the test server database.
• If there is no production server, and the
  implementation of the database is still in flux,
  then it is much more difficult to synchronize the
  test server.

16
3. Design
17
Notes on Design

• Database design has three levels conceptual,
  logical, and phyiscal
• Poor performance is often due to bad design
• Test and QA may have to reverse engineer what the
  actual requirements are, in terms of
• data storage
• constraints and business rules
• scalability and performance

18
4. Performance
19
Notes on Performance

• Acceptable performance standards must be defined
  and agreed upon
• Queries use indexes to improve performance
• Load establish that the system can sustain the
  required load
• Stress find the maximum load that the system can
  sustain
• Special tools are available for load and stress
  testing
• Scalability goals must be defined

20
5. Availability
21
Availability Notes

• Redundant hardware such as SAN, multiple NICs,
  redundant routers, and standby servers are
  commonly used to satisfy these requirements.
• Site redundancy can be a tough issue due to
  expense and complexity.
• A full disaster recovery plan is a must for any
  mission-critical database system, and it needs
  testing.

22
6. Security
23
Security Notes

• Database software vendors are supplying security
  patches
• Strong passwords can be tested using password
  generating tools
• Logins should never have more permission than
  they need
• Users should access data through some API views,
  stored procedures, etc. This adds flexibility as
  well as additional security.
• Injection is a kind of buffer overrun, where SQL
  syntax can be hacked.

24
7. Database Code
25
Diagram Database Code
26
Database Code Notes

• Typically, database code is stored in the
  database (stored procedures, triggers, etc.)
• Database code does not typically interface with
  source code control systems
• Some database systems allow online editing,
  objects are not locked.

27
8. Data Quality
28
Data Quality Notes

• Databases continue to grow and change after
  release to production
• Quality needs to be periodically checked testing
  data quality is an ongoing process
• Accuracy and timeliness must be sampled
• User feedback required for credibility
• Redundancy can undercut credibility

29
9. Operations
30
Operations Notes

• Sometimes operations personnel at a large data
  center can only handle one function, such as
  backup.
• Diverse operations personnel may be required for
  disaster recovery, security, and schema/code
  changes.

31
Conclusion

• A good checklist can help
• achieve testing coverage of the database
  component
• help organize a testing and QA strategy.
• A good checklist treats the database as
• An independent component of the application
• A complex and feature-rich software product in
  its own right

32
Thanks

• Contact rtalmage_at_prospice.com