Building Applications Using SQL Azure

NameJob Title

Microsoft Corporation

Building Applications using SQL Azure

NameJob Title

Microsoft Corporation

Database

Business AnalyticsReporting 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 Data Platform as a Service – beyond hosting

SQL Azure Database

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”

MSDatacenter

Windows Azure Compute

Browser

SOAP/RESTHTTP/S

T-SQL (TDS)

App Code(ASP.NET)

SQL Server Report Server(on-premises)

ADO.NET/REST - EDMHTTP/S

- AD Federation (LiveId /AppFabric AC)

SQL AzureDatabase

T-SQL (TDS)

Relational database service

• SQL Server technology foundation

• Highly symmetrical

• Highly scaled

• Highly secure

Database “as a Service” – beyond hosting

Highly scaled out relational database as a service

Database

6

SQL Azure Network Topology

Application

Load Balancer

TDS (tcp:1433)

TDS (tcp: 1433)

TDS (tcp: 1433)

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

Load balancer forwards ‘sticky’ sessions to TDS protocol tier

Data Node Data Node Data Node Data Node Data NodeData Node

Gateway Gateway Gateway Gateway Gateway Gateway

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

7

Performance Considerations

• The distance your application travels to perform data access will affect performance

8

V1 Application Topologies

MSDatacenter

SOAP/RESTHTTP/S

SQL Azure access from within MS Datacenter (Azure compute – ADO.NET)

Windows Azure

T-SQL (TDS)

MSDatacenter

T-SQL (TDS)

App Code / Tools

SQL Azure access from outside MS Datacenter (On-premises – ADO.NET)

App Code(ASP.NET)

ADO.NET Data Svcs/REST - EFHTTP/S

Code Far

SQL Data Services

SQL Data Services

Application/ Browser

Code Near

9

Data Hub“An aggregation of Enterprise, Partner, Desktop, and Device data within SQL Azure”

Sync Sync

DesktopClient App

Device

Sync

Enterprise

EnterpriseOn-premises

App

User Management

Device Management

Sync Gateway

Business Logic / Rules

SQL Azure

Windows Azure

Azure App

Sync Client

Client App

Sync Client

10

Service Provisioning Model

• 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

Account

Server

Database

11

Setting Up SQL Azure Account

• Request token for SQL Azure at http://www.azure.com/

• Tokens will generally be provisioned within a couple days

• Redeeming tokens‒ Setup administrative user‒ Specify geo-location

Preparing your SQL Azure Account

Demo (click-through and portal)

13

SQL AzureDeployment

SQL AzureTDS Gateway

DB Script

14

SQL AzureAccessing databases

Your App

Change Connection String

SQL AzureTDS Gateway

15

Connecting to SQL Azure

• Connect via ADO.NET, ODBC, etc.• OLE DB is NOT supported• May need to include <login>@<server>• USE statement is NOT currently supported• Use familiar tools (sqlcmd, osql, SSMS, etc)• Current version of SSMS is not fully supported but

it works (demo to come)• Tools will evolve before PDC for increased support

Connecting to SQL Azure

Demo

17

SQL Azure Security

• Supports SQL Server Security

• On-premise SQL Server security concepts still apply‒ Server-level: sds_dbcreator, sds_securityadmin roles‒ Database-level: same as on-premise SQL Server

• SSMS Object Explorer is NOT currently supported

• Administrative user is equivalent to sa

Managing Logins and Security in

SQL Azure

Demo

19

T-SQL Support (full or partial)

• Constants• Constraints• Cursors• Index management and

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

• Tables, joins, and table variables• Transact-SQL language

elements such as ‒ Create/drop databases‒ Create/alter/drop tables‒ Create/alter/drop users

and logins‒ …

• User-defined functions• Views

20

T-SQL Not Supported (v1)

• Common Language Runtime (CLR)• Database file placement• Database mirroring• Distributed queries• Distributed transactions• Filegroup management• Full Text Search• Global temporary tables• Spatial data and

indexes

• SQL Server configuration options• SQL Server Service

Broker• System tables• Trace Flags

Creating Objects in SQL Azure

Demo

22

Migrating Databases

• “Just change the connection string” * once database is migrated

• Generate script wizard from SSMS currently creates unsupported DDL

• SQL Server Management Studio 2008 R2 has increased support for SQL Azure

• Migrating Databases to SQL Azure HOL in the Windows Azure Platform Training Kit

Migrating Database Schemas to

SQL Azure

Demo

24

Moving data

• Scripted INSERT statements

• SQL Server Integration Services‒ Available in Developer and Trial editions‒ Use ADO.NET endpoint

• BCP (bulk copy) is supported

• DataSync will enable Microsoft Sync Framework

Moving Data into SQL Azure with SSIS

Demo

26

Special Considerations: Database Size

• 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• See scale out SQL Azure content within the

Windows Azure Platform Training Kit for additional guidance

27

Special Considerations: Throttling

MSDN

• Use traditional SQL Server best practices• CTP cluster has throttling limits turned up to allow

for building logic into apps for handling this case• Build in retry logic especially if you expect very high

throughput demands• Consider scaling out for high throughput scenarios

28

Special Considerations: PHP

• SQL Azure does NOT support Multiple Active Result Sets (MARS)

• Must use the SQL Server for PHP v1.1 or newer with MARS connection option set to FALSE

Building a Windows Azure App Connected to SQL Azure

Demo

30

Learning SQL Azure

• www.azure.com

• Hands on Labs in Windows Azure Platform Training Kit

• Follow the team bloggers

31

Want to Know More?

• Windows Azure Platformhttp://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 Bloghttp://blogs.msdn.com/ssds

A not-for-profit, user-run association to enable the SQL Server community to connect, share, and learn

Get ongoing SQL Server learning from MVPs, top experts, and real-world users:

• Training webcasts, technical education, worldwide event info, and moreSign up for your FREE PASS membership today: www.sqlpass.org

• Go to local PASS Chapter meetings Visit the PASS Chapter booth to learn about upcoming local events, or visit www.sqlpass.org/PASSChapters

• No local Chapter? Start a Chapter with support and resources from PASS

www.sqlpass.org/getchapterinfo

• Attend PASS Summit 2010World's largest and best conference for SQL Server and BI technical education www.sqlpass.org/na2010 Follow / Join us online: Twitter @sqlpass #sqlpass, LinkedIn group, Facebook

group

33

Q & A

34

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