Managing Managing Test Data and Stress Test Data and Stress Managing Managing Test Data and Stress Test Data and Stress Testing Your SQL Applications Testing Your SQL Applications

Speaker: Joel Champagne

San Francisco SQL Server User GroupJanuary 12, 2011

Mark Ginnebaugh, User Group Leaderwww.bayareasql.org

Tonight’s SpeakerTonight’s Speakerg pg p

Joel Champagne Joel Champagne Developer of large enterprise applications for 20 years Focus on data, in particular using the Microsoft stack (SS*S) Focus on data, in particular using the Microsoft stack (SS S)

and .NET Involved in all stages of application life-cycle, from

envisioning through implementation Areas of Interest:

Tool development work Developer productivity

T i ht’ T i M i T t D t d St T ti Tonight’s Topic: Managing Test Data and Stress Testing Your SQL Applications

Upcoming TrainingUpcoming Training 

• Upcoming full‐day training (minimal cost, target is p g y g ( , glate Feb or March 2011):  www.codexframework.com/training

SQL Source Control– SQL Source Control– Stress/Volume Testing– SQL Unit Testing– SQL‐Hero – more details (www.codexframework.com)

l l l d f k• Email Joel:  joelc@codexframework.com• Twitter:  @sqlheroguy

What I want to cover…What I want to cover…

• The why & howThe why & how

• Large data volumes – benefits, practical looks

S ifi l i d il• Specific examples, in detail

• Obfuscation of existing data

• Load testing

• Both MS and non‐MS optionsBoth MS and non MS options

• Let’s keep it interactive

In the beginning…In the beginning…

• … of the development process… of the development process– We can know characteristics of entities– We can know ways to optimize (e.g. indexes)y p ( g )– We can have good intentions

• Ultimately, the little details matter:Ultimately, the little details matter:– Style counts! – not always shortest or most elegant performs best

– SQL can seem like an art instead of a science sometimes

Problem is…Problem is…

• How can we know what we’ve got is going to:o ca e o at e e got s go g to:– Perform well, not just as we develop, but years from now, in production

– Perform well if reality changes– Actually  behave as expected with lots of data

A d ’d lik t• And, we’d like to:– Work with semi‐realistic data, even before users have had a chance to do a lot of interaction with the apphad a chance to do a lot of interaction with the app

– In some cases may want to work with a “well known” data set, to support repeatable unit & system testing

SolutionsSolutions

• VS 2010VS 2010– Database project ‐> Data Generation Plan– Premium/Ultimate/– Custom generator extensibility

• SQL‐HeroSQL Hero– Generate data option

• OthersOthers– Custom developed (scripting, bcp, PowerShell, etc.)

Things to consider…Things to consider…

• “Realism”Realism– Cardinality– Use of NULL– Foreign key lookups– Implied rules (sequence number example)p ( q p )– … essentially rules at both column and table level (constraints)

– Names, addresses, etc. – pros, cons

• Deterministic vs. True Random

Understanding reality…Understanding reality…Cardinality:

Scenario #1Scenario #1

• New development effort empty databaseNew development effort, empty database

• We have a search screen we’ve written –seems like it’s fast but not a lot of dataseems like it s fast but not a lot of data

• … what about 3 years from now?– 12,000,000+ Customers

– 26,000,000+ Orders

– 16,500,000 Addresses

Options #1Options #1

• VS 2010VS 2010

• SQL‐Hero

• Data Model

• Demos

Scenario #2Scenario #2

• Let’s take an end‐to‐end look at a “real example” et s ta e a e d to e d oo at a ea e a p efrom a customer…– Team structure– “The strategy”

• A fourth database, user participation

Design doc from BA– Design doc from BA– The process of creating data and testing– Tuning effortsTuning efforts– Re‐testing– Conclusions…

Scenario #3Scenario #3

• Large database, on‐going development work, g , g g p ,post‐implementation

• As we try to modify or fix bugs, some issues rely on production quality dataon production‐quality data

• Option:  Copy prod to dev/QA• Problems:• Problems:

– Security– Coordinating with on‐going dev work

• Another scenario:  just looking to add more data to an existing DB

Options #3Options #3

• VS 2010VS 2010– Data Transformation

• SQL Hero• SQL‐Hero– “Scramble” existing data option

• Demos

Issue here:  Assumes inherently that should reseed…

Conclusions…‐ Data generation plans are very extensible (due to custom generators and such)but do have limitations:  prepare to invest some time in getting them “just right”

Scenario #4Scenario #4

• Actual “stress testing” being high concurrentActual  stress testing , being high concurrent load

• Need to understand the results of high load• Need to understand the results of high load– Slowness

Of i bl ki– Often times, blocking

– Sometimes deadlocks we didn’t anticipate

– Have seen lead to on‐going monitoring, tuning efforts

Options #4• SQLIO

– SQLIOStress creates separate data and log files to simulate the I/O patterns that SQL Server will generate to its data file (.mdf) and its log file (.ldf). SQLIOStress does not use the SQL Server engine to perform the(.ldf). SQLIOStress does not use the SQL Server engine to perform the stress activity so it can be used to exercise a computer before you install SQL Server." (From SQLIOStress Readme.doc)

• SQL ProfilerC ll kl d d “ l ”– Can collect a workload and “replay”

• VS 2010– Extensive Load Testing support – Test rigs, multiple agents possible

Invocation of test cases in NET pros and cons– Invocation of test cases in .NET – pros and cons• SQL‐Hero

– Executing script with concurrency option – generate high load using “real” trace – simple, visualization for results ‐ successfultrace  simple, visualization for results  successful

– Data visualization of collected trace information (often the key in analysis)– Data visualization of trace information, over longer timeframes– Production monitoringg– Screen shots:  daily life examples– Future:  Template to build web test code to invoke from VS2010 test rig

Putting it together…Putting it together…

• Build processu d p ocess– Build DB from SC ‐> Populate Fully ‐> Use

• Advantage:  you know will match source of truth• Disadvantage:  longer‐term testing, relying on existing data

– Scripting / automationMicrosoft guidance doc:– Microsoft guidance doc: http://vsdatabaseguide.codeplex.com

– Hybrid options common

• Another important element:  Unit testing– Knowing if something becomes broken, early

To learn more or inquire about speaking opportunities, please q p g pp , pcontact:

Mark Ginnebaugh, User Group Leader mark@designmind.com