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

Linode十八周年及未来展望

这两天Linode发布了十八周年的博文和邮件,回顾了过去取得的成绩和对未来的展望。作为一家运营18年的VPS主机商,Linode无疑是有一些可取之处的,商家提供基于KVM架构的VPS主机,支持随时删除(按小时计费),可选包括美国、英国、新加坡、日本、印度、加拿大、德国等全球十多个数据中心,所有机器提供高出入网带宽,最低仅$5/月($0.0075/小时)。This month marks Linod...

ManSora:英国CN2 VPS,1核/1GB内存/10GB SSD/1TB流量/100Mbps/KVM,$18.2/月

mansora怎么样?mansora是一家国人商家,主要提供沪韩IEPL、沪日IEPL、深港IEPL等专线VPS。现在新推出了英国CN2 KVM VPS,线路为AS4809 AS9929,可解锁 Netflix,并有永久8折优惠。英国CN2 VPS,$18.2/月/1GB内存/10GB SSD空间/1TB流量/100Mbps端口/KVM,有需要的可以关注一下。点击进入:mansora官方网站地址m...

bgpto:独立服务器夏季促销,日本机器6.5折、新加坡7.5折,20M带宽,低至$93/月

bgp.to对日本机房、新加坡机房的独立服务器在搞特价促销,日本独立服务器低至6.5折优惠,新加坡独立服务器低至7.5折优惠,所有优惠都是循环的,终身不涨价。服务器不限制流量,支持升级带宽,免费支持Linux和Windows server中文版(还包括Windows 10). 特色:自动部署,无需人工干预,用户可以在后台自己重装系统、重启、关机等操作!官方网站:https://www.bgp.to...

sqlserver2000挂起为你推荐
企业电子邮局企业邮箱怎么使用?sqlserver2000挂起SQL server2000 安装为什么老是提示挂起?360免费建站我用的360免费建站,但自己买的一级域名要先备案,360不提供备案,我要怎么做才能把我的域名绑定网站啊?govya银花珠树晓来看姗姗而来的 作文加多宝与王老吉王老吉和加多宝什么关系?35邮箱邮箱地址怎么写zencart模板zen cart套用模板后,外观控制显示红色打不开,为什么?帖子标题百度贴吧如何改帖子的标题顽固木马专杀工具腾讯顽固木马专杀怎么授权
淘宝虚拟主机 申请域名 浙江vps 个人域名备案流程 新网域名管理 pw域名 l5639 seovip 好看的桌面背景图 1g空间 免费申请个人网站 支持外链的相册 789电视剧 美国独立日 英国伦敦 cxz 德隆中文网 广东主机托管 双十二促销 xshell5注册码 更多