Redirectionserver2008

ProSQLServer2008MirroringRobertL.
Davis,KenSimmonsProSQLServer2008MirroringCopyright2009byRobertL.
Davis,KenSimmonsAllrightsreserved.
Nopartofthisworkmaybereproducedortransmittedinanyformorbyanymeans,electronicormechanical,includingphotocopying,recording,orbyanyinformationstorageorretrievalsystem,withoutthepriorwrittenpermissionofthecopyrightownerandthepublisher.
ISBN-13(pbk):978-1-4302-2423-5ISBN-13(electronic):978-1-4302-2424-2PrintedandboundintheUnitedStatesofAmerica987654321Trademarkednamesmayappearinthisbook.
Ratherthanuseatrademarksymbolwitheveryoccurrenceofatrademarkedname,weusethenamesonlyinaneditorialfashionandtothebenefitofthetrademarkowner,withnointentionofinfringementofthetrademark.
LeadEditor:JonathanGennickTechnicalReviewer:GlennBerryEditorialBoard:ClayAndres,SteveAnglin,MarkBeckner,EwanBuckingham,TonyCampbell,GaryCornell,JonathanGennick,JonathanHassell,MichelleLowman,MatthewMoodie,JeffreyPepper,FrankPohlmann,DouglasPundick,BenRenow-Clarke,DominicShakeshaft,MattWade,TomWelshProjectManager:KylieJohnstonCopyEditor:HastingsHartCompositor:MarySudulIndexer:JulieGradyArtist:AprilMilneDistributedtothebooktradeworldwidebySpringer-VerlagNewYork,Inc.
,233SpringStreet,6thFloor,NewYork,NY10013.
Phone1-800-SPRINGER,fax201-348-4505,e-mailorders-ny@springer-sbm.
com,orvisithttp://www.
springeronline.
com.
Forinformationontranslations,pleasecontactApressdirectlyat233SpringStreet,NewYork,NY10013.
E-mailinfo@apress.
com,orvisithttp://www.
apress.
com.
ApressandfriendsofEDbooksmaybepurchasedinbulkforacademic,corporate,orpromotionaluse.
eBookversionsandlicensesarealsoavailableformosttitles.
Formoreinformation,referenceourSpecialBulkSales–eBookLicensingwebpageathttp://www.
apress.
com/info/bulksales.
Theinformationinthisbookisdistributedonan"asis"basis,withoutwarranty.
Althougheveryprecautionhasbeentakeninthepreparationofthiswork,neithertheauthor(s)norApressshallhaveanyliabilitytoanypersonorentitywithrespecttoanylossordamagecausedorallegedtobecauseddirectlyorindirectlybytheinformationcontainedinthiswork.
Thesourcecodeforthisbookisavailabletoreadersathttp://www.
apress.
com.
CONTENTSiiiContentsataGlanceContents.
ivAbouttheAuthorsxiiAbouttheTechnicalReviewerxiiiIntroduction.
xivChapter1:HighAvailabilityOptions.
1Chapter2:DatabaseMirroringOverview.
11Chapter3:PlanningandBestPractices.
23Chapter4:DatabaseMirroringSetup31Chapter5:AutomatingSetup.
53Chapter6:MaintenanceSimplified.
95Chapter7:MonitoringandAlerting143Chapter8:UpgradingtoSQLServer2008.
171Chapter9:ReportingConsiderations.
189Chapter10:PlanningforFailure209Index227CONTENTSivContentsContentsataGlance.
iiiContents.
ivAbouttheAuthors.
xiiAbouttheTechnicalReviewer.
xiiiIntroduction.
xivChapter1:HighAvailabilityOptions.
1HighAvailabilityvs.
DisasterRecovery.
1DatabaseMirroring.
2FailoverClustering.
2LogShipping.
4Replication.
6ComparingHigh-AvailabilityFeatures7DesigningaCompleteHigh-AvailabilitySolution.
8Summary10Chapter2:DatabaseMirroringOverview11DefiningKeyTerms.
11ChoosinganOperatingMode.
12High-SafetyMode.
14High-PerformanceMode15UnderstandingSessions.
17PausingandResumingSessions.
17CONTENTSvRunningMultipleSessions17UnderstandingMirroringStates.
18SwitchingRoles.
20TransparentClientRedirection.
20EnhancedFeatures.
20LogStreamCompression20AutomaticPageRecovery.
21AvailabilityEnhancements21FeaturesbyEdition.
22Summary22Chapter3:PlanningandBestPractices23StepOne:KnowYourEnvironment.
23AreYourServersReady23IsYourDatabaseReady24DoYouKnowYourServiceAccounts24DoYouKnowYourDatabaseSize25IsYourMirrorComparabletoYourPrincipal25AreYouCombiningwithOtherTechniques25StepTwo:KnowYourApplications.
25HowAreYourApplicationsConnectingtotheServers26DoAnyComponentsNotSupportAutomaticFailover26DoYourApplicationsHaveDependenciesonOtherDatabases26DoYourApplicationsRelyonExternalResources27StepThree:KnowYourPlan27SamplePlan.
27BestPractices.
28Summary30CONTENTSviChapter4:DatabaseMirroringSetup31BeforeYouBegin31PartOne:AdvancedPreparation.
31PartTwo:Pre-setupConfiguration32FollowingtheWizard33SettingUpMirroringManually.
43Step1:CreateEndpoints.
44Step2:CreateLogins45Step3:GrantConnectonEndpointtoLogins.
45Step4:SetthePrincipalPartneronMirror45Step5:SettheMirrorPartneronPrincipal.
46Step6:SettheWitnessPartneronPrincipal.
46Step7:ChangetheOperatingMode.
47SpecialCase:MirroringwithCertificates47Step1:CreateDatabaseMasterKey.
47Step2:CreateaCertificate48Step3:CreateanEndpoint.
48Step4:BackUptheCertificate.
48Step5:CreateLogins49Step6:CreateUsers.
49Step7:AssigntheCertificate.
49Step8:GrantConnectonEndpointtoLogins.
49Step9:ConfigureDatabaseMirroring.
49Test,Monitor,andWrapUp.
49TroubleshootingCommonSetupIssues50RestoreIssues.
50TransactionLogIssues.
51CommunicationIssues51CONTENTSviiSummary52Chapter5:AutomatingSetup.
53PreparetheDatabaseforMirroring.
53BackUpthePrincipalDatabase55RestoreFullBackupontheMirrorDatabasewithNoRecovery.
60BackUptheLogofthePrincipalDatabase.
64RestoretheLogontheMirrorDatabasewithNoRecovery.
64CreateEndpointsonInstances65CreateLoginfortheServiceAccountsandGrantConnect.
67SettheMirroringandWitnessPartners.
69ChangetheOperatingModeIfNotUsingaWitness.
72HelperProceduretoManageLinkedServers.
73TheMasterProcedure74Parameters.
74SampleExecution.
75TheCode.
75AlternativeSetupUsingPowerShell82ThePowerShellSetupScript.
83Summary94Chapter6:MaintenanceSimplified.
95RoutineMaintenanceConsiderations.
95TransactionLogBackups95BackingUptheLogFile.
96WritetheScript.
96MaketheScriptUsable97AutomatingLogBackups.
99ManagingLogBackupRetention.
106CONTENTSviiiDeletingLogBackups.
107LogFileSize110IdentifyingLogFilestoShrink.
111ShrinkingaLogFile.
111RoutinePatchingandSQLServerUpgrades.
115InstallingGeneralPatches.
116UpgradingorPatchingSQLServer.
116FailBacktotheOriginalPrincipal.
117ReasonstoFailBacktotheOriginal.
117ProceduretoFailBacktotheOriginalPrincipal.
118SettingDatabaseOwnerandTRUSTWORTHYProperty.
119ControlledFailoverofMirroredDatabases121ProblemsYouMightEncounter121ProceduretoPerformaControlledFailover121AutomaticFailoverofMirroredDatabases129CreatingaSQLAlerttoDetectAutomaticFailovers130UsingServiceBrokertoDetectAutomaticFailovers.
133StoredProceduretoRespondtoaFailover.
135ServiceBrokerQueue.
138ServiceBrokerServiceandRoute.
139Summary141Chapter7:MonitoringandAlerting143MonitoringComponents.
143DatabaseMirroringStatusTable.
143DatabaseMirroringMonitorJob.
144UnderstandingWarningThresholds.
145UsingtheDatabaseMirroringMonitor.
146RegisteringMirroredDatabases.
146CONTENTSixPerformingHealthChecks.
148ManagingWarningThresholds.
150UsingSystemStoredProcedures152Monitoring152ManagingWarningThresholds.
154UnderstandingPerformanceCounters.
157ConfiguringDatabaseMail159CreatinganOperator161EnablingSQLServerAgentNotifications.
161CreatingAlerts.
162WarningThresholds.
162PerformanceEvents166UsefulViews168UsingtheStateChangeTraceEvent169Summary170Chapter8:UpgradingtoSQLServer2008171UsingtheUpgradeAdvisor171UpgradeStrategies.
180PerforminganIn-PlaceUpgrade181ConsiderationsPriortoPerforminganUpgrade.
181UpgradingHigh-PerformanceMode.
182UpgradingHigh-SafetyModeWithoutaWitness.
182UpgradingHigh-SafetyModewithaWitness.
183MinimizingtheDowntimeofaSide-by-SideUpgrade.
184Post-UpgradeProcedures.
185ChangingCompatibilityLevel185CheckingObjectIntegrity186CorrectRowandPageCounts.
186CONTENTSxSettingthePageVerificationMethod.
186UpdatingStatistics187Summary187Chapter9:ReportingConsiderations.
189UsingDatabaseSnapshots.
189CreatingaDatabaseSnapshot190WorkingwithDatabaseSnapshots.
191DroppingaSnapshot.
193RollingSnapshots.
193UsingLogShipping.
195ImplementingLogShippingwithDatabaseMirroring.
196ConfiguringLogShippingforReporting199UsingReplication.
199TypeofReplicationDatabasetoUse.
200UnderstandingtheTerminology200DesignConsiderations.
200ImplementingReplicationwithDatabaseMirroring.
201Summary207Chapter10:PlanningforFailure.
209DetectingErrors.
209SoftErrors209HardErrors210Non-FailoverErrors211FailoverTime.
211FailureScenarios.
211Scenario1:PrincipalServerUnavailable.
212Scenario2:WitnessServerUnavailable.
212Scenario3:MirrorServerUnavailable.
213CONTENTSxiScenario4:MirrorServerandWitnessServerUnavailable.
214Scenario5:PrincipalServerandWitnessServerUnavailable.
215ProceduretoDropMirroringSessions.
216FailoverConsiderations220ProceduretoCopyLoginsandRoles.
220PlanningandResearch.
223SQLServerWebSites.
223WhitePapers224Webcasts.
224Podcasts.
225FreeTrainingEvents.
225MicrosoftSupportOptions.
225Summary226Index.
227CONTENTSxiiAbouttheAuthorsKenSimmonsisadatabaseadministratoranddeveloperspecializinginSQLServerand.
NET.
Heisaco-authorofProSQLServer2008Administration(Apress,2009).
HehasbeenworkingintheITindustrysince2000andcurrentlyholdscertificationsforMCP,MCAD,MCSD,MCDBA,andMCTSforSQL2005.
KenisactiveintheonlinecommunityandoftenparticipatesintheSQLforumsonMSDNandSQLServerCentral.
com.
HeenjoyssharingtipsbywritingarticlesforSQLServerCentral.
comandMSSQLTips.
com.
Whenheisnotworking,KenenjoystravelingwithhiswifeSusanandsonNathan,andhecanoftenbefoundonacruiseship,ataDisneyresort,oratthebeachinhishometownofPensacola,Florida.
RobertL.
DavisisadatabaseadministratoranddataarchitectconsultingatMicrosoftwhohasworkedextensivelyinsettingupandcreatingautomatedprocessestomanagedatabasemirroringformanyinternalMicrosoftapplications.
HeadditionallyconsultswithvariousinternalMicrosoftITgroupstoassistthemwiththeirdatabasemirroringneeds.
RobertiscurrentlyworkingontheArchitecture&PerformanceEngineeringteamoftheCAPapplicationprogramgroup.
CAPistheinternalsoftwareusedbysupportpersonnelforallofMicrosoft'scustomer-facingproductssuchasXbox,Zune,MSN,WindowsLive,andHotmail.
HehasovereightyearsofSQLServeradministrationexperience,muchofthatinhigh-availabilityenvironments.
Robwritesfrequentlyaboutmirroringandrelatedtopicsinhisblogathttp://www.
sqlservercentral.
com/blogs/robert_davis/default.
aspx.
CONTENTSxiiiAbouttheTechnicalReviewerGlennBerryworksasaDatabaseArchitectatNewsGatorTechnologiesinDenver,CO.
HeisaSQLServerMVP,andhehasawholecollectionofMicrosoftcertifications,includingMCITP,MCDBA,MCSE,MCSD,MCAD,andMCTS,whichprovesthathelikestotaketests.
HeisalsoanAdjunctFacultymemberatUniversityofDenverUniversityCollege,wherehehasbeenteachingsince2000.
CONTENTSxivIntroductionProSQLServer2008Mirroringisyourcompleteguidetoplanning,using,deploying,andmaintainingdatabasemirroringasahigh-availabilityoption.
Mirroringprotectsyoubymaintainingoneormoreduplicatecopiesofyourdatabaseforuseintheeventtheprimarycopyisdamaged.
Itisakeycomponentofanyproduction-level,high-availabilitysolution.
Thisbookcoversthefullspectrumofdatabasemirroring,takingyoufromtheplanningphasethroughtheimplementationtothemaintenancephaseandbeyond.
WhoThisBookIsForProSQLServer2008MirroringisaimedatSQLServer2008databaseadministratorsandespeciallyatthosewhodesiretomirrortheirdatabasesinsupportofhighavailability.
SystemadministratorsandoperationalengineerswhomanageWindowsServersystemsthatsupportmirroreddatabaseenvironmentswillalsofindmuchofinterestinthebook.
HowThisBookIsStructuredThisbookwalksyouthroughthebasicconfigurationofdatabasemirroringtotheadvancedtopicsyoushouldknowafteritisimplemented.
Chapter1comparesdatabasemirroringwithotherhigh-availabilitytechniquesandevenprovidesasampleplanthatcombinesmultiplehigh-availabilitytechniques.
Chapter2providesyouanoverviewofdatabasemirroring,includingthedifferenttypesofimplementationandthenewfeaturesofferedinSQLServer2008.
Chapter3givesyoutheknowledgeyouneedduringtheplanningphaseofdatabasemirroring,providingyouwithsomebest-practicetechniquesyoushouldknowbeforedeployingdatabasemirroring.
Chapter4coversthebasicsetupofdatabasemirroringusingthedatabasemirroringwizardandalsoprovidesstep-by-stepinstructionstosetupdatabasemirroringusingT-SQL.
Chapter5providesyouthescriptsyouneedinordertoquicklyautomatedatabasemirroringsetupforseveraldatabasesatonceinordertoavoidthetime-consumingprocessofconfiguringdatabasemirroringonasingledatabaseatatime.
Chapter6providesyouthemaintenancescriptsyouneedtorunonyourdatabasemirroringserversinordertokeepthemrunningsmoothly.
Chapter7coversthevarioustechniquesyouneedtoknowinordertomonitordatabasemirroringandtoalertyouwhencertainunwantedconditionsarise.
Chapter8walksyouthroughthestepstoperformasuccessfuldatabasemirroringupgradeincludingpre-andpost-upgradeconsiderations.
INTRODUCTIONxvChapter9coversthevariousreportingoptionsyouhavewithdatabasemirroringbytakingadvantageofdatabasesnapshots,logshipping,andreplication.
Chapter10stressestheimportanceofknowinghowyourdatabasemirroringconfigurationwillreacttodifferenttypesoffailuresandwhatyoushoulddotobepreparedforthem.
PrerequisitesDatabasemirroringwasintroducedinSQLServer2005butwasn'tofficiallysupporteduntilServicePack1.
Youcanrundatabasemirroringintheoriginalrelease,butyouwillneedtosetatraceflag.
Inordertorunthesamplesinthisbookhowever,werecommendrunningSQLServer2005ServicePack1orgreater,andifatallpossibleServicePack3.
However,inordertotakeadvantageofallofthelatestfeatures,youwillneedtoberunningSQLServer2008.
DownloadingtheCodeYoucandownloadthesourcecodeforthebookontheApresswebsiteintheSourceCodesectionathttp://www.
apress.
com/book/sourcecode.
AlloftheT-SQLcodewillbein.
sqlfilesprefacedwiththenameofthestoredproceduresusedinthebook.
TherewillbeonePowerShellscriptwitha.
ps1extension.
ThesampledatabasesarenolongerprovidedasapartoftheSQLServer2008installation.
Ifyouwanttodownloadasetofsampledatabasesyoucanusefortestingpurposes,theycanbefoundontheCodePlexwebsiteathttp://www.
codeplex.
com/MSFTDBProdSamples.
YouwillwanttodownloadtheSQLServer2008ProductSampleDatabasesfromthiswebsiteandfollowtheinstallationinstructions.
ContactingtheAuthorsYoucancontactKenSimmonsbye-mailinghimatcyberjunkyks@yahoo.
com,oryoucanvisithisblogathttp://cybersql.
blogspot.
com.
Therewillbeatagonhisblogcalled"ProSQLServer2008Mirroring"youcanusetofilterpoststhataredirectlyrelatedtothebook.
Pleaseincludethebooktitleinanye-mailstohelpidentifyquestionsorcommentsaboutthebook.