SQL Server on Linux in Azure VMs Get

SQL Server on Linux in Azure VMs, Get Started in minutes

Agenda

SQL is Everywhere SQL Server 2019 SQL virtual machines SQL on Linux SQL managed instances SQL in containers SQL databases SQL on Kubernetes SQL Edge

Why run SQL Server on Linux?

Compatibility

SQL Server on Linux Choice with Compatibility Windows Docker containers and Kubernetes Linux

The same abstraction layer with SQL Server on Linux System resource & latency sensitive code paths RDBMS IS AS RS SQL Platform Abstraction Layer (SQLPAL) Windows Host Ext. Linux Host Extension Windows Linux All other systems SQL Platform Abstraction Layer (SQLPAL) SQL OS API Win 32 -like APIs SQL OS v 2 Host extension mapping to OS system calls (IO, Memory, CPU scheduling) Choice across OS and containers

What’s in SQL Server on Linux Editions Services Windows Linux Developer, Express, Web, Standard, Enterprise Database Engine, Integration Services, SQL Server Agent Analysis Services, Reporting Services, MDS, DQS Unlimited 24 TB 12 TB 524 PB Basic OLTP (Basic In-Memory OLTP, Basic operational analytics) Advanced OLTP (Advanced In-Memory OLTP, Advanced operational analytics, adaptive query processing) SQL Server Replication Basic high availability (2 -node single database failover, non-readable secondary) Advanced HA (Always On - multi-node, multi-db failover, readable secondaries) Basic security (Basic auditing, Row-level security, Data masking, Always Encrypted, Active Directory Authentication) Advanced security (Transparent Data Encryption) Poly. Base Basic data warehousing/data marts (Basic In-Memory Column. Store, Partitioning, Compression) Advanced data warehousing (Advanced In-Memory Column. Store) Advanced data integration (Fuzzy grouping and look ups) Windows ecosystem: Full-fidelity Management & Dev Tool (SSMS & SSDT), command line tools Linux/OSX/Windows ecosystem: Dev tools (VS Code), DB Admin GUI tool, command line tools Programmability (T-SQL, CLR, Data Types, JSON, Graph) Distributed Transactions Machine Learning Services Maximum number of cores Maximum memory utilized per instance Maximum database size Mission critical performance and HADR Security Data warehousing Tools Developer NEW NEW

SQL Server 2019 on Linux Tools and programmability Windows SQL Server Management Studio (SSMS) Windows SQL Server Data Tools (SSDT) 3 rd party tools continue to work Existing drivers/frameworks supported Azure Data Studio Visual Studio Code mssql extension Native OS command line tools • sqlcmd, bcp, sqlpackage mssql-cli mssql-scripter

Enterprise Ready

Performance on SQL Server 2019 on Linux #1 TPC-H Benchmark 1 1, 009, 065 Qph. H with $0. 47 per Qph. H 180 billion rows scanned in <20 seconds with 480 CPUs Go read what our customers say World’s first enterprise-class diskless database Hybrid Buffer Pool Enlightened SQL Server I/O Columnstore Indexes and In-Memory OLTP capabilities Intelligent Query Processing and Automatic Tuning Linux performance best practices guidance

TPC-H Benchmarks with SQL Server 2017 SQL/Linux has taken #1 position in all categories (non-clustered category)

TPC-H Benchmarks with SQL Server 2017 SQL/Linux has taken #1 position in all categories (non-clustered category)

#1 WORLD RECORD TPCH 30 TB benchmark with SQL Server 2019

HADR with SQL Server on Linux Failover Cluster Instance High Availability Group Read-Scale Availability Group Pacemaker and Corosync Single SQL Server instance CLUSTER_TYPE = EXTERNAL Auto failover SQL resource agent (mssql-server-ha) Pacemaker SQL resource agent (mssql-server-ha) CLUSTER_TYPE=NONE No clustering required Manual or forced failover Sync or async replicas Read scale routing Cross-platform Open-source resource agent is available on Git. Hub Repository 1 Shared Storage ISCSI, NFS, SMB 3 replicas required Configuration only replica for metadata Full AG capabilities Industry-leading solutions available from HA/DR partners 2

Layers of security for enterprise-grade protection Defense in depth Information Protection Trusted: most secure over last 8 years Encryption-at-rest Transparent data encryption (TDE) Backup encryption Cell-level encryption Encryption in transit Transport Layer Security (TLS 1. 2) Client-side encryption Always Encrypted SQL Access Management Database access SQL Server Logins Active Directory Authentication Roles and permissions Schemas Threat Protection Tracking & investigation Fine-grained audit Vulnerability Assessment Data Discovery & Classification Application access Row-Level Security Dynamic Data Masking

SQL Linux on Azure Iaa. S

Azure Iaa. S Five-Year Present Value *A Forrester Total Economic Impact™ Study Commissioned By Microsoft June 2017

New high availability and disaster recovery benefits for SQL Server NEW Free Failover servers for disaster recovery in Azure

Scale on Azure Iaa. S VM up to Storage up to 416 v-cores sub milisecond latency 11 TB Memory 160, 000 IOPS per disk 14 TB Local SSD 64 TB single disk 2 Gb/s Disk Throughput 100 TB, 100, 000 IOPS File Share 32000 Mbps Network Throughput 256 TB Capacity

Optimize Price-Perf with Memory Optimized VMs https: //docs. microsoft. com/en-us/azure/virtual-machines/windows/sizes-memory

Optimize SQL Server license cost with Constraint v. CPU VMs https: //docs. microsoft. com/en-us/azure/virtual-machines/windows/constrained-vcpu

Performance Guidance for SQL Server on Azure Iaa. S Locked Pages in Memory (Windows) Instant File Initialization (Windows) Standard SQL Query Performance Best Practices and Tuning SQL Linux Performance Best Practices RHEL 8 has XFS optimizations 1 - Start with collecting peak IOPS requirement for Data, Log and Temp DB. 2 - Choose the VM size that can scale to the total IOPS requirement 3 - If workload is Temp DB heavy, locate Temp DB on Local SSD 4 - Use RO cache enabled Premium SSD disks <4 TB for Data Files 5 - Use disk striping to achieve Data IOPS requirement 6 - Provision Ultra Disk only for Log file 7 - Monitor SQL DB performance, write queues on Data Files becoming the bottleneck for throughput then move Data files to Ultra SSD. Don’t forget about File-Snapshot Backups in Azure

Introducing the Giga. OM performance study

SQL Server 2017 Enterprise (RHEL) on Azure and AWS Performance Price/performance Transactions per second (TPS) 3 year pricing / TPS 3. 0 x Faster 878. 03 293. 50 $865. 79 67% Less $2, 648. 00 Performance and price-performance claims based on data from a study commissioned by Microsoft and conducted by Giga. Om in October 2019. The study compared price performance between SQL Server 2017 Enterprise edition on Red Hat Enterprise Linux 7. 4 in Azure E 64 s_v 3 instance type with 4 x P 30 1 TB Storage Pool data (Read Only Cache) + 1 x P 20 0. 5 TB log (No Cache) and the SQL Server 2017 Enterprise edition on Red Hat Enterprise Linux 7. 4 in AWS EC 2 r 4. 16 xlarge instance type with 1 x 4 TB gp 2 data + 1 x 1 TB gp 2 log. Benchmark data is taken from a Giga. Om Analytic Field Test derived from a recognized industry standard, TPC Benchmark™ E (TPC-E), and is based on a mixture of read-only and update intensive transactions that simulate activities found in complex OLTP application environments. Price-performance is calculated by Giga. Om as the cost of running the cloud platform continuously for three years divided by transactions per second throughput. Prices are based on publicly available US pricing in West US for SQL Server on Azure Virtual Machines and Northern California for AWS EC 2 as of October 2019. Price-performance results are based upon the configurations detailed in the Giga. Om Analytic Field Test. Actual results and prices may vary based on configuration and region.

Summary of Azure Iaa. S benefits for SQL Linux

Linux SQL Iaa. S Images • Azure Marketplace offers: Windows • SQL Server 2019 and SQL Serve 2017 on Ubuntu 1604 • SQL Server 2019 and SQL Server 2017 on REHL 7. 4 • SQL Server 2017 SUSE Enterprise Linux (SLES), • SQL Server 2019 and SQL Server 2017 on Windows Server 2019 and Windows Server 2016 Linux/Windows container Linux

SUSE at a Glance 1 st Enterprise Open Stack Distribution 20000+ 25+ Years of Linux Engineering Experience Certified Systems 7000+ Certified Applications 5000+ Global Partners 150+ Support Engineers 18% Revenue Growth 2/3+ of the Fortune Global 100 use SUSE Linux Enterprise

Focus on Digital Transformation Building on Open Source Solutions and Two Decades of Joint Innovation • Microsoft SQL Server, a feature-packed solution known for its security and reliability now available on SUSE Linux Enterprise Server • Enterprise-grade • High Performance • High Availability • Lower TCO • Integrated Support Model • Build once for Linux solutions on Azure and Azure Stack • SUSE Linux Enterprise Server for HPC is optimized for Microsoft Azure with Infiniband connectivity for enhanced networking and performance

SQL SERVER ON SUSE LINUX ENTERPRISE SERVER Microsoft + SUSE Technical Support Workflow on Azure Integrated technical support ensures escalations are properly resolved. The following workflow applies to technical support levels 1 -3 on Azure. Microsoft SQL Server 2017 Microsoft Azure Support FAQ Service request opened and evaluated Customer requests support SUSE Support FAQ SUSE Linux Enterprise Server Electronic transfer of service request to partner, if necessary Completed customer request and confirmed solution Satisfied customer

Murphy’s Law is universal "whatever can go wrong, will go wrong. " Faults will occur Challenge Hardware crash, flood, fire, power outage, earthquake Service outage and loss of data You might afford a five second blip, but can you afford a longer outage? Can you afford low availability? How much does downtime cost?

Cluster Concepts 33

What is a High Availability cluster?

High Availability is a Process, not a Product

Continuous Operation

Data Integrity

Single Point of Failure

minimize Mean Time To Recover

SUSE Linux Enterprise High Availability ®

Most modern and complete open source solution for high availability Linux clusters A suite of robust open source technologies that is Overview of SUSE High Availability Easy to use Integrated Virtualization agnostic Used with SUSE Linux Enterprise Server, it helps to Maintain business continuity Protect data integrity Reduce unplanned downtime for mission-critical workloads

SQL Server on Linux -High Availability Architecture

SQL Server on Linux HA Architecture Ø Pacemaker § Ø Coro. Sync SQL Server HA Resource Agent

SQL Server on Linux - Cluster Setup

1)zypper install -t pattern ha_sles node 1 # ha-cluster-init Pacemaker Cluster Setup node 2 # ha-cluster-join -c node 1 node 3 # ha-cluster-join -c node 1 https: //HAWKSERVER: 7630/ crm status

Cluster Setup Demo

Please evaluate this session Your feedback is important to us! https: //aka. ms/ignite. mobileapp https: //myignite. techcommunity. microsoft. com/evaluations

Find this session in Microsoft Tech Community