This post is based on my experience as Database Tester. Database Testing involves the following Activities for a OLTP System
- Verify Table Schema, Column names as per Design Document
- Verify Column Length and DataType
- Verify Unicode Support (Storing Chinese/Japanese Charaters) - NVARCHAR Datatype
- Verify Indexes on Table (Clustered, Non-Clustered), Triggers for Auditing
- Verify Primary Key and Foreign Key Constraints defined on the Tables as in Design Document
- Verify Default Values of Columns, NOT NULLABLE columns, Constraints defined on Columns
This is more towards White-box testing, Verifing the SQL Statements, Queries in the Procedure from performance perspectice. The objective here is to ensure
- Verify Access Methods (Seeks over SCANS)
- Verify JOINs used and ensure its Optimal (Nested vs Merge Vs Hash)
- Very Logging and Auditing Enabled for Error Handling
- Verify Errors are logged and they contain Error Message, Supporting Details of Error
- Verify Coding Guidelines (Set based Operation vs Cursors, Try-Catch Block). Please refer coding guidelines section
- Verify Isolation levels used. Recommend used of Read-Committed Snapshot isolation level to avoid potential blocking issues
- Verify Rollback is handled in Transactions (Eg. Order Creation is failed due to PK Violation, Entire Transaction should be rolled back, All tables associated with the transaction need to be rolled back)
- Use Profiler, Activity Montiors, DMV queries to find top IO, CPU consuming queries, You can find more details in Performance Tuning Section
Testing the Migration of Data From One Database to Another
SQLbits Session - Database Testing-Minimizing "If it can break, it will."
Database Testing - John Morrison's Blog
Agile database development 101
SQL University: Database testing and refactoring tools and examples
Database Security Testing
Tools for Everyday Testing – Part 1
Tools for Everyday Testing – Part 2
Tools for Everyday Testing – Part 3
Happy Reading!!!
No comments:
Post a Comment