SQL Server Unit TestingSQL Server Unit Testing

Joel ChampagnePresident CodeX EnterprisesPresident, CodeX Enterprises

July 2011

Mark Ginnebaugh, User Group Leader, mark@designmind commark@designmind.com

Who am I?Who am I?

• President CodeX Enterprises LLCPresident CodeX Enterprises LLC

• 20 years consulting experienceL i i iti l t– Large mission critical systems

– .NET, SQL 2000‐2008, SSAS, SSRS, SSIS

– Architecture, Data Architecture

• Focus on developer productivity tools

Why?Why?• Unit Testing…  what’s a “unit”?U it t ti h l bl i• Unit testing has proven valuable in many languages

• Things can break for countless reasons – findThings can break for countless reasons  find those breakages early

• Some may say SQL has gotten the short end of h i k h f ithe stick here for various reasons– Harder:  time/effort to write tests for possibly thousands of objects (many of which could be CRUD)j ( y )

– Separation of development process– Statefulness:  dependence on specific data to make it fly (well)fly (well)

Scenarios for FailureScenarios for Failure

• Suppose there’s an existing procedure which has been working for ages

• Someone renames a table column, missedSomeone renames a table column, missed updating this procedure for whatever reason (e.g. different database)(e.g. different database)

• Fails at run‐time…  you would have known, however if it had at least been “exercised”however, if it had at least been  exercised

• Also, what does SSMS tell us when we commit th b k d ?the broken procedure…?

Integration PointsIntegration Points

• On DemandOn Demand

• Scheduled

S i• Strategic– Build / moving objects

• Automation– Includes “on change”

– Continuous Integration strategies

Visual Studio 2010Visual Studio 2010

• Ability to run tests within VSAbility to run tests within VS

• Setup tests, per object

C h “ d l ”• Can have “expected results”

• Schema View offers easy way to script, per object

• Demo

Observations…• Quite powerful as we can craft data driven tests using a bit of C#tests using a bit of C#– Not necessarily testing SQL directly (can be .NET components)p )

– Next slide example

• Hosts tests in “runnable” code (SQL scripts ( Q pthemselves stored in resource file)

• Integrates as part of a larger testing g p g ginfrastructure available with Visual Studio– Test rigs, agents, etc.– Can make as complex as desired

Data‐driven example

SQL‐Hero• Problems trying to address:

– Developers often don’t like to invest time in writing tests• At least get some “touch tests” as a starting pointAt least get some  touch tests  as a starting point

– Want to exercise tests early and often!• On ALTER and scheduled (and on demand)

L d h i i i i– Leverage tests we do have at strategic times:  e.g. scripting changes to a new place

• Can include these automatically from the schema compare tool!

– Setting of targets, not just thresholds (so we can do reporting / analysis vs. targets)

– Tests can be somewhat “portable” (i.e. do not need toTests can be somewhat  portable  (i.e. do not need to strictly tie to a single database)

– Reality of databases with hundreds of tables, reference data etc may not be practical to re gen the entire thingdata, etc. – may not be practical to re‐gen the entire thing from scripts, etc.

Configurationg

TestingPolicies.xmlExample:  a proc parameter that ends in AreaID, populate using a select from Area table that is filtered

<PARAMETER>

<PATTERN>AreaID$</PATTERN>

/<SQL_LOOKUP>SELECT AreaID FROM Area a WHERE a.AreaAbbr LIKE 'A%'</SQL_LOOKUP>

</PARAMETER>

Example:  a proc parameter of @DocTypeID that relates to a table called CommDocumentType

<PARAMETER>PARAMETER

<NAME>@DocTypeID</NAME>

<CHANGE_NAME_TO>CommDocumentType</CHANGE_NAME_TO>

</PARAMETER>

Example:  a sample plug‐in, put in the program files\sqlhero\plugins directory

<PLUGIN>

<ASSEMBLY_FILE>SampleTestingPlugIn.dll</ASSEMBLY_FILE>

<TYPENAME>SampleTestingPlugIn.MyTestPolicy</TYPENAME>

</PLUGIN></PLUGIN>

Example:  all objects in the Debug schema would be excluded from testing

<OBJECT>

<PATTERN>^\[?Debug\]?\.</PATTERN>

<IS_EXCLUDED>True</IS_EXCLUDED>

</OBJECT>

NotesNotes

• The test generation process will look atThe test generation process will look at captured workloads to see if an already‐executed invocation “will work”executed invocation  will work

• Is using a randomized process, but influenced by testing policy fileby testing policy file

• No “side‐effects”– Rollback per object

– Advantages / Disadvantages

Creating Testsg

Running TestsRunning Tests

• SchedulingScheduling

• Via Template

i l i h ll l i !• Via Template, with parallel execution!

• From SHCommand.exe

• From .sqlheroproj Visual Studio project type

• DemoDemo

Some Observations…• Presence of touch testing has proven value from experience ‐ exampleexperience  example

• SQL‐Hero testing can augment VS testing:– VS for formal unit tests which are part of build process, complex cases where can use C#

– SH for touch tests that are just “there” behind the scenes to find certain issues for you transparentlyy p y

– Output of SH testing template can be used in VS, if you like!

• Not intending SQL Hero to “do it all” but• Not intending SQL‐Hero to  do it all  but considering some new features along the lines of turning captured workloads into complex test 

( )cases (C#)

Some Observations…• Presence of touch testing has proven value from experience ‐ exampleexperience  example

• SQL‐Hero testing can augment VS testing:– VS for formal unit tests which are part of build process, complex cases where can use C#

– SH for touch tests that are just “there” behind the scenes to find certain issues for you transparentlyy p y

– Output of SH testing template can be used in VS, if you like!

• Not intending SQL Hero to “do it all” but• Not intending SQL‐Hero to  do it all  but considering some new features along the lines of turning captured workloads into complex test 

( )cases (C#)

A more complex use case…p• Problems trying to address:

– Data quality in development regions– Security and isolation of production data– In progress work (using external source control?)– Making this easyMaking this easy

• How does unit testing relate?– Integrated as part of the process to ensure things “still work” at the end (could be a different process too)work” at the end (could be a different process too)

– Overall process can serve as one way to achieve the coveted “known starting point” – easily

b i l l (lik l• YMMV – may be simpler or more complex (likely supported!)

• Watch for a whitepaper describing this in great detailp p g g• Demo

A more complex use case ‐ illustrated…

Production QA/DevelopmentProduction QA/Development

Prod(remote)

QAQANewBackup

Backup / Restore

BackupBackup

QANew(remote)

Remove…

Scramble sensitive data

A more complex use case ‐ illustrated…

Production QA/DevelopmentProduction QA/Development

Detach, MoveProd

(remote)QAQANew

Backup

Detach, Move Files, Attach…

Sync Pending Changes

QANewBackup

Restore

FTP Landing Zone

QANew

Perform tests

A more complex use case ‐ illustrated…

Production QA/DevelopmentProduction QA/Development

Prod(remote)

QAOld

E‐mail relevantparties about

QA

parties about completion…

Putting it together…• VS Database Project• VS Database Project

– “Build” ‐> Deploy (local or shared)– Can exercise static code analysis– Can include unit testingCan include unit testing– Can use MSBuild, which in turn integrates with TFS / CI options

• SQL‐Hero– Precludes none of the abovePrecludes none of the above– Adds touch testing capability during development cycle, transparently– Source control integration interops with Database Projects!– Large data sets are encouraged and easy to build– Compliance checking incorporated there acts against “real” databases, can 

be scheduled, email delivery (advantages!)– Supports automated operations

• Example: DB reconcile from branched dev to branched QA (both stateful worked• Example:  DB reconcile from branched dev to branched QA (both stateful, worked pretty flawlessly with a simple .cmd script)

– More use cases to support:  testing from a collected workload• Your environment

– Do developers do actual data modeling?– Do developers want a shared environment?

Offers…Offers…• Looking for Customer Advisory Board membersmembers– Free enterprise edition in exchange for on‐going feedback (iterative development on v‐nextfeedback (iterative development on v next features)

• Next release coming soon (along with new g ( gwhitepapers)– Still free!– www.codexframework.com (Library)– @sqlheroguy– joelc@codexframework.com

To learn more or inquire about speaking opportunities, please contact:o ea o e o qu e about spea g oppo tu t es, p ease co tact:

Mark Ginnebaugh, User Group Leadermark@designmind.com