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@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. 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? 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. 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 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. 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 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. 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 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. 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 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. 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 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. 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 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. 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 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. 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 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@prospice.com