SLIDE 1
Getting Started with Azure SQL Database
Pittsburg TechFest June 2, 2018
Getting Started with Azure SQL Database Pittsburg TechFest June 2, - - PowerPoint PPT Presentation
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
SLIDE 2
SLIDE 3
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
SLIDE 4
Options to Bring Your Database to the Cloud
SLIDE 5
Getting Started with Azure SQL Database
Microsoft’s Data Platform
page 05
- 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
SLIDE 6
Getting Started with Azure SQL Database
Microsoft’s Data Platform
page 06
SLIDE 7
Getting Started with Azure SQL Database
Why Azure SQL Database
page 07
Less expensive than on-premise/hosted Less database-related downtime Five-year ROI Increase in IT staff efficiencies Total five-year average discounted business benefits per Azure SQL Database Payback period
53.2% 46.7% 98.0% 18.7k 406% 8.6
Months
SLIDE 8
Getting Started with Azure SQL Database page 08
Azure SQL Database
New cloud-designed applications that have time constraints in development and marketing Teams that need built-in high availability, disaster recovery, and upgrade for the database Teams that do not want to manage the underlying operating system and configuration settings Databases of up to 4-TB
SQL Server on Azure VMs
Existing applications that require fast migration to the cloud with minimal change Teams that can configure and mange high availability, disaster recovery, and patching for SQL Server You need a customized environment with full administrative rights SQL Server instances with up to 64-TB
- f storage
Azure SQL Database versus SQL Server on Azure Virtual Machines
SLIDE 9
Getting Started with Azure SQL Database page 09
Azure SQL Database
New cloud-designed applications that have time constraints in development and marketing Teams that need built-in high availability, disaster recovery, and upgrade for the database Teams that do not want to manage the underlying operating system and configuration settings Databases of up to 4-TB
SQL Server on Azure VMs
Existing applications that require fast migration to the cloud with minimal change Teams that can configure and mange high availability, disaster recovery, and patching for SQL Server You need a customized environment with full administrative rights SQL Server instances with up to 64-TB
- f storage
Azure SQL Database versus SQL Server on Azure Virtual Machines
SLIDE 10
Getting Started with Azure SQL Database page 010
Azure SQL Database
- DaaS hosted in Azure cloud
that falls into SaaS and PaaS
- Develop directory on the
service using built-in features and functionality
- Pay-as-you-go
- Options to scale up or out
SQL Server on Azure VMs
- IaaS
- Built on standardized
hardware that is owned, hosted, and maintained by Microsoft
- Pay-as-you-go or bring your
- wn license
- Full administrative rights
Azure SQL Database versus SQL Server on Azure Virtual Machines
SLIDE 11
Getting Started with Azure SQL Database
SQL Server Features Not Supported
page 011
- Change Data Capture
- Common Language Runtime
- Cross-database queries (Elastic queries yes)
- Cross-database transactions
- Database mail
- Database mirroring
- Data Quality Services
- Database snapshots
- Most DBCC statements supported
- Most DDL statements supported
- Only Database DDL Triggers
- Distributed partition views
- Distributed transactions (Elastic transactions
yes)
- Most DML triggers supported
- Most DMVs supported
- Event notifications
- Some extended events
- Extended stored procedures
- Filestream
- Most functions supported
- Master Data Services
- Minimal logging in bulk import
- Modifying system data
- OPENDATASOURCE
- OPENQUERY
- OPENROWSET
- Polybase
- Policy-based management
- Resource governor
- RESTORE statements
- Sematic search
- Service Broker
- Server configuration settings
- SQL Server Agent
- SQL Server Analysis Services (SSAS)
- SQL Server Auditing
- SQL Server Profiler
- SQL Sever Reporting Services (SSRS)
- Only some system stored procedures
supported
- Only some system catalog views supported
- Trace flags
SLIDE 12
What is Azure SQL Database
SLIDE 13
Getting Started with SQL Azure Database
What is SQL Azure Database
page 013
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!
SLIDE 14
Scalable Performance and Pools
SLIDE 15
Getting Started with Azure SQL Database
Scalable Performance and Pools
page 015
- Adjust performance and scale without downtime
SLIDE 16
Getting Started with Azure SQL Database
Scalable Performance and Pools
page 016
- Elastic Pools to maximize resource utilization
SLIDE 17
Getting Started with Azure SQL Database
Scalable Performance and Pools
page 017
- Blend single databases with pooled databases
SLIDE 18
Getting Started with Azure SQL Database
Scalable Performance and Pools
page 018
- Extensive monitoring and alerting capabilities
SLIDE 19
Built-In Intelligence
SLIDE 20
Getting Started with Azure SQL Database
Built-In Intelligence
page 020
- Automatic performance monitoring and tuning
- Automatic Index Management
- Automatic Plan Correction
SLIDE 21
Getting Started with Azure SQL Database
Built-In Intelligence
page 021
- Automatic performance monitoring and tuning
- Automatic Index Management
- Automatic Plan Correction
- Adaptive Query Processing
SLIDE 22
Getting Started with Azure SQL Database
Built-In Intelligence
page 022
- Automatic performance monitoring and tuning
- Automatic Index Management
- Automatic Plan Correction
- Adaptive Query Processing
- Intelligent Threat Detection
SLIDE 23
Advanced Security and Compliance
SLIDE 24
Getting Started with Azure SQL Database
Advanced Security and Compliance
page 024
- Auditing for compliance and security
SLIDE 25
Getting Started with Azure SQL Database
Advanced Security and Compliance
page 025
- Auditing for compliance and security
- Data encryption at rest
SLIDE 26
Getting Started with Azure SQL Database
Advanced Security and Compliance
page 026
- Auditing for compliance and security
- Data encryption at rest
- Data encryption in motion
SLIDE 27
Getting Started with Azure SQL Database
Advanced Security and Compliance
page 027
- Auditing for compliance and security
- Data encryption at rest
- Data encryption in motion
- Dynamic data masking
SLIDE 28
Getting Started with Azure SQL Database
Advanced Security and Compliance
page 028
- Auditing for compliance and security
- Data encryption at rest
- Data encryption in motion
- Dynamic data masking
- Row-level security (RLS)
SLIDE 29
Getting Started with Azure SQL Database
Advanced Security and Compliance
page 029
- 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
SLIDE 30
Getting Started with Azure SQL Database
Advanced Security and Compliance
page 030
- 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
SLIDE 31
Getting Started with Azure SQL Database
Microsoft Azure Compliance Offerings
page 031
SLIDE 32
Pricing Models
SLIDE 33
Getting Started with Azure SQL Database page 033
DTU
- Based upon bundled
measure of compute, storage, and IO resources
- Performance levels
expressed in terms of Database Transaction Units (DTUs) and elastic Database Transaction Units (eDTUs)
- Best for customers who
want simple, pre-configured resource options vCore
- Allows Independently scale
compute and storage – up to 80 vCores, 4-Tb of data storage, and 200,000 IOPS
- Allows you to use Azure
Hybrid Benefit for SQL Server to gain cost savings
- Best for customers who
value flexibility, control, and transparency
Azure SQL Database Pricing Models
(Black Box) (Transparent Box)
SLIDE 34
Getting Started with Azure SQL Database
What are Database Transaction Units (DTUs)
page 034
SLIDE 35
Getting Started with Azure SQL Database
What are Database Transaction Units (DTUs)
page 035
SLIDE 36
Getting Started with Azure SQL Database
What are elastic Database Transaction Units (eDTUs)
page 036
SLIDE 37
Getting Started with Azure SQL Database
How can I determine the number of DTUs needed by my workload
page 037
- DTU Calculator – dtucalculator.azurewebsites.net
- SQL Database Query Performance Insight
- sys.dm_db_resource_stats
- sys.resource_stats
SLIDE 38
Getting Started with Azure SQL Database
More details about vCore-based purchasing model
page 038
- 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 vCores + generation of hardware)
- Type and amount of data and log storage
- Number of IOs
- Backup storage (RA-GRS)
SLIDE 39
Demos
SLIDE 40
Getting Started with Azure SQL Database
Demos
page 040
- Create an Azure SQL Database
- Add records to the database
- Use Azure SQL Query
- Dynamic Data Masking
- Geo-Replication
- Apply Performance Recommendations
- Enable Automatic Tuning
SLIDE 41
Create an Azure SQL Database
SLIDE 42
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 042
SLIDE 43
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 043
SLIDE 44
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 044
SLIDE 45
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 045
SLIDE 46
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 046
SLIDE 47
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 047
SLIDE 48
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 048
SLIDE 49
Getting Started with Azure SQL Database
Create an Azure SQL Database
page 049
SLIDE 50
Add records to the database
SLIDE 51
Getting Started with Azure SQL Database
Add Records to the Database
page 051
SLIDE 52
Getting Started with Azure SQL Database
Add Records to the Database
page 052
SLIDE 53
Getting Started with Azure SQL Database
Add Records to the Database
page 053
SLIDE 54
Getting Started with Azure SQL Database
Add Records to the Database
page 054
SLIDE 55
Getting Started with Azure SQL Database
Add Records to the Database
page 055
SLIDE 56
Getting Started with Azure SQL Database
Add Records to the Database
page 056
SLIDE 57
Getting Started with Azure SQL Database
Add Records to the Database
page 057
SLIDE 58
Getting Started with Azure SQL Database
Add Records to the Database
page 058
SLIDE 59
Getting Started with Azure SQL Database
Add Records to the Database
page 059
SLIDE 60
Use Azure SQL Query
SLIDE 61
Getting Started with Azure SQL Database
Use Azure SQL Query
page 061
SLIDE 62
Getting Started with Azure SQL Database
Use Azure SQL Query
page 062
SLIDE 63
Getting Started with Azure SQL Database
Use Azure SQL Query
page 063
SLIDE 64
Getting Started with Azure SQL Database
Use Azure SQL Query
page 064
SLIDE 65
Getting Started with Azure SQL Database
Use Azure SQL Query
page 065
SLIDE 66
Dynamic Data Masking
SLIDE 67
Getting Started with Azure SQL Database
Dynamic Data Masking
page 067
SLIDE 68
Getting Started with Azure SQL Database
Dynamic Data Masking
page 068
SLIDE 69
Getting Started with Azure SQL Database
Dynamic Data Masking
page 069
SLIDE 70
Getting Started with Azure SQL Database
Dynamic Data Masking
page 070
SLIDE 71
Getting Started with Azure SQL Database
Dynamic Data Masking
page 071
SLIDE 72
Geo-Replication
SLIDE 73
Getting Started with Azure SQL Database
Geo-Replication
page 073
SLIDE 74
Getting Started with Azure SQL Database
Geo-Replication
page 074
SLIDE 75
Getting Started with Azure SQL Database
Geo-Replication
page 075
SLIDE 76
Getting Started with Azure SQL Database
Geo-Replication
page 076
SLIDE 77
Getting Started with Azure SQL Database
Geo-Replication
page 077
SLIDE 78
Getting Started with Azure SQL Database
Geo-Replication
page 078
SLIDE 79
Getting Started with Azure SQL Database
Geo-Replication
page 079
SLIDE 80
Getting Started with Azure SQL Database
Geo-Replication
page 080
SLIDE 81
Getting Started with Azure SQL Database
Geo-Replication
page 081
SLIDE 82
Getting Started with Azure SQL Database
Geo-Replication
page 082
SLIDE 83
Getting Started with Azure SQL Database
Geo-Replication
page 083
SLIDE 84
Getting Started with Azure SQL Database
Geo-Replication
page 084
SLIDE 85
Getting Started with Azure SQL Database
Geo-Replication
page 085
SLIDE 86
Getting Started with Azure SQL Database
Geo-Replication
page 086
SLIDE 87
chadgreen@chadgreen.com chadgreen.com @ChadGreen ChadwickEGreen bit.ly/PTFSQLD
SLIDE 88