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

@chadgreen

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

Titanium Sponsors

Platinum Sponsors

Gold Sponsors

@chadgreen

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

Insert title

Sed ut perspiciat unde

• mni iste natu error

volptatem acusantium rem aperiam.

Insert title

Sed ut perspiciat unde

• mni iste natu error

volptatem acusantium rem aperiam.

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.

@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
• 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

Single Databases

@chadgreen

Scalable performance and pools

• SQL Database allows you to define the amount of resources assigned
• 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

Elastic Pools

@chadgreen

Scalable performance and pools

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

Managed Instances

@chadgreen

Azure SQL Database Purchasing Models

• Bundled measure of compute,

storage, and IO resources

• Database Transaction Units

(DTUs)

• Elastic Database Transaction

Units (eDTUs)

• Simple, pre-configured

resource options

• Independently choose

compute and storage resources

• Azure Hybrid Benefit for SQL

Server

• Flexibility, control, and

transparency

DTU vCore

@chadgreen

What are DTUs

@chadgreen

DTU Service Tiers

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

• Service tiers differentiated by range of compute sizes with a fixed

amount of included storage, fixed retention periods, and fixed price

@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

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)

@chadgreen

vCore Service Tiers

General Purpose Business Critical Hyperscale Storage Uses remote storage Single Database: 5-Gb to 4-Tb Managed Instance: 32-Gb to 8-Tb Uses local SSD storage Single Database: 5-Gb to 4-Tb Managed Instance: 32-Gb to 8-Tb Uses local SSD Flexible autogrow of storage as needed up to 100-Tb I/O Throughput IOPS per vCore with 7,000 maximum IOPS 5,000 IOPS per core with 200,000 maximum IOPS Depend on workload Availability 1 replica, no read-scale replicas 3 replicas, 1 read-scale replica 1 read-write replica, plus 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

• Independently scale compute and storage resource, match on-

premise performance, and optimize prize

@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- Purpose service tier availability Premium and Business Critical 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

Microsoft Azure Compliance Offerings

@chadgreen

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++

Choose a Version of Azure SQL

SQL Server on VM

• Full Control
• Up to 99.95%

availability

• Full parity with on-

premise

• Fixed database

version

• Easy migration
• Private IP Address
• Ability to host apps on

same VM

Managed Instance

• Highly compatibility
• 99.99% availability
• Built-in backups,

patching, recovery

• Latest stable version
• Easy migration
• Private IP address
• Built-in advanced

intelligence and security

• Online change of

resource

Single Database

• Most commonly used

features

• 99.99% availability
• Built-in backups,

patching, recovery

• Latest stable version
• Ability to assign

necessary resources to individual databases

• Online change of

resources

@chadgreen

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 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

Thank You