Getting Started with Azure SQL Database Pittsburg TechFest June 2, - PowerPoint PPT Presentation

Getting Started with Azure SQL Database Pittsburg TechFest June 2, 2018

Chad Green , Data & Solutions Architect ProgressiveHealth chadgreen@chadgreen.com chadgreen.com @ChadGreen ChadwickEGreen

Database as a Service (DaaS) Cloud computing service model that provides users with some form of access to a database without the need for setting up physical hardware, installing software

Options to Bring Your Database to the Cloud

Microsoft’s Data Platform • Four basic options for hosting SQL Server • SQL Server on physical machines • SQL Server in on-premise virtualized machines • SQL Server in Azure Virtual Machine • Azure SQL Database page Getting Started with Azure SQL Database 05

Microsoft’s Data Platform page Getting Started with Azure SQL Database 06

Why Azure SQL Database 53.2% 46.7% Less expensive than on-premise/hosted Increase in IT staff efficiencies 98.0% 18.7k Total five-year average discounted business Less database-related downtime benefits per Azure SQL Database 8.6 406% Five-year ROI Payback period Months page Getting Started with Azure SQL Database 07

Azure SQL Database versus SQL Server on Azure Virtual Machines Azure SQL Database SQL Server on Azure VMs Existing applications that require fast New cloud-designed applications that migration to the cloud with minimal have time constraints in development change and marketing Teams that need built-in high Teams that can configure and mange availability, disaster recovery, and high availability, disaster recovery, and upgrade for the database patching for SQL Server Teams that do not want to manage the You need a customized environment underlying operating system and with full administrative rights configuration settings SQL Server instances with up to 64-TB Databases of up to 4-TB of storage page Getting Started with Azure SQL Database 08

Azure SQL Database versus SQL Server on Azure Virtual Machines Azure SQL Database SQL Server on Azure VMs Existing applications that require fast New cloud-designed applications that migration to the cloud with minimal have time constraints in development change and marketing Teams that need built-in high Teams that can configure and mange availability, disaster recovery, and high availability, disaster recovery, and upgrade for the database patching for SQL Server Teams that do not want to manage the You need a customized environment underlying operating system and with full administrative rights configuration settings SQL Server instances with up to 64-TB Databases of up to 4-TB of storage page Getting Started with Azure SQL Database 09

Azure SQL Database versus SQL Server on Azure Virtual Machines Azure SQL Database SQL Server on Azure VMs • IaaS • DaaS hosted in Azure cloud that falls into SaaS and • Built on standardized PaaS hardware that is owned, hosted, and maintained by • Develop directory on the Microsoft service using built-in features and functionality • Pay-as-you-go or bring your own license • Pay-as-you-go • Full administrative rights • Options to scale up or out page Getting Started with Azure SQL Database 010

SQL Server Features Not Supported Change Data Capture Minimal logging in bulk import • • Common Language Runtime Modifying system data • • Cross-database queries (Elastic queries yes) OPENDATASOURCE • • Cross-database transactions OPENQUERY • • Database mail OPENROWSET • • Database mirroring Polybase • • Data Quality Services Policy-based management • • Database snapshots Resource governor • • Most DBCC statements supported RESTORE statements • • Most DDL statements supported Sematic search • • Only Database DDL Triggers Service Broker • • Distributed partition views Server configuration settings • • Distributed transactions (Elastic transactions SQL Server Agent • • yes) SQL Server Analysis Services (SSAS) • Most DML triggers supported SQL Server Auditing • • Most DMVs supported SQL Server Profiler • • Event notifications SQL Sever Reporting Services (SSRS) • • Some extended events Only some system stored procedures • • Extended stored procedures supported • Filestream Only some system catalog views supported • • Most functions supported Trace flags • • Master Data Services • page Getting Started with Azure SQL Database 011

What is Azure SQL Database

What is SQL Azure Database Azure SQL Database is a cloud- based relational database service, built on the Microsoft SQL Server engine designed to deliver predictable performance and scalability, with virtually no downtime and near-zero administration. SQL Server with out the administration headaches! page Getting Started with SQL Azure Database 013

Scalable Performance and Pools

Scalable Performance and Pools • Adjust performance and scale without downtime page Getting Started with Azure SQL Database 015

Scalable Performance and Pools • Elastic Pools to maximize resource utilization page Getting Started with Azure SQL Database 016

Scalable Performance and Pools • Blend single databases with pooled databases page Getting Started with Azure SQL Database 017

Scalable Performance and Pools • Extensive monitoring and alerting capabilities page Getting Started with Azure SQL Database 018

Built-In Intelligence

Built-In Intelligence • Automatic performance monitoring and tuning • Automatic Index Management • Automatic Plan Correction page Getting Started with Azure SQL Database 020

Built-In Intelligence • Automatic performance monitoring and tuning • Automatic Index Management • Automatic Plan Correction • Adaptive Query Processing page Getting Started with Azure SQL Database 021

Built-In Intelligence • Automatic performance monitoring and tuning • Automatic Index Management • Automatic Plan Correction • Adaptive Query Processing • Intelligent Threat Detection page Getting Started with Azure SQL Database 022

Advanced Security and Compliance

Advanced Security and Compliance • Auditing for compliance and security page Getting Started with Azure SQL Database 024

Advanced Security and Compliance • Auditing for compliance and security • Data encryption at rest page Getting Started with Azure SQL Database 025

Advanced Security and Compliance • Auditing for compliance and security • Data encryption at rest • Data encryption in motion page Getting Started with Azure SQL Database 026

Advanced Security and Compliance • Auditing for compliance and security • Data encryption at rest • Data encryption in motion • Dynamic data masking page Getting Started with Azure SQL Database 027

Advanced Security and Compliance • Auditing for compliance and security • Data encryption at rest • Data encryption in motion • Dynamic data masking • Row-level security (RLS) page Getting Started with Azure SQL Database 028

Advanced Security and Compliance • Auditing for compliance and security • Data encryption at rest • Data encryption in motion • Dynamic data masking • Row-level security (RLS) • Azure Active Directory integration and multi-factor authentication page Getting Started with Azure SQL Database 029

Advanced Security and Compliance • Auditing for compliance and security • Data encryption at rest • Data encryption in motion • Dynamic data masking • Row-level security (RLS) • Azure Active Directory integration and multi-factor authentication • Compliance certification page Getting Started with Azure SQL Database 030

Microsoft Azure Compliance Offerings page Getting Started with Azure SQL Database 031

Pricing Models

Azure SQL Database Pricing Models DTU (Black Box) vCore (Transparent Box) • Allows Independently scale • Based upon bundled compute and storage – up measure of compute, to 80 vCores, 4-Tb of data storage, and IO resources storage, and 200,000 IOPS • Performance levels • Allows you to use Azure expressed in terms of Hybrid Benefit for SQL Database Transaction Units Server to gain cost savings (DTUs) and elastic Database Transaction Units (eDTUs) • Best for customers who value flexibility, control, and • Best for customers who transparency want simple, pre-configured resource options page Getting Started with Azure SQL Database 033

What are Database Transaction Units (DTUs) page Getting Started with Azure SQL Database 034

What are Database Transaction Units (DTUs) page Getting Started with Azure SQL Database 035

What are elastic Database Transaction Units (eDTUs) page Getting Started with Azure SQL Database 036

How can I determine the number of DTUs needed by my workload • DTU Calculator – dtucalculator.azurewebsites.net • SQL Database Query Performance Insight • sys.dm_db_resource_stats • sys.resource_stats page Getting Started with Azure SQL Database 037