SQL Server Evolution Overview Myles Matheson and Scott

SQL Server Evolution Overview Myles Matheson and Scott Klein M 211

The Evolution of Microsoft Data Platform XML ● KPIs SQL Server 2000 Management Studio ● Mirroring SQL Server 2005 Compression ● Policy-Based Mgmt ● Programmability SQL Server 2008 Power. Pivot ● Share. Point Integration ● Master Data Services SQL Server 2008 R 2 Always. On ● Column. Store Index ● Data Quality Services ● Power View ● Cloud Connectivity SQL Server 2012 In-Memory Across Workloads ● Performance & Scale ● Hybrid Cloud Optimized ● HDInsight ● Cloud BI SQL Server 2014 Operational Analytics ● Stretch. DB ● Polybase ● R Integration ● Temporal ● Query Data Store ● RLS ● Always Encrypted SQL Server 2016

Do more. Achieve more.

Mission critical platform Performance Security Availability Scalability Operational analytics Always Encrypted Enhanced Always. On Support for Windows Server 2016 • Minimize performance impact running real-time analytics on transaction data • Avoid data sprawl In-memory OLTP for more applications Query Store Row level security Dynamic Data Masking • 3 synchronous replicas for auto failover across domains • Round robin load balancing of replicas • DTC for transactional integrity across database instances with Always. On Enhanced online operations 12 TB 16 Sockets

Deeper insights across data Access any data Scale and manage Powerful insights Poly. Base Enterprise-grade Analysis Services Built-in advanced analytics Power Query for analytics and reporting Enhanced SSIS • Designer support for previous SSIS versions • Support for Power Query New single SSDT in Visual Studio 2015 Enhanced MDS • • Excel add-in 15 x faster More granular security roles Archival options for transaction logs Reuse entities across models Business insights through rich visualizations on mobile devices Enhanced Reporting Services

Hyperscale cloud Hybrid solutions Simplicity Consistency Stretch tables into Azure Easy migration of on-premises SQL Server Common development, management and identity tools Power BI with on-premises data Hybrid scenarios with SSIS Enhanced backup to Azure Simplified Add Azure Replica Wizard Consistent experience from on-premises to Azure

Mission-critical performance Performance Operational analytics Insights on operational data; Works with in-memory OLTP and disk-based OLTP In-memory OLTP enhancements Greater T-SQL surface area, terabytes of memory supported, and greater number of parallel CPUs Query data store Security Always encrypted Sensitive data remains encrypted at all times with ability to query Row-level security Apply fine-grained access control to table rows Round robin load balancing of replicas Automatic failover based on database health Other enhancements Support for SSIS with Always. On Real-time obfuscation of data to prevent unauthorized access Native JSON TDE support for storage of inmemory OLTP tables Query data as points in time Three synchronous replicas for auto failover across domains DTC for transactional integrity across database instances with Always. On Audit success/failure of database operations Temporal database support Enhanced Always. On Dynamic data masking Monitor and optimize query plans Expanded support for JSON data Availability Enhanced auditing for OLTP with ability to track history of record changes Scalability Enhanced database caching Cache data with automatic, multiple Temp. DB files per instance in multi-core environments

Traditional operational/analytics architecture BI analysts § Key issues § Complex implementation § Requires two servers (capital IIS Server expenditures and operational expenditures) § Data latency in analytics § More businesses demand; requires real-time analytics

Minimizing data latency for analytics BI analysts IIS Server § Benefits § No data latency § No ETL § No separate data warehouse § Challenges § Analytics queries are resource intensive and can cause blocking § Minimizing impact on operational workloads § Sub-optimal execution of analytics on relational schema

Motivation for JSON § JSON became ubiquitous § Compact and simple data exchange format § The choice on the web § Recommended scenario § I CAN accept JSON, easily parse and store it as relational § I CAN export relational easily as JSON § I CAN correlate relational and non-relational

§ Data exchange with JSON § SELECT * FROM my. Table FOR JSON AUTO Number Date Customer Price Quantity SO 43659 2011 -05 -31 T 00: 00 AW 29825 59. 99 1 SO 43661 2011 -06 -01 T 00: 00 AW 73565 24. 99 3 SELECT * FROM OPENJSON(@json)

§ JSON and relational § CREATE TABLE Sales. Order. Record ( Id int PRIMARY KEY IDENTITY, Order. Number NVARCHAR(25) NOT NULL, Order. Date DATETIME NOT NULL, § § JSales. Order. Details NVARCHAR(4000) CONSTRAINT Sales. Order. Details_IS_JSON CHECK ( ISJSON(JSales. Order. Details)>0 ), AS CAST(JSON_VALUE(JSales. Order. Details, '$. Order. Qty') AS int) ) § GO § JSON is plain text ISJSON guarantees consistency CREATE INDEX idx. Json ON Sales. Order. Record(Quantity) INCLUDE (Price); Optimize further with computed column and INDEX

Deeper insights across data Access any data Poly. Base Insights from data across SQL Server and Hadoop with simplicity of T-SQL Enhanced SSIS Designer support for previous SSIS versions Support for Power Query Scale and manage Enterprise-grade Analysis Services Enhanced performance and scalability for analysis services Single SSDT in Visual Studio 2015 (CTP 3) Build richer analytics solutions as part of your development projects in Visual Studio Enhanced MDS Excel add-in 15 x faster; more granular security roles; archival options for transaction logs; and reuse entities across models Powerful Insights Mobile BI Business insights for your onpremises data through rich visualization on mobile devices with native apps for Windows, i. OS and Android Enhanced Reporting Services New modern reports with rich visualizations Advanced analytics R integration (CTP 3) Bringing predictive analytic capabilities to your relational database Analytics libraries (CTP 3) Expand your “R” script library with Microsoft Azure Marketplace

Poly. Base Query relational and non-relational data with T-SQL Capability Quote: ************ T-SQL query *********************** SQL Server Name DOB State Jim Gray 11/13/58 WA Ann Smith 04/29/76 ME Hadoop $658. 39 T-SQL for querying relational and non-relational data across SQL Server and Hadoop Benefits Æ New business insights across your data lake Æ Leverage existing skillsets and BI tools Æ Faster time to insights and simplified ETL process

Built-in advanced analytics In-database analytics at massive scale Example Solutions Extensibility • Sales forecasting R Integration • Warehouse efficiency • Predictive maintenance R ? New R scripts • Credit risk protection 0100100 010101 Analytic Library 0100100 010101 Data Scientist Interact directly with data 0100100 010101 T-SQL Interface Relational Data Built-in to SQL Server Data Developer/DBA Manage data and analytics together 0100100 010101 Microsoft Azure Marketplace

Rich visualizations on mobile devices Capabilities Business insights through rich visualizations on any device. Native apps for Windows, i. OS and Android Benefits Æ Access your data from anywhere Æ Touch optimized data exploration and perfect scaling to any screen form-factor Æ Collaborate with colleagues on the go

Modern reports with SQL Server Reporting Services Report consumption from modern browsers Improved parameters Modern themes New chart types

Continuous investments Live Power BI reports & dashboards Live Query Cloud On-premises Scheduled Refresh

Mobile BI apps for SQL Server

Data visualization and publishing

Deeper insights across data Hybrid solutions Stretch Database Stretch operational tables in a secure manner into Azure for cost effective historic data availability works with Always Encrypted and Row Level Security Power BI with on-premises data New interactive query with Analysis Services. Customer data stays behind your firewall Hybrid Scenarios with SSIS Azure Data Factory integration with SSIS, package lineage and impact analysis and connect SSIS to cloud data source Enhanced Backup to Azure Faster restore times and 50% reduction in storage, support larger DBs with Block blobs and custom backup schedule with local staging Simplicity Easy migration of on-premises SQL Server Simple point and click migration to Azure Simplified Add Azure Replica Wizard Automatic listener configuration for Always. On in Azure VMs Consistency Common development, management and identity tools Including Active Directory, Visual Studio, Hyper. V and System Center Consistent Experience from SQL Server on-premises to Microsoft Azure Iaa. S and Paa. S

Stretch SQL Server into Azure Securely stretch cold tables to Azure with remote query processing Capability Apps continue working without change or disruption Azure On-premises Hot/active data Benefits Cold/closed data Cost effective historical data availability for operational DBs Trickle data movement and remote query processing Orders In-memory OLTP table Order history Stretched table Stretch large operational tables from on-premises to Azure with the ability to query BI integration for on-premises and cloud Entire table is online and queryable Transparent to application Supports new TCE transparent encryption and Row Level Security

Stretch SQL Server into Azure Stretch warm and cold tables to Azure with remote query processing Capability Microsoft Azure Jim Gray ox 7 ff 654 ae 6 d 3/18/2005 Order history Stretch to cloud Name SSN John Smith Date Customer data cm 61 ba 906 fd 2/28/2005 Product data ox 7 ff 654 ae 6 d 3/18/2005 Order History i 2 y 36 cg 776 rg 4/10/2005 Bill Brown nx 290 pldo 90 l Jane Doe Jim Gray 4/27/2005 Stretch cold database tables from on-premises SQL Server Databases to Azure with remote query processing Benefits Æ Cost effective historical data Æ Entire table is online and remains queryable from on-premises apps Æ Transparent to applications Æ Supports Always Encrypted & Row Level Security Query SQL Server App

Connect live to on-premises Analysis Services data Capability Publish on-premises Analysis Services data models for consumption in Power BI Live dashboards and exploration Benefits AS Connector Analysis Services on-premises Tabular model SQL Server v. Next Interactive query Faster time to solution without having to move your data to the cloud. Optimized query performance for sub-second response times. Manage and secure data residing on-premises in SQL Server Analysis Services.

VISUALIZE + DECIDE Applications Reports Dashboards Natural language query Mobile Modeling Machine learning TRANSFORM + ANALYZE Orchestration The Microsoft data platform Information management Complex event processing CAPTURE + MANAGE {} Relational Non-relational No. SQL Streaming Internal & external

Session code M 211 M 218 M 320 M 225 M 324 M 327 M 229 M 236 M 410 M 242 M 244 M 352 M 355 M 359 M 252 M 370 M 378 M 383 Session name The SQL Server Evolution: Overview Upgrading Microsoft SQL Server In-Memory OLTP: The Road Ahead Using Azure Machine Learning to predict Trade Me auction prices Why you should be thinking Hybrid with SQL Server 2016 Big Data for the SQL Ninja Introduction to Cortana Analytics Microsoft SQL Server BI Drill Down SQL Server Execution Plans and the Query Optimizer Microsoft Azure SQL Data Warehouse Overview BI in Action How Microsoft Finance uses Power BI BI in the cloud, is it possible? Sure is with Azure! End-to-End SQL Server Master Data Services Advanced SSDT and DACFx - Practical techniques for real world database development and deployment. Telling Stories with Data BI Tools as a Key Component of the Business Narrative Azure Machine Learning: From Design to Integration Elastic for SQL shards, pools, stretch Mission Critical features in SQL Server 2016 Start Time 9/2/2015 9: 00 9/2/2015 10: 40 9/2/2015 11: 55 9/2/2015 13: 55 9/2/2015 15: 10 9/2/2015 16: 30 9/3/2015 9: 00 9/3/2015 10: 40 9/3/2015 11: 55 9/3/2015 13: 55 9/3/2015 15: 10 9/3/2015 16: 30 9/4/2015 9: 00 9/4/2015 10: 40 9/4/2015 11: 55 9/4/2015 13: 55

Azure Consistent Service Delivery Overview 5 Security and Assurance Overview 1 2 Server Virtualisation Overview 6 What’s New in System Centre for Management 3 Networking Overview 4 Storage Overview NZ 1 Wed 10: 00 am NZ 2 Wed 1: 30 pm SKYCITY Theatre Thu 11: 00 am SKYCITY Theatre Thu 3: 30 pm NZ 4 Fri 9: 00 am NZ 1 Fri 11: 00 am Find me later at… § Hub Happy Hour Wed 5: 30 -6: 30 pm § Hub Happy Hour Thu 5: 30 -6: 30 pm § Closing drinks Fri 3: 00 -4: 30 pm

Free Online Learning http: //aka. ms/mva Subscribe to our fortnightly newsletter http: //aka. ms/technetnz http: //aka. ms/msdnnz Sessions on Demand http: //aka. ms/ch 9 nz

© 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.