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
博鳌云是一家以海外互联网基础业务为主的高新技术企业,运营全球高品质数据中心业务。自2008年开始为用户提供服务,距今11年,在国人商家中来说非常老牌。致力于为中国用户提供域名注册(国外接口)、免费虚拟主机、香港虚拟主机、VPS云主机和香港、台湾、马来西亚等地服务器租用服务,各类网络应用解決方案等领域的专业网络数据服务。商家支持支付宝、微信、银行转账等付款方式。目前香港有一款特价独立服务器正在促销,...
这两天在站长群里看到不少有使用DEDECMS织梦程序的朋友比较着急,因为前两天有看到来自DEDECMS,我们熟悉的织梦程序官方发布的公告,将会在10月25日开始全面商业用途的使用DEDECMS内容管理程序的会采用授权收费模式,如果我们有在个人或者企业商业用途的,需要联系且得到授权才可以使用,否则后面会通过维权的方式。对于这个事情,我们可能有些站长经历过,比如字体、图片的版权。以及有一些国内的CMS...
2021年9月中秋特惠优惠促销来源:数脉科技 编辑:数脉科技编辑部 发布时间:2021-09-11 03:31尊敬的新老客户:9月优惠促销信息如下,10Mbps、 30Mbps、 50Mbps、100Mbps香港优质或BGPN2、阿里云线路、华为云线路,满足多种项目需求!支持测试。全部线路首月五折起。数脉官网 https://my.shuhost.com/香港特价数脉阿里云华为云 10MbpsCN...
sqlserver2000挂起为你推荐
搜狗360360影视大全怎样免费看大片cisco2960cisco 2960 和3560支付宝账户是什么支付宝帐号,指的是什么帐号 是网营密码吗滴滴估值500亿滴滴拉屎 App 为何能估值 100 亿美金?是怎么计算出来的传奇域名谁有霸气一点的传奇名字。给个温州都市报招聘劳务市场找工作可靠吗什么是seo小红妹 seo是什么意思?seo网站优化该怎 随机阅读 seo是什么意思团购程序团购的流程有哪几种模式独立访客猎流的访问量都是真实的吗?想试试本帖隐藏的内容本帖隐藏的内容需要回复才可以浏览 是怎么弄的 我像弄1个 178的帖
解析域名 成都主机租用 免费ftp空间 云图标 台湾谷歌网址 40g硬盘 毫秒英文 空间论坛 新家坡 免费外链相册 宏讯 网页提速 重庆电信服务器托管 lick smtp服务器地址 论坛主机 万网主机 云销售系统 winds 建站技术 更多