Checkmssql2005

DELLPOWERSOLUTIONSReprintedfromDellPowerSolutions,February2006.
Copyright2006DellInc.
Allrightsreserved.
February200626DATABASES:SQLSERVER2005Enterprisedatacentersthathostbusiness-criticalappli-cationsanddatabasescanbeatriskfromfailuresanddisasters.
Availabilityandrecoverabilitybecomecrucialforbusinessoperations.
Currentdatabaseavailabilitytechnolo-giesvaryintheircomplexity,cost,levelofautomation,incurreddowntime,andsupporteddistance.
Amajorchal-lengeforanenterpriseisdeterminingthemostcost-effectiveandleastcomplexsolutiontohelpensuredatabaseavail-abilityandmeetbusinessneeds.
MicrosoftSQLServer2005introducesseveralhigh-availabilityfeaturesandfeatureenhancementsfortheSQLServerdatabaseplatform.
FailoverclusteringFailoverclusteringwithMicrosoftClusterService(MSCS)isapopularwaytoachievehighavailabilityforSQLServer.
WithMSCS,multipleserversornodesarelinkedtofunctionasasinglesystemandprovideanautomaticfailoversolution.
Enterprisesareconnectedtovirtualserv-ersandnotphysicalservers;eachactivephysicalservermayhostoneormorevirtualservers.
Ifoneofthephysicalserversfailsbecauseofeitherahardwareorsoftwareproblem,MSCSdetectsthefailureandmovesresourcesthatresideonthefailingserver—includingthevirtualserver(s)—tooneormoreremainingphysicalservers.
EndusersconnectedtothefailedserverobserveonlyamomentarydelayinaccessingitsresourceswhileMSCSrestartsSQLServerservicesontheremainingserver(s)andremapsthevirtualserverconnections.
Installingafailoverclusterrequiresthefollowing:MicrosoftWindows2000Server(AdvancedServerorDatacenterServer)orMicrosoftWindowsServer2003(EnterpriseEdition,DatacenterEdition,Enterprisex64Edition,orDatacenterx64Edition)MicrosoftSQLServer2005(StandardEditionorEnterpriseEdition)SharedstoragebasedonSCSI,FibreChannel,orSerialAttachedSCSIAllcomponents(server,hostbusadapter,andstor-agesystem)andtheclustersolutionlistedintheMicrosoftWindowsServerCatalogFigure1showstwotypicaltwo-nodefailoverclusterconfigurationsforSQLServer2005,oneinactive/passiveBYANANDASANKARAN,DATNGUYEN,ANDNAMNGUYENExploringHigh-AvailabilityFeaturesinMicrosoftSQLServer2005sMicrosoftSQLServer2005offersfailoverclustering,databasemirroring,logshipping,replication,anddatabasesnapshotfeaturesthatcanhelpsafeguardanenterprise'scriticaldataandoperations.
Thisarticlediscussesthesefeaturesandtheirassociatedtrade-offs.
RelatedCategories:ClusteringDatabaseHighavailability(HA)MicrosoftSQLServer2005Visitwww.
dell.
com/powersolutionsforthecompletecategoryindex.
DATABASES:SQLSERVER2005www.
dell.
com/powersolutionsReprintedfromDellPowerSolutions,February2006.
Copyright2006DellInc.
Allrightsreserved.
DELLPOWERSOLUTIONS27modeandoneinactive/activemode.
Inafailoverclusterenvi-ronment,SQLServer2005offersseveraladditionalfeaturesandimprovementscomparedtoSQLServer2000:Supportformultinodeclustering:StandardEditionallowsuptotwo-nodeclusteringandEnterpriseEditionuptoeight-nodeclustering.
SupportforSQLservices:AnalysisServicesfeatureoffersmulti-instancesupportandfull-textindexing.
Easierinstallation:SystemConfigurationCheckautomati-callydetectsandinstallsSQLServerasvirtualserversacrossthenodesinthecluster.
DatabasemirroringDatabasemirroringisdesignedtoaugmenttheavailabilityofSQLServer2005.
Withthisfeature,transactionstoadatabasehostedonaSQLServerinstance,referredtoastheprincipal,canbecon-tinuouslyduplicatedinrealtimeontoacopyhostedonanotherinstance,referredtoasthemirror.
Theprincipalandmirrorserversareconsideredpartnersinadatabasemirroringsession.
Transac-tions(suchasinsertions,updates,anddeletions)ontheprincipaldatabaseareduplicatedbycontinuouslysendingtransactionlogrecordstothemirroroverthenetwork(seeFigure2).
Themirrorreceivesthelogrecordscontinuouslyandrestoresthem.
Iftheprincipalormirrorserverincursafailure,themirror-ingsessionisdisconnected.
Beforestartingthemirroringsession,administratorsmustinitializethemirrordatabasefromaprincipaldatabasefull-restorewiththeNORECOVERYoption.
Besidestheprimaryandmirror,amirroringsessionmayincludeanoptionalthirdserver,referredtoasthewitness.
Thewitnessserverenablesautomaticfailoverafteraprincipalserverfailurebypromotingthemirrortofunctionastheprincipal.
Usingawitnessserverhelpsachieveaquorumandpreventsaccidentalpromotionofthemirrordatabasethatresultsfromcommunicationfailuresbetweentheprincipalandmirror.
Databasemirroringrequiresthefollowingconfiguration:Theprimaryandmirrordatabasesmustbehostedonsepa-rateSQLServer2005instances.
TheprincipaldatabasemustbesettotheFullRecoverymodel,nottheBulk-LoggedRecoveryorSimpleRecoverymodel.
Databasemirroringcanbeconfiguredinanyofthefollowingthreeoperatingmodesbasedontransactionsafetylevelandthepresenceofawitnessserver:High-availabilitymode:Inthismode,logrecordsaretrans-ferredsynchronouslyfromtheprincipaldatabasetothemirror.
Theprincipalwaitsforanacknowledgmentfromthemirrorbefore"hardening"logrecordstoitsdisk.
Inadditiontoprovidingsynchronoustransfer,thismodeusesawitnessservertoenableautomaticfailover.
High-protectionmode:Thismodeissimilartothehigh-availabilitymodeexceptthatitdoesnotsupportawitnessserver,andthusautomaticfailoverisnotpossible.
However,failovercanbeperformedmanuallytopromotethemirrordatabase.
Becausethelogrecordsaretransferredsynchro-nouslyinthismode,themirrordatabaseissynchronousandconsistentwiththeprincipal.
High-performancemode:Inthismode,logrecordsaretransferredasynchronouslyfromtheprincipaltothemirrordatabase.
Theprincipaldoesnotwaitforanacknowledg-mentfromthemirrorbefore"hardening"itslogstothedisk,Figure1.
FailoverclusterconfigurationsforSQLServer2005SQLServer2005db1db2SQLServer2005HeartbeatLANActivePassiveSQLServer2005db1SQLServer2005HeartbeatLANActiveActivedb2Figure2.
Databasemirroringfordatabasedb1inhigh-availabilitymodeLANPrincipalMirrordb1db2db3SQLServer2005db5db6SQLServer2005SQLServer2005db1TransactionlogrecordsLogrecoveryWitnessDATABASES:SQLSERVER2005DELLPOWERSOLUTIONSReprintedfromDellPowerSolutions,February2006.
Copyright2006DellInc.
Allrightsreserved.
February200628andthemirrorisnotguaranteedtobesynchronouswiththeprincipalatanypoint.
Thismodedoesnotsupportawit-nessserver,andthusneitherautomaticnormanualfailoverispossible.
However,aforcedfailovercanbeperformedtorecoverthemirror,withsomedatalossoccurringbecausemirroringisasynchronous.
Akeyadvantageofdatabasemirroringisthatitprovidesquickfailoverandhighavailabilitywithminimalhardwarecostandcomplexity.
Formerly,suchSQLServermirroringsolutionswereoftenfeasibleonlywithexpensive,proprietaryhardware-basedmechanisms.
Withacceptablelatenciesandbandwidth,mirroringcanbedeployedoveralong-distancewideareanetwork(WAN)aspartofadisasterrecoverysolution.
Duringthemirroringsession,themirrordata-baseisnotavailableforclientaccessbecauseitisinarecoverystate,butadatabasesnapshotcanbecreatedonthemirrorforlimitedreporting.
Onlyonemirrordatabasecanbeestablishedforaprinci-paldatabase.
However,theparticipatingSQLServerinstancescanassumediffer-entrolesfordifferentmirror-ingsessions.
Forexample,aSQLServerinstancecanserveasaprincipalforonedatabase,asamirrorforanotherdatabase,andasawitnessforyetanothermirroringsession.
Thenotionofavirtualserver,asisusedinfailoverclustering,doesnotexist.
However,MicrosoftADO.
NETprovidesanapplicationprogramminginterface(API)forclientstoautomaticallyreconnecttothemirrorserverduringfailures.
LogshippingLogshippingissimilartodatabasemirroring.
Itallowsadatabasehostedonaprimaryservertoautomaticallysendtransactionlogstoasecondarydatabaseforduplication.
Thelogrecordsarenottransferredcontinuouslyasindatabasemirroring,butratherinintervals.
Logshippingcomprisesbackingupthetransactionlogsontheprimaryserverinstance,copyingthebacked-uplogstothesecondaryserverinstance,andrestoringthecopiedlogsonthesecondaryserverinstanceperiodically.
TheprimarydatabaseshouldbeconfiguredfortheFullRecoveryorBulk-LoggedRecoverymodel.
ThesecondarydatabaseshouldbeinitializedbyafullrecoveryoftheprimarydatabasewiththeNORECOVERYorSTANDBYoption.
Logshippingcanoptionallyincludeathirdservernamed"mon-itor"torecordthehistoryoflog-shippingoperationsandtoraisealertsduringfailures.
Log-shippingoperationsarecarriedoutbyfourMicrosoftSQLServeragentjobs:backupjob,copyjob,restorejob,andalertjob.
Duringlogshipping,thesecondarydatabaseisnotcompletelysynchronizedwiththeprimarydatabase.
Also,logshippingdoesnotprovideautomaticfailovertothesecondaryserverduringafailure.
Logshippingsupportsmultiplesecondaryserversforaprimaryserver.
Inaddition,aSQLServerinstancecanfunctionasasecondaryormonitorserverformultipleprimaryservers.
Thesecondaryservercanbeuseddirectlyforlimitedqueryprocessing.
Logshippingcanbeusedinscenarioswheremultipledestina-tionserversareneededandwheredelaysinrestoringlogsontheduplicateareacceptable.
ReplicationReplicationisasetoftechnologiesfordistributingdataanddata-baseobjectsacrossMicrosoftSQLServerdatabasesoveranet-work.
Replicationinvolvesapublisherdatabaseinstancethatmakesdataavailableforcopying,adistributorinstanceforcopyingdataandmaintainingmetadata,andsubscriberinstancesthatcanreceivereplicateddata.
SQLServer2005providesthreetypesofreplicationtech-nologiessuitablefordifferentneeds:transactional,merge,andsnapshot.
SQLServer2005addsenhancementstotheserepli-cationmethodstohelpimprovescalability,performance,andmonitoringcapabilities.
Someoftheimprovementsincludeadditionalreplicationdatatypes,supportforpartitionedtablesandfull-textindex,directDataDefinitionLanguage(DDL)replicationforschemachanges,andreplicationfromOracledatabases.
Intransactionalreplication,SQLServer2005addsanimpor-tantfeaturecalledpeer-to-peerreplication,whichenhancestheexistingbidirectionalreplicationoption.
Advantagesofreplicateddatabasesincludedataloadbalancinganddisasterrecovery.
Withthepeer-to-peerreplicationfeature,administratorscansetupmultiplepeer-to-peertransactionalreplicationpairsamongdifferentdatacenters.
Thatway,ifonedatacenterisdown,itcanbefailedovertoitspeercenter(s).
Althoughthereisnolimittothenumberofnodesinapeer-to-peertopology,man-ageabilityofthepeer-to-peerrelationshipsbetweennodescanbecomeaconstraint.
SQLServer2005providesthreetypesofreplicationtechnologiessuitablefordifferentneeds:transactional,merge,andsnapshot.
SQLServer2005addsenhancementstothesereplicationmethodstohelpimprovescalability,performance,andmonitoringcapabilities.
DATABASES:SQLSERVER2005www.
dell.
com/powersolutionsReprintedfromDellPowerSolutions,February2006.
Copyright2006DellInc.
Allrightsreserved.
DELLPOWERSOLUTIONS29DatabasesnapshotsAdministrativeandapplicationerrorscanbeamajorcauseofdatabasedowntime.
SQLServer2005introducesafeaturecalledData-baseSnapshotstohelpprotectadatabasefromerroneoususeroperationsandtoaugmentdatabasebackupandrestoreoperations.
TheDatabaseSnapshotsfeatureallowsrecoveryfromusererrorsbyallowingthedatabasestatetoreverttoapointintimebeforetheerror(s)occurred.
Itworksinstand-alone,failoverclus-tering,anddatabasemirroringenvironments.
However,itdoesnotworkwithalog-shippingsecondarydatabase.
Adatabasesnapshotisasnapshotofanentiredatabaseatagiventime.
Thesnapshotiscreatedinstantlyandcanbeusedforread-onlytaskssuchasreporting.
Asnapshotmusthavebeencre-atedbeforetheerroroccurredforanadministratortorestorethedatabasetoaconsistentstate.
Snapshotcreationdoesnotimposerestrictionsonthebasedatabaseoperations.
Administratorscancreatemultiplesnapshotsfromadatabaseaswell.
TheSQLServer2005DatabaseSnapshotfeatureemploysacopy-on-writemechanism.
Onlychangestothebasedatabase—nottheentirebasedatabase—arerecordedafterthesnapshothasbeentaken.
Thesnapshotisapointerimagetothebasedatabase.
Itsharestheunchangedpagesofthebasedatabaseandrequiresonlyextrastorageforchangedpages.
TheDatabaseSnapshotmechanismisillustratedinFigure3.
TheDatabaseSnapshotfeatureisdesignedtobeextremelyspace-efficient.
Anymodificationsuchasawrite(step1inFigure3)tothebasedatabaseafterasnapshotistakenwillberecorded(step2inFigure3)aspartofthetargetsnapshot.
IfI/Oreadrequests(step3inFigure3)aretoblocksthathavenotchangedsincethesnapshotwascreated,thentherequestwillreadfromthebasedatabase.
IfI/Orequestsaretoblocksthathavechangedsincesnapshotcreation,thentherequestwillbereadfromthesnapshot.
Figure4summarizesthehigh-availabilityfeaturesandoptionsavailableinMicrosoftSQLServer2005.
Ahigh-availabilitydatabaseplatformMicrosoftSQLServer2005offersseveralfeaturesthataredesignedtohelpaugmentavailabilityofdata.
DellPowerEdgeservers,DellPowerVaultstorage,andDell/EMCstoragearealsodesignedwithabroadarrayofredundanthardwareandsoftwarefeaturestomaximizehardwareavailability.
ThefeaturesinDellhardwareandSQLServer2005provideanintegrated,industry-standardapproachtohighdatabaseavailability.
Acombinationofthesecomponentscangreatlybenefitenterpriseswithhigh-availabilitydatabaserequirements.
AnandaSankaranisasystemsengineerintheHigh-AvailabilityClusterDevelopmentGroupatDell.
Hiscurrentinterestsrelatedtohigh-availabilityclusteringincludestoragesystems,applicationperformance,businessconti-nuity,andclustermanagement.
Anandahasamaster'sdegreeinComputerSciencefromTexasA&MUniversity.
DatNguyenisasystemsengineerintheHigh-AvailabilityClusterDevelop-mentGroupatDell.
Hisresponsibilitiesincludedevelopingstorageareanetwork(SAN)–basedhigh-availabilityclusteringproducts.
Hiscurrentinterestsareinenterprisestorageproductsandtechnologies.
DathasaB.
S.
inElectricalEngineeringfromtheUniversityofHouston.
NamNguyenisaseniorconsultantintheHigh-AvailabilityClusterDevelop-mentGroupatDell,andtheleadengineerforDellFibreChannelPowerEdgeClusterproducts.
Hiscurrentinterestsincludebusinesscontinuity,cluster-ing,andstoragetechnologies.
HehasaB.
S.
andanM.
S.
inElectricalEngineeringfromTheUniversityofTexasatAustin.
FORMOREINFORMATIONMicrosoftSQLServer2005:www.
dell.
com/sqlwww.
microsoft.
com/sqlFigure3.
DatabaseSnapshotmechanisminSQLServer2005123456789101112123456789101112123SnapshotsPageBasedatabaseFigure4.
High-availabilityoptionsandfeaturesinSQLServer2005Solution-specicAutomaticcerticationGranularityDatalossfailoverDowntimerequirementsFailoverclusteringSystemandNoYesApproximatelyClustersolutionlisteddatabase20secondsplusinMicrosoftWindowsdatabaserecoverytimeServerCatalogDatabasemirroringDatabaseDependsonYesLessthan3secondsNooperatingmodePeer-to-peerreplicationTableorviewSomeOptionalVariable(littletonone)NoLogshippingDatabaseSomeNoVariableNoDatabasesnapshotsTableorviewSomeNoVariableNo