Transcript Introduction to SQL Azure - Johns Hopkins University

Windows Azure Platform
Training Workshop
Introduction to SQL Azure
Name
Title
Organization
Email
3
Data Platform: Device to Cloud
RDBMS
Service
Protection
Mining
Sync
Load
Caching
Analysis Query
Integration
Backup
Reporting
Search
Model & policy based development and management
Type
Place
4
In
Memory
Multi
Dim
Relational
BLOB
XML
File
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
SQL Azure Database
Highly scaled out relational database as a service
Relational database service
Browser
• SQL Server technology foundation
SOAP/REST
HTTP/S
• Highly symmetrical
ADO.NET/REST - EDM
HTTP/S
• Highly scaled
• Highly secure
App Code
(ASP.NET)
Windows Azure
Compute
T-SQL (TDS)
SQL Azure
Database
SQL Server Report Server
(on-premises)
T-SQL (TDS)
MS
Datacenter
- AD Federation (LiveId /AppFabric AC)
Database “as a Service” – beyond hosting
Customer Value Props
 Self-provisioning and capacity on demand
 Symmetry w/ on-premises database platform
 Automatic high-availability and fault-tolerance
 Automated DB maintenance (infrastructure)
 Simple, flexible pricing – “pay as you grow”
The Evolution of SQL Azure
REST Client
Application
Browser
Application
REST Client
SQL Client*
REST Client
REST/SOAP + ACE Model
OLD SDS
HTTP
HTTP+REST
Windows Azure
Data Center
Web App
Evolves
Data Center
Windows Azure
7
Application
Cloud
HTTP
HTTP+REST
Cloud
Browser
Application
TDS
Application
REST (Astoria)
Web App
ADO.Net + EF
SQL Client*
TDS + TSQL Model
SQL Azure
* Client access enabled using TDS for ODBC,
ADO.Net, OLEDB, PHP-SQL, Ruby, …
ODBC, OLEDB,
ADO.Net PHP,
Ruby, …
Database Choices
Value Props:
Value Props:
Roll-your-own HA/DR/scale
Dedicated
Resources
On-premise
Value Props:
Hosted
SQL Azure (RDBMS)
Shared
Low
8
“Friction”/Control
High
Scenarios for V1
Departmental Applications
Simple application built by individual or department
Need simple deployment, self-management, IT: “Empowerment and
Governance”
Web Applications
Small business or startup that uses the cloud as their IT
Simple deployment, self-management, scale on demand
ISV
ISV hosting software on behalf of customer
Multi-tenant support for billing and isolation
Data Hub (Shortly After V1)
Sharing and aggregating of data across tiers and across enterprises
Centralized place for data, high scale, sync with existing data sources
9
V1 Application Topologies
SQL Azure access from within MS Datacenter
SQL Azure access from outside MS Datacenter
(Azure compute – ADO.NET)
(On-premises – ADO.NET)
Application/
Browser
SOAP/REST
HTTP/S
App Code
/ Tools
ADO.NET Data Svcs/REST - EF
HTTP/S
App Code
(ASP.NET)
Windows Azure
T-SQL (TDS)
T-SQL (TDS)
SQL Data
Services
MS
Datacenter
Code Near
10
SQL Data
Services
MS
Datacenter
Code Far
Service Provisioning Model
Each account has zero or more servers
Account
Azure wide, provisioned in a common portal
Billing instrument
Each server has one or more databases
Server
Contains metadata about the databases and usage
Unit of authentication
Unit of Geo-location
Generated DNS based name
Each database has standard SQL objects
Database
11
Unit of consistency
Unit of multi-tenancy
Contains Users, Tables, Views, Indices, etc.
Most granular unit of billing
Architecture
Shared infrastructure at SQL database and below
Request routing, security and isolation
Scalable HA technology provides the glue
Automatic replication and failover
Provisioning, metering and billing infrastructure
SDS Provisioning (databases, accounts, roles, …, Metering, and Billing
Machine 4
SQL Instance
User
DB1
SQL DB
User
DB2
User
DB3
User
DB4
User
DB1
Machine 5
Machine 6
SQL Instance
SQL Instance
SQL DB
User
DB2
User
DB3
User
DB4
User
DB1
SQL DB
User
DB2
User
DB3
User
DB4
Scalability
andFailover,
Availability:Replication,
Fabric, Failover,and
Replication,
and Load balancing
Scalability and Availability:
Fabric,
Load balancing
12
SQL Azure
Deployment
Web Portal
(API)
DB
Script
13
SQL Azure
TDS
SQL Azure
Accessing databases
Web Portal
(API)
Your
App
SQL Azure
TDS
Change Connection String
14
Database Replicas
Replica 1
DB
Replica 2
Replica 3
15
Shared Environment
A
B
Hardware Boundary
B
Hardware Boundary
16
C
D
Hardware Boundary
C
C
A
D
A
B
D
Hardware Boundary
SQL Azure
Database Monitoring & Recovery
Web Portal
(API)
Your
App
17
SQL Azure
TDS
!
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
For v1 will publish best practices for scale out
Post-v1 we are looking at building an abstraction to hide
some of the complexities of partitioning
Sharding Databases
1 x 10GB database
1 Instances
10 x 1GB databases
10 Instances
19
SQL Tooling & Development
20
Compatibility Goals
Support common application patterns
Logical/policy based administration
Patterns work from SQL Server to SQL Azure
Multi-tenancy considerations
Throttling and load balancing
Limits on DB size, transaction duration, …
V1: Address the needs of the majority of web and departmental application
21
Sample of SQL Compatibility
In Scope for v1
Tables, indexes and views
Stored Procedures
Triggers
Constraints
Table variables,
session temp tables (#t)
…
22
Out of Scope for v1
Distributed Transactions
Distributed Query
CLR
Service Broker
Spatial
Physical server or catalog
DDL and views
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
Cannot hop across DBs (no USE)
23
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
Load balancing of data to ensure SLA
DBA role places more focus on logical management
24
Deployment
Support for basic deployment options
SQL scripts work (but not attach database)
Geo-location of Windows Azure compute and
SQL Azure Databases
Support for Application and multi-server
management model
Support for application packages
Cloud or on-premise is a deployment time choice
Visibility of data across on-premise and the cloud
Support existing and new forms of deployment
25
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
Security model is 100% compatible with on-premise SQL
26
Pricing
Web Edition
1 GB Database
$9.99 / month
Bandwidth
$0.10 /GB inbound
$0.15 /GB outbound
Business Edition
10GB Database
$99.99 / month
Bandwidth
$0.10 /GB inbound
$0.15 /GB outbound
Specified by MAXSIZE on CREATE DATABASE command or portal
Can NOT switch betweenen Web and Business Editions
Monthly billing period
27
Platform Readiness (EHA)
Exchange Hosted Archive (EHA) is high scale
archival service
Runs on SQL Azure infrastructure
In production prior to SQL Azure v1
Rebuilt to address cost and scale issues
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000003098
Reduced COGS with increased customer and business benefit:
 Larger scale (e.g. larger customers ~20TB+ each)
 More self-managing (e.g. fault-tolerance)
28
 Better query performance for fan-out
 Faster provisioning of new customers
SQL Azure
Opportunities and Futures
Partitioned databases
Geo-location and geo-redundancy
Distributed query
Security w/AD, WLID, etc
Support for multiple levels of hardware
and software isolation
29
Release Plan
MIX ‘09
Mar 09
TechEd
Apr 09
May 09
WWPC
Jun 09
Internal
Adoption
Jul 09
PDC
Aug 09
SQL Azure Early
Adopters
Sep 09
Oct 09
SQL Azure
CTP
SQL Azure Early Adopters CTP – On-board invitation-only early adopters
SQL Azure CTP - Register for an invitation code
Commercial Availability with Windows Azure in 2009
Sign up for our CTP:
http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx
30
Nov 09
Commercial
Availability (V1)
PDC 2009
Ready for Go-Live
Global
Availability
Service
Update
31
•
•
•
•
South Central USA
North Central USA (Dec-2009)
Southeast Asia
North Europe
1/1/2010
Start Billing
2/1/2010
End Free Period
Key Takeaways
SQL Azure available – PDC ’09
Integrated part of the SQL Data Platform
Leverage you existing SQL Server knowledge and
tools
SQL Azure is the data platform of the
Windows Azure Platform
Want to Know More?
Windows Azure Platform
http://www.azure.com/
Windows Azure Platform Training Kit
http://www.microsoft.com/downloads/details.aspx?FamilyID=4
13E88F8-5966-4A83-B309-53B7B77EDF78&displaylang=en
MSDN Development Center
http://msdn.microsoft.com/en-us/sqlserver/dataservices
Team Blog
http://blogs.msdn.com/ssds
33
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
34