sql azure march 2010

Lynn Langithttp://blogs.msdn.com/SoCalDevGal

twitter - @llangit

Windows Azure Platform

Compute: Virtualized compute environment based on

Windows Server

Storage: Durable, scalable, & available storage

Management: Automated, model-driven management

Database: Relational processing for structured/unstructured data

Service Bus: General purpose application bus

Access Control: Rules-driven, claims-based access

control

Familiar SQL Server relational model Uses existing APIs & tools Built for the Cloud with availability and scale Accessible to all from .NET, PHP, Ruby, or Java

Microsoft SQL Azure

Clear Feedback: “I want a database in the Cloud”

Focus on combining the best features of SQL Server running at scale with low friction

Initial Services Database – Core SQL Server database capabilities

Future Services Data Sync – Enables the sync framework (soon after PDC)

Additional SQL Server capabilities available as a service: Business Intelligence and Reporting

New services: Reference Data and Secure Data Hub

Extending SQL Data Platform to Cloud

SQL Azure Database

Data SyncReference Data

Reporting

Business Intelligenc

e

Symmetric Programming Model

Data Hub Aggregation

Database ChoicesR

eso

urc

es

Dedicated

Shared

Low High“Friction”/Control

SQL Azure (RDBMS)

Value Props:

Auto HA, Fault-ToleranceFriction-free scaleSelf-provisioningHigh compatibility

Virtual DB serverResource governance @

LDB Security @ LDB

Hosted Hosted SQL Server or

other Resource governance @

VM Security @ DB Server/OS

On-premise SQL Server or other s/w on-

premise Resource governance @ machine Security @ DB Server/OS

Value Props:Full h/w control –

size/scale100% compatibilityRoll-your-own

HA/DR/scale

Value Props:

100% of API surface areaRoll-your-own HA/DR/scale

V1 Application Topologies

MSDatacenter

SOAP/RESTHTTP/S

SQL Azure access from within MS Datacenter

Windows Azure

T-SQL (TDS)

MSDatacenter

T-SQL (TDS)

App Code / Tools

SQL Azure access from outside MS Datacenter

App Code

(ASP.NET)

ADO.NET Data Svcs/REST - EFHTTP/S

Code Far

SQL Data Services

SQL Data Services

Application/ Browser

Code Near

Departmental Applications Web Applications ISV Data Hub (Shortly After V1)

Scenarios for V1

“It meets the need of Small Business customers that need a true RDBMS for mission critical applications. After many years of implementing enterprise RDBMS applications for customers with in-house infrastructure and talent, how can I in good conscience develop and implement mission critical applications for my small business customers and leave the OS and RDBMS maintenance to a company with no technical staff?”

“Because of the market segment that it addresses, there is no need for enterprise-like functionality, only high-availability and zero-maintenance.”

“It is very well priced. You get a true RDBMS for a fraction of typical in-house hosting costs. The fact that it is a shared resource does not diminish the feature-set, it only amplifies the need for a well architected and optimized design.”

Customer Quotes

Database Replicas

Replica 1

Replica 2

Replica 3

DB

Single Database Multiple Replicas

Single Primary

SLA Information

SQL Azure customers will have connectivity between the database and our Internet gateway.

SQL Azure will maintain a “Monthly Availability” of 99.9% during a calendar month. “\

Monthly Availability Percentage” for a specific customer database is the ratio of the time the database was available to customer to the total time in a month.

Time is measured in 5-minute intervals in a 30-day monthly cycle. Availability is always calculated for a full month. An interval is marked as unavailable if the customer’s attempts to connect to a database are rejected by the SQL Azure gateway.Download SQL Azure SLA.

SQL AzureDeployment

Web Portal(API)

SQL AzureTDS

DB Script

Support existing and new forms of deployment

Each account has zero or more servers Azure wide, provisioned in a common portal Billing instrument

Each server has one or more databases Contains metadata about the databases and usage Unit of authentication Unit of Geo-location Generated DNS based name

Each database has standard SQL objects Unit of consistency Unit of multi-tenancy Contains Users, Tables, Views, Indices, etc. Most granular unit of billing

Service Provisioning Model

Account

Server

Database

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

Logical vs. Physical Administration

DBA role places more focus on logical management

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

Security model is 100% compatible with on-premise SQL

SQL Azure portal - http://sql.azure.com

Demo – SQL Azure Portal

SQL AzureAccessing databases

Web Portal(API)

SQL AzureTDS

Your App

Change Connection String

Use ADO.NET, ODBC, PHP (NOT OLE DB) Client libraries pre-installed in Azure roles Support for ASP.NET controls Clients connect directly to a database

Cannot hop across DBs (no USE) May need to include <login>@<server> Use familiar tools (sqlcmd, osql, SSMS, etc) SSMS 2008 R2 CTP can connect

http://blogs.msdn.com/ssds/archive/2009/11/11/9921041.aspx

Connection Model

Connecting to SQL Azure with SSMS 2008 R2

SQL Azure Explorer for VS 2010 http://sqlazureexplorer.codeplex.com/

SQLCMD – can also be used SSIS – can also be used

Demo – Connect via tools

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

Maximum single database size is currently 10GB Database size calculation

Includes: primary replica data, objects and indexes Does NOT include: logs, master database, system

tables, server catalogs or additional replicas V1 does not support auto-partitioning or fan-out

queries Must handle partitioning logic within the

application

Special ConsiderationsDatabase Size

Sharding Databases

1 x 10GB database 1 Instances

10 x 1GB databases 10 Instances

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, …

Compatibility Goals

V1: Address the needs of the majority of web and departmental applications

Compatibility for V1

In Scope

• Tables, indexes and views• w/ clustered indices

• Stored Procedures• Triggers• Constraints• Table variables • Session temp tables

Out of Scope

• Distributed Transactions• Distributed Query• CLR• Service Broker• Spatial datatype• Physical server or catalog DDL and views

Constants Constraints Cursors Index management and

rebuilding indexes Local temporary tables Reserved keywords Stored procedures Statistics management Transactions

Supported T-SQL (v1)

Triggers Tables, joins, and table

vars T-SQL elements Create/drop databases Create/alter/drop tables Create/alter/drop users

and logins Views

CLR Database file

placement Database mirroring Distributed queries Distributed

transactions Filegroup

management Full Text Search

Unsupported T-SQL (v1)

Global temporary tables Spatial data and

indexes SQL Server

configuration options SQL Server Service

Broker System tables Trace Flags

Remove unsupported features, such as…

Migrating an existing schema

User Defined Data Types

Specifying Filegroups Extended Properties USE DATABASE NOT FOR REPLICATION

Various Index options Windows logins Unsupported XML features Indices need to be clustered

Full list at http://www.microsoft.com/sqlserver/2008/en/us/R2.

aspx

Generating, then modifying, the SQL DDL script

Loading in the data (bcp, etc…)-OR- SQL Azure Migration Wizard

http://sqlazuremw.codeplex.com

Demo – Data into the Cloud

Simple Application – just change the connection string! All compatibility requirements must be met (T-

SQL) in the DDL Create destination DB schema and populate

with data Firewall rules set up via SQL Azure portal (test

connectivity)

Dem0 – SQL Azure application

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 (post-CTP1) Monthly billing period

SQL Azure and Windows Azure Table Comparison

SQL Azure Tables Fully structured Strongly typed Relational (RDMS) Highly scalable

Windows Azure Tables Semi-structured Loosely typed Non-Relational (Not RDMS) Massively scalable

Added new Dynamic Management Views (DMVs) Allows return of state information for health monitoring &

performance tuning sys.dm_exec_connections , _requests , _sessions,

_tran_database_transactions, _active_transactions, _partition_stats

Added ability to move between Web or Business edition ALTER DATABASE database_name { MODIFY (MAXSIZE = {1 | 10}

GB) }

Increased idle session conn timeout (from 5 to 30 minutes) Improves experience while using connection pooling and other

interactive tools

Improved long running transaction algorithm Allows import and export of much larger amounts of data without

having to chunk your data

Recently Added (Feb 2010)

Partitioned databases Geo-location and geo-redundancy Distributed query Security w/AD, WLID, etc Support for multiple levels of hardware and software isolation

BI features – SSRS, etc…

SQL AzureOpportunities and Futures

Windows Azure Platform - http://www.azure.com/

Windows Azure Platform Training Kithttp://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78&displaylang=en

MSDN Development Centerhttp://msdn.microsoft.com/en-us/sqlserver/dataservices

Team Blog - http://blogs.msdn.com/ssds

Want to Know More?

Lynn LangitBlog: http://blogs.msdn.com/SoCalDevGalTwitter: @llangit

Contact Me