SQL Server 2016 New Features

22 de Julio 2015 (12 pm GMT -5)Percy Reyes

Resumen:En esta sesión revisamos las nuevas mejoras y funcionalidades que estarán implementadas en la siguiente versión de SQL Server principalmente en Seguridad, Rendimiento y Alta Disponibilidad.

Está por comenzar: Próximos Eventos

Moderador: Jose Luis Rivera

Excel Power Pivot como herramienta de apoyo para la estratetia de BI Self-Service 29 de Julio

Julián Castiblanco

Visualización. Mejorando las respuestas a las preguntas de Negocio

05 de AgostoSalvador Ramos

Sincronizando Azure Search con SQL Server

12 de AgostoDavid Sánchez

Manténgase conectado a nosotros!

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

/SpanishPASSVC

lnkd.in/dtYBzev

/user/SpanishPASSVC

/SpanishPASSVC

3

4

Oportunidades de Voluntariado

PASS no pudiera existir sin personas apasionadas y dedicadas de todas partes del

mundo que dan de su tiempo como voluntarios.

Se un voluntario ahora!!

Para identificar oportunidades locales visita volunteer.sqlpass.org

Recuerda actualizar tu perfil en las secciones de “MyVolunteering” y MyPASS para mas

detalles.

Sigan Participando!

• Obtén tu membresía gratuita en sqlpass.org

• Linked In: http://www.sqlpass.org/linkedin• Facebook: http://www.sqlpass.org/facebook• Twitter: @SQLPASS• PASS: http://www.sqlpass.org

SQL Server 2016 New Features

22 de Julio de 2015

Percy ReyesMicrosoft SQL Server MVPTwitter @percyreyes

Moderador: Jose Luis Rivera

Performance Security Availability Scalability

Operational analyticsInsights on operational data; Works with in-memory OLTP and disk-based OLTP

In-memory OLTP enhancementsGreater T-SQL surface area, terabytes of memory supported, and greater number of parallel CPUs

Query data store Monitor and optimize query plans

Native JSON Expanded support for JSON data

Temporal database supportQuery data as points in time

Always encryptedSensitive data remains encrypted at all times with ability to query

Row-level securityApply fine-grained access control to table rows

Dynamic data maskingReal-time obfuscation of data to prevent unauthorized access

Other enhancementsAudit success/failure of database operationsTDE support for storage of in-memory OLTP tablesEnhanced auditing for OLTP with ability to track history of record changes

Enhanced AlwaysOnThree synchronous replicas for auto failover across domainsRound robin load balancing of replicas Automatic failover based on database health DTC for transactional integrity across database instances with AlwaysOnSupport for SSIS with AlwaysOn

Enhanced database caching Cache data with automatic, multiple TempDB files per instance in multi-core environments

Agenda

Improvements on AlwaysOn Availability Groups Standard Edition will support AGs support with one database per group synchronous

or asynchronous, not readable (HA/DR only) Improved Log Throughput Up to 3 sync replicas Load balancing of read-only requests on multiple secondaries Better policies for failover, for example, looking at individual database health Microsoft DTC support

Improvements on Security Always Encrypted: Columns are encrypted using keys on the application driver side Dynamic Data Masking: Establishes policies to mask sensitive column values to

specific users or roles Row Level security: Establishes policies that filter our specific rows based on the user

role

Improvements on In-Memory OLTP Will support Foreign Keys, Check and Unique constraints between in-memory tables Size limit increased from 256GB to 2TB Storage file garbage collector tuned to be faster by decoupling the GC from FS Transparent Database Encryption (TDE) is supported now for in-memory tables Multi-threaded checkpoint for in-memory (one per container) ALTER PROCEDURE and sp_recompile supported (fully online) ALTER TABLE for schema and index changes (offline). Requires 2x memory. Parallel plans are now possible on in-memory tables Native compilation support for: outer joins, OR, NOT, UNION (ALL),DISTINCT,

Subqueries (IN, NOT IN, EXISTS, NOT EXISTS) Natively compiled stored procedures can be nested Ability to create natively compiled UDFs Trigger support added to in-memory tables Improvements on the migration wizard Wide range of collations supported now LOB support

Improvements on Columnstore Indexes Parallel insert Nonclustered B-Tree indexes on top of the Clustered Columnstore Index Read/write Nonclustered Columnstore on top of a Regular Clustered Index In-Memory Columnstore Index on In-Memory OLTP table More T-SQL constructs run in batch mode. More predicates and aggregates REORGANIZE will remove deleted rows and merge small rowgroups, online New DMVs, XEvents and Perfmon counters Support for RCSI or Snapshot isolation Fully readable on an Availability Groups secondary Use filtered Columnstore indexes for high performance

CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI ON T_account (accountkey, accountdescription, unitsold) WHERE accountkey > 0;SELECT SUM( unitsold ) FROM t_account WHERE accountkey >=0 AND accountkey < 1000;

Improvements on DBA features Query Store: Allows tracking changes of execution plans, comparing different plans

and easily telling the optimizer what plan to use. Online ALTER COLUMN Truncate at Partition level Instance will have better handling of multiple Temdb files.

Improvements on Dev features Native JSON support. SQL Server 2016 will natively parse and store JSON as relational

data and will support exporting relational data to JSON. Temporal database: transparent and built-in functionality to track historical changes

in the data (for example, an address or name change). SQL Dev integrated back into one experience: Sql Server Data Tools for Visual Studio

2015 will have both relational and BI database projects.

JSON (JavaScript Object Notation) is an standardized data exchange format that is currently not supported natively by SQL Server. To perform JSON imports and exports you need to hand-code complex T-SQL, SQLCLR or JavaScript.

Improvements on Azure integrations Stretch a SQL Server table to Azure. Have a table that expands from on premises SQL

Server to Azure Supports Always Encrypted & Row Level Security Snapshot backup to Azure storage when running as a VM on Azure and with files

directly on blob storage

Integration with Hadoop Polybase included in SQL Server to query HDFS with T-SQL. Integrated with SQL Server To stand up a cluster of SQL2016 instances, one head node and one or more compute

nodes and do parallel processing with Hadoop through Polybase.

Improvements on BI Datazen is available now for any SQL 2008+ Enterprise + SA license holder, should be

further integrated with the BI stack by release time. SSRS is getting a modern look, mobile experience, all browser compatibility,

paramater layout customization, new visualizations. SSIS designer support for previous versions. PowerQuery support in SSIS. PowerBI Q&A through the SSAS on-premises data connector. Pin SSRS reports to PowerBI dashboards. Many to Many relationships on SSAS Tabular. Built-in time intelligence on SSAS Tabular models, new DAX functions, parallel

partition processing. New scripting language for Tabular model projects. Netezza as a native data source for SSAS. DBCC instruction support for multidimensional for maintenance related tasks. Tight integration and improvements in relation to Sharepoint vNext.

Excel Power Pivot como herramienta de apoyo para la estratetia de BI Self-Service

29 de Julio (12 pm GMT -5)Julián Castiblanco

Resúmen:Mostrar algunos de los escenarios en que Excel Power Pivot se presenta como una herramienta de apoyo para analizar información en las diferentes áreas de la organización.

Próximo Evento