Edge Case Testing for the Database Professional

1. Edge Case Testing for the Database Professional Vicky Harp

2. About Me – Vicky Harp • Product Manager at Idera • Community Manager at Idera • http://community.idera.com • Twitter: @vickyharp • Email: vicky.harp@idera.com

3. Let’s talk bugs • All software has failure conditions • Corruption • Tampering • Most software has bugs • Errors in logic • Errors in execution

4. Let’s talk bugs • Grey areas • Scalability • Platform and Hardware Compatibility • Language and Regional Support • If a customer experiences a problem, it’s no longer a grey area – it’s a bug

5. The Blame Game • Blame the developer • All development done as sysadmin on a local Developer edition server with no test data • Data access layer design treated as an afterthought • Cowboy code • “Cool tricks” that rely on insecure, unstable, or deprecated functionality • Continuing to use deprecated features • Absurdly low fault tolerance • Overuse of ad hoc SQL

6. The Blame Game • Blame the DBA • No access to reasonable development environments • No availability of QA environments • QA environments used as semi-production • Uneducated use of obscure configuration options, trace flags, and data topology • Database compatibility modes • Failure to maintain server as a stable code platform • Mismatched tempdb collation • Triggers in msdb

7. The Blame Game • Blame the designer • Preposterous object names • Trailing spaces • Special characters • Over/Under-normalization • User objects in system databases • Underdocumentation

8. The Blame Game • Blame management • No time allotted for QA • Bugs are expected to be fixed too quickly • Insufficient personnel • Insufficient hardware • Unreasonable expectations

9. Setting Boundaries • Define expectations for what is and is not supported in your application • Document, communicate, enforce, and maintain these boundaries • Example: SQL 2005 SP2 through SQL 2008 R2 on case insensitive, US-regionalized English language instances

10. Enter QA • All software, internal or external, application or database, needs QA • Professionals test their code • Not testing your code is unprofessional

11. Creating Test Cases • Most testing done in a small environment is ad hoc • Did this change work? • More robust testing relies on test cases and use cases

12. Use Cases • Expected behavior for the application • Example: • When User clicks the Cancel button: • The window will close • No data will be changed • When User clicks the OK button: • The window will close • Data will be saved • If there is an exception, retry twice before returning an error message

13. Test Cases • Test definition with expected output • Example: • Precursors: Product is running on a supported platform but the SQL Server is offline • User clicks Cancel • Expected: Window closes • User clicks OK • Expected: Exception returned to user

14. Using Test Cases • Almost no limit to how highly documented test cases can be • If you’re starting from 0, a good start is to start making checklists • Things to check before accepting a build • Things to check after failing over a cluster

15. Test Plans • A collection of test cases is a test plan • The best case scenario is to write the test plan before the feature • Realistically this often needs to be done with an application in-situ

16. Types of Test Cases • Main Success Cases • Edge Cases • Corner Cases • Others? It depends

17. Main Success Cases • What most people think of when they think of testing • Test case that supports a use case • Example: • Use Case: User runs sales forecasting report • Main Success Case: Report returns correct data with no error messages within 30 seconds

18. Edge Cases • Test cases which explore the boundaries of an application • The boundary may be based on the use case or based on technology • Based on use case: User clicks all the buttons on a view • Based on technology: What is the behavior of the application on SQL 2000?

19. Edge Cases • Ideally edge cases should be identified at design time • This is part of good programming practice, regardless of language • DBAs should be aware of where their configuration is introducing new edge cases that may not have been accounted for

20. Edge Cases • Most common edge for databases is datatypes • Name field backed by an nvarchar(100) column • Edge case: NULL • Edge case: 0-length string • Edge case: 100 character Unicode string • Primary key on a smallint column • Edge case: key seeded at 0 and 32,768 rows inserted • Edge case: key seeded at -32,768 and 65536 rows inserted

21. Corner Cases • The intersection of two cases • Not necessarily the intersection of two edges • Example: • Mail merge – inserting a customer’s name into a message, then saving the text to a column • Corner cases • Special character in either name or message, plus • Extremely long string for either name or message

22. Corner Cases • SQL Server is especially prone to corner cases • Shared resources are a common source of corner cases • Shared disks • Shared tempdb • Shared memory • Be aware that configuration decisions can introduce corner cases that a developer cannot possibly anticipate

23. Common Edge Cases • Data Types • Language and Regionalization • Date/Time Considerations • Performance • Usability/Maintainability • Security • Integration • Recoverability • Compatibility

24. Data Types • Column and variable types • int, bigint, nvarchar, char, xml, etc. • Most basic of all test cases, so no excuse not to test this

25. Data Types • Max and min values • Collation and sort order • Special characters • NULL values

26. Data Type Overflows • Overflow during aggregation • select sum(intcolumn) • select sum(cast(intcolumn as bigint)) • Overflow or truncation with isnull • Use same data types or coalesce()

27. DEMO

28. Data Types • Let the engine help you out – use table constraints! • Name your constraints such that any error message returned will help you understand the problem

29. Language/Regionalization • Behavior when using different languages and regional settings • Particularly affects numeric and date fields, may also cause other issues

30. Language/Regionalization

31. Language/Regionalization

32. Language/Regionalization

33. Language/Regionalization

34. DEMO

35. Language/Regionalization • Make your code language and region agnostic • 2012-01-07 11:00:00 AM • 1000 instead of 1,000 • Use “set language” • set language us_english • As a DBA, seriously consider the region settings for your SQL Server • Mismatch between SQL and Windows can cause heartache

36. Collation Conflicts • Using different collations on the same database or on the same server can cause conflicts • Use a COLLATE statement when comparing two strings • Rule of thumb: if you are going to normalize case with LOWER or UPPER, use COLLATE • Never assume rows will come back in a specified order

37. Date/Time Considerations • Inaccuracies in scheduling and in date arithmetic • Can become a real nightmare on a WAN

38. Date/Time Considerations • Daylight savings time • When DST ends, the clock goes from 1:59 AM -1:00 AM • SQL Agent will pause for an hour • February and Leap Day • Day-of-year calculations after the 60th day

39. Date/Time Considerations • Timezones • Not all timezones are on even hour boundaries so you need to be able to account for 30 and 15 minute differences • Date Arithmetic • Region settings can affect functions like datepart() • Date math functions can and do overflow. It takes less than 25 days in milliseconds.

40. DEMO

41. Performance • This is a very large topic • DBAs sometimes take this more seriously than developers • Making your development environment look and behave more like production goes a long way toward identifying these cases • Unless it is a single-user application, test with multiple users

42. Usability/Maintainability • How hard is it to track down bugs? • Are error messages helpful? • Classic problems: • Huge stored procedures • TSQL embedded in other application code • Obfuscated table names • Encrypted stored procedures

43. Security • Doing all dev work with an administrator login is not realistic • Default databases for logins • Compliance issues • xp_cmdshell

44. Integration • Does this application play well with others? • Do two projects that live together in prod have separate test environments? • Does maintenance overlap?

45. Recoverability • Are you backing up your data? Can it be restored? • Is your application mirror or cluster aware?

46. Compatibility • Compatibility with SQL Server and Windows versions • Non-default configurations • Database compatibility modes • The master database will be set at 80 compatibility on a 2000 instance that has been upgraded to 2005 • DMF functions, COLLATE statements, and JOIN statements are all subject to failure in certain compatibility modes • Know what you support and be sure those collations exist in your test environment

47. How To Test? • Dev and Test Environments • Ad Hoc testing • Automated testing • Bug tracking

48. Dev and Test Environments • You should have a dev and/or test environment • This environment should be more challenging than production, not less • Having a difficult dev environment means that code is more likely to perform in prod

49. Dev and Test Environments • Example • Korean language and regionalization • case sensitive collation • large number of databases • many agent jobs • long database and object names with special characters

50. Ad Hoc Testing • Set aside time to “poke around” in code • Enter long strings into fields • Input negative numbers • Change the clock and see what happens on Leap Day • Set high seed values for primary keys and change default values for tables