Transcript Azure SQL Database: Under the Hood





Dedicated
Scale-up
Full h/w control
Roll-your-own HA/DR/scale
Premium, up to 500GB
SQL Server
Raw iron
Resources
100% of API, Virtualized
Roll-your-own HA/DR/scale
SQL Server in IaaS
Virtualized Machine
Web/Business, up to 150GB
Auto HA, Fault-Tolerance
Friction-free scale
Self-provisioning, mgmt @ scale
SQL Database - PaaS
Virtualized Database
Shared
High
Friction & Control
Low
•
•
•
High-Availability
Single Logical
Database
Multiple synchronous replicas
Transparent automatic failover
DB
Ack
Value Read
Write
P
Ack
S
Write
Ack
Write
S
•
Reads are completed at the primary
•
Writes are replicated to secondary replicas
Self-Service Restore
SQL Database
Backups
sabcp01bl21
Programmatic “oops recovery” of
data deletion or alteration
Point-in-time Restore
Restore points available up to 35 days back
Creates a side-by-side database copy, non-disruptive
REST API, PowerShell or Azure Portal
Available in Premium edition
Georeplicated
Restore from
backup
Azure Storage
sabcp01bl21
Active Geo-Replication
Mission-critical business continuity on
your terms
Self-service activation
Create up to 4 readable secondary replicas
Replicate to any Azure region
Automatic data replication, asynchronous
REST API, PowerShell or Azure Portal
Available in Premium edition
GeoReplication
Monitoring
and Control
Task
API
Details
Start Continuous Copy
PowerShell
REST
Optional RPO setting
Stop Continuous Copy
PowerShell
REST
Forced or friendly termination
Get Status
PowerShell
REST
T-SQL
Retrieves DMV
DMV
Values
Visibility
is _interlink connected
Yes
No
sys.dm_database_copies
Database state
ONLINE
COPYING
sys.databases
Replication state
SEEDING
CATCH_UP
sys.dm_database_copies
Replication lag
Seconds
sys.dm_continuous_copy_status
Last_replication
Timestamp
sys.dm_continuous_copy_status
Master
MSDB
Instance Collation
Logins
Credentials
Linked Server Defs.
CLR
…
Agent
Replication
DB Mail
…
…
TempDB Collation
Other Apps
TempDB
User DB
DB
User
Master
Other DBs
User DB
Temp
MSDB



DMV
Details
Use
sys.dm_exec_query_stats
Cumulative view of query statistics
Total and average resource
consumption
sys.dm_exec_query_sql_text
Returns the text of the SQL batch that is identified by the
specified sql_handle
Provide overall batch text for
statement
sys.dm_exec_query_plan
Returns plan in XML for specified plan handle
Provide plan for tuning and analysis
sys.dm_exec_requests
Current requests executing on your DB
Check for blocking, contention
related issues, convoys, etc
 Execute in isolation with STATISTICS (IO/TIME) ON
1
2
3
5
4
X
?
X
1.
2.
Request is
executed
on the Server
has
not yet reached
the server
 Retry for reads and
is safe
writes is safe

for writes
is NOTthe
safeserver
TheRetry
request
has reached
 Retry for reads is safe
 Retry for writes is NOT safe
1.
2.
9.
Read
Write
Just re-execute
Requires tracking of
transaction ID
Requires outer transaction
Low cost operation
High cost operation
A general system wide backoff strategy is typically a
good idea
Query dependent back-off
strategy to avoid
overloading the system
Get all @ once
Batch updates
SELECT…
SELECT…
SELECT…
NN
One client server
roundtrip per execution
SELECT…
N  <N
XML
Delimited list
Table Valued Parameter
All executions in one
batch
Roundtrip per
execution
Fully streaming
Easy to use
Performance is good
No SQL Injection
Can be strongly typed
No SQL Injection
Nice option if your
data is already XML!
Great flexibility
Strongly typed
No SQL Injection
Performance is great!
Easy to use
Allows for some level
of streaming
Poor performance
Potential for SQL
Injection attacks
Requires SQLCLR
Data is not strongly
typed
Cumbersome
implementation
Can be simplified by
created one TVF per
“list type”
Not strongly typed by
default
Performance is ok but
not the best
Less cumbersome
than the delimited list
but still somewhat
cumbersome
Less flexible than XML
Allows for streaming,
but only to the server


Customer
Customer
Customer
Customer
Customer
App
App
App
App
App
DB
DB
DB
DB
DB




SaaS
Customer
DB1
Customer
DB2
Customer
DB3
…
Customer
DB N
Data Model Sharding



Sharded Model





1


Directory
1.

2.
3.
2
Shard 1
Shard 2
3


Usage
Pattern
Small but
growing set of
highly active
users


Long Tail of Colder
Databases CSV Goal: COGS
Reduction
Distribution of CSV Tenants

11
Report
Program


3,6,9 Temp
5
2

10
8





1
4
Shard 1 Shard 2
7
Shard 3
Storage
 http://msdn.microsoft.com/library/azure/jj879332.aspx
 http://msdn.microsoft.com/library/azure/jj156164.aspx
 http://msdn.microsoft.com/en-us/library/azure/dn248436.aspx