configurando aplicaciones para réplicas de lectura de sql-server alwayson - carlos rojas

28
Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn 24 de Septiembre (12 pm GMT -5) Carlos Rojas Resúmen: Cómo configurar SQL-Server y Aplicaciones .NET para aprovechar la funcionalidad de Lectura de las Réplicas en AlwaysOn de SQL-Server Próximos Eventos Introducción a SSIS con BIML 15 de Octubre Guillermo Caicedo Indices columnares en SQL Server 2014 1 de Octubre José L. Rivera Power BI para office 365 8 de Octubre Jorge Castañeda Está por comenzar:

Upload: spanishpassvc

Post on 22-Nov-2014

189 views

Category:

Data & Analytics


5 download

DESCRIPTION

Cómo configurar SQL-Server y Aplicaciones .NET para aprovechar la funcionalidad de Lectura de las Réplicas en AlwaysOn de SQL-Server

TRANSCRIPT

Page 1: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn

24 de Septiembre (12 pm GMT -5)

Carlos RojasResúmen:Cómo configurar SQL-Server y Aplicaciones .NET para aprovechar la funcionalidad de Lectura de las Réplicas en AlwaysOn de SQL-Server

Próximos Eventos

Introducción a SSIS con BIML

15 de OctubreGuillermo Caicedo

Indices columnares en SQL Server 2014

1 de OctubreJosé L. Rivera

Power BI para office 365

8 de OctubreJorge Castañeda

Está por comenzar:

Page 2: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn24 Setiembre 2014

Carlos Rojas VargasMVP | MCSA | MCITP | MCTS | MCPD | MCSE | MCTConsultor Senior CMA / Trainer CTESQL-Server Users Group – Costa Ricahttp://sqlugcr.net

Page 3: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

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)

Page 4: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

AlwaysOn Technologies in SQL Server 2012/2014

AlwaysOn Failover Cluster Instance

• Server-level protection

Windows Server Failover

Cluster

AlwaysOn Availability Group

• Database-level protection

Windows Server Failover

Cluster

Page 5: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

SQL-Server 2012/2014 HADR

AlwaysOn High Availability Group (HAG)

Page 6: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas
Page 7: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

AlwaysOn: High Availability Group

• Installation

• Windows cluster

• No-clustered SQL Server

Page 8: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

A Flexible Solution

AlwaysOn provides the flexibility of different HA configurations

Synchronous Data Movement

Asynchcronous Data Movement

Shared Storage, regional and geo secondaries

A

A

A

A

A

Direct attached storage local, regional and geo target

AA

Page 9: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

High Availability Summary

High Availability and Disaster Recovery

SQL Server Solution

Potential Data Loss

(RPO)

Potential Recovery

Time (RTO)

Automatic

Failover

Readable Secondari

es

AlwaysOn Availability Group - synchronous-commit

Zero Seconds Yes(4) Yes

AlwaysOn Availability Group - asynchronous-commit

Seconds Minutes No Yes

AlwaysOn Failover Cluster Instance NA(5) Seconds-to-minutes

Yes NA

Database Mirroring(2) - High-safety (sync + witness)

Zero Seconds Yes NA

Database Mirroring(2) - High-performance (async)

Seconds(6

)

Minutes(6) No NA

Log Shipping Minutes(6) Minutes-to-hours(6)

No Not duringa restore

Backup, Copy, Restore(3) Hours(6) Hours-to-days(6)

No Not duringa restore

Page 10: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Application Failover

Availability Groups Listener allow applications to failover seamlessly to any secondaryApplication reconnects using a virtual name after a failover to a secondary

AG_HR

HRDB

HRDB

Primary SecondaryHR_VNN

-server HR_Listener;-catalog HRDB

Application retry during failover

Connect to new primary once failover is completeand the listener is online

Primary

SecondarySecondary

HRDB

ServerA ServerB ServerC

Page 11: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Active Secondary Replicas

Primary Replica

Secondary Replica(Read-Intent)

ListenerSecondary Replica

(Read-Only)

ApplicationIntent=ReadOnly

Backup

Page 12: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

AlwaysOn Active Secondary

• IT efficiency and cost-effectiveness is critical for businesses Idle hardware is not an option anymore

• AlwaysOn Active Secondary enables efficient utilization of high availability hardware resources thereby improving overall IT efficiency

• Active Secondary can be utilized for Balancing read-only workloads Offloading Backup Operations

Page 13: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Readable secondary allow offloading read queries to secondary

Close to real-time data, latency of log synchronization impact data freshness

Backups in Secondary Replicas

DB2DB1

SQLservr.exe

SQLservr.exe

InstanceA

DB2DB1

Primary Secondary

InstanceB

PrimarySecondary

Failover

ReportsBackups

ReportsBackups

AlwaysOn Availability Groups

Page 14: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Active SecondaryEnabling Backup On Secondary

Backups can be done on any replica of a databaseBackups on primary replica still worksLog backups done on all replicas form a single log chainDatabase Recovery Advisor makes restores simple

R/W workload

Primary

Backups

Secondary

Backups

Secondary

Backups

Page 15: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Read-Only Client Connectivity

Read-Only client connection behavior determined by Availability Replica Option+ ApplicationIntent PropertyApplicationIntent is a connection property Replica option determines whether a replica is enabled for read access when in a secondary role

Read-Only Routing enables redirection of client connection to new secondary on role changeEnable seamless redirection of application connection across replicas without manual intervention

Page 16: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas
Page 17: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas
Page 18: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas
Page 19: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Demo SQL-Server Always On

View Clustering Services 2012R2

Implementing AlwaysOn High Availability Group (HAG)

Testing Readable Secondaries

Page 20: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

20

Manténgase conectado a nosotros!

Visítenos en http://globalspanish.sqlpass.org

/SpanishPASSVC

lnkd.in/dtYBzev

/user/SpanishPASSVC

/SpanishPASSVC

Page 21: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

PASS Community News

September 2014

Page 22: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Planning on attending PASS Summit 2014?

• The world’s largest gathering of SQL Server & BI professionals

• Take your SQL Server skills to the next level by learning from the world’s SQL Server experts, in 190+ technical sessions

• Over 5000 attendees, representing 2000 companies, from 52 countries, ready to network & learn

Ask your Chapter Leader how to save $150 off registration!

$1,895UNTIL SEPTEMBER

26, 2014

Page 23: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

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 the world 5 Session Tracks, covering Application & Database

Development to Professional Development A taste of what you can expect at PASS Summit 2014 in

November

www.24HoursofPASS.com

Page 24: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Upcoming SQLSaturdays

Upcoming 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 São Paulo

• Sep 27 #343 Medellin

• Oct 04 #335 Athens

• Oct 04 #336 Holland (Utrecht)

• Oct 04 #344 Tirana

Visit www.sqlsaturday.com to register for an event near you!

Page 25: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

25

Volunteering with PASS

Volunteer Today!

Now it’s easier than ever to volunteer with PASS.

Let us know your volunteer preferences by updating the “MyVolunteering” section of your

MyPASS profile.

PASS would not exist without our passionate, dedicated, and hardworking volunteers from

around the globe.

Page 26: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

26

Outstanding Volunteer Award

Do you know a volunteer that has gone above and beyond recently?

Nominate them for the monthly OVA.

[email protected]

PASS Volunteer Awards

PASSion AwardThe highest of PASS accolades

presented yearly to a volunteer.

Nominations open now!http://www.sqlpass.org/Community/Volunteers/PASSionAward.aspx

Page 27: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

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

Page 28: Configurando Aplicaciones para Réplicas de Lectura de SQL-Server AlwaysOn - Carlos Rojas

Indices columnares en SQL Server 2014

1 de Octubre (12 pm GMT -5)

José L. RiveraResúmen:Los índices columnares, disponibles desde SQL Server 2012, prometen ser una herramienta fundamental para obtener alto rendimientos en consultas de altos volúmenes de datos. Estos índices introducen un nuevo paradigma de construcción y uso el cual estaremos discutiendo en esta sesión. Veremos sus características principales y como tomar ventaja de los mismos en nuestros entornos laborales.

Próximo Evento