Configurando Aplicaciones para Rplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

  • Published on
    22-Nov-2014

  • View
    161

  • Download
    4

DESCRIPTION

Cmo configurar SQL-Server y Aplicaciones .NET para aprovechar la funcionalidad de Lectura de las Rplicas en AlwaysOn de SQL-Server

Transcript

  • 1. Configurando Aplicaciones para Rplicas deLectura de SQL-Server AlwaysOn24 de Septiembre (12 pm GMT -5)Carlos RojasResmen:Cmo configurar SQL-Server y Aplicaciones .NETpara aprovechar la funcionalidad de Lectura de lasRplicas en AlwaysOn de SQL-ServerPrximos EventosIndices columnares en SQL Server20141 de OctubreJos L. RiveraPower BI para office 3658 de OctubreJorge CastaedaIntroduccin a SSIS con BIML15 de OctubreGuillermo CaicedoEst por comenzar:

2. Configurando Aplicaciones para Rplicas deLectura de SQL-Server AlwaysOn24 Setiembre 2014Carlos Rojas VargasMVP | MCSA | MCITP | MCTS | MCPD | MCSE | MCTConsultor Senior CMA / Trainer CTESQL-Server Users Group Costa Ricahttp://sqlugcr.net 3. History of SQL-based Data RedundancySQL Backup and Restore (SQL Server 6.5)SQL Log Shipping (SQL Server 7.0)SQL Transactional Replication (SQL Server 7.0)SQL Failover Cluster (SQL Server 7.0)- HADR Enhancements (SQL Server 2000)SQL Snapshot (SQL Server 2005)SQL Mirroring (SQL Server 2005)- HADR Enhancements (SQL Server 2008 & R2)SQL AlwaysOn (SQL Server 2012)SQL AlwaysOn Enhanced (SQL Server 2014) 4. AlwaysOn Technologies inSQL Server 2012/2014AlwaysOn Failover ClusterInstance Server-level protectionWindows ServerFailover ClusterWindows ServerFailover ClusterAlwaysOn Availability Group Database-level protection 5. SQL-Server 2012/2014 HADRAlwaysOn High Availability Group(HAG) 6. AlwaysOn: High Availability Group Installation Windows cluster No-clustered SQLServer 7. A Flexible SolutionAlwaysOn provides the flexibility of different HAconfigurationsSynchronousData MovementAAsynchcronousData MovementAAAShared Storage, regional and geo secondariesAAADirect attached storage local, regional and geo target 8. High Availability SummaryHigh Availability and DisasterRecoverySQL Server SolutionPotentialData Loss(RPO)PotentialRecoveryTime (RTO)AutomaticFailoverReadableSecondariesAlwaysOn Availability Group - synchronous-commitZero Seconds Yes(4) YesAlwaysOn Availability Group - asynchronous-commitSeconds Minutes No YesAlwaysOn Failover Cluster Instance NA(5) Seconds-to-minutesYes NADatabase Mirroring(2) - High-safety (sync +witness)Zero Seconds Yes NADatabase Mirroring(2) - High-performance (async) Seconds(6) Minutes(6) No NALog Shipping Minutes(6) Minutes-to-hours(6)No Not duringa restoreBackup, Copy, Restore(3) Hours(6) Hours-to-days(6)No Not duringa restore 9. Application FailoverAvailability Groups Listener allow applications to failover seamlessly to any secondaryApplication reconnects using a virtual name after a failover to a secondaryServerA ServerB ServerCHRDBAG_HRHRDBHR_VNNPrimary SecondaryHRDBPrimary SSeeccoonnddaarryyApplication retry during failover-server HR_Listener;-catalogHRDBConnect to new primary oncefailover is completeand the listener is online 10. Active Secondary ReplicasPrimary ReplicaSecondary Replica(Read-Intent)ListenerSecondary Replica(Read-Only)ApplicationIntent=ReadOnlyBackup 11. AlwaysOnActive Secondary IT efficiency and cost-effectiveness is critical for businesses Idle hardware is not an option anymore AlwaysOnActive Secondary enables efficient utilization of high availability hardwareresources thereby improving overall IT efficiency Active Secondary can be utilized for Balancing read-only workloads Offloading Backup Operations 12. SQLservr.exe SQLservr.exeInstanceADB1 DB2Readable secondary allow offloading read queries to secondaryClose to real-time data, latency of log synchronizationimpact data freshnessBackups in Secondary ReplicasDB1 DB2Primary SecondaryInstanceBSecondary PrimaryReportsBackupsReportsBackupsAlwaysOn Availability Groups 13. Active SecondaryEnabling Backup On SecondaryBackups can bedone on any replicaof a databaseBackups onprimary replica stillworksLog backups doneon all replicas forma single log chainDatabaseRecovery Advisormakes restoressimpleR/W workloadPrimaryBackupsSecondaryBackupsSecondaryBackups 14. Read-Only Client ConnectivityRead-Only client connection behavior determined by Availability ReplicaOption+ ApplicationIntent PropertyApplicationIntent is a connection propertyReplica option determines whether a replica is enabled for read access when in a secondary roleRead-Only Routing enables redirection of client connection to new secondaryon role changeEnable seamless redirection of application connection across replicas without manual intervention 15. DemoSQL-Server Always OnView Clustering Services 2012R2Implementing AlwaysOnHigh Availability Group (HAG)Testing Readable Secondaries 16. Mantngase conectado a nosotros!Vistenos en http://globalspanish.sqlpass.org20/SpanishPASSVClnkd.in/dtYBzev/user/SpanishPASSVC/SpanishPASSVC 17. PASS Community NewsSeptember 2014 18. Planning on attending PASS Summit 2014? The worlds largest gathering of SQL Server & BI professionals Take your SQL Server skills to the next level by learning from the worldsSQL Server experts, in 190+ technical sessions Over 5000 attendees, representing 2000 companies, from 52 countries,ready to network & learnAsk your Chapter Leader how tosave $150 off registration!$1,895UNTIL SEPTEMBER 26,2014 19. Join me for a sneak peek of PASS Summit 2014 24 free SQL Server and BI training webcasts Content delivered by PASS Summit speakers & experts from around theworld 5 Session Tracks, covering Application & Database Development toProfessional Development A taste of what you can expect at PASS Summit 2014 in Novemberwww.24HoursofPASS.com 20. Upcoming SQLSaturdaysUpcoming North America Events Upcoming International Events Sep 6 #320 Raleigh Sep 13 #300 Kansas City Sep 20 #331 Denver Sep 20 # 340 San Diego Sep 27 #334 Boston (BI Edition) Sep 27 #318 Orlando Sep 27 #342 Mobile Sep 13 # 323 Paris Sep 20 #290 Kiev Sep 20 #310 Dublin Sep 27 #325 So Paulo Sep 27 #343 Medellin Oct 04 #335 Athens Oct 04 #336 Holland (Utrecht) Oct 04 #344 TiranaVisit www.sqlsaturday.com toregister for an event near you! 21. 25Volunteering with PASSVolunteer Today!Now its easier than ever to volunteer with PASS.Let us know your volunteer preferences by updating theMyVolunteering section of your MyPASS profile.PASS would not exist without our passionate, dedicated,and hardworking volunteers from around the globe. 22. 26PASS Volunteer AwardsOutstanding Volunteer AwardDo you know a volunteer that has goneabove and beyond recently?Nominate them for the monthly OVA.VolunteerRecognition@sqlpass.orgPASSion AwardThe highest of PASS accolades presentedyearly to a volunteer.Nominations open now!http://www.sqlpass.org/Community/Volunteers/PASSionAward.aspx 23. Stay Involved! Sign up for a free membership today at sqlpass.org Linked In: http://www.sqlpass.org/linkedin Facebook: http://www.sqlpass.org/facebook Twitter: @SQLPASS PASS: http://www.sqlpass.org 24. Indices columnares en SQL Server 20141 de Octubre (12 pm GMT -5)Jos L. RiveraResmen:Los ndices columnares, disponibles desde SQL Server2012, prometen ser una herramienta fundamental paraobtener alto rendimientos en consultas de altosvolmenes de datos. Estos ndices introducen un nuevoparadigma de construccin y uso el cual estaremosdiscutiendo en esta sesin. Veremos sus caractersticasprincipales y como tomar ventaja de los mismos ennuestros entornos laborales.Prximo Evento