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

Vultr新注册赠送100美元活动截止月底 需要可免费享30天福利

昨天晚上有收到VULTR服务商的邮件,如果我们有清楚的朋友应该知道VULTR对于新注册用户已经这两年的促销活动是有赠送100美元最高余额,不过这个余额有效期是30天,如果我们到期未使用完的话也会失效的。但是对于我们一般用户来说,这个活动还是不错的,只需要注册新账户充值10美金激活账户就可以。而且我们自己充值的余额还是可以继续使用且无有效期的。如果我们有需要申请的话可以参考"2021年最新可用Vul...

QQ防红跳转短网址生成网站源码(91she完整源码)

使用此源码可以生成QQ自动跳转到浏览器的短链接,无视QQ报毒,任意网址均可生成。新版特色:全新界面,网站背景图采用Bing随机壁纸支持生成多种短链接兼容电脑和手机页面生成网址记录功能,域名黑名单功能网站后台可管理数据安装说明:由于此版本增加了记录和黑名单功能,所以用到了数据库。安装方法为修改config.php里面的数据库信息,导入install.sql到数据库。...

buyvm美国大硬盘VPS,1Gbps带宽不限流量

buyvm正式对外开卖第四个数据中心“迈阿密”的块存储服务,和前面拉斯维加斯、纽约、卢森堡一样,依旧是每256G硬盘仅需1.25美元/月,最大支持10T硬盘。配合buyvm自己的VPS,1Gbps带宽、不限流量,在vps上挂载块存储之后就可以用来做数据备份、文件下载、刷BT等一系列工作。官方网站:https://buyvm.net支持信用卡、PayPal、支付宝付款,支付宝付款用的是加元汇率,貌似...

sqlserver2000挂起为你推荐
sns平台sns是什么平台wordpress模板wordpress的模版怎么用cisco2960配置思科2960G交换机如何将配置百兆改为千兆配置购物车通过自己的体会总结购物车的作用银花珠树晓来看谜语白色花无人栽一夜北风遍地开。旡根无叶又无枝不知是谁送花来。谜底是什么厦门三五互联科技股份有限公司厦门三五互联怎么样?300051三五互联请问300051三五互联还会继续盘升吗?oa办公软件价格一个oa系统多少钱付款方式淘宝有哪几种付款方式?账号通如何绑定天下贰手机账号通?
域名城 备案未注册域名 荣耀欧洲 singlehop googleapps 天猫双十一抢红包 建站代码 网站被封 主机合租 全站静态化 ftp教程 中国电信测网速 可外链相册 1美金 网站在线扫描 万网主机管理 服务器维护 东莞服务器托管 杭州电信宽带优惠 国外网页代理 更多