Bài giảng Cơ sở dữ liệu nâng cao - Chapter 7: Maintaining high availability

Bài giảng Cơ sở dữ liệu nâng cao - Chapter 7: Maintaining high availability. Chương 7 gồm có những nội dung chính sau đây: Availability, log shipping, data mirroring, clustering. Mời các bạn cùng tham khảo.
Nội dung trích xuất từ tài liệu:
Bài giảng Cơ sở dữ liệu nâng cao - Chapter 7: Maintaining high availabilityMaintainingHighAvailabilityAgenda1. Availability2. Logshipping3. Datamirroring4. Clustering1. AvailabilityHighavailabilityisn’talwaysaboutfulltimeoperations, but,aboutservicesbeingaccessibletoyouruserswhen theyneedthem2. Log shippingLogshippingis: Tosendtransactionlogsfromtheprimarydatabasetothe secondarydatabase. Tobackupthetransactionlogsfromaprimarydatabaseand thencopyandrestorethemtoasecondarydatabase,keep thesecondarydatabasenearlysynchronizedwiththe primarydatabase.Thedestinationserveractsasabackupserver.Logshippingcanbeusedwithdatabasesusingthefullor bulkloggedrecoverymodels.2. Log shippingLogshippingarchitecture:2. Log shippingLogshippingarchitecture: Primaryserver:  TheSQLinstancethatneedstobeprotectedincaseof hardwarefailure,softwareerror,...  TheprimaryserverisconfiguredtoexecuteaSQLAgent jobtobackupthetransactionlogtoafile Secondaryserver:  ThebackupSQLServer2008instancethatmaintainsa copyoftheprimaryserverdatabase.  ThesecondaryserverisconfiguredwithtwoSQLAgent jobs:onetocopythetransactionlogfilefromtheshared backupfolderandtheothertorestorethetransactionlog2. Log shippingLogshippingarchitecture: Monitorserver:  Havingamonitorserveraspartoflogshippingis optionalbutrecommended.  Whenthemonitorserverparticipatesinlogshipping,it managesthejobsthatproducemonitoringinformation (suchasthelasttimethetransactionlogwasbackedup ontheprimaryserver,thelasttransactionlogthatwas restoredonthesecondaryserver,…)2. Log shippingLogshippingprocess: Backupthetransactionlogontheprimaryserver.ASQL Agentjobontheprimaryserverbacksupthetransactionlog atauserconfigurabletimeintervaltoafileinabackup folder. Copythetransactionlogtothesecondaryserver.ASQL Agentjobonthesecondaryserveraccessesthebackup folderontheprimaryservertocopythetransactionlogfile toalocalfolderonthesecondaryserver. Restorethetransactionlogonsecondaryserver.ASQL Agentjobonthesecondaryserverrestoresthetransaction logonthesecondaryserver.2. Log shippingLogshippingdeployment: Beforebeginningthelogshippingprocess,needtodosome initialconfigurationfirst. Then,chooseregardinghowtodeploy:usingtheSQL Server2008ManagementStudioorusingTSQLscripts. Typically,aDBAusesSQLServer2008Management StudiotoconfigurelogshippingandthengeneratesSQL scriptsforfutureredeployment.2. Log shippingLogshippingdeployment: Initialconfiguration:  Createabackupfolderthattheprimaryservercan access;shareit,andensurethatitisaccessiblebythe secondaryserver.  Createadestinationfolderonthesecondaryserver,The secondaryserver’sSQLAgentaccountortheproxy accountexecutingthejobmusthavereadandwrite permissiontothisfolder.  Therecoverymodelforthelogshippeddatabasemustbe settoeitherFullorbulk_logged2. Log shippingEnablinglogshippinginvolvesthefollowingbasicsteps: Chooseserversforyourprimaryserver,secondaryserver, andoptionalmonitorserver. Chosethebackupcompressionbehavioroflogbackupsif neccessary. Createafileshareforthetransactionlogbackups. Chooseabackupschedulefortheprimarydatabase. Createafolderforeachsecondaryserverintowhichthe transactionlogbackupfileswillbecopied.(Thesefolders areusuallylocatedonthesecondaryservers.) Configureoneormoresecondarydatabases.3. Database mirroringDatabasemirroringisaprimarilysoftwaresolutionfor increasingdatabaseavailability.Mirroringisimplementedonaperdatabasebasisand worksonlywithdatabasesthatusethefullrecovery model.Databasemirroringmaintainstwocopiesofasingle databasethatmustresideondifferentserverinstancesof SQLServerDatabaseEngine.3. Database mirroringOneserverinstanceservesthedatabasetoclients(the principalserver).Theotherinstanceactsasaserver(themirrorserver), dependingontheconfigurationandstateofthemirroring session.Whenadatabasemirroringsessionissynchronized, databasemirroringprovidesahotstandbyserverthat supportsrapidfailoverwithoutalossofdatafrom committedtransactions.Whenthesessionisnotsynchronized,themirrorserveris typicallyavailableasawarmstandbyserver(with possibledataloss).3. Database mirroringDatabasemirroringarchitecture:3. Database mirroringDatabasemirroringarchitecture: PrincipalServer—Theprincipalserverhoststhecopyofthe databasethatclientsconnecttoandinteractwith.As transactionsoccurontheprincipaldatabase,thetransaction logrecordsareforwardedtothemirrordatabase. MirrorServer—Themirrorserverhostsacopyofthe principaldatabaseandappliesthetransactionlogrecords sentbytheprincipaldatabasetokeepthemirroreddatabase insyncwiththeprincipaldatabase.3. Database mirroringDatabasemirroringarchitecture: WitnessServer(optional):  Neededifautomaticfailovertothemirrorserverisrequiredinthe caseofaprincipaldatabasefailure.  Thewitnessservermonitorsthestatusoftheprincipalandmirror serversinahighavailabilityconfiguration3. Database mirroringHowdatabasemirroringworks: Theprincipalandmirrorserverscommun ...