introduction to sql database on azure
TRANSCRIPT
What is SQL Database?Introduction to SQL Database
Athens Feb 24, 2017
Pre
se
nte
r In
fo1982 I started working with computers
1988 I started my professional career in computers industry.
1996 I started working with SQL Server 6.0
1998 I earned my first certification at Microsoft as Microsoft Certified Solution Developer (3rd in Greece)
I started my career as Microsoft Certified Trainer(MCT) with more than 30.000 hours of training until now!
2010 I became for first time Microsoft MVP on Data Platform
I created the SQL School Greece www.sqlschool.gr
2012 I became MCT Regional Lead by Microsoft Learning Program.
2013 I was certified as MCSE : Data Platform& MCSE : Business Intelligence
2016 I was certified as MCSE: Data Management & Analytics
Antonios ChatzipavlisSQL Server Expert & Evangelist
MCT, MCSE, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA, ITIL-F
SQ
Ls
ch
oo
l.g
r
Μια πηγή ενημέρωσης για τον Microsoft SQL Server προς τους Έλληνες IT Professionals,
DBAs, Developers, Information Workers αλλά και απλούς χομπίστες που απλά τους αρέσει ο SQL Server.
@antoniosch / @sqlschool
fb/sqlschoolgr / fb/groups/sqlschool
yt/c/SqlschoolGr
SQL School Greece group
Follow us in socials
Help line
• Articles about SQL Server• SQL Server News• SQL Nights• Webcasts• Downloads• Resources
What we are doing here
Sign up for a free membership today at sqlpass.org.
Linked In: http://www.sqlpass.org/linkedin
Facebook: http://www.sqlpass.org/facebook
Twitter: @SQLPASS
PASS: http://www.sqlpass.org
Pre
sen
tatio
n C
on
ten
t
What is SQL Database on Azure?
SQL Database Service Tiers
Database Transaction Units
Azure SQL Database logical server
Management Tools for SQL Database
Securing your SQL Database
What is SQL Database on Azure?
It’s a relational database service in the Microsoft cloud based on the Microsoft SQL Server engine
It’s capable of handling mission-critical workloads.
It delivers predictable performance at Multiple service levels
Dynamic scalability with no downtime
Built-in business continuity
Data protection
All with near-zero administration
These capabilities allow you to focus on rapid app development
SQL Database supports existing SQL Server tools, libraries, and APIs
What is SQL Database on Azure?
SQL Database Features
Feature SQL Server Azure SQL Database
Active Geo-Replication Not supported - see AlwaysOn Availability Groups Supported
Always Encrypted Supported Supported
AlwaysOn Availability Groups Supported Not supported - See Active Geo-Replication
Attach a database Supported Not supported
Application roles Supported Supported
Auto scale Not supported Supported
Azure Active Directory Not supported Supported
Azure Data Factory Not supported - see SQL Server Integration Services (SSIS) Supported
Auditing Supported Supported
BACPAC file (export) Supported Supported
BACPAC file (import) Supported Supported
BACKUP and RESTORE statements Supported Not supported
Built-in functions Supported Most
Change data capture Supported Not supported
Change tracking Supported Supported
Collation statements Supported Supported
Columnstore indexes Supported Premium edition only
Common language runtime (CLR) Supported Not supported
Contained databases Supported Built- in
Contained users Supported Supported
Control of flow language keywords Supported Supported
Cross-database queries Supported Elastic queries
Cursors Supported Supported
Data compression Supported Supported
Database backups Exposed for users Built- in
Database mail Supported Not supported
Database mirroring Supported Not supported
Database configuration options Supported Supported
Data Quality Services (DQS) Supported Not supported
Database snapshots Supported Not supported
Data types Supported Supported
DBCC statements All Some
DDL statements Supported Most
DDL triggers Supported Database only
Distributed transactions MS DTC Limited intra-SQL Database scenarios only
DML statements Supported Most
DML triggers Supported Supported
DMVs All Some
elastic pools Not supported Supported
Elastic jobs Not supported - see SQL Server Agent Supported
Elastic queries Not supported - see Cross-database queries Supported
Event notifications Supported Supported
Expressions Supported Supported
Extended events Supported Some
Extended stored procedures Supported Not supported
File groups Supported Primary only
Filestream Supported Not supported
Full-text search Supported Not supported third-party word breakers
Functions Supported Most
In-memory optimization Supported Premium edition only
Jobs SQL Server Agent Supported
JSON data support Supported Supported
Language elements Supported Most
Linked servers Supported Not supported - see Elastic query
Log shipping Supported Not supported - see Active Geo-Replication
Management commands Supported Not supported
Master Data Services (MDS) Supported Not supported
Minimal logging in bulk import Supported Not supported
Modifying system data Supported Not supported
Online index operations Supported Transaction size limited by service tier
Operators Supported Most
Point in time database restore Supported Supported
Polybase Supported [Not supported]
Policy-based management Supported Not supported
Predicates Supported Most
Resource governor Supported Built- in
Restore database from backup Supported From built-in backups only
Row Level Security Supported Supported
Security statements Supported Some
Semantic search Supported Not supported
Sequence numbers Supported Supported
Service Broker Supported Not supported
Server configuration options Supported Not supported - see Database configuration options
Set statements Supported Most
Spatial Supported Supported
SQL Server Agent Supported Not supported - See Elastic jobs
SQL Server Analysis Services (SSAS) Supported Not supported - see Azure Analysis Services
SQL Server Integration Services (SSIS) Supported Not supported - see Azure Data Factory
SQL Server PowerShell Supported Supported
SQL Server Profiler Supported Not supported - see Extended events
SQL Server Replication Supported Transactional and snapshot replication subscriber only
SQL Server Reporting Services (SSRS) Supported Not supported
Stored procedures Supported Supported
System stored functions Supported Some
System stored procedures Supported Some
System tables Supported Some
System views Supported Some
Table Partitioning Supported Primary filegroup only
Temporary tables Local and global Local only
Temporal tables Supported Supported
Transaction statements Supported Supported
Variables Supported
Transparent data encryption (TDE) Supported Supported
Windows Server Failover clustering Supported Not supported - See Active Geo-Replication
XML indexes Supported Supported
XML statements Supported Supported
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features
Not supported features of SQL Server• Attach database
• Backup – Restore statements
• Change Data Capture
• CLR
• Database mail
• Database Mirroring
• Limited Distributed Transactions
• Extended Stored Procedures
• Only Primary Filegroup
• Filestream
• Linked Servers
• Log Shipping
• Online Index operations
• Semantic search
• Service Broker
• SQL Server Agent (Elastic Jobs)
SQL Database Service Tiers
Service Tiers
Basic
Standard
Premium
Service Tiers
Basic
Standard
Premium
ALL have an uptime
SLA of 99.99%
Service Tiers
Basic
Standard
Premium
Target workloads
Best suited for a small database, supporting typically one single active operation at a given time.Examples include databases used for development or testing, or small-scale infrequently used applications.
The go-to option for cloud applications with low to medium IO performance requirements, supporting multiple concurrent queries. Examples include workgroup or web applications.
Designed for high transactional volume with high IO performance requirements, supporting many concurrent users. Examples are databases supporting mission critical applications.
Single database service tiers and performance levels
Basic Standard Premium
S0 S1 S2 S3 P1 P2 P4 P6 P11 P15
Max DTUs 5 10 20 50 100 125 250 500 1000 1750 4000
Max database size in GB of data files (not log files)
2 250 250 250 250 500 500 500 500 1024 1024
Max in-memory OLTP storage in GB
- - - - - 1 2 4 8 14 32
Max concurrent workers 30 60 90 120 200 200 400 800 1600 2400 6400
Max concurrent logins 30 60 90 120 200 200 400 800 1600 2400 6400
Max concurrent sessions 300 600 900 1200 2400 30000 30000 30000 30000 30000 30000
Price / hour € 0,0057 0,0017 0,034 0,085 0,17 0,5271 1,0541 2,1083 4,2165 7,9355 18,14
Database Transaction Units
It’s a unit of measure of the resources that are guaranteed to be available to a single Azure SQL database at a specific performance level within a single database service tier.
It’s a blended measure of CPU, memory, and data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads.
What are Database Transaction Units (DTUs)?
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-benchmark-overview
If you are looking to migrate an existing on-premises or SQL Server virtual machine workload to Azure SQL Database, you can use the DTU Calculator to approximate the number of DTUs needed
For an existing Azure SQL Database workload, you can use SQL Database Query Performance Insight to understand your database resource consumption (DTUs) to get deeper insight into how to optimize your workload.
You can also use the sys.dm_db_ resource_stats DMV to get the resource consumption information for the last one hour.
Alternatively, the catalog view sys.resource_stats can also be queried to get the same data for the last 14 days, although at a lower fidelity of five-minute averages.
How can I determine the number of DTUs needed by my workload?
If your workload is hitting the limits in one of CPU/Data IO/Log IO limits, you continue to receive the resources at the maximum allowed level, but you are likely to see increased latencies for your queries.
These limits do not result in any errors, but rather a slowdown in the workload, unless the slowdown becomes so severe that queries start timing out.
If you are hitting limits of maximum allowed concurrent user sessions/requests (worker threads), you see explicit errors.
What happens when I hit my maximum DTUs?
Resource governance Azure SQL Database behaves as if the database is running on its own machine, isolated from other
databases. Resource governance emulates this behavior
If the aggregated resource utilization reaches the maximum available CPU, Memory, Log I/O, and Data I/O resources assigned to the database, resource governance queues queries in execution and assign resources to the queued queries as they free up
Enforcement of limits Resources other than CPU, Memory, Log I/O, and Data I/O are enforced by denying new requests
when limits are reached. Clients receive an error message depending on the limit that has been reached
Azure SQL Database resource limits
Azure SQL Database logical server
An Azure SQL Database logical server acts as a central administrative point for multiple databases
In SQL Database, a server is a logical construct that is distinct from a SQL Server instance that you may be familiar with in the on-premises world
Specifically, the SQL Database service makes no guarantees regarding location of the databases in relation to their logical servers, and exposes no instance-level access or features
Azure SQL Database logical server
Is created within an Azure subscription, but can be moved with its contained resources to another subscription
Is the parent resource for databases, elastic pools, and data warehouses
Provides a namespace for databases, elastic pools, data warehouses
Is a logical container with strong lifetime semantics - delete a server and it deletes the contained databases, elastic pools,
data warehouses
Participates in Azure role-based access control (RBAC); databases, elastic pools within a server inherit access rights from
the server
Is a high-order element of the identity of databases and elastic pools for Azure resource management purposes (see the
URL scheme for databases and pools)
Collocates resources in a region
Provides a connection endpoint for database access (.database.windows.net)
Provides access to metadata regarding contained resources via DMVs by connecting to a master database
Provides the scope for management policies that apply to its databases: logins, firewall, audit, threat detection, etc.
Is restricted by a quota within the parent subscription (six servers per subscription)
Provides the scope for database quota and DTU quota for the resources it contains (such as 45000 DTU in V12)
Is the versioning scope for capabilities enabled on contained resources (latest version is V12)
Server-level principal logins can manage all databases on a server
Can contain logins similar to those in instances of SQL Server on your premises that are granted access to one or more
databases on the server, and can be granted limited administrative rights
What is an Azure SQL Database logical server?
Authentication and authorization: SQL authentication
Azure Active Directory Authentication (with certain limitations)
You can connect and authenticate to Azure SQL databases through the server's master database or directly to a user database
Windows Authentication is not supported.
TDS Microsoft Azure SQL Database supports tabular data stream (TDS) protocol client version 7.3 or later.
TCP/IP Only TCP/IP connections are allowed.
SQL Database firewall To help protect your data, a SQL Database firewall prevents all access to your database server or its
databases until you specify which computers have permission
How do I connect and authenticate to an Azure SQL Database logical server?
Management Tools for SQL Database
Azure portal New SQL Database Query Editor
SQL Server Management Studio (SSMS)
SQL Server Data Tools (SSDT) in Visual Studio
PowerShell
Management Tools for SQL Database
Securing your SQL Database
Control Access Firewall and firewall rules
Authentication using SQL Authentication or Azure AD
Authorization by user account’s database role memberships and object-level permissions
Row-level security
Data masking
Proactive monitoring Auditing
Threat detection
Data masking
Compliance ISO/IEC, CSA/CCM, ITAR, CJIS, HIPAA, IRS 1075
Securing your SQL Database
Demo
SELECT KNOWLEDGE FROM SQL SERVER
Copyright © 2017 SQLschool.gr. All right reserved. PRESENTER MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION