#SQSummit

SQL Server 2014 para Hobbits y Enanos

Director Relacional

MCT, SQL Server MVP

Eladio Rincón

@erincon

erincon@solidq.com

44x próxima década

EXPLOSIÓN DE DATOS

CONSUMERIZACIÓN DE TI

NUBE PÚBLICA Y PRIIVADA

2015: servicios de nube pública tendrán 46% de crecimiento neto en gasto de TIIDC Digital Universe Study, sponsored by EMC, May 2010.Worldwide and Regional Public IT Cloud Services 2011 – 2015 Forecast, IDC , June 2011—Doc #228485

Datos digitales crecerán

Tendencias de la Industria

Adopción de Cloud en Términos Generales

0.001

0.100

10.000

1,000.000

100,000.000

10,000,000.000

1,000,000,000.000$/Mbyte (Y)

$/Mbyt...

Ley de Moore1

95

7

19

62

19

67

19

72

19

82

19

87

19

92

19

97

20

02

20

07

20

12

19

77

Donde gasta el tiempo RDBMS en TPCC

http://static.cs.brown.edu/courses/csci2270/papers/looking-glass.pdf

Pilares SQL Server 2014

Tecnologías In-memory

Escalabilidad y Alta Disponibilidad

Nuevos Escenarios Híbridos

In-Memory OLTP• 5–20x performance gain for

OLTP integrated into SQL Server

In-Memory Data Warehouse• 5–25x performance gain and

high data compression • Updatable and clustered

SSD Buffer Pool Extension• 4–10x RAM and up to 3x

performance gain transparently for apps

AlwaysOn enhancements • Increased availability and

improved manageability of active secondaries

Scale-Up Architectures• PDW, HDInsight, FTDW

Online database operations• Increased availability for

index and partition maintenance

Backup to Azure• Easy to implement and cost

effective disaster recovery solution to Windows Azure Storage

High availability to Azure Virtual Machines• Easy to implement and cost

effective high availability solution with Windows Azure Virtual Machines

Deploy to Azure• Deployment wizard to migrate

database

Before

15,000 requests/s

ec

16x fasterwith in-memory

250,000 requests/s

ec

SQL Server 2014 with in-memory

Bwin game application

Bwin game application

Granularidad de tabla: la que necesites

Se ajusta al hardware existente,

Necesitas mucha memoria

10x mejora de media, cambiando poco de la aplicación

No está soportado todo TSQL

Tipos de datos

Válido para todas las necesidades: OLTP, DW y BI

Incluido en Edición Enterprise

Tecnologías In-memory

Nuevo in-memory OLTP

Mejorado Almacenamiento Columnar para DW

In-memory BI con PowerPivot

Buffer pool extension a SSDs and mejoras en process. consultas

Before

15,000 requests/s

ec

16x fasterwith in-memory

250,000 requests/s

ec

SQL Server 2014 with in-memory

Bwin game application

Bwin game application

Granularidad de tabla: la que necesites

Se ajusta al hardware existente,

Necesitas mucha memoria

Válido para todas las necesidades: OLTP, DW y BI

Incluido en Edición Enterprise

Tecnologías In-memory

Nuevo in-memory OLTP

Mejorado Almacenamiento Columnar para DW

In-memory BI con PowerPivot

Buffer pool extension a SSDs and mejoras en process. consultas

Before

15,000 requests/s

ec

16x fasterwith in-memory

250,000 requests/s

ec

SQL Server 2014 with in-memory

Bwin game application

Bwin game application

10x mejora de media, cambiando poco de la aplicación

No está soportado todo TSQL

Tipos de datos

Válido para todas las necesidades: OLTP, DW y BI

Incluido en Edición Enterprise

Tecnologías In-memory

Nuevo in-memory OLTP

Mejorado Almacenamiento Columnar para DW

In-memory BI con PowerPivot

Buffer pool extension a SSDs and mejoras en process. consultas

Before

15,000 requests/s

ec

16x fasterwith in-memory

250,000 requests/s

ec

SQL Server 2014 with in-memory

Bwin game application

Bwin game application

Granularidad de tabla: la que necesites

Se ajusta al hardware existente,

Necesitas mucha memoria

10x mejora de media, cambiando poco de la aplicación

No está soportado todo TSQL

Tipos de datos

Tecnologías In-memory

Before With in-memory

Daily asynchronous sync

Online retailers

Near real-time sync

Online retailers

Customers

Customers

Hasta 640 procesadores lógicos64 vCPUs/VM1TB memory/VM64 nodes/cluster

Network virtualization provides flexibility and isolationAssign minimum and maximum bandwidth

Storage virtualizationEnterprise-class network storage on standard hardwareStorage tiering for higher performance

SQL Server 2014Resource Governor adds IO governance

SysPrep at cluster level

Windows Server 2012 R2Hyper-V

Storage spaces

NIC teaming

Online VHDX resize

Network and storage QoS

Pool 1

Pool 2

Pool 3

Escalabilidad y Afinidad

Arquitecturas de Referencia: Fast-Track-DWConfiguraciones escalables de 1 a 95 TB.– Optimizado para necesidades concretas

• Consultas con largos recorridos

– HWD balanceado; ideal para DataMarts– Buen precio/rendimiento– Testeado, best practices, …– Soporte fabricante

Entry Ref. Arch.ProLiant DL370 (2P) Internal disks (5.5 TB*)Expansion (14 TB*)

Basic Ref. Arch.ProLiant DL38x (2P) MSA P2000 G3(Rec. Capacity 20 TB*)

Mainstream Ref. Arch.ProLiant DL58x (4P) MSA P2000 G3(Rec. Capacity 40 & 60 TB*)

Premium Ref. Arch.ProLiant DL980 (8P) MSA P2000 G3(Rec. Capacity: 95 TB*)

Escalabilidad (Scale-Up)

PDW Analytics Platform System

Escalabilidad (Scale-Up)

Multi-DB failover, manual or automaticUp to 8 secondary replicas100% availability during all online operations

HA without expensive SANsOffload reads and backups to secondaries

Deployment and management wizardsMonitoring dashboard within SSMS and System Center

Primary Secondary 2Running BI workloads

Secondary 1Running backups

Synchronous data movement

Asynch

ronous

data movementAG_Listener

SQL Server 2014

Enhanced AlwaysOn

Replica Wizard

Windows Server 2012 R2

Clustered shared volume support

Online VHDX support

System Center 2012 R2

Manage on-premises and cloud apps

Alta Disponibilidad mejorada

DB 1DB 2

DB 3

DB 1

DB 3DB 2

Recursos compartidosWeb y Business editions

Recursos Reservados

Premium Edition

SQL Server en PaaS – SQL Azure

Se cambia

Modelo!

(Abril 2015)

Manual or automaticAt an instance level with point in time restoreMeasures DB usage patterns to set backup frequency

Fast disaster recovery (low RTO)Easy to deploy and manage

Cloud burstingGreater global reachBetter isolation of internal assets

On-premise network

Domain controller

Periodic Snapshot (automatic/manu

al)

SQL Server

Restore to VM

Geo replication

Nuevos Escenarios Híbridos

Manual or automaticAt an instance level with point in time restoreMeasures DB usage patterns to set backup frequency

Fast disaster recovery (low RTO)Easy to deploy and manage

Cloud burstingGreater global reachBetter isolation of internal assets

On-premise network

Domain controller

VPN tunnel (Windows Azure Virtual Network)Asynchronous

commit

Primary replica

Secondary replica

Synchronous commit

Run backups

Run BI reports

Nuevos Escenarios Híbridos

Manual or automaticAt an instance level with point in time restoreMeasures DB usage patterns to set backup frequency

Fast disaster recovery (low RTO)Easy to deploy and manage

Cloud burstingGreater global reachBetter isolation of internal assets

On-premise network

Domain controller(authenticating all users

on-premise and cloud)

VPN Tunnel Windows Azure Virtual Network

SQL Server

Lin

ked

Serv

er

Web server

Nuevos Escenarios Híbridos

Easily migrate on-premise SQL Server to a Windows Azure VMDesigned for users unfamiliar with Windows Azure

Ideal for existing apps and dev/test new appsFull SQL Server functionalityFull VM control

Ideal for new variable-demand appsDynamic scale-out of databaseNo patching of OS or DBHA built-in with 99.9% SLADB

Entrando en la Nube

DEMOExtensión a la Nube: Copias, Archivos de datos y Linked Server

Icon de tecnologíaIcon de tecnologíaIcon de tecnología

Pilares SQL Server 2014

Tecnologías In-memory

Escalabilidad y Alta Disponibilidad

Nuevos Escenarios Híbridos

In-Memory OLTP• 5–20x performance gain for

OLTP integrated into SQL Server

In-Memory Data Warehouse• 5–25x performance gain and

high data compression • Updatable and clustered

SSD Buffer Pool Extension• 4–10x RAM and up to 3x

performance gain transparently for apps

AlwaysOn enhancements • Increased availability and

improved manageability of active secondaries

Online database operations• Increased availability for

index and partition maintenance

Backup to Azure• Easy to implement and cost

effective disaster recovery solution to Windows Azure Storage

High availability to Azure Virtual Machines• Easy to implement and cost

effective high availability solution with Windows Azure Virtual Machines

Deploy to Azure• Deployment wizard to migrate

database

Power BI para usuarios de negocio

23

Curso onlineClases virtuales presenciales14, 15, 16, 21, 22 y 23 de JulioDe 16 a 20 h

Máster en BI 4ª Edición (Inicio Octubre 2014)- Clases presenciales virtuales- 450 horas (60 ECTS) - SolidQ – UPM

- Clases + trabajo práctico + proyecto- Beca de hasta 1.300 € para los primeros inscritos.

Máster en Big Data & Analytics1ª Edición (Inicio Octubre 2014)- Clases presenciales virtuales- 1 año (60 ECTS) UMA

- Clases + trabajo práctico + proyecto

Información e inscripción: http://university.solidq.com / ibinfo@solidq.com