Slide 1: Change the Rules
Lap around SQL Azure
Anko Duizer
Slide 2: Change the Rules
Introduction
• Anko Duizer
– Chief Software Architect – Blog: http://www.ankoduizer.nl – Twitter: ankoduizer – – – – Lead generation > 5000 sites World wide Microsoft platform
• TellUs
Slide 3: Change the Rules
INTRODUCTION
Slide 4: Change the Rules
Data platform: device to cloud
RDBMS
Service Type Place
Sync Load Mining Caching Protection Query Analysis Integration Backup
Reporting Search
In Memory
Multi Dim
Relational BLOB
XML
File
Mobile/Desktop
Desktop
Server
Mega-server
Data Services
Slide 5: Change the Rules
Reporting
Business Analytics
Data sync
•
The power of the SQL Data Platform in the cloud
•
•
Leverages existing skills and rich tooling ecosystem
Enables database applications with new, “cloud” capabilities
Slide 6: Change the Rules
Cloud application
Data in the cloud
On-premises application
Slide 7: Change the Rules
Value Props:
Dedicated Resources
Value Props:
On-premise
Hosted
Value Props:
SQL Azure
Roll-your-own HA/scale
Shared Low
Control
High
Slide 8: Change the Rules
SQL AZURE
Slide 9: Change the Rules
SQL Azure
• Mangebility • Scalability • Developer empowerment
Slide 10: Change the Rules
Application
REST Client
Browser application
Application
SQL Client*
ODBC, OLEDB, ADO.Net PHP, Ruby, …
Cloud
HTTP+REST HTTP
Windows Azure Web App REST (Astoria)
Cloud
ADO.Net + EF
SQL Client*
TDS + TSQL Model
SQL Azure
TDS
Slide 11: Change the Rules
Account
Server=server1.data.database.windows.net Database=testDB Login=nigele[@server1] (maps to testuser)
Server
Database
Slide 12: Change the Rules
Logical vs. Physical administration
• SQL Azure focus on logical administration
– Schema creation and management – Query optimization – Security management (Logins, Users, Roles)
• Service handles physical management
– Automatically replicated with HA “out of box” – Transparent failover in case of failure
Slide 13: Change the Rules
Architecture
Provisioning (databases, accounts, roles, …, metering, and billing
Machine 4
Machine 5
SQL Instance
User DB4 User DB1 User DB2
SQL Instance
User DB1 User DB2
Machine 6 SQL Instance
User DB4
SQL DB
User DB3
SQL DB
User DB3
SQL DB
User DB1 User DB2 User DB3 User DB4
Scalability and Availability: Fabric, Failover, Replication, and scalability and availability: fabric, failover, replication, and load balancing Load balancing
Slide 14: Change the Rules
Deployment
• Support for basic deployment options
– SQL scripts work (but not attach database)
• Geo-location of Windows Azure compute and SQL Azure databases • Cloud or on-premise is a deployment time choice
Slide 15: Change the Rules
SQL Azure deployment
Web Portal (API)
DB Script
SQL Azure TDS
Slide 16: Change the Rules
SQL Azure - Accessing databases
Web Portal (API)
Your App
SQL Azure TDS
Change connection string
Slide 17: Change the Rules
Database replicas
Replica 1
DB
Replica 2
Replica 3
Slide 18: Change the Rules
Database monitoring & recovery
Web Portal (API)
Your App
SQL Azure TDS
!
Slide 19: Change the Rules
Failure Scenario
Database Backups
Solution
Multiple replicas of all databases with automatic failover. System backups for additional protection Use BCP, SSIS or other custom tools to backup data Database clone to create on-demand, backups of databases in the local or remote SQL Azure region
When
Today
Responsible Party
SQL Azure - Built-in to the base service
X
HW and Media failures
Today
Customer
Customer application or administrator errors leading to data loss
• Cloning is done with transactional consistency • When clone operation is complete the replica is accessible as a normal database
Continuous backup option with point-in-time restore capabilities • Configurable retention and lag period • Backups available for read operations • Local or remote region
1H 2010
Customer
1.
2H 2010
Customer enables and configures it. Service will maintain it
2.
Slide 20: Database clone
xyz.database.windows.net
Master prod1 Prod2 prod2 clone prod2 clone
Change the Rules
efg.database.windows.net
DR1 Master
abc.database.windows.net
Master Dev1 prod2 clone
South central US
North central US
CREATE DATABASE efg.prod2clone abc.prod2clone xyz.prod2clone
AS CLONE OF xyz.prod2
Slide 21: Change the Rules
PROGRAMMING SQL AZURE
Slide 22: Change the Rules
Programming model
• Small data sets
– Use a single database – Same model as on premise SQL Server
• Large data sets and/or massive throughput
– Partition data across many databases – Use parallel fan-out queries to fetch the data – Application code must be partition aware in v1
Slide 23: Change the Rules
Connection model
• Use existing client libraries
– ADO.NET, ODBC, PHP
• Client libraries pre-installed in Azure roles • Support for ASP.NET controls • Clients connect directly to a database
Slide 24: Change the Rules
Compatibility goals
• • • • Support common application patterns Logical/policy based administration Patterns work from SQL Server to SQL Azure Multi-tenancy considerations
– Throttling – Limits on DB size, transaction duration, …
Slide 25: Change the Rules
v1 • Tables, indexes and views • Stored procedures • Triggers • Constraints • Table variables, session temp tables (#t)
Sample of SQL compatibility
vNext • Distributed transactions • Distributed query • CLR • Service broker • Spatial • Physical server or catalog DDL and views
Slide 26: Change the Rules
Security model
• Uses regular SQL security model
– Authenticate logins, map to users and roles – Authorize users and roles to SQL objects
• Limited to standard SQL Auth logins
– Username + password
• Future AD Federation, WLID, etc as alternate authentication protocols
Slide 27: Change the Rules
FUTURE
Slide 28: Change the Rules
Planned
• • • • Partitioned databases Spatial data Security AD, WLID, etc Increased database size
Slide 29: Change the Rules
Requests…
• • • • • Add support for profiler, DMVs … Full-text search support Change tracking support Support for CLR Cloud based Business Intelligence (BI) solutions
Slide 30: Change the Rules
USEFUL?
Slide 31: Change the Rules
Scenarios for V1
• • • • Departmental applications Web Applications ISV
– Simple application built by individual or department – Need simple deployment, self-management, IT: “Empowerment and governance” – Small business or startup that uses the cloud as their IT – Simple deployment, self-management, scale on demand – ISV hosting software on behalf of customer – Multi-tenant support for billing and isolation – Sharing and aggregating of data across tiers and across enterprises – Centralized place for data, high scale, sync with existing data sources
Data hub
Slide 32: Change the Rules
Application/ Browser
App Code
Azure
Slide 33: Change the Rules
Application/ Browser
How much data is transfered?
App Code
On premise
Azure
Slide 34: Change the Rules
Source
SSIS
On premise
backup
Azure
Slide 35: Change the Rules
Application/ Browser
App Code
X
Y
Z
Azure
Slide 36: Change the Rules
Sync framework
Source
Azure
Data hub
Sync framework
On-premise/ department
Source
Laptop
Slide 37: Change the Rules
NOT USEFUL!
Slide 38: Change the Rules
Application/ Browser
Too much data transferred
App Code
On premise
Azure
Slide 39: Change the Rules
SSIS
On premise
Source
destination
Azure
Azure
Slide 40: Change the Rules
Is it cheaper?
Slide 41: Change the Rules
• • •
1GB Database $9.99 / month Bandwidth
– $0.10 /GB inbound – $0.15 /GB outbound
• • •
10GB Database $99.99 / month Bandwidth
– $0.10 /GB inbound – $0.15 /GB outbound
Slide 42: Change the Rules
Question?
