SQL Server Replication Providing High Availability using Database Mirroring.docx
《SQL Server Replication Providing High Availability using Database Mirroring.docx》由会员分享,可在线阅读,更多相关《SQL Server Replication Providing High Availability using Database Mirroring.docx(27页珍藏版)》请在冰点文库上搜索。
SQLServerReplicationProvidingHighAvailabilityusingDatabaseMirroring
SQLServerReplication:
ProvidingHighAvailabilityusingDatabaseMirroring
SQLServerTechnicalArticle
Writers:
GopalAshok(MicrosoftCorporation),PaulS.Randal(SQL)
TechnicalReviewers:
HilaryCotter(RelevantNoise),PremMehra,LindseyAllen,SanjayMishra,GlennBerry(SQLServerMVP),JimmyMay,MikeRuthruff,MichaelRedman,JosephSack
ProjectEditor:
DianaSteinmetz
Published:
August 2008
AppliesTo:
SQLServer2008,SQLServer2005
Summary:
Thiswhitepaperdescribeshowtousedatabasemirroringtoincreasetheavailabilityofthereplicationstreaminatransactionalenvironment.Itcoverssettingupreplicationinamirroredenvironment,theeffectofmirroringpartnershipstatechanges,andtheeffectofmirroringfailoversonreplication.Inaddition,itdescribeshowtouseLSN-basedinitializationtorecoverfromthefailoverofamirroredsubscriberdatabase.
Althoughbriefoverviewsaregivenofbothreplicationanddatabasemirroring,itiseasiertounderstandthiswhitepaperifthereaderhassomeexperiencewithoneorbothofthesetechnologies,andhasatleastarudimentaryknowledgeofdatabaseconceptssuchastransactions.
Copyright
TheinformationcontainedinthisdocumentrepresentsthecurrentviewofMicrosoftCorporationontheissuesdiscussedasofthedateofpublication.BecauseMicrosoftmustrespondtochangingmarketconditions,itshouldnotbeinterpretedtobeacommitmentonthepartofMicrosoft,andMicrosoftcannotguaranteetheaccuracyofanyinformationpresentedafterthedateofpublication.
ThisWhitePaperisforinformationalpurposesonly.MICROSOFTMAKESNOWARRANTIES,EXPRESS,IMPLIEDORSTATUTORY,ASTOTHEINFORMATIONINTHISDOCUMENT.
Complyingwithallapplicablecopyrightlawsistheresponsibilityoftheuser.Withoutlimitingtherightsundercopyright,nopartofthisdocumentmaybereproduced,storedinorintroducedintoaretrievalsystem,ortransmittedinanyformorbyanymeans(electronic,mechanical,photocopying,recording,orotherwise),orforanypurpose,withouttheexpresswrittenpermissionofMicrosoftCorporation.
Microsoftmayhavepatents,patentapplications,trademarks,copyrights,orotherintellectualpropertyrightscoveringsubjectmatterinthisdocument.ExceptasexpresslyprovidedinanywrittenlicenseagreementfromMicrosoft,thefurnishingofthisdocumentdoesnotgiveyouanylicensetothesepatents,trademarks,copyrights,orotherintellectualproperty.
Unlessotherwisenoted,theexamplecompanies,organizations,products,domainnames,e-mailaddresses,logos,people,placesandeventsdepictedhereinarefictitious,andnoassociationwithanyrealcompany,organization,product,domainname,emailaddress,logo,person,placeoreventisintendedorshouldbeinferred.
©2008MicrosoftCorporation.Allrightsreserved.
Microsoft,SQLServer,andtheServerIdentityLogoareeitherregisteredtrademarksortrademarksofMicrosoftCorporationintheUnitedStatesand/orothercountries.
Thenamesofactualcompaniesandproductsmentionedhereinmaybethetrademarksoftheirrespectiveowners.
TableofContents
Introduction1
Technologies2
TransactionalReplicationArchitecture2
DatabaseMirroringArchitecture3
DeployingDatabaseMirroringandReplicationTogether5
MirroringthePublicationDatabase6
ConfiguringReplicationwithaMirroredPublicationDatabase6
EffectoftheMirroringStateontheReplicationLogReader7
ChangingtheReplicationLogReaderBehaviorbyUsingTraceFlag14488
EffectofaMirroringFailoverontheReplicationLogReader9
LogReaderAgentBehavioriftheMirroringPartnershipisBroken10
MirroringtheSubscriptionDatabase11
ConfiguringtheDistributionRetentionPeriod12
ManualSynchronizationTypesforSubscriptions12
HowDoesInitializingfromanLSNWork?
13
RecoveringtheReplicationStreamFollowingaMirroringFailover15
Conclusion18
Introduction
TransactionalreplicationisthemechanismthatMicrosoft®SQL Server®providestopublishincrementaldataandschemachangestosubscribers.Thechangesarepublished(thereplicationstream)intheorderinwhichtheyoccur,andtypicallythereislowlatencybetweenthetimethechangeismadeonthePublisherandthetimethechangetakeseffectontheSubscriber.Thisenablesanumberofscenarios,suchasscalingoutaqueryworkloadorpropagatingdatafromacentralofficetoremoteofficesandvice-versa.Thisformofreplicationalwaysusesahierarchicalhubandspoketopology.
Theadditionofpeer-to-peertransactionalreplicationinSQLServer 2005simplifiestheimplementationofabi-directionaltransactionalreplicationtopology,wherethereplicationstreamflowsbothways.Inthistopology,anyparticipatingnodemayreadorupdatethedata.Properlypartitionedmodificationsarepropagatedbetweenallnodesinafullmeshtopology(asshowninFigure 1),allowingthedatatobehighlyavailableintheeventthatoneserverisunavailable.ThisfeaturehasbeenfurtherimprovedinSQLServer 2008withconflictdetectionandonlinechangesforpeer-to-peertopologies.
Figure1:
Fullmeshtopologyforpeer-to-peerreplicationwiththreeandfournodes
Transactionalreplicationtopologiescanbemademoreresilienttoserverfailures,andhencemorehighlyavailable,byaddingredundantcopiesofthevariousdatabasesinvolved.Thisisespeciallyimportantforhubandspoketopologies.Caremustbetaken,however,becausereplicationisreliantontheservernamesoftheserversinthetopology,soanyfailovertoanotherservercanresultinthereplicationstreambeingbroken.
VariousmechanismsinSQL Serverprovidedatabase-levelredundancy,suchasbackup/restore,logshipping,anddatabasemirroring(inSQLServer 2005andlater).Databasemirroringistheonlymechanismthatprovidesareal-time,exactcopyoftheprotecteddatabasewiththeguaranteeofzerodataloss(whenthemirrorissynchronized).
Thiswhitepaperdescribeshowtousedatabasemirroringtoincreasetheavailabilityofthereplicationstreaminatransactionalenvironment.Itcoverssettingupreplicationinamirroredenvironment,theeffectofmirroringpartnershipstatechanges,andtheeffectthatmirroringfailovershaveonreplication.Inaddition,itdescribeshowtouseLSN-basedinitializationtorecoverfromthefailoverofamirroredsubscriberdatabase.
Technologies
TransactionalReplicationArchitecture
Transactionalreplicationandpeer-to-peerreplicationusethesamearchitecturetomovechangesbetweentheserversinareplicationtopology.Thefollowingillustrationisanoverviewofthecomponentsinvolvedintransactionalreplication.
Figure2:
Transactionalreplicationarchitectureoverview
Aminimumofthreeserverrolesarerequiredfortransactionalreplication:
∙Publisher,hostingthepublicationdatabase
∙Distributor,hostingthedistributiondatabase
∙Subscriber,hostingthesubscriptiondatabase
Dependingonthecomplexityofthereplicationtopology,theremaybemultipleSubscriberserversor,inthecaseofpeer-to-peerreplication,multiplepeerserverswiththereplicationstreamflowinginbothdirectionsbetweenthepeers.Furthermore,therolesofthevariousreplicationserverscanbeplayedbyoneserverorbyindividualservers(themorecommoncase),anditispossibleforaservertoplayanycombinationofroles.Regardless,thevariousserversanddatabasesmustbeprotectedtoensurethatthereplicationstreamishighlyavailable.
Transactionalreplicationreliesonvariousagentstoperformthetasksassociatedwithtrackingchangesanddistributingdata.Theseagentsare:
∙SnapshotAgent,whichrunsattheDistributor.Thisagentpreparesschemaandinitialdatafilesofpublishedtablesandotherobjects,storesthesnapshotfiles,andrecordsinformationaboutsynchronizationinthedistributiondatabase.
∙LogReaderAgent,whichrunsattheDistributor.ThisagentconnectstothePublisherandmovestransactionsmarkedforreplicationfromthetransactionlogofthepublicationdatabasetothedistributiondatabase.
∙DistributionAgent,whichrunsattheDistributorforpushsubscriptions,andattheSubscriberforpullsubscriptions.Thisagentappliesthe(optional)initialsnapshottotheSubscribersandmovestransactionsheldinthedistributiondatabasetoSubscribers
∙QueueReaderAgent,whichrunsattheDistributor.ThisagentisonlyusedfortransactionalreplicationwithupdateablesubscriptionsandmoveschangesmadeontheSubscribersbacktothePublisher.
ItshouldbementionedthatthereisalsoaMergeAgent,butitisusedonlyformergereplication,whichisnotcoveredinthispaper.
ThiswhitepaperfocusmainlyontheLogReaderAgentandtheDistributionAgent.
FormoredetailedinformationonSQLServerReplication,seethefollowing"SQL ServerReplication"topicsinSQL ServerBooksOnline:
∙forSQLServer2008
∙forSQLServer2005
DatabaseMirroringArchitecture
Databasemirroringworksatthedatabaselevelandprovidesasinglecopyofthemirroreddatabasethatmustresideonadifferentserverinstance,usuallyonaseparatephysicalserverinadifferentlocation.Oneserverinstanceservesthedatabasetoclients(theprincipalserver).Theotherinstanceactsasahotorwarmstandbyserver(themirrorserver),dependingontheconfigurationofthedatabasemirroringsessionandthemirroringstateofthemirroreddatabases.Thetwoserversaresaidtobepartnersinthemirroringsession.
Whenthemirroreddatabaseissynchron