SQL Server Replication Providing High Availability using Database MirroringWord下载.docx

资源描述

SQL Server Replication Providing High Availability using Database MirroringWord下载.docx

《SQL Server Replication Providing High Availability using Database MirroringWord下载.docx》由会员分享，可在线阅读，更多相关《SQL Server Replication Providing High Availability using Database MirroringWord下载.docx（27页珍藏版）》请在冰点文库上搜索。

SQL Server Replication Providing High Availability using Database MirroringWord下载.docx

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.

2008MicrosoftCorporation.Allrightsreserved.

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

展开阅读全文