Lynn Langit http://blogs.msdn.com/SoCalDevGal twitter - @llangit
Windows Azure Platform
Compute: Virtualized compute environment
Storage: Durable, scalable, & available storage
Management: Automated, model-driven management
Database: Relational processing for
structured/unstructured data – Data Marketplace
Service Bus: General purpose application bus
Data Storage Choices
Res
ou
rc
es
Dedicated SharedLow
“Friction”/Control
HighSQL Azure (RDBMS) Hosted
On-premise
Windows Azure Storage
Application Topologies
Application/ Browser App Code (ASP.NET ) App Code (ASP.NET) T S Q L T DS SQL Azure Windows Azure Code NearApp code/ Tools
SQL Azure Code Far Hybrid Da ta S y nc SQL Azure
App code/ Tools
App Code (ASP.NET ) App Code (ASP.NET) T -S Q L / T DS T S Q L T DS Windows Azure
Database Replicas
Replica 1 Replica 2 Replica 3 DBLogical vs. Physical Administration
• SQL Azure focus on logical
administration
– Schema creation and management – Query optimization
– Security management (Logins, Users, Roles)
• Service handles physical management
– Automatically replicated with HA “out of box” – Transparent failover in case of failure
– Load balancing of data to ensure SLA
Demo
- Portal for Database
Management
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
SQL Azure Database Access
Web Portal
Your App
Connection Model
• Use ADO.NET, ODBC, PHP (NOT OLE DB)
– Client libraries pre-installed in Azure roles – Support for ASP.NET controls
• Clients connect directly to a database
– Cannot hop across DBs (no USE)
– May need to include <login>@<server>
– Use familiar tools (sqlcmd, osql, SSMS, etc) – Use connection pooling for efficiency
• SSMS 2008 R2 can connect
Demo
– Connect via tools
• Web Client – Silverlight (Portal) • SSMS 2008 R2
• VS2010/LightSwitch • SQLCMD
• SSIS
• SSRS/SSIS/SSAS
– w/SQL Azure as source data – use R2 versions
Query Optimizer and Client Statistics can be used with SQL Azure data
Compatibility Overview
• Feature not yet implemented
• Physical layer – Microsoft handles it
– Commodity vs. Business
• Multi-tenant
Database Size Limits
• Maximum single database size is 50GB • Database size calculation
– Includes: primary replica data, objects and indexes
– Does NOT include: logs, master database, system tables, server catalogs or additional replicas
• Announced CTP support for auto-partitioning & fan-out queries (Federation)
– Currently must handle partitioning logic within the application – Also called Database „sharding‟
– Sharding Utility, using TPL at
Compatibility
In Scope
• Tables, indexes and views • w/ clustered indices • Stored Procedures • Triggers • Constraints • Table variables
• Session temp tables • Spatial datatype Out of Scope • Distributed Transactions • Distributed Query • CLR • Service Broker
• Physical server or catalog DDL and views
Supported T-SQL
• Tables (joins) ,
Views , Stored
Procedures,
Triggers
• Indices
– Index Management
– Statistics
Management
• Spatial data
• Local Transactions
T-SQL elements Reserved keywords Create/drop databases Create/alter/drop tables Create/alter/drop users and logins Constants, Constraints, Cursors, Local temporary tables Table VariablesUnsupported T-SQL
• CLR
• Database file
placement
• Database mirroring
• Distributed queries
• Distributed
transactions
• Filegroup
management
• Full Text Search
Global temporary tables
SQL Server configuration options
SQL Server Service Broker
System tables
Demo
– SQL Migration Wizard
• Generating, then modifying, the SQL DDL script • Loading in the data (bcp, SSIS, etc.)
• SQL Azure Migration Wizard
– http://sqlazuremw.codeplex.com
Data Migration Tools
• Wizards
– SQL Azure Migration Wizard (CodePlex) – here
– Migration Assistant for Access or for MySQL – here or here
– Import/Export Wizard – Generate Script Wizard
• Copy Database command
• CREATE DATABASE DB2A AS COPY OF Server1.DB1A
– Other Tools – SSIS – BCP – DACPAC enhanced (CTP) – DataSync (CTP) – OData (CTP)
SQL Azure Data Sync
• Elastic Scale
– Service scales as resources requirements grow
• No-Code Sync Configuration
– Easily define data to be synchronized
• Schedule Sync
– Choose how often data is synchronized
• Conflict Handling
– Handle issues where same data is changed in multiple locations
• Logging and Monitoring
– Administration capabilities for tracking data and monitoring potential issues
SQL Azure Data Sync CTP3
• New UX integrated with the Windows Azure management portal.
• Ability to select a subset of columns to sync as well as to specify a filter so that only a subset of rows are synced. • Ability to make certain schema
changes to the database being
synced without having to re-initialize the sync group and re-sync the data. • Conflict resolution policy can be
specified.
SQL Azure - Import/Export
DAC v2.0 adds two new DAC services: import and export
Import and export services let you deploy & extract both schema & data from a single file identified with the “.bacpac” extension
OData Support
ATOM PUB
JSON
HTTP
https://www.sqlazurelabs.com
Data-tier Application Components
New unit of deployment for T-SQL apps
Supports Install, Uninstall, and in the future Upgrade and Repair
Contains developer intent as policies.
Data-tier Application Component
Schema LOGICAL Tables, Views, Constraints, SProcs, UDFs, PHYSICAL Users, Logins, Indexes
Future - DAC Deployment Profile
Deployment Requirements, Management Policies, Failover Policies
Un it of De pl oym en t
DAC (Data-tier Application) Packages
• Single unit (Package) for authoring, deploying, and managing the data-tier objects through the project lifecycle
• Development Lifecycle (VS 2010) - editing DACs – Schema and DB Code Development,
– Code Analyses, Deployment Policy Settings, – Schema Comparison and more…
– Building DACs – the self contained database package • Management Lifecycle (SSMS 2008 R2) – managing DACs
– Registering existing database as DACs
DACPAC in SSMS
In SSMS • Extract • Register • Monitor w/UCPs In VS 2010 • Import • CreateApplication Integration
• Custom applications
– Can use Windows Azure – not required
– Can connect via any client with valid
connection string
• Microsoft applications
– Connectivity into Office applications
• Excel, Excel PowerPivot, SharePoint, etc…
– Connectivity into BI applications
Local and Cloud-Based
No SDK required – simply change connection string from local to cloud copy of DB
SQL Azure application
• Simple – just change the connection string!
– All compatibility requirements must be met (T-SQL) in the DDL
– Create destination DB schema and populate with data – Firewall rules set up via SQL Azure portal (test
connectivity)
Microsoft Application Integration
• Office 2010
– Excel
– Excel Power Pivot – SharePoint
• SQL Server 2008
– SQL Server Reporting Services – Report Builder 3.0
– SQL Server Integration Services – SQL Server Analysis Services
• Visual Studio 2010
– Server Explorer – Entity Framework – DACPAC
• Windows Azure Marketplace DataMarket
Office 2010 -
DEMO
Database Engine Relational Data Management Replication Full Text Search Integration Services ETL Processing Data Profiling StreamInsight* Complex Event Processing Analysis Services Classic OLAP Data Management Data Mining PowerPivot* Self Service Analytics Reporting Services Managed Reporting Self Service Reporting Embedded Reporting Master Data Services* Master Data Management
Opportunities and Futures
• SQL Web Management and Administration (SWA) • Partitioning Utility in future CTP (Federation)
• Profiler-like traces / deadlock graphs • Geo-location and geo-redundancy • Distributed query
• Security w/Active Directory, Windows Live ID, etc
• Support for multiple levels of hardware and software isolation • BI features – SSRS, SSIS, SSAS
Pricing
Edition WEB BUSINESS Bandwidth
Max 1 GB $ 9.99 / month n/a 10 cents/GB in 15 cent/GB out *higher in Asia (see notes) Max 5 GB $ 49.95 / month n/a same Max 10 GB n/a $ 99.99 / month same Max 20 GB n/a $ 199.98 / month same Max 30 GB n/a $ 299.97 / month same Max 40 GB n/a $ 399.96 / month same Max 50 GB n/a $ 499.95 / month same
Want to Know More?
• SQL Azure Community – here
• Windows Azure Platform Training Kit – here
• MSDN Development Center – here
