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》由会员分享，可在线阅读，更多相关《SQL Server Replication Providing High Availability using Database Mirroring.docx（27页珍藏版）》请在冰豆网上搜索。

SQL Server Replication Providing High Availability using Database Mirroring.docx

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.

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.

Microsoft,SQLServer,andtheServerIdentityLogoareeitherregisteredtrademarksortrademarksofMicrosoftCorporationintheUnitedStatesand/orothercountries.

Thenamesofactualcompaniesandproductsmentionedhereinmaybethetrademarksoftheirrespectiveowners.

TableofContents

Introduction1

Technologies2

TransactionalReplicationArchitecture2

DatabaseMirroringArchitecture3

DeployingDatabaseMirroringandReplicationTogether5

MirroringthePublicationDatabase6

ConfiguringReplicationwithaMirroredPublicationDatabase6

EffectoftheMirroringStateontheReplicationLogReader7

ChangingtheReplicationLogReaderBehaviorbyUsingTraceFlag14488

EffectofaMirroringFailoverontheReplicationLogReader9

LogReaderAgentBehavioriftheMirroringPartnershipisBroken10

MirroringtheSubscriptionDatabase11

ConfiguringtheDistributionRetentionPeriod12

ManualSynchronizationTypesforSubscriptions12

HowDoesInitializingfromanLSNWork?

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

展开阅读全文