sql2005数据库镜像配置过程

SQLServer2008R2DatabaseMirroringEnvironment：OSWindows2008enterpriseR2SQLServer2008R2MasterhostINMS1Internalip:10.10.10.1/24gw:noreOutboundip142.2.70.208/16gw:142.2.70.13MirrorhostINMS2Internalip:10.10.10.2/24gw:noreOutboundip142.2.70.209/16gw:142.2.70.13Prepareenvironment:Createaloginforsqlservice,srestartonINMS,INMS2HOSTSettingclassicauthmodeongrouppolicy,otherwiseINMS1DBinstancecouldnotLocalComputerPolicy}LocalComputerPolicy}ComputerCorifiguratiori田巴SoftwareSettingsIE .WiridowsSettingsH一NameR-esolutionPulicy囱Scripts(Startup/Shutdown)日3SecuritySettings田-jAccountPoliciesSaLQ二'PoliciesS逼AuditPolicy田吗UserRightsAssignmentaSeizLirityOptionsPi诋y |SecuritySetting上KjjInteractivelogon:Numberofpreviouslogonstocache(incased...10logons眼Microsoftnetworkserver:Amountofidletimerequiredbeforesu...15minutesDumainmember:Maximummachineaccountpasswordage 30daysInteractivelogori：Promptusertochangepasswordbeforeexpir...5days匾Accounts:Reriameadministratoraizcount Administrator1Networkaccess:SharingandsecuritymodelforlocalaccountsClassic-localusersauthe...Accounts:Guestaccountstatus― ―DisabledAudit:Audittheaccessofglobalsystemobjects DisabledAudit:AudittheuseofBackupandRestoreprivilege DisabledAudit:5hutdownsystemimmediatelyifunabletologsecurityauditsDisabledLogin:sql-servicesPassword:Wec251InmsWhenyouinstallsql2008R2,pleasesetloginsql-servicesasserviceberestartedlogin,localadministratorassqlserveradmin.-IT|2_J| W1Ld5QLServerConFigurationManager(Local)NameState|StartMode1LogOnAs|ProcessID1=15QLServerSeMizel■■蜜■■黛■玛SQLFull-textFilterDaemonLauncheri：MSSQLSERVER：iRunninqManualNTAUTHORITY\LOC...3800J|一5QL5erverNetworkConfiguration(32bit)故)SQLServer(MSSQLSERVER)RunningAutomatic<j5qt-servitE^>3884SQLNativeClient10.0ConFiguration(32bit：i|^iSQLServerReportingServices(MSSQLSERVER)RunningAutomatic也施蜕明理二1436且.SQLServerNetworkConfiguration询SQLServerBrowserStoppedOther(Boot.,Sys...NTAUTHORITY\LOC...0SQLNativeClient1U.UConfiguration⑥SQLServerAgent(M55QL5ERVER；iRunningAutomaticr+jql^ervice5-j：.5l=it>LlPmt口匚口1Pmt口匚口1Nams■^-SharedMemoryNamedPipes■^TTCP/IP|Status<gTiabled7?.<EnableJ2?>Disabled5QLServerConfigurationManager(Local)|5QLServerServices5QLServerNetworkConfiguration(32bit).受5QLNativeClient10.0Configuration(32bit)%ClientProtocolsWAliases5QLServerNetworkConfigurationProtocolsForM55QL5ERVER.受5QLNativeClient10.0Configuration

5QLPdativeClient10.0CLinfiguratiun(32bit)为ClientProtoculsWAliases5QLServerNetworkConfiguratiun5QLPdativeClient10.0CLinfiguratiun(32bit)为ClientProtoculsWAliases5QLServerNetworkConfiguratiun&ProtocolsforM55QL5ERVERSQLNative匚值「it10.0CoriFiguratiunITCP/IPPropertiesProtocolIPAddresses|ActiveYesEnabledNoIPAddressfe80::349f:3491:71fd:b92f%23TCPDynamicPortsTCPPort —ActiveYes —EnabledNo \IPAddress142.2.70.208\.TCPDynamicPortsyPort1433ActiveYesEnabledNoTP-rlrlrRvqFftRH!!149rl!F7r?! !49aa%11^TCP/IP EnabledRunthebelowscriptonmasterhost:A:backuptheDB;runthebelowscriptonmasterBackupdatabasetesttodisk=’d:\backup\test.bak,Backuplogtesttodisk=’d:\backup\testlog.bak,CopytheupperDBandLogbackuptomirrorhosttorestore!B:runthebelowscriptonmirrorhost:Restoredatabsetestfromdisk=’d:\back\test.bak,withnorecoveryRestorelogtestfromdisk=’d:\back\testlog.bak,withnorecoveryAfterruntheupperscript,youwillseethedatabasestatusisinrestoringmode!masterDBtransactionlogmustisfullrecoverymodemirrorDBusenorecoveryrecovermirrorDBSettingEndpointRunthebelowscriptonmasterhostUsemastergo

createendpointDbMirroringstate=startedastcp(listener_port=5022,LISTENER_IP=(10.10.10.1))fordatabase_mirroring(role=partner,encryption=supported)youcanseeit,itslistenporthavebeencreated!\Users\Administrator>netstat-anctiueConnectionsopppppppppppppppppppppprccccccccccccccccccccccPTTTTTTTTTTTTTTTTTTTTTTLocalAddress0.0.0.0:800.0.0.0:1350.0.0.0:4450.0.0.0:14330.0.0.0:23010.0.0.0:23810.0.0.0:33890.0.0.0:47001a.0.8.0:491520.0.0.0:491530.0.0.0:491540.0.0.0:491550.0.0.0:491660.0.0.0:491670.0.0.0:4960310.10.10.1:13510.10.10.1:139lu.IM.10.1:502210.1H.iH.ii4560310.10.10.1:5630610.10.10.1:5634810.10.10.1:56349n000000000000000100J±mff ■1-M--K--K--K--K-0-M--M--K--K--K-s-M--M--Me 1--illr-M--M--M--M--M-_M-_M-_M-_M--M--M--M--M--M--M-■_M-_M-■■■■,u 0--8000F000000000000000100J±me4r5H-■■d2-M--K--K--K--K-_M--M--M--M--K--K--K-■_M-StateLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGLISTENINGESTABLISHEDLISTENINGLISIENINGESIRBLISHEDESTABLISHEDTIME_WAITESTABLISHEDRunthebelowscriptonmirrorhost:UsemastergocreateendpointDbMirroringstate=startedastcp(listener_port=5022,LISTENER_IP=(10.10.10.2))fordatabase_mirroring(role=partner,encryption=supported)youcanseeitonthemanagementstudio:-_JServerObjects+_JBackupDevices-_JEndpoints+_JSystemEndpoints-_JD^fcabase-Mittoringd_J.IMirroring:select*fromsys.databse_mirroring_endpointsselect*fromsys.database_mirroringselecydatabaseidfromsys.databaseifyouhavewitnesshost,runthebelow:createendpointDbMirroringstate=startedastcp(listener_port=5022)fordatabase_mirroring(role=witness,encryption=supported)go3:settingsecuritygrantSQLengineerservice,srestartloginmusthaveconnectrights!onmasterhostrunthebelow:usemastergograntconnectonendpoint::Dbmirroringto"inms1\sql-services"grantconnectonyoucanbySQLmanagementstudiotosettingit:onmirrorhostrunthebelow:usemasterE]SINM51(SQLServer10.50.1600-IMMSl'i.Adr^田ODatabases日□SecurityE]SINM51(SQLServer10.50.1600-IMMSl'i.Adr^田ODatabases日□Security日后Logins&##M5_PolicyEventProces5ingLoi息##M5_PolicyTsqlExecutionLogin息U'-JMS1T4d「nin阮rmb"INM5l\sql-services"--：:：1AUTHORITTiiSTSTErvl版NTSERVICE'iJ'-'lSSQLSERVER拶NT5ERYICE'@3L三ER.VER.AGENTAsa田□ServerR.oles田口Credentials田□CryptographicProviders田□Audits田□ServerAuditSpecificationsB□ServerObjects田□BackupDevices臼□Endpoints田口SystemEndpoints日□DatabaseMirroring[JMirroring S窘StatusConnectionServer:INMS1Connection:INMS1\AdministratorName1TypeJ.DedicatedAdminConnectionEndpointPrMkoringiEndpdint^一十羽tzB加ultTCR 1—卜EnUpbinLTSQLDefaultVIAEndpointTSQLLocalMachineEndpointTSQLf'JamedPipesEndpointPermissionsforMirroring:PermissionGrantor1GrantWithGrAlterINMSIAdministratorrr匚二二~^or_inectINMSI^Administrator_Z^==>r1 .k-.frr.—.l1K1k.4'"iLJ、.*-II.-..r1—r-Explicit|Effective]gograntconnectonendpoint::Dbmirroringto"inms2\sql-servicesplorer ▼XDatabaseMail耳3DistributedTransactionCoordinatorSClLegacy密SQLServerAgentNM52(SQLServer10.50.1600-INM51\AdrJDatabasesSQSystemDatabases王1□DatabaseSnapshots3[JReport5erverS|ReportserverTempDE：±1□testJSecurity3CjLogins:##M5_PtjliqrE罩巳ntF丫口二巳&&i「igLm占*##M5_PolicyTsqlExecutionLogin息INMSg\Admhislzrabiir，：二二三INM52\sql-services->一莒而.UTHOFiiT陌i^TEM匐NT5ERVICE\M55QLbER.VER匐NT5ERVICE\5QL5ERVERAGENT.53S□ServerRoles+\QCredentials |ServerRolesUserMappingSecurablesStatusConnectionServer:INMS2Coririectiori：INMS1\AdministratorJiJ1l'IlI .kI.wLoginname: 11NMS2\sql-servicesPermission|Grantor|GrantAlterINMS2\Administratorr=zFnnnficl： INMS2^AdministratorControl INMS2'''Adrriini：i：tratorExplicit|Effective|4:startDBmirroringmirrorhostsetting:alterdatabasetestsetpartner='tcp:/masterhost:5022'alterdatabasetestsetalterdatabasetestsetpartner=,tcp://inms1:5022,;masterhostsetting:alterdatabasetestsetpartner='tcp:/mirrorhost:5022'alterdatabasetestsetpartner=,tcp://inms2:5022,;5:settransactionlevelalterdatabasetestsetsafetyfulladvancedlevelprotectionmode4:verifydatabasemirroringselect*fromsys.databse_mirroring_endpointselect*fromsys.databse_mirroringwheredatabase_id=(selectdatabase_idfromsys.databasewherename='test')sys.database_mirroring5:failuretestmanuallyfailovertransferonmaster-hostusermastergoalterdatabasetestsetpartnerfailoverAutotransfermasterDBtomirroringDBManualbreak10.10.10.1network,thenetworkisindisconnectmode!Onl6INM51(SQLServer10.50.1600-INM51\Administr-_JDatabases+_JSystemDatabases+_JDatabaseSnapshots+jReportServertest(PrincipalDisconnected)JTablesJViewsJSynonymsMirroringDBisinmodeINM52(5QLServer10.50.1600-INM52\Admini;-_JDatabases_JSystemDatabases_JDatabaseSnapshotsJReportserverJReportServerTempDB-jtest(MirrorjDisconnected/InRecovery+~~~~T_5smit7 ~~———+_JServerObjects+_JReplication+_JManagementOnmirroringhost,runthebelowscripttocancelmirroring:alterdatabasetestsetpartneroffthemirrorDBmodechangeinto:-INM52(5QLServer10.50.1600-INM52\Admin-_jDatabases_jSystemDatabases_jDatabaseSnapshots\Report5erver_LRBEicirWBryBrTBq^Etest(RestoringTj--^)+ ————_jServerObjects_JReplication_jManagementg5QLServerAgentBackupthetaillogonmasterDB:backuplogtesttodisk='f:\backup\testtaillog.bak'withno_truncateonmirroringhosttorestorethetaillogrestoredatabsetestfromdisk='F:\backup\testtaillog.bak,withrecoveryE_|JE_|JReportServerTempDEi国Q-Security国QS