Building applications using sql azure

Windows Azure Platform Training Workshop

Building Applications using SQL Azure Name Title Organization Email 3

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 • Highly symmetrical SOAP/REST ADO.NET/REST - EDM HTTP/S HTTP/S • Highly scaled • Highly secure App Code (ASP.NET) Database “as a Service” – beyond hosting SQL Server Report Server Windows Azure (on-premises) Customer Value Props Compute  Self-provisioning and capacity on demand T-SQL (TDS) T-SQL (TDS)  Symmetry w/ on-premises database platform SQL Azure  Automatic high-availability and fault-tolerance Database MS  Automated DB maintenance (infrastructure) Datacenter  Simple, flexible pricing – “pay as you grow” - AD Federation (LiveId /AppFabric AC)

SQL Azure Network Topology Applications use standard SQL client Application libraries: ODBC, ADO.Net, … TDS (tcp:1433) Load balancer forwards ‘sticky’ Load Balancer sessions to TDS protocol tier TDS (tcp: 1433) Gateway Gateway Gateway Gateway Gateway Gateway TDS (tcp: 1433) Data Node Data Node Data Node Data Node Data Node Data Node Scalability and Availability: Fabric, Failover, Replication and Load balancing 6

Performance Considerations The distance your application travels to perform data access will affect performance 7

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 App Code / Tools SOAP/REST ADO.NET Data Svcs/REST - EF HTTP/S HTTP/S App Code (ASP.NET) Windows Azure T-SQL (TDS) T-SQL (TDS) SQL Data SQL Data MS MS Services Services Datacenter Datacenter Code Near Code Far 8

Data Hub “An aggregation of Enterprise, Partner, Desktop, and Device data within SQL Azure” Windows Azure Enterprise Azure App On-premises App SQL Azure Enterprise User Management Device Management Desktop Device Sync Client App Client App Business Logic / Rules Gateway Sync Client Sync Client 9

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 Contains metadata about the databases and usage Unit of authentication Server Unit of Geo-location Generated DNS based name Each database has standard SQL objects Unit of consistency Database Unit of multi-tenancy Contains Users, Tables, Views, Indices, etc. Most granular unit of billing 10

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 11

Preparing your SQL Azure Account (click-through & portal) 12

SQL Azure Deployment DB SQL Azure Script TDS Gateway 13

SQL Azure Accessing databases Change Connection String Your SQL Azure TDS Gateway App 14

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 15

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 17

Managing Logins & Security in SQL Azure 18

T-SQL Support (full or partial) Constants Tables, joins, and table Constraints variables Cursors Transact-SQL language Index management and elements such as rebuilding indexes Create/drop databases Create/alter/drop tables Local temporary tables Create/alter/drop users Reserved keywords and logins Stored procedures … Statistics management User-defined functions Transactions Views Triggers 19

T-SQL Not Supported (v1) Common Language SQL Server configuration Runtime (CLR) options Database file placement SQL Server Service Broker Database mirroring System tables Distributed queries Trace Flags Distributed transactions Filegroup management Full Text Search Global temporary tables Spatial data and indexes 20

Creating Objects in SQL Azure 21

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 22

Migrating Database Schemas to SQL Azure 23

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 24

Moving Data into SQL Azure with SSIS 25

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 26

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 27

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 28

Building a Windows Azure App Connected to SQL Azure 29

Learning SQL Azure www.azure.com Hands on Labs in Windows Azure Platform Training Kit Follow the team bloggers 30

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 31

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

Building applications using sql azure

More Related Content

What's hot

Viewers also liked

Similar to Building applications using sql azure

Recently uploaded

Building applications using sql azure

Editor's Notes