SQL Server Managing Test Data & Stress Testing January 2011

Managing TManaging eT st Data and Stress eTeT sting Yeosting Y ur SQL Applications oSpeaker: Joel ChampagneSan Francisco SQL Server User GroupJanuary 12, 2011Mark Ginnebaugh, User Group Leaderwww.bayareasql.orgTonin gi ht’g sht’ Ss peakpeeak r Joel ChampaJgne Developer of large enterprise applications for 20 years FoF cus on data, in particular using the Microsoft stacticular using the Micrk (SS*S) S) and .NET Involved in all stages of application life-cycle, from envisioning through implementation  Areas of Interest: Tool development work Developer productivity T ion ghtht’s ’ T Topi c: M ana ig ng T Tes t Dt Dat a an d Std Stress T Testiting Your SQL Applications UpcomingUpc TraTr ia nin ng • Upcoming g full‐day y training g (minimal cost, targetg  is late Feb or March 2011):  www.codexframework.com/training– SQL SourceSour  ContCon rt or lo– Stress/Volume Testing– SQL Unit Testing– SQL‐Hero – more details (www.codexframework.com)• Email Joel:  joelc@cod fex ramewo kr .com• Twitter:  @sqlheroguyWhatWha  I wantwa  to cover…• The whywh  & how• Large data volumes – benefits, practical looks• Sipec fiific exam lp es, in det ia lil• Obfuscation of existing data• Load testing• Both MS and non‐MS options• Let’s keep it interactiveIn the beginning…• … of the  devde ev lopmenetlopmen  processpr– We can know characteristics of entities– We can know waysy  to optimizpe (e.g. indexes)– We can have good intentions• UltimatUltima et lye ,ly  the littlelit detde ailst matma tt er:t– Style counts! – not always shortest or most elegant performs best– SQL can seem like an art instead of a science sometimesProblemPr is…• Howo  can we e knowo  what at we’ve e e got t is going 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• And, ’dwe lik  e t  o:– Work with semi‐realistic data, even before users have had a   chance to   do a lot of intin et re ar ctiona with the app – In some cases may want to work with a “well known” data set, to support repeatable unit & system testingSolutions• VS 2010– Database project ‐> Data Generation Plan– Premium/Ultima/te– Custom generator extensibility• SQL‐HeroHer– Generate data option• OthersOther– Custom developed (scripting, bcp, PowerShell, etc.)Things to consn idi ed r…• “ReR alisme”– Cardinality– Use of NULL– Foreign key lookups– Impliedp rules (sequenceq number examplep )– … essentially rules at both column and table level (constraints)– Names, addresses, etc. – pros, cons• Deterministic vs. True RandomUndersUnder ts andingt rer ality…Cardinality:Scenario #1• NewNe de vde ev lopmenetlopmen  effoeff ro t, empty  datda at base• We have a search screen we’ve written –seems likelik  it’s fasfa ts  but not a lot of datda at• … what about 3 years from now?– 12,000,000+ Customers– 26,000,000+ Orders– 16,500,000 Addresses