Introduction to SQL Azure

PresenterTitleCompany

Session Objectives and TakeawaysSession Objectives:

Get up to speed on SQL AzureLearn about the architecture and cloud specific features of SQL AzureDemonstration of some core features in action

Takeaways:SQL Azure lets you innovate faster because it’s a managed service that lets you scale on demandSQL Azure is uniquely positioned as an ideal data platform for Windows Azure applications

SQL Azure DatabaseThe first and only true database as a service

Scale On Demand

Innovate FasterManaged Serviceo Database utility; pay as

you growo Business-ready SLAso Enable multi-tenant

solutionso World-wide presence

o Easy provisioning and deployment

o Auto high-availability and fault tolerance

o No need for server or VM administration

o Build cloud-based database solutions on consistent relational model

o Leverage existing skills through existing ecosystem of developer and management tools

Application TopologiesFrom

Windows AzureFrom Outside

Microsoft Datacenter From Windows Azure & Outside

Microsoft Datacenter

Application / Browser

Windows Azure

SQL Azure

Code Near

App Code / Tools

SQL Azure

MicrosoftDatacenter

Code Far Hybrid

Microsoft Datacente

r

SQL Azure

SQL Server

Microsoft Datacenter Windows

Azure

SQL Azure Data Sync

App Code / Tools

Architecture

Shared infrastructure at SQL database and below

Request routing, security and isolationScalable HA technology provides the glue

Automatic replication and failoverProvisioning, metering and billing infrastructure

Database Replicas

Replica 1

Replica 2

Replica 3

DB

Single Database Multiple Replicas

Single Primary

Replica 4

!

Behind the Scenes of SQL AzureApplicatio

n

Internet

LBTDS (tcp)

TDS (tcp)

TDS (tcp)

Apps use standard SQL client libraries: ODBC, ADO.Net, PHP, …

Load balancer forwards ‘sticky’ sessions to TDS protocol tier

Security Boundary

Gateway

Gateway

Gateway

Gateway

Gateway

Gateway

Scalability and Availability: Fabric, Failover, Replication, and Load balancing

SQL SQL SQL SQL SQLSQL

Gateway: TDS protocol gateway, enforces AUTHN/AUTHZ policy; proxy to backend SQL

Service Provisioning ModelEach account has zero or more logical servers

Provisioned via a common portalEstablishes a billing instrument

Each logical server has one or more databases

Contains metadata about database & usageUnit of authentication, geo-location, billing, reportingGenerated DNS-based name

Each database has standard SQL objects

Users, Tables, Views, Indices, etcUnit of consistency

Account

Server

Database

Working With SQL Azure

Connection ModelSQL Azure exposes native SQL Server TDS protocolUse existing client libraries

ADO.NET, ODBC, PHPClient libraries pre-installed in Windows Azure rolesSupport for ASP.NET controlsApplications connect directly to a database

Cannot hop across DBs (no USE)

Connecting to SQL AzureSQL Azure connection strings follow normal SQL syntaxApplications connect directly to a database

“Initial Catalog = <db>” in connection stringNo support for context switching (no USE <db>)

Encryption securitySet Encrypt = True, only SSL connections are supportedTrustServerCertificate = False, avoid Man-In-The-Middle-Attack!

Format of username for authentication:ADO.Net:Data Source=server.database.windows.net;User ID=user@server;Password=password;...

Setup your firewall rules first!

Logical vs. Physical AdministrationSQL Azure focus on logical administration

Schema creation and managementQuery optimizationSecurity management (Logins, Users, Roles)

Service handles physical managementAutomatically provides HA “out of box”Transparent failover in case of failureLoad balancing of data to ensure SLA

DBA role places more focus on logical management

DeploymentDeploy via T-SQL scripts Support for SQL Server Data-Tier Applications (DAC) feature

DACPAC is unit of deploymentCloud or on-premise is a deployment time choice

Create Logical Server in same region as Windows Azure Affinity Group for code-near architecture

Support existing and new forms of deployment

Security ModelUses regular SQL security model

Authenticate logins, map to users and rolesAuthorize users and roles to SQL objects

Support for standard SQL Auth loginsUsername + password

Security model is compatible with on-premise SQL

Deploying and Managing SQL Azure Databases with Visual Studio 2010 Data-tier ApplicationsPresenterTitleCompany

demo

SQL Azure Compatibility

Currently SupportedTables, indexes and viewsStored ProceduresTriggersConstraintsTable variables, session temp tables (#t)Spatial types, HierarchyId

Not Currently Supported

Data TypesSparse Columns, Filestream

PartitionsFull-text indexesSQL-CLR

Tables require clustered indexes

Database EditionsTwo SQL Azure Database SKUs: Web & Business

Web Edition: 1 GB @ $9.99/month | 5 GB @ $49.95/monthBusiness Edition: Up to 50 GB @ $99.99/10 GB/month10 GB @ $99.99 | 20 GB @ $199.98 | 30 GB @ $299.97 | 40 GB @ $399.96 | 50 GB @ $499.95

You specify Web or Business EditionWeb: EDITION = webBusiness: EDITION = business

You specify MAXSIZEWeb: MAXSIZE = 1GB | 5GBBusiness: MAXSIZE = 10GB | 20GB | 30GB | 40GB | 50GB

This is the maximum size we will not let you grow beyondYou will only be charged for the actual peak size in any one day rounded upFor example, a 3.4 GB Web Edition will be charged 5GB rate.

CREATE DATABASE foo1 (EDITION='business', MAXSIZE=50GB);CREATE DATABASE foo2 (EDITION='business', MAXSIZE=30GB);ALTER DATABASE foo2 MODIFY (EDITION='web', MAXSIZE=5GB);

Up to 50 GB10 GB increments

Business Edition

1 GB or 5 GBWeb Edition

Summary

SQL Azure provides a highly availablecloud database service.

Managed ServiceScale On DemandInnovate Faster

© 2010 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.