building applications using sql azure
DESCRIPTION
TRANSCRIPT
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.