a database testing and qa roadmap n.
Skip this Video
Loading SlideShow in 5 Seconds..
A Database Testing and QA Roadmap PowerPoint Presentation
Download Presentation
A Database Testing and QA Roadmap

Loading in 2 Seconds...

play fullscreen
1 / 32

A Database Testing and QA Roadmap - PowerPoint PPT Presentation

  • Uploaded on

A Database Testing and QA Roadmap. SASQAG July 18, 2002. Ron Talmage. Prospice, LLC. 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'A Database Testing and QA Roadmap' - asta

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
a database testing and qa roadmap

A Database Testing and QA Roadmap


July 18, 2002

Ron Talmage

Prospice, LLC

speaker bio
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@prospice.com
  • 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
why a checklist is needed
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
database independence
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)
database complexity
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
checklist overview
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




Out of Date

Is there a clear narrative stating the purpose of the database system?

Did we reverse engineer it?

Are the database requirements documented?

Did we reverse engineer it?

Is database design documented?

Did we reverse engineer it?

Are database naming and coding standards documented?

notes on discovery 1
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
notes on discovery 2
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
notes on discovery 3
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
notes on discovery 4
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
notes on discovery 5
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
2 test environment



The test database server is separated and isolated from the production server

The test database server has the same configuration as the production server

The test database is synchronized with the production database

The test database structures are current

The test database data is representative of production data

2. Test Environment
test environment notes
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.
3 design



The conceptual design satisfies database requirements

The logical design implements the conceptual design efficiently in entities and attributes

The physical design efficiently and faithfully stores the required objects in the DBMS

The database schema is appropriate for the task

The database schema is properly normalized

The database design is flexible, adaptable, and scalable

3. Design
notes on design
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
4 performance



The database performs within or better than acceptable standards

Index usage is efficient and minimal

The database can handle the required load

The database does not break down under expected levels of stress

The database can scale upwards in size to meet required and expected growth without losing required performance

4. Performance
notes on performance
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
5 availability



The system is protected from disk failure

The system is protected from network failure

The system is protected from CPU failure

The system is protected from site failure

The system is not dependent on a single person

There is a well-documented disaster recovery plan

The disaster recovery plan is periodically tested

5. Availability
availability notes
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.
6 security



All current security patches are applied to the database system

All database logins have strong passwords

All logins have minimal permissions to accomplish their tasks

Application users do not have direct access to tables

The system is protected from SQL injection

6. Security
security notes
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.
7 database code



The database code operates correctly and is adequately tested

Database code follows stated naming conventions and coding styles

Database code is stored separately and is under source code control

Database code is debugged and tested

7. Database Code
database code notes
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.
8 data quality



The data is sufficiently current and timely

The data is accurate

Users find the database data reliable and credible

Redundant data is kept to a minimum

8. Data Quality
data quality notes
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
9 operations



Operations personnel are appropriately and adequately trained

Operations personnel understand and follow the backup plan

Operations personnel understand and periodically rehearse the disaster recovery plan

Operations personnel understand and reliably implement the security plan

Operations personnel enforce change control on database schema and code changes

9. Operations
operations notes
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.
  • 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
  • Contact: rtalmage@prospice.com