Pam Lahoud Sr Program Manager Microsoft Mixture of

Pam Lahoud, Sr. Program Manager, Microsoft

Mixture of SQL Server 2008, 2014, and 2016 servers Expensive ETL applications for data sources outside of SQL Server Should we use “Big Data” technologies? Query performance tuning expensive Some database applications suffer from I/O performance Need data secure end-to-end with classification and auditing Applications need better availability and less downtime Our company uses a mixture of different operating system platforms Popularity of containerized applications growing What, when, and how should I migrate to the cloud? Desire to build more intelligent applications with AI and Machine Learning

SQL

Performance Security Availability Developer • Query Store • Adaptive Query Processing • Automatic Tuning • Columnstore and In-Memory OLTP • “It Just Runs Faster” • Always Encrypted • Row Level Security • Dynamic Data Masking • Clusterless Availability Groups • Distributed Transactions for Availability Groups • Resumable Index Maintenance • JSON • Temporal Tables • Graph Database Modern Platform • Linux and Containers • Machine Learning Services with R and Python

Modernize Now with big data clusters Choice of platform and language Industry-leading performance Most secure over the last 8 years 5 180 In-memory across all workloads Private cloud 80 60 40 20 A L H AN SQ SA P DB 2 gr e Po st cle IB M O ra L 0 er #1 DW performance on 1 TB 2, 10 TB 3, and 30 TB 4 100 y. S Q Python Ruby. NET core 120 M SCALA Node. js C#/VB. NET 140 Se rv T-SQL Java C/C++ #1 OLTP performance 1 160 L Vulnerabilities (2010 -2018) Analytics over structured and unstructured data with the power of SQL and Apache Spark Insights in minutes and rich reports 200 SQL SQ Intelligence over any data The best of Power BI and SQL Server Reporting Services in Power BI Report Server Most consistent data platform 1/10 th the cost of Oracle All TPC Claims as of 1/19/2018. 1 http: //www. tpc. org/4081; 2 http: //www. tpc. org/3331; 3 http: //www. tpc. org/3326; 4 http: //www. tpc. org/3321; 5 National Institute of Standards and Technology Comprehensive Vulnerability Database Public cloud

SQL Server 2019 Key New Functionality ciphertext Polybase++ Query SQL Learn Intelligent Query Processing Secure Enclaves 1010 0101 0110 Memory grant In-Memory Database plaintext Run query High Availability Enclave Built-in Machine Learning and Extensibility R Modern Platforms with Compatibility Arm 64

The Intelligent Query Processing feature family Intelligent QP The intelligent database Adaptive QP Adaptive Joins Batch Mode Table Variable Deferred Compilation Interleaved Execution Batch Mode for Row Store Memory Grant Feedback Batch Mode Row Mode Approximate QP Approximate Count Distinct Scalar UDF inlining

Always Encrypted with secure enclaves Confidential computing plaintext ciphertext Enhanced client driver plaintext Enclave

Availability groups on Kubernetes Keep SQL Server running Pod Load balancer Operator AG Pod SQL Server primary AG agent Load balancer Pod SQL Server secondary AG agent

Extend T-SQL with R, Python, and Java R SQL Server

Windows Docker containers and Kubernetes Linux

Portable Run anywhere Docker is supported Container configuration Persisted storage Lightweight Reduced disk, CPU, and memory footprint Consistent image of SQL Server, scripts, and tools Efficient Faster deployment, reduced patching, and less downtime Switch for simple upgrades Container Database Container

Built-in HADR orchestration with no clustering required Node Pod Kubernetes SQL Server Load Balancer Service SQL Server User Pod SQL Server Persistent Volume Storage

Availability groups on Kubernetes Pod Load balancer Operator AG Pod SQL Server primary secondary AG agent Load balancer Pod SQL Server primary secondary AG agent SQL Server secondary AG agent

“It’s all about Data Virtualization” ü Distributed compute engine integrated with SQL Server ü Query data where it lives using T-SQL ü Distributed, scalable query performance Analytics T-SQL Apps ü Manual/deploy with SQL Server ü Auto deploy/optimize with Big Data Clusters SQL Server Poly. Base external tables ODBC Intelligence over all data No. SQL Relational databases Big data

SQL

Data virtualization Analytics T-SQL Managed SQL Server, Spark, and data lake Apps SQL Server External Tables Complete AI platform Admin portal and management services Integrated AD-based security SQL Server Spark REST API containers for models SQL Server ML Services Spark & Spark ML Compute pools and data pools Scalable, shared storage (HDFS) Open database connectivity No. SQL Relational databases External data sources HDFS Combine data from many sources without moving or replicating it Store high volume data in a data lake and access it easily using either SQL or Spark Easily feed integrated data from many sources to your model training Scale out compute and caching to boost performance Management services, admin portal, and integrated security make it all easy to manage Ingest and prep data and then train, store, and operationalize your models all in one system

Control Plane Controller Svc Azure FSM Engine Configuration Store (SQL Server) Kibana Grafana Elastic Search Influx. DB Custom apps BI App Deploy Compute pool SQL Compute Node SQL Data Node Storage Node SQL Compute Node … SQL Compute Node Directly read from HDFS Storage pool Spark SQL Server HDFS Data Node SQL Server master instance Compute pool Data mart Io. T data Analytics Spark SQL Server HDFS Data Node … Spark SQL Server HDFS Data Node Kubernetes pod Node Persistent storage Node HDFS Tiering Node

String or binary data would be truncated in table '%. *ls', column '%. *ls’. Truncated value: '%. *ls' SELECT page_info. * FROM sys. dm_exec_requests AS d CROSS APPLY sys. fn_Page. Res. Cracker(d. page_resource) AS r CROSS APPLY sys. dm_db_page_info(r. db_id, r. file_id, r. page_id, 'DETAILED') AS page_info;

Migration from legacy SQL Server Migration from external databases Azure Site Recovery Database Experimentation Assistant SQL Legacy SQL Server Data Migration Assistant Azure Database Migration Service * Coming by GA SQL Server 2017 SQL Server 2019* SQL Server in Azure Virtual Machine In-Place (Windows) Database Backup Log Shipping Bulk Import SSIS SQL Server Microsoft Access Query Tuning Assistant SQL Server Migration Assistant (SSMA)

http: //aka. ms/bobwardms http: //aka. ms/bobsqldemos http: //aka. ms/sqllinuxbook Use our free training at https: //aka. ms/sqlworkshops Learn from videos and demos at https: //aka. ms/sqlchannel Download and try it at http: //aka. ms/ss 19 What’s new for SQL 2019 documentation Sign-up for the EAP program at https: //aka. ms/eapsignup

Thank You @SQLGoddess