POWERSOLUTIONS65ENTERPRISECLUSTERENVIRONMENTRecommendationsandTechniquesforScalingMicrosoftSQLServerTosupportmanymoreusers,adatabasemusteasilyscaleoutaswellasup.
ThisarticledescribestechniquesandstrategiesforscalingouttheMicrosoftSQLServerrelationaldatabasemanagementsystem(RDBMS)andprovidesscenariosillustratingscale-outdeployments.
MostenterpriseapplicationstodayrunonaMicrosoftWindows,UNIX,orLinuxoperatingsystem–basedrelationaldatabasemanagementsystem(RDBMS),suchasMicrosoftSQLServer2000.
Scalabilityhasbecomeacriticalfactorinthesuccessoftheseapplicationsasthenumberofusersrelyingonthemhasgrown.
TheInternetalsohasprofoundlyaffectedtheneedforscalability.
Onceexposedtojustafewthousandusers,thedatainmanycorporatedatabasesnowmustbeaccessedbytensofthousandsofconcurrentusersthroughe-commercesites,Webservices,andotherInternet-basedapplications.
Scalingdatabasestosupporttheseusersisamajorcon-cernforbothdatabasesoftwaredevelopersanddatabaseadministrators.
DifferencesbetweenscalingupandscalingoutWhendatabaseperformanceworsens,administratorstypicallyaddresstheproblemfirstbyscalingup—thatis,bytryingtooptimizeperformanceinthecurrentenvi-ronment.
Becausemanydatabaseapplicationshaveinefficientdesignsorbecomeinefficientastheirusagepatternschange,findingandimprovingtheareasofinefficiencycanyieldsignificantperformancebenefits.
Fine-tuningthedatabaseservercanhelpperformmorequeries,handlemoreusers,andrunmoreefficiently.
SQLServerscalesupfairlywell—toapoint.
Inonereal-worldscenario,forexample,acompany'sdatabaserequiredanine-tablejointolookupasinglecustomeraddress.
Selectivelydenormalizingthetablesandapply-ingstrategicindexesallowedSQLServertoexecuteaddressqueriesmuchfaster.
Becauseaddresslookupswereacommontaskforthiscompany,evenaminorper-queryimprovementsignificantlyenhancedoverallserverperformance.
Unfortunately,scalingupislimitedinhowmuchitcanimproveanapplication'sperformanceandabilitytosupportmoreusers.
Forexample,takeadatabasewhosesolefunctionistoperformasingle,simplequery—nojoins,noneedforindexes.
Ahigh-performanceSQLServercomputer—forexample,aquad-processorserverwith4GBofRAMandBYDONJONESWhendatabaseperformanceworsens,administratorstypicallyaddresstheproblemfirstbyscalingup.
severalfastharddrives—couldprobablysupporttensofthou-sandsofuserswhomustconcurrentlyexecutethatonequery.
However,thisservermightnotbeabletosupportamillionusers.
Inthissituation,scalingup—fine-tuning—wouldbeinsuf-ficient,becausesuchasimplequeryleaveslittleroomforimprovement.
Tobeginsupportingmanymoreusers,scalingoutisabettersolution.
Scale-outstrategiesredistributeworkloadsScalingoutSQLServer,amorecomplicatedprocessthanscalingup,requiressplittingadatabaseintovariouspieces,thenmovingthepiecestodifferent,independentSQLServercomputers.
Thegrocery-storecheckoutlinepresentsagoodanalogyforcompar-ingthetwoprocesses.
Inabusygrocerystorewithonlyonecheckoutlaneopen,alonglineofunhappycustomerswouldquicklymaterialize.
Ascale-upapproach—installingfasterbarcodescanners,requir-ingeveryonetouseacreditcardinsteadofwritingacheck,orhiringafastercashier—canmakethecheckoutprocessitselfmoreefficient.
Thesemeasuresmightimprovethesituation,butnotsolvetheproblem;customerswouldmovethroughthelinemorequickly,buttheystillwouldhaveonlyonecheckoutlane.
Abettersolutionwouldbetoscaleout—inthisanalogy,byopen-ingadditionalcheckoutlanes.
Customerscouldnowbeprocessedinparallelbycompletelyindependentlanes.
Tomaketheanalogyclosertoadatabasescale-outscenario,thegrocerystorecouldhavespe-cializedlanes:onethatexpeditesprocessing(customerspurchasing15itemsorfewer),andanotherthatfocusesonproduce,whichoftentakeslongerbecauseitmustbeweighedandnotsimplyscanned.
Anideal,ifunrealistic,solutionmightbetoretainasinglelaneforeachcustomer,buttodivideeachcustomer'spurchasesintocategoriestobehandledbyspecialists:produce,meat,boxeditems,andsoforth.
Specializedcashierscouldminimizetheirinteractionswitheachother,keepingtheprocessmovingspeedilyalong.
Althoughunworkableinarealgrocerystore,thissolutionillus-tratesareal-worldmodelforscalingoutdatabases.
GeneralstrategiesforscalingoutdatabasesDatabasemanagerscanconsidertwobasicscale-outstrategiesfordistributingtheworkloadofadatabaseacrossmultipleservers.
MostmajorRDBMSplatforms,includingSQLServer,providethemeanstomakethesestrategiespossible.
SQLServerfarmsreplicatethedatabaseThefirstapproachsimplyaddsmoreservers.
ConsiderascenarioinwhichacompanyhasanofficeinNewYorkandoneinLosAngeles.
Bothofficeshaveseveralthousanduserswhofrequentlyquerydatafromacorporateapplication,suchasanorder-processingdatabase.
Usersrarelychangedatainthesystem,buttheyfrequentlyaddnewdata.
Inthisscenario,usersinbothofficesareoverloadingthedatabase.
Evenifthedatabaseisawell-writtenmultitierapplication,pro-cessingalltheinformationononlyonedatabaseserveratthebackendcancreateabottleneck.
Figure1illustratesonewaytoaddresstheproblem:aSQLServerfarm.
Inthistechnique,twodata-baseserverseachcontainacom-pletecopyofthedatabase.
Eachofficehousesoneserver,andtheusersineachofficeconnectonlytotheirlocalserver.
ChangesandnewrecordsarereplicatedbetweentheserversbyusingSQLServerreplication.
Toavoidconflictswhenaddingnewrecords,eachofficemight,forexample,beassignedauniquerangeoforderIDnum-bers,ensuringthatnewrecordscreatedineitherofficecanbeuniquelyidentifiedacrossbothcopiesofthedatabase.
ThisstrategyisperhapsthesimplestmeansofscalingoutSQLServer.
AlthoughreplicationisnoteasytosetupandmaintainonSQLServer,neitherisitextremelydifficult.
Thestrategyworkswellevenwithmanyserversandcopiesofthedatabase.
However,thedatareplicationstrategydoesincursomedraw-backs,especiallylatency.
Neithercopyofthedatabasewillevermatchtheotherexactly.
Asnewrecordsareaddedtoeachcopy,timeelapsesbeforereplicationbegins.
Withonlytwoserversinthecompany,eachservermightbeasmuchasanhouroutofsyncwiththeother,dependinguponhowadministratorssetupreplication.
Addingmoreservers,however,involvesdifficultreplicationdecisions.
Foranotherscenario,considerthesix-officesetupdepictedinFigure2.
EachofthesixofficeshasitsownindependentSQLServersystem—anexcellentdesignforscalability.
However,latencycouldbeveryhigh.
IfeachSQLServerreplicateswithitspartnersjustonceeveryhour,thentotalsystemlatencycouldbethreehoursormore.
AchangemadeintheLosAngelesofficewouldreplicateENTERPRISECLUSTERENVIRONMENTPOWERSOLUTIONSNovember200366WorkstationsServerServerWorkstationsReplicationLosAngelesofficeNewYorkofficeFigure1.
SQLServerfarmScalingoutSQLServer,amorecomplicatedprocessthanscalingup,requiressplittingadatabaseintovariouspieces,thenmovingthepiecestodifferent,independentSQLServercomputers.
toNewYorkandLasVegasinaboutanhour.
Anhourlater,thechangewouldreachLondonandDenver.
Anhourlater,itwouldarriveinOrlando.
Givensuchhighlatency,theentiresystemwouldprobablyneverbesynchronizedcompletely.
Administratorscanreducelatency,butataperformancecost.
Ifeachofthesixserversreplicatedwitheachoftheotherfiveservers,thesystemcouldconverge,orbeuniversallyinsync,aboutonceanhour(assumingagainthatreplicationoccurredeveryhour).
Figure3showssuchafullyenmesheddesign.
Inthisfullyenmesheddesign,eachservermustmaintainrepli-cationagreementswithfiveotherservers,andmustreplicatewitheachservereveryhour.
Thismuchreplication,particularlyinabusydatabaseapplication,wouldlikelyslowresponsesomuchthattheperformancegainachievedbycreatingaserverfarmwouldbelost.
Eachofficemightrequiretwoserversjusttomaintainrepli-cationandmeetusers'needs.
Althoughfairlyeasytoimplement,theserverfarmtechniquehasapointofdiminishingreturns.
DistributedpartitioneddatabasesmovetaskstodifferentserversAmoresophisticatedstrategy—butonethatisalsomoredifficulttoimplement—involvespartitioningthedatabaseandmovingthepiecestodifferentservers.
Unlikethesimplifiedorder-processingdatabaseexamplepreviouslydiscussedin"SQLServerfarmsrepli-catethedatabase,"mostreal-worlddatabaseapplicationstendtorelyonanequalmixofdatareadinganddatawriting.
Forexample,anorder-processingapplicationmightincludeaproductcatalogthatislargelyreadonly,acustomer-orderdatabasethatiswriteheavy,andtablescontainingsupplierinformationthatareequallyread-write.
Thesethreecloselyrelateddata-basesegments—catalog,orders,andsuppliertables—arefairlytask-independent:diverseuserswithintheorganizationtendtouseeachdatabasedifferently.
Mer-chandisersmightwritetothecat-alogbutdolittleelse.
Customerservicerepresentativesmightreadthecatalogandwritetotheorderstablesbutneveraccessthesup-pliertables.
Thewarehousestaffmightreadthecatalogandreadfromandwritetothesuppliertables.
Thisdivisionoflaborindicateswherethedatabaseitselfcanbesplit,asFigure4illustrates.
Administratorscanusetwobasicapproachestoimplementingthedistributedpartitioneddatabasestrategy.
Thefirstistomodifytheclientapplicationsothatitunderstandsthedivisionofthedatabaseacrossmultipleservers.
Straightforwardyetsomewhattime-consuming,thissolutiondoesnotworkwellforthelongterm.
Futurechangestotheapplicationcouldresultinadditionaldivisions,whichwouldinturnrequireadditionalreprogramming.
Abetterapproachistoprogramtheclientapplicationtousestoredprocedures,views,andotherserver-sideobjects—anordi-narybestpracticeforaclient-serverapplication—sothattheclientapplicationneednotbeawareofthephysicallocationofthedata.
SQLServeroffersdifferenttechniques,suchasdistributedpartitionedviews,tohandlethissetup.
ENTERPRISECLUSTERENVIRONMENTwww.
dell.
com/powersolutionsPOWERSOLUTIONS67NewYorkLosAngelesLasVegasDenverOrlandoLondonFigure3.
Fullyenmeshedsix-serverfarmReplicationReplicationReplicationReplicationReplicationReplicationNewYorkLosAngelesLasVegasDenverOrlandoLondonFigure2.
Six-serverfarmScalingoutSQLServercanofferbenefitsnotonlyinimprovedapplicationperformance,butalsoingreaterredun-dancyandavailability.
Scale-outtechniquesusingSQLServerandWindowsSQLServerandWindowsofferseveraltechniquestoenablescalingout,includingSQLServer–specificfeaturessuchasdis-tributeddatabasesandviewsandWindows-specificfunctionssuchasWindowsClustering.
DistributedpartitionedviewshelpcreatevirtualtablesSQLServerdistributedpartitionedviewsallowdeveloperstocreateviewsthatcombinetablesfrommultipleSQLServercomputersintoasinglevirtualtable.
ThismethodlogicallydividesadatabaseacrossmultipleSQLServercomputers.
Ratherthanreprogrammingclientapplicationstounderstandthedivisionofthedatabases,develop-erscancreatedistributedviewsthatpresentavirtualizedversionofthem.
Thesetablesappeartoclientapplicationsasiftheywereonasingleserver.
Meanwhile,SQLServercombinesthetables,whicharespreadacrossmultipleservers,intoasingleview.
Distributedviewsareapowerfultoolinscalingout.
Theyallowdeveloperstoredistributedatabasestransparentlytotheendusersandtheirbusinessappli-cations.
Aslongasclientapplica-tionsaredesignedtousetheviewsratherthanthedirecttables,thetablesthemselvescanberearrangedandscaledoutasnec-essarywithouttheclientapplica-tionbeingawareofanychange.
Theworkloadrequiredtocreateandpresenttheviewtoclientcomputersissharedbyallserversparticipatingintheview—orbyallserversinthefederation.
SQLServer2000isthefirstversionofSQLServertomakeasignificantimprovementtothisapproach,becausethedatawithintheviewscanbeupdatedbyclientapplicationsasifthedatawereinaregulartable.
Theupdatesarecascadedbacktothenecessaryparticipantservers.
ReplicationofdistributedpartitioneddatabasesreduceslatencyAnotherscale-outapproachinvolvespartitioningadatabaseacrossmultipleserversandthenreplicatingthedatabasecopies.
Likethesix-serverorder-processingfarmdescribedearlier,eachservercontainsacompletedatabase.
Inthismethod,eachserverisresponsibleforadifferentsetofrows.
SQLServerreplicationisusedtokeepeachcopyofthedatabaseupdated.
Thismethodallowseachservertoimme-diatelyaccessitsownrowsandprovidesreasonablylowlatencyforaccesstorowscreatedonotherservers.
Clientapplicationsoftenmustbemodifiedtounderstandthisstructure.
Inmanypartitioneddatabaseschemes,datarowsmaybemodifiedonlyontheserverthatownsthem,withthechangesthenbeingmovedtotheotherserversthroughreplication.
Clientapplicationsmustknowhowtodeterminewhichserverownsarowbeforemakingmodifications.
WindowsClusteringfacilitateshighavailabilityandscalabilityBesidesimprovingperformance,WindowsClusteringcanhelpavoidtheriskofserverfailurewhenscalingout.
Forexample,atwo-nodeactive/activeclusterhastwoindependentSQLServerservers.
Thesenodescanbeconfiguredasaserverfarm,inwhicheachservercon-tainsacompletecopyofthedatabaseandusersaredistributedbetweenthem.
Analternativeisadistributeddatabasearchitecture,inwhicheachservercontainsonelogicalhalfoftheentiredatabase.
Ineitherarchitecture,afailureofoneserverisnotcatastrophicbecauseWindowsClusteringenablestheotherservertotranspar-entlytakeoverandactastwoservers.
Over-engineeringisthekeytoasuccessfulactive/activecluster.
Eachnodeshouldbedesignedtooperateatamaximumof60percentcapacity.
Ifonenodefails,theothernodecanbeginrun-ningat100percentcapacity,incurringonlyabouta20percentlossofefficiency.
Still,performanceisgenerallywellwithinanaccept-ablerangeconsideringthat,afterfailover,applicationsmustrunonhalfasmuchhardware.
Settingupclusterscanbeextremelycomplex.
InWindowsClustering,thesoftwareisnotdifficulttouse,buttheunderlyinghard-waremustbeabsolutelycompatiblewithWindowsClustering—andmosthardwarevendorshaveexactingrequirementsforclustersetups.
Purchasingpreconfiguredclustersfromamajorservervendor,suchasDell,canhelpsimplifyclustersetup.
Theclusterisdesignedtobereadytorunondelivery,andboththevendorandMicrosoftcanpro-videcluster-specifictechnicalsupportifnecessary.
High-performancestoragetoboostSQLServerresponseHigh-performancestorageisanoften-overlookedperformancebenefitforSQLServer—particularlyexternalstorageareanetworksENTERPRISECLUSTERENVIRONMENTPOWERSOLUTIONSNovember200368ServerServerServerCatalogOrdersSuppliersMerchandisingCustomerserviceWarehouseWriteWriteRead/writeReadReadFigure4.
Identifyingtask-baseddivisionsinthedatabasedesignSQLServerandWindowsofferseveraltechniquestoenablescalingout,includingSQLServer–specificfeaturessuchasdistributeddatabasesandviewsandWindows-specificfunctionssuchasclustering.
(SANs)thatrelyonFibreChanneltechnologyratherthantraditionalSCSIdisksubsystems.
Becausehigh-performancestorageenablesanexistingservertohandleagreaterworkload,itconstitutesanexam-pleofscalingupratherthanout.
SQLServerisahighlydisk-intensiveapplication.
AlthoughSQLServerincludeseffectivememory-basedcachingtechniquestoreducediskreadsandwrites,databaseoperationsrequiresignificantdatatrafficbetweenaserver'sdisksanditsmemory.
Themorequicklythedisksubsystemcanmovedata,thefasterSQLServerwillper-form.
Someindustryestimatessuggestthat75percentofidletimeinSQLServerresultsfromwaitingforthedisksubsystemtodeliverdata.
ImprovingthespeedofthedisksubsystemcanmarkedlyimproveoverallSQLServerperformance.
MovingtoadditionalRAID-5arraysontraditionalcopperSCSIconnectionsisasimplewaytoimprovediskspace.
However,high-speedFibreChannelSANsofferthebestspeed,aswellasmyriadinnovativerecoveryandredundancyoptions—makingthemasaferplacetostoreenterprisedata.
Scale-outstrategyforimprovingSQLServerperformance,redundancy,andavailabilityAsapplicationsgrowtosupporttensandhundredsofthousandsofusers,scalingisbecomingamission-criticalactivity.
Scalingup—improvingefficiencybyfine-tuningqueries,indexes,andsoforth—helpsITorganizationsdomorewithless.
However,scalingupcanrequirehighadministrativeoverheadandmayhavelimitedeffect.
Administratorsmightspendtwoweekstoachievea1per-centperformancegain,animprovementthatcannotcomparetothemuchhighergainspromisedbyawell-plannedscale-outdesign.
Althoughseldomconsideredasatargetforscalingout,SQLServeriswellsuitedtothisstrategy,inbothserverfarmandmoresophisticateddistributeddatabaseapproaches.
ScalingoutSQLServercanofferbenefitsnotonlyinimprovedapplicationperfor-mance,butalsoingreaterredundancyandavailability.
DonJonesisafoundingpartnerofBrainCore.
Net,andhasmorethanadecadeofexperi-enceintheITindustry.
Don'scurrentfocusisonhigh-endenterpriseplanning,includingdataavailabilityandsecuritydesign.
ENTERPRISECLUSTERENVIRONMENTwww.
dell.
com/powersolutionsPOWERSOLUTIONS69Databasescanbeinefficientforseveralreasons:Poordesign:Manyapplicationdevelopersdonotexcelatdatabasedesign.
Some,forexample,havebeentaughttofullynormalizethedatabaseatallcosts,whichcanleadtosignificantlydegradedperformance.
Sometimesprojectschedulesdonotpermitenoughdesigniterationsbeforethedatabasemustbelockeddownandsoft-waredevelopmentbegins.
Insomecases,theapplicationitselfisnotdesignedwell,resultinginanincompletedatabasedesignthatmustbepatchedandexpandedastheapplicationiscreated.
Change:Anapplicationusedinawayunintendedbyitsdesignerscanreduceefficiency.
Theapplicationmayhaveexpandedandbegunsufferingfrom"scopecreep"—thegrowthorchangeofprojectrequirements.
Inthiscase,redesigningtheapplicationfromthebeginningtomeetcurrentbusinessneedsmaybethebestsolutiontodatabaseinefficiency.
Growth:Databasesaredesignedforaspecificdatavolume;oncethatvolumeisexceeded,queriesmaynotworkastheywereintended.
Indexesmightneedtoberedesignedoratleastrebuilt.
Queriesthatwereintendedtoreturnafewdozenrowsmaynowreturnthousands,affectingtheunderlyingdesignoftheapplicationandthewaydataishandled.
Theseproblemsaredifficulttoaddressinalive,productionappli-cation.
Scalinguptendstohavealimitedeffect.
Althoughdevelopersmayagreethattheapplication'sdesignisinefficient,companiesarereluctanttodestroyaserviceableapplicationandstartoverwithoutseriousconsideration.
Scalingoutcanofferalessdrasticsolution.
Althoughscalingoutrequiresconsiderableworkontheserverside,itmaynotrequiremuchmorethanminorrevisionstoclient-sidecode,makingtheprojectapproachablewithoutcompletelyre-architectingtheapplication.
Scalingoutmightnotbethemostelegantorefficientwaytoimproveperformance,butitdoeshelpalleviatemanydatabaseandapplicationdesignflaws.
Italsocanallowcompaniestogrowtheirdatabaseapplicationswithoutneedingtoredesignthemfromthebeginning.
FORMOREINFORMATIONThisarticleisbasedonanexcerptfromthefreeeBookTheDefinitiveGuidetoScalingOutSQLServer(Realtimepublishers.
com)byDonJones,availableathttp://www.
dell.
com/sql/ebookDell|MicrosoftSQLServer2000:http://www.
dell.
com/us/en/esg/topics/products_software_pedge_001_database.
htmMicrosoftSQLServer:http://www.
microsoft.
com/sqlUNDERSTANDINGDATABASEINEFFICIENCY
轻云互联成立于2018年的国人商家,广州轻云互联网络科技有限公司旗下品牌,主要从事VPS、虚拟主机等云计算产品业务,适合建站、新手上车的值得选择,香港三网直连(电信CN2GIA联通移动CN2直连);美国圣何塞(回程三网CN2GIA)线路,所有产品均采用KVM虚拟技术架构,高效售后保障,稳定多年,高性能可用,网络优质,为您的业务保驾护航。官方网站:点击进入广州轻云网络科技有限公司活动规则:用户购买任...
御云怎么样?炎炎暑期即将来临,御云(royalyun)香港、美国服务器开启大特惠模式。御云是新成立的云服务提供商,主要提供香港、美国的云服务器,不久将开启虚拟主机业务。我们的香港和美国主机采用CN2 GIA线路。目前,香港cn2 gia vps仅7.9元每月起,美国vps仅8.9/月,续费同价,可叠加优惠,香港云服务器国内延迟一般在50ms左右,是搭建网站的最佳选择,但是请不要用于违法用途。点击进...
cyun怎么样?cyun蓝米数据是一家(香港)藍米數據有限公司旗下品牌,蓝米云、蓝米主机等同属于该公司品牌。CYUN全系列云产品采用KVM架构,SSD磁盘阵列,优化线路,低延迟,高稳定。目前,cyun推出的香港云服务器性价比超高,香港cn2 gia云服务器,1核1G1M/系统盘+20G数据盘,低至29元/月起;香港多ip站群云服务器,16个ip/4核4G仅220元/月起,希望买香港站群服务器的站长...
sqlserver2000挂起为你推荐
现有新的ios更新可用请从ios14be苹果手机更新不了最新14系统是怎么回事?360搜狗奇虎360到底是做什么的?asp.net什么叫ASP.NET?360邮箱邮箱地址指的是什么?波音737起飞爆胎客机起飞的时候时速是多少?支付宝是什么什么是支付宝? 请详细介绍.密码cuteftp传奇域名自己的传奇服务器怎么建设?billboardchina中国有进美国BillBoard榜的人吗美国独立美国独立战争
万网域名注册 linuxvps 花生壳域名贝锐 lamp安装 host1plus 香港机房托管 godaddy续费优惠码 回程路由 阿里云浏览器 中国电信测网速 搜索引擎提交入口 跟踪路由命令 512mb 阿里云邮箱登陆 后门 服务器防御 七十九刀 phpinfo nic cdn免备案空间 更多