1 / 22

SQL Server High Availability en Database Mirroring

SQL Server High Availability en Database Mirroring. Peter ter Braake trainsql@live.nl @ pbraake. Agenda. Overview Mirroring Theorie Demo Vragen. Availability. Data moet beschikbaar zijn op het moment dat het nodig is. Tijdens kantooruren 24 / 7 (99.999% uptime?)

sharne
Download Presentation

SQL Server High Availability en Database Mirroring

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server High Availabilityen Database Mirroring Peter ter Braaketrainsql@live.nl @pbraake

  2. Agenda • Overview • Mirroring • Theorie • Demo • Vragen

  3. Availability • Data moet beschikbaar zijn op het moment dat het nodig is. • Tijdens kantooruren • 24 / 7 (99.999% uptime?) • Bescherming tegen hardware failures • Complete server valt uit • Netwerk(kaart) valt uit • … • Installeren Services Packs, Patches, … • Bescherming tegen rampen (overstroming, ...)

  4. Availability: AlwaysOn • Online index operations (Enterprise) • Online restoreoperations (Enterprise) • Online system changes • Hot Add CPU • Hot AddMemory • Redundante hardware • Meer cpu’s • RAID • Dubbel netwerk

  5. Strategieën • Verschillende High Availability strategieën mogelijk • SQL Server features, SAN, virtualisatie, … • Keuze hangt af van • Budget • SLA • Hoeveel downtime is nog OK? • Hoeveel data-loss is nog OK? • …

  6. Techniekenvergeleken • Log shipping (per database) • Down time • Data loss • Goedkoop • Mirroring (per database) • +- Down time • +- Data loss • +- Goedkoop • Clustering (instance) • No Down time • No Data loss • Duur • Replicatie (object)

  7. Availability Groups • Mirroring 2.0 (?) • Highlights: • Zelfgroep van databases definieren • Tot 4 secondaries • Read-only secondaries • Backup from secondary • In vergelijking met anderestrategieen: • +- downtime, +- dataloss, +- duur

  8. Agenda • Overview • Mirroring • Theorie • Demo • Vragen

  9. Database mirroring • Beschikbaar vanaf Standard Edition • Extra SQL Server voor Mirror database • Geenlicentienodigalsgeenproductieserver • Full Recovery model vereist • 3 mogelijkeconfiguraties: • High Performance (Enterprise edition) • High Protection • High Availability

  10. Mirroring: High Performance Applicatie 2. Succes 1. Transactie Principal Database Mirror (Recovering …) 2. Transactie • Mirror database kan achterlopen: data-loss • Geen automaticfailover • Nagenoeg geen impact op performance van principal

  11. Mirroring: High Protection Applicatie 4. Succes 1. Transactie Principal Database Mirror (Recovering …) 2. Transactie 3. Succes • Mirror database exacte kopie van principal: gegarandeerd geen data-loss • Geen automaticfailover • Wel impact op performance van principal

  12. Mirroring: High Availability Applicatie 4. Succes 1. Transactie Principal Database Mirror (Recovering …) 2. Transactie 3. Succes • Automatic database failover • Automaticclientfailover met SQL nativeclient Quorum Witness (Server)

  13. principle loss

  14. Mirroring Demo

  15. Opzetten mirroring • Maak mirroringendpoints op alle servers • SSMS: database properties, Mirroring • T-SQL: CREATE ENDPOINT [Mirroring]AS TCP (LISTENER_PORT =5022)FOR DATA_MIRRORING (ROLE = PARTNER) • Gebruik Full recovery model • Restore Full database backup op Mirror server (withnorecovery) • Denk aan server level objects (logins, linked servers, ...)

  16. Starten mirroring • Op mirror server: • ALTER DATABASE AdvWorks SET PARTNER = N'tcp://SQL2008SERVER:5022' • Op principal server: • ALTER DATABASE AdvWorks SET WITNESS = N'tcp://SQL2008SERVER:5024' • ALTER DATABASE AdvWorks SET PARTNER = N'tcp://SQL2008SERVER:5023' • Wel / geen high performance • ALTER DATABASE AdvWorksSET SAFETY ON

  17. Monitor mirroring • SSMS mirroring monitor • Configure tresholds • Performance counters • http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-New-Performance-Counters-for-Database-Mirroring.aspx • DMO’s • sys.dm_db_mirroring_connections

  18. Failover • Automatisch in geval van Quorum • High protection:ALTER DATABASE AdvWorksSET PARTNER FAILOVER • High performance:ALTER DATABASE AdvWorksSET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

  19. Denk aan: • Amount of RAM available on the principal and mirror servers (RAM). • Processing power of the principal and mirror servers (CPU). • Bandwidth available for the I/O subsystem on the principal and mirror servers • Network bandwidth between the two servers. • Volume of transactions on the principal database that generate transaction log records (i.e. transactions that change the database in any way). • See: http://support.microsoft.com/kb/2001270

  20. Agenda • Overview • Mirroring • Theorie • Demo • Vragen

  21. Peter terBraake trainsql@live.nl @pbraake Vragen ???

  22. Peter terBraake trainsql@live.nl Bedankt !!! Vergeetniet: Denny Cherry – Clustering (na lunch) Robert Hartskeerl – AlwaysOn 2012 (laatstesessie)

More Related