What’s new in Azure SQL Database

2. What’s new in Azure SQL Database Lindsey Allen Joachim Hammer Ajay Jagannathan Alain Dormehl BRK3166

3. Agenda What’s new in Intelligent DB Introduction & look back What’s new in Reliability What’s new in Security What’s new in Platform Summary and resources

4. Agenda What’s new in Intelligent DB Introduction & look back What’s new in Reliability What’s new in Security What’s new in Platform Summary and resources

5. Cloud-first approach breeds innovation New innovations Continuous enhancements SQL SQL SQL SQL Across customer base

6. Azure Relational Database PlatformAutopilot for your databases Power BI Azure App Services Azure Data Factory Azure Analysis Services Azure Machine Learning Azure Cognitive Services Azure Bot Service PostgreSQL MySQL SQL Database MariaDB SQL DW Hyperscale Database Services Platform Intelligentautomatic performance and cost optimization monitoring at scale TrustedReliability, Resiliency with 99.99% SLA, Enterprise security & privacy FlexibleOn-demand scaling across SLOsResource governance and sharing Azure Compute / Service Fabric /Accelerated Network Azure Storage Global Azure in 50 regions

7. What’s New in Azure SQL Database [2018] • Zone Redundancy GA • Data Sync GA • Storage Add-ons GA • Resumable Online Index Creation Public preview • Elastic Jobs Pub preview • TDE with Azure Key Vault Integration GA • Long Term Retention preview • vCore-based model preview • Reserved Capacity GA Apr Sept Aug May Jul Jun • MI Business Critical Public Preview • Long Term Backup Retention GA • Auto-Failover GA • vCore-based model GA • Advanced Threat Protection GA • Vulnerability Assessment GA • Database Migration Service GA • AHB for SQL Server to Managed Instance

8. Agenda What’s new in Intelligent DB What’s new in Reliability Introduction What’s new in Security What’s new in Platform Summary and resources

9. Announcements

10. Azure SQL Database deployment options Azure SQL Database Single Elastic Pool Managed Instance Database-scoped deployment option with predictable workload performance Shared resource model optimized for greater efficiency of multi-tenant applications Instance-scoped deployment option with high compatibility with SQL Server and full PaaS benefits Best forapps that require resource guarantee at database level Best forSaaS apps with multiple databases that can share resources at database level, achieving better cost efficiency Best formodernization at scale with low friction and effort SQL

11. SQL Database Managed Instance Generally Available VNet1 VNet-to-VNet Web app (public IP) Network isolation (customer VNET) SQL Instance #1 SQL Instance #2 Tenant isolation (compute, storage) SQL Instance #3 “Virtual data cluster” dedicated to customer (virtual private cluster, VNET, private IPs) VPN / Express Route Gateway IPsec IKE S2S VPN Tunnel оn-premises

12. SQL Database Managed Instance Generally Available Get the best of SQL Server with all the benefits of a fully-managed service Use your existing SQL Server licenses to save up to 55% with the Azure Hybrid Benefit for SQL Server Fully managed SQL Server with all the popular capabilities including CLR, native backup restore, linked sever, logical replication, SQL Agent, DBMail Experience all the benefits of SQL Database – HA built-in, failover, Geo-DR, built-in intelligence Near Zero downtime migration using Azure Database Migration Service Managed Instance available with two options: General Purpose: GA 10/1/18 Business Critical is in preview. Migrate Assess Create MI 1 2 3 Azure Database Migration Service Feature parity check Prepare network environment Create Managed Instance Compatibility check

13. Identify andassess Enroll in programs Generate plan of care Manageand intervene Care coordination and engagement Healthcare analytics Utilization & financial analytics Care management Knowledge hub Patient outreach Quality improvement Number of databases: 50+ Primary DB size: 500GB – 10TB Risk stratification Cohort designer Data platform Data aggregation and normalization Information sharing Privacy and security Provisioning identity management Enterprise master patient index Intelligence platform Open exchange Single sign-on & context management

14. Azure SQL Database Hyperscale (Preview) Read Only Read and Write A new, highly scalable service tier that adapts on-demand to your workload's needs, auto-scaling up to 100TB per database. Storage dynamically adapts to your workloads’ needs, auto-scaling up to 100TB. Provision one or more additional compute nodes that can serve your read-only workload and use them as a hot-standby, in case of failover. Perform operations in constant time, regardless of the size of the data operation. Compute and storage resources scale rapidly and independently without sacrificing performance. RBPEX Data Cache RBPEX Data Cache RBPEX Data Cache RBPEX Data Cache Secondary Compute sqlservr.exe Secondary Compute sqlservr.exe Secondary Compute sqlservr.exe Primary Compute sqlservr.exe Log Service Compute Landing Zone (Azure Premium Storage) ….. Log Destaging Log Cache Long Term Storage for PITR (Azure Standard Storage) Log Storage Data Pages Data Pages Data Pages 1 TB data file 1 TB data file 1 TB data file Covering RBPEX Data Cache Covering RBPEX Data Cache Covering RBPEX Data Cache Local SSD Cache ….. File Snapshots File Snapshots File Snapshots Resilient Buffer Pool Extension RBPEX Log Pathway Data Pathway Hyperscale is built based on a new cloud-born architecture which decouples compute, log and storage.   Azure Storage

15. Demo – SQL Database Hyperscale Lindsey Allen

16. Azure is the most economical destination for SQL Workloads Save up to 55% with Azure Hybrid Benefit for SQL Server and up to 80% with Reserved Capacity • How does a customer qualify? • Customer must have on-premises SQL Server licenses with active Software Assurance • How does a customer activate this benefit? • Deploy a vCore-based SQL DB option and select Azure Hybrid Benefit for SQL Server as an option in the portal. • Buy reserved capacity in the Azure portal for 1 or 3 year period for one or multiple subscriptions • Learn more Up to 33% savings1 Total monthly cost of 8 vCore SQL DB Business Critical option Up to 80% savings2 Customer total cost With SQL Database reserved capacity License included Reserved Capacity With Azure Hybrid Benefit 1 Savings based on eight vCore SQL Database managed instance general purpose in West2 US region, running 730 hours per month. Savings are calculated from on demand full price (license included) against 3-year reserved capacity license Included. Actual savings may vary based on region, instance size, and performance tier. Prices as of May 2018, subject to change. 2 Savings based on eight vCore SQL Database managed instance business critical in West2 US region, running 730 hours per month. Savings are calculated from on demand full price (license included) against base rate with Azure Hybrid Benefit plus 3-year reserved capacity. Savings excludes Software Assurance cost for SQL Server Enterprise edition, which may vary based on EA agreement. Actual savings may vary based on region, instance size, and performance tier. Prices as of May 2018, subject to change.

17. Agenda What’s new in Intelligent DB What’s new in Reliability Introduction What’s new in Security What’s new in Platform Summary and resources

18. Announcements

19. Enterprise Grade Security that is Easy-to Use Trusted: most secure over last 7 years Defense-in-depth  Security Management Vulnerability Assessment  + SQL Threat Detection SQL Auditing Threat Protection SQL Permissions VNET SQL Firewall (server- and database-level) SQL Authentication Azure Active Directory Authentication (w/ MFA) Row-level security  Identity & Access Management Encryption-in-flight (Transport Layer Security TLS over TDS) Encryption-at-rest (Transparent Data Encryption TDE) Service- or User-managed keys Backup encryption Encryption-in-use (Always Encrypted) Data Masking Data Discovery and Classification  Information Protection

20. Confidential Computing Demo Joachim Hammer

21. Agenda What’s new in Intelligent DB What’s new in Reliability Introduction What’s new in Security What’s new in Platform Summary and resources

22. Announcements

23. SQL Graph

24. Merge DML • Merge DML Bob lives_in SFO. If the edge does not exist, insert. If an edge is found, update the street address. MERGEhas_creditcard HCC USING (creditcard_transas CT joinAccountHolderAS AH onAH.CustomerID=CT.CustomerID joinCreditCardAS CC on CT.CCID = CC.CCID) ONMATCH(AH-(HCC)->CC) WHENMATCHEDTHEN UPDATESETHCC.CreditLimit=CT.CreditLimit WHENNOTMATCHEDTHEN INSERTVALUES (AH.$node_id,CC.$node_id,CT.CreditLimit); Available on Azure SQL Database for public preview!

25. View or Derived Table in MATCH Query • View or Derived table in MATCH query Find if ‘Alice’ shares any piece of her contact information (Address, Phone, SSN) with other bank customers. … FROM … (SELECT street ascontact_info,'address'contact_typeFROMaddress UNIONALL SELECTcast(phonenumberasvarchar(200)),'phone'FROMphonenumber UNIONALL SELECTcast(ssnasvarchar(200)),'ssn'FROMssn ) ASShared_contact, … WHEREMATCH(AccountHolder-(has_contact)->Shared_contact) Derived tables are available on Azure SQL Database for public preview in some regions, coming soon to other regions!

26. Edge Constraints • Edge Constraints has_addressedge can only connect AccountHolderTO Address and not vice versa. CREATETABLEhas_address( CONSTRAINTc_haCONNECTION( AccountHolderTO Address) ONDELETENOACTION)ASEDGE Coming soon to Azure SQL Database!

27. Demo Ajay Jagannathan Fraud ring and financial risk detection using SQL Graph

28. Intelligent Query Processing The intelligent query processing feature family includes features with broad impact that improve the performance of existing workloads with minimal implementation effort

29. Demo Memory Grant Feedback Approximate Count Distinct SQL Graph Ajay Jagannathan

30. Agenda What’s new in Intelligent DB Introduction & look back What’s new in Reliability What’s new in Security What’s new in Platform Summary and resources

31. Announcements

32. Accelerated Database Recovery

33. Recovery time is proportional to the longest running transaction. Rolling back large batch operations (i.e. bulk insert) takes a long time. Transaction log cannot be truncated during a long running transaction. 60% of the customers that we surveyed, have experienced more than 1 hour of database unavailability due to long running recovery or long transaction rollback times in their production environments. Microsoft Confidential Top 3 customer pain points related to recovery & long running transactions

34. SQL Server Database Recovery (today) The process of reconciling the data files and the transaction log to bring a database to a consistent point in time. Recovery Phase / Transaction Log Phase 1: Analysis Determines the state of each transaction in the system at the time SQL Server stopped. Phase 1: Analysis Phase 2: Redo Phase 3: Undo Phase 2: Redo Returns the database to the state it was in at the time the SQL Server stopped [DB is PARTIALLY available after Redo] Phase 3 Undo Rolls back any uncommitted transaction(s) individually [DB is FULLY available after Undo] Log End Log Start Oldest uncommitted Transaction (XACT_BEGIN_LSN) Checkpoint (or oldest dirty page LSN)

35. SQL Server Database Recovery (with ADR) • Versioned-based SQL Server Recovery • Redo phase starts from the last successful checkpoint • Database becomes fullyavailable after the Redo phase • Fast and consistent Database Recovery (irrespective of the Tx size) • Instantaneous Transaction rollback • Aggressive Log Truncation ADR Components PVS Persisted Version Store sLog Special In-Memory Log Stream Cleaner Logical Revert

36. SQL Server Database Recovery (with ADR) PVS Persisted Version Store Logical Revert sLog Special Log Stream Cleaner Fast Database Recovery (irrespective of the Tx size) Instantaneous Transaction rollback Aggressive Log Truncation Phase 1: Analysis + Reconstructs sLog and copies log records for non-versioned ops Phase 1: Analysis Recovery Phase / Transaction Log Phase 2: Redo (2-Phase) - 2a: Redo from sLog - 2b: Redo from Tx Log (starts from last checkpoint) Phase 3: Undo from sLog Phase 2a: Redo from sLog Phase 2b: Redo [DB is FULLY available and alllocks are released after Redo] Phase 3 Undo - Instantaneous Undo with sLog Log End Log Start Oldest uncommitted Transaction (XACT_BEGIN_LSN) Checkpoint (or oldest dirty page LSN)

37. Demo Ajay Jagannathan Accelerated Database Recovery

38. Public Preview Released Mar ‘18 Promote greater fault resiliency with replicas across different availability zones Create multiple redundant replicas for each database within a region Use Azure portal, PowerShell or REST API for easy configuration Initially available in East US2, Central US, West Europe and France Central regions, with additional regions added over time https://azure.microsoft.com/en-us/blog/azure-sql-database-now-offers-zone-redundant-premium-databases-and-elastic-pools/ Zone Redundancy in SQL Database Premium tier databases and pools

39. Public Preview Released Apr ‘18 Benefit from additional capacity at no extra cost Load balance read-only workloads using capacity from read-only replicas instead of sharing the read-write replica Support for session-level consistency Enable read scale-out via PowerShell or REST API https://docs.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out Read scale-out support

40. Agenda What’s new in Intelligent DB Introduction & look back What’s new in Reliability What’s new in Security What’s new in Platform Summary and resources

41. Focus on your businessautopiloting your databases We take care of your database management

42. Azure SQL Database Breakout Sessions

43. References Intelligent query processing in Azure SQL Database https://aka.ms/intelligentQP Approximate Count Distinct enters Public Preview in Azure SQL Database https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/07/16/approximate-count-distinct-enters-public-preview-in-azure-sql-database/ Public Preview of Row Mode Memory Grant Feedback in Azure SQL Database https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/07/16/public-preview-of-row-mode-memory-grant-feedback-in-azure-sql-database/ Public Preview of Table Variable Deferred Compilation in Azure SQL Database https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/07/16/public-preview-of-table-variable-deferred-compilation-in-azure-sql-database/ • Learn more about Intelligent QP

44. Please evaluate this sessionYour feedback is important to us! Please evaluate this session through MyEvaluations on the mobile appor website. Download the app:https://aka.ms/ignite.mobileApp Go to the website: https://myignite.techcommunity.microsoft.com/evaluations

46. New flexibility and control over performance and storage configurations Independently scale compute and storage for more flexibility and control Easily match performance requirements of your existing on-premises workloads in the cloud Familiar vCore used as a measure of compute capacity Easy to understand, transparent pricing model https://azure.microsoft.com/en-us/pricing/details/sql-database/ New vCore-based purchasing model https://azure.microsoft.com/en-us/blog/a-flexible-new-way-to-purchase-azure-sql-database/

47. Azure hybrid benefit for sql server License trade-in values • Take an inventory of on-premises licenses to determine potential for conversion • Convert on-premises cores to vCores to maximize value of investments • 1 Standard license core = 1 General Purpose core • 1 Enterprise license core = 1 Business Critical core • 1 Enterprise license core = 4 General Purpose cores (virtualization benefit) SQL Server cores with SA license vCores on Azure SQL Database SQL ServerStandard Edition General purpose vCore Business critical vCore SQL Server Enterprise Edition 4x General purpose vCores

48. Azure is the most economical destination for SQL Workloads Save up to 55% with Azure Hybrid Benefit for SQL Server and up to 80% with Reserved Capacity • How does a customer qualify? • Customer must have on-premises SQL Server licenses with active Software Assurance • How does a customer activate this benefit? • Deploy a vCore-based SQL DB option and select Azure Hybrid Benefit for SQL Server as an option in the portal. • Buy reserved capacity in the Azure portal for 1 or 3 year period for one or multiple subscriptions • Learn more Up to 33% savings1 Total monthly cost of 8 vCore SQL DB Business Critical option Up to 80% savings2 Customer total cost With SQL Database reserved capacity License included Reserved Capacity With Azure Hybrid Benefit 1 Savings based on eight vCore SQL Database managed instance general purpose in West2 US region, running 730 hours per month. Savings are calculated from on demand full price (license included) against 3-year reserved capacity license Included. Actual savings may vary based on region, instance size, and performance tier. Prices as of May 2018, subject to change. 2 Savings based on eight vCore SQL Database managed instance business critical in West2 US region, running 730 hours per month. Savings are calculated from on demand full price (license included) against base rate with Azure Hybrid Benefit plus 3-year reserved capacity. Savings excludes Software Assurance cost for SQL Server Enterprise edition, which may vary based on EA agreement. Actual savings may vary based on region, instance size, and performance tier. Prices as of May 2018, subject to change.