www.dell.com/powersolutions

sqlserver2000挂起  时间:2021-04-23  阅读:()
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

vpsdime:VPS内存/2核/VPS,4G内存/2核/50gSSD/2T流量/达拉斯机房达拉斯机房,新产品系列-Windows VPS

vpsdime上了新产品系列-Windows VPS,配置依旧很高但是价格依旧是走低端线路。或许vpsdime的母公司Nodisto IT想把核心产品集中到vpsdime上吧,当然这只是站长个人的猜测,毕竟winity.io也是专业卖Windows vps的,而且也是他们自己的品牌。vpsdime是一家新上来不久的奇葩VPS提供商,实际是和backupspy以及crowncloud等都是同一家公司...

轻云互联-618钜惠秒杀,香港CN2大宽带KVM架构云服务器月付22元,美国圣何塞精品云月付19元爆款!海量产品好货超值促销进行中!

官方网站:点击访问青云互联活动官网优惠码:终身88折扣优惠码:WN789-2021香港测试IP:154.196.254美国测试IP:243.164.1活动方案:用户购买任意全区域云服务器月付以上享受免费更换IP服务;限美国区域云服务器凡是购买均可以提交工单定制天机防火墙高防御保护端口以及保护模式;香港区域购买季度、半年付、年付周期均可免费申请额外1IP;使用优惠码购买后续费周期终身同活动价,价格不...

易探云:香港CN2云服务器低至18元/月起,183.60元/年

易探云怎么样?易探云最早是主攻香港云服务器的品牌商家,由于之前香港云服务器性价比高、稳定性不错获得了不少用户的支持。易探云推出大量香港云服务器,采用BGP、CN2线路,机房有香港九龙、香港新界、香港沙田、香港葵湾等,香港1核1G低至18元/月,183.60元/年,老站长建站推荐香港2核4G5M+10G数据盘仅799元/年,性价比超强,关键是延迟全球为50ms左右,适合国内境外外贸行业网站等,如果需...

sqlserver2000挂起为你推荐
支持ipad企业cms目前最好用的企业cms是哪个?destoondestoon有多少人用Destoon做站centos6.5如何安装linux centos6.5波音737起飞爆胎飞机会爆胎的吗?申请支付宝账户申请支付宝账号注册加多宝和王老吉王老吉和加多宝是什么关系泉州商标注册泉州商标注册找什么公司?pintang目前世界上最稀有、最珍贵的钱币是什么?香港空间香港有什么标志性建筑?
warez ddos 主机点评 windows主机 59.99美元 网站保姆 轻量 ibrs 个人域名 免空 已备案删除域名 老左来了 qq云端 息壤代理 多线空间 网站在线扫描 raid10 免费asp空间 贵阳电信测速 umax 更多