Transcript Session Title - Seattle Area Software Quality Assurance Group

SASQAG
July 18, 2002
A Database Testing
and QA Roadmap
Ron Talmage
Prospice, LLC
1
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)
– [email protected]
2
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
3
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
4
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)
5
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
6
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
7
1. Discovery
Yes
No
Current
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?
8
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
9
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
10
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
11
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
12
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
13
2. Test Environment
Yes
No
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
14
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.
15
3. Design
Yes
No
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
16
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
17
4. Performance
Yes
No
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
18
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
19
5. Availability
Yes
No
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
20
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.
21
6. Security
Yes
No
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
22
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.
23
7. Database Code
Yes
No
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
24
Diagram: Database Code
25
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.
26
8. Data Quality
Yes
No
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
27
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
28
9. Operations
Yes
No
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
29
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.
30
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
31
Thanks
• Contact: [email protected]
32