building applications using sql azure
DESCRIPTION
TRANSCRIPT
![Page 1: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/1.jpg)
Training Workshop
Windows Azure Platform
![Page 2: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/2.jpg)
3
Building Applications using SQL Azure
NameTitleOrganizationEmail
![Page 3: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/3.jpg)
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
![Page 4: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/4.jpg)
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
![Page 5: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/5.jpg)
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
![Page 6: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/6.jpg)
7
Performance Considerations
The distance your application travels to perform data access will affect performance
![Page 7: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/7.jpg)
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
![Page 8: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/8.jpg)
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
![Page 9: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/9.jpg)
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
![Page 10: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/10.jpg)
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
![Page 11: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/11.jpg)
12
Preparing your SQL Azure Account
demo (click-through & portal)
![Page 12: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/12.jpg)
13
SQL AzureDeployment
SQL AzureTDS Gateway
DB Script
![Page 13: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/13.jpg)
14
SQL AzureAccessing databases
Your App
Change Connection String
SQL AzureTDS Gateway
![Page 14: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/14.jpg)
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
![Page 15: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/15.jpg)
16
Connecting to SQL Azure
demo
![Page 16: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/16.jpg)
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
![Page 17: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/17.jpg)
18
Managing Logins & Security in SQL Azure
demo
![Page 18: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/18.jpg)
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
![Page 19: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/19.jpg)
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
![Page 20: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/20.jpg)
21
Creating Objects in SQL Azure
demo
![Page 21: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/21.jpg)
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
![Page 22: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/22.jpg)
23
Migrating Database Schemas to SQL Azure
demo
![Page 23: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/23.jpg)
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
![Page 24: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/24.jpg)
25
Moving Data into SQL Azure with SSIS
demo
![Page 25: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/25.jpg)
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
![Page 26: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/26.jpg)
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
![Page 27: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/27.jpg)
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
![Page 28: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/28.jpg)
29
Building a Windows Azure App Connected to SQL Azure
demo
![Page 29: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/29.jpg)
30
Learning SQL Azure
www.azure.com
Hands on Labs in Windows Azure Platform Training Kit
Follow the team bloggers
![Page 30: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/30.jpg)
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
![Page 31: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/31.jpg)
32
Q & A
![Page 32: Building applications using sql azure](https://reader036.vdocuments.us/reader036/viewer/2022081412/5455b603af7959b53e8b72b9/html5/thumbnails/32.jpg)
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.