Getting Started with Azure SQL Database K C D C C h a d G r e e n - PowerPoint PPT Presentation

Getting Started with Azure SQL Database K C D C C h a d G r e e n J u l y 1 8 , 2 0 1 9 @chadgreen

Titanium Sponsors Platinum Sponsors Gold Sponsors

Who is Chad Green D i r e c t o r o f S o f t w a r e D e v e l o p m e n t S c h o l a r R x chadgreen@chadgreen.com chadgreen.com ChadGreen ChadwickEGreen @chadgreen

Microsoft’s Data Platform @chadgreen

Database as a Service (DaaS) Cloud computing service model that Insert title Insert title provides users with some form of access to a Sed ut perspiciat unde Sed ut perspiciat unde omni iste natu error omni iste natu error database without the need for setting up volptatem acusantium volptatem acusantium rem aperiam. rem aperiam. physical hardware, installing software. @chadgreen

What is Azure SQL 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! @chadgreen

What is Azure SQL Database • General-purpose relational database managed service • Highly-available and high-performance data storage • Based upon latest stable version of Microsoft SQL Server • Cloud-first strategy = new capabilities first • Two different purchasing models: VCore and DTU • Microsoft handles patching and updates • Delivers predictable performance with near-zero administration • Currently available at 38 data centers around the world Focus on rapid application development @chadgreen

Three Deployment Options @chadgreen

Deployment Options @chadgreen

Deployment Options Azure SQL Database Serverless @chadgreen

Scalable performance and pools • SQL Database allows you to define the amount of resources assigned Single Databases • Each database is isolated • Guaranteed amount of compute, memory, and storage • Dynamically scale up and down • Options of 1 to 80 vCores; 32-Gb to 4-Tb • Hyperscale enables scaling up to 100-Tb @chadgreen

Scalable performance and pools • SQL Database allows you to define the amount of resources assigned Elastic Pools • Assign resources to be shared in the pool • Create new databases or move existing databases • Maximize the use of resources and save money • Dynamically scale resources up and down @chadgreen

Scalable performance and pools • SQL Database allows you to define the amount of resources assigned Managed Instances • Instance is isolated from other instances with guaranteed resources • Instance databases share set of resources • Ability to dynamically scale up and down @chadgreen

Azure SQL Database Purchasing Models DTU vCore • • Bundled measure of compute, Independently choose storage, and IO resources compute and storage • Database Transaction Units resources • (DTUs) Azure Hybrid Benefit for SQL • Elastic Database Transaction Server • Units (eDTUs) Flexibility, control, and • Simple, pre-configured transparency resource options @chadgreen

What are DTUs @chadgreen

DTU Service Tiers • Service tiers differentiated by range of compute sizes with a fixed amount of included storage, fixed retention periods, and fixed price Basic Standard Premium Maximum Storage Size 2-Gb 1-Tb 4-Tb Maximum DTUs 5 3,000 4,000 CPU Low Low, Medium, High Medium, High IO Throughput 2.5 IOPS / DUT 2.5 IOS / DUT 48 IOPS / DTU IO Latency 5 ms (read), 10 ms (write) 5 ms (read), 10 ms (write) 2 ms (read/write) Backup retention 7 days 35 days 35 days @chadgreen

What are eDTUs Basic Standard Premium Maximum storage size per database 2-Gb 1-Tb 1-Tb Maximum storage size per pool 156-Gb 4-Tb 4-Tb Maximum eDTUs per database 5 3,000 4,000 Maximum eDTUs per pool 1,600 3,000 4,000 Maximum number of database per pool 500 500 100 @chadgreen

How Many DTUs Do I Need? • Migration • DTU Calculator – dtucalculator.azurewebsites.net • Existing Azure SQL Database workloads • SQL Database Query Performance Insight • sys.dm_db_resource_stats • sys.resource_stats

What about vCore? • Represents the logical CPU offered with an option to choose between generations of hardware • Flexibility, control, transparency of individual resource consumption, and straightforward way to translate on-premises workload requirements to the cloud • Allows you to scale compute, memory, and storage • Pay for • Compute (service tier + number of vCors + generation of hardware) • Type and amount of data and log storage • Number of Ios • Backup storage (RA-GRS)

vCore Service Tiers • Independently scale compute and storage resource, match on- premise performance, and optimize prize General Purpose Business Critical Hyperscale Storage Uses remote storage Uses local SSD storage Uses local SSD Single Database: Single Database: Flexible autogrow of 5-Gb to 4-Tb 5-Gb to 4-Tb storage as needed up to Managed Instance: Managed Instance: 100-Tb 32-Gb to 8-Tb 32-Gb to 8-Tb I/O Throughput IOPS per vCore with 7,000 5,000 IOPS per core with Depend on workload maximum IOPS 200,000 maximum IOPS Availability 1 replica, no read-scale 3 replicas, 1 read-scale 1 read-write replica, plus replicas replica 0-4 read-scale replicas Backups 7-35 days (default is 7) 7-35 days (default is 7) Snapshot-based backups IO Latency 5 ms (read), 10 ms (write) 5 ms (read), 10 ms (write) 2 ms (read/write) Backup retention 7 days 35 days 35 days @chadgreen

Extensive monitoring and alerting capabilities • Set of advanced monitoring and troubleshooting features give full insights into the workload characteristics • Features categorized as • Built-in monitoring – enable you to get real-time performance insights • PaaS monitoring capabilities – enable managing large number of database instances and provide troubleshoot advice @chadgreen

Extensive monitoring and alerting capabilities • Set of advanced monitoring and troubleshooting features give full insights into the workload characteristics • Query Store • Records performance of queries and enable you to identify potential performance issues • Automatic tuning recommendations @chadgreen

Built-In Availability Capabilities Basic, Standard, and General- Premium and Business Critical Purpose service tier availability service tier availability @chadgreen

Built-In Availability Capabilities • Automatic Backups • Point-in-Time Restore • Active Geo-Replication • Auto-Failover Groups • Zone-Redundant Databases @chadgreen

Built-In Intelligence • Dramatically reduce costs of running and managing databases • Maximizes both performance and security of your application • Automatic performance monitoring and tuning • Automatic Index Management • Automatic Plan Correction • Adaptive Query Processing @chadgreen

Advanced Security and Compliance • Range of built-in security and compliance features • Advanced Threat Protection • Data Discovery and Classification • Vulnerability Assessment • Threat Detection • Auditing for compliance and security • Data encryption • Azure Active Directly integration and multi-factor authentication • Compliance certification @chadgreen

Microsoft Azure Compliance Offerings

Plethora of Tools and Supported Application Stacks • Tools • Azure Portal • SQL Server Management Studio • Azure Data Studio • SQL Server Data Tools in Visual Studio • Visual Studio Code • Application Stacks • .NET, .NET Core, Python, Java, Node.js, PHP, Ruby, C++ @chadgreen

Choose a Version of Azure SQL SQL Server on VM Managed Instance Single Database • • • Full Control Highly compatibility Most commonly used • • Up to 99.95% 99.99% availability features • • availability Built-in backups, 99.99% availability • • Full parity with on- patching, recovery Built-in backups, • premise Latest stable version patching, recovery • • • Fixed database Easy migration Latest stable version • • version Private IP address Ability to assign • • Easy migration Built-in advanced necessary resources • Private IP Address intelligence and to individual • Ability to host apps on security databases • • same VM Online change of Online change of resource resources

Azure SQL Walkthrough G e t t i n g S t a r t e d w i t h A z u r e S Q L D a t a b a s e @chadgreen

Thank You c h a d g r e e n @ c h a d g r e e n . c o m c h a d g r e e n . c o m C h a d G r e e n C h a d w i c k E G r e e n @chadgreen