Training Workshop

Windows Azure Platform

3

Building Applications using SQL Azure

NameTitleOrganizationEmail

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 PropsSelf-provisioning and capacity on demandSymmetry w/ on-premises database platformAutomatic high-availability and fault-toleranceAutomated 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 TopologyApplication

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 serversAzure wide, provisioned in a common portalBilling instrument

Each server has one or more databasesContains metadata about the databases and usageUnit of authenticationUnit of Geo-locationGenerated DNS based name

Each database has standard SQL objectsUnit of consistencyUnit of multi-tenancyContains 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 tokensSetup administrative userSpecify geo-location

12

Preparing your SQL Azure Account

demo (click-through & 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 supportedMay need to include <login>@<server>USE statement is NOT currently supportedUse 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

16

Connecting to SQL Azure

demo

17

SQL Azure SecuritySupports SQL Server Security

On-premise SQL Server security concepts still apply

Server-level: sds_dbcreator, sds_securityadmin rolesDatabase-level: same as on-premise SQL Server

SSMS Object Explorer is NOT currently supported

Administrative user is equivalent to sa

18

Managing Logins & Security in SQL Azure

demo

19

T-SQL Support (full or partial)

ConstantsConstraintsCursorsIndex management and rebuilding indexesLocal temporary tablesReserved keywordsStored proceduresStatistics managementTransactionsTriggers

Tables, joins, and table variablesTransact-SQL language elements such as

Create/drop databasesCreate/alter/drop tablesCreate/alter/drop users and logins…

User-defined functionsViews

20

T-SQL Not Supported (v1)

Common Language Runtime (CLR)Database file placementDatabase mirroringDistributed queriesDistributed transactionsFilegroup managementFull Text SearchGlobal temporary tablesSpatial data and indexes

SQL Server configuration optionsSQL Server Service BrokerSystem tablesTrace Flags

21

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

23

Migrating Database Schemas to SQL Azure

demo

24

Moving data

Scripted INSERT statements

SQL Server Integration ServicesAvailable in Developer and Trial editionsUse ADO.NET endpoint

BCP (bulk copy) is supported

DataSync will enable Microsoft Sync Framework

25

Moving Data into SQL Azure with SSIS

demo

26

Special Considerations: Database Size

Maximum single database size is currently 10GBDatabase size calculation

Includes: primary replica data, objects and indexesDoes NOT include: logs, master database, system tables, server catalogs or additional replicas

V1 does not support auto-partitioning or fan-out queriesMust handle partitioning logic within the applicationSee scale out SQL Azure content within the Windows Azure Platform Training Kit for additional guidance

27

Special Considerations: ThrottlingMSDN

• 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

29

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

32

Q & A

33

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