sql azure june_2010
DESCRIPTION
SQL Azure June and July 2010 deliveriesTRANSCRIPT
Windows Azure Platform
Compute: Virtualized compute environment
Storage: Durable, scalable, & available storage
Management: Automated, model-driven
management
Database: Relational processing for
structured/unstructured data
Service Bus: General purpose application bus
Access Control: Rules-driven, claims-based
access control
Data Storage ChoicesR
eso
urc
es
Dedicated
Shared
Low High“Friction”/Control
SQL Azure (RDBMS) Virtual DB server
Resource governance @ LDB
Security @ LDBAuto HA, Fault-ToleranceSelf-provisioningHigh RDMS
compatibility
Hosted Hosted SQL Server or
other Roll-your-own HA/DR/scale Security @ DB Server/OS
On-premise SQL Server or other s/w on-
premise Resource governance @ machine Full h/w control – 100%
compatibilityRoll your own HA/DR/scale
Windows Azure Storage
Tables Queues Blobs (also
VMs)
Application Topologies
Application/ Browser
SOAP/RESTHTTP/S
App Code
(ASP.NET)
App Code(ASP.NET)
TS
QL T
DS
SQL Azure WindowsAzure
Code Near
App code/ Tools
SQL Azure
Windows
Azure
Code Far
Hybrid
SQ
L A
zure
/ D
ata
Syn
c
Windows
Azure
SQL Azure
SQL Server App code/ Tools
App Code
(ASP.NET)
App Code(ASP.NET)
T-S
QL /
TD
S
TS
QL T
DS
Database Replicas
Replica 1
Replica 2
Replica 3
DB
Single Database Multiple Replicas
Single Primary
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
Logical vs. Physical Administration
DBA role places more focus on logical management
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
Service Provisioning Model
Account
Server
Database
SQL AzureAccessing databases
Web Portal(API)
SQL AzureTDS
Your App
Change Connection String
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 CTP can connect http
://blogs.msdn.com/ssds/archive/2009/11/11/9921041.aspx
Connection Model
Connecting to SQL Azure with SSMS 2008 R2
SQL Azure Explorer for VS 2010 http://sqlazureexplorer.codeplex.com/
SQLCMD – can also be used SSIS – can also be used
Demo – Connect via tools
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 No support for auto-partitioning or fan-out queries
(yet) Must handle partitioning logic within the application Also called Database ‘sharding’
Database Size Limits
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
Constants Constraints Cursors Index management and
rebuilding indexes Local temporary tables Reserved keywords Stored procedures Statistics management Transactions
Supported T-SQL
Spatial data and indexes Triggers Tables, joins, and table
vars T-SQL elements Create/drop databases Create/alter/drop
tables Create/alter/drop users
and logins Views
CLR Database file
placement Database mirroring Distributed queries Distributed
transactions Filegroup
management Full Text Search
Unsupported T-SQL
Global temporary tables
SQL Server configuration options
SQL Server Service Broker
System tables Trace Flags
Remove unsupported features, such as…
Migrating an existing schema
User Defined Data Types
Specifying Filegroups Extended Properties USE DATABASE NOT FOR REPLICATION
Various Index options Windows logins Unsupported XML features Indices need to be clustered
Full list at http://www.microsoft.com/sqlserver/2008/en/us/R2.
aspx
Deployment Options
SSMS - Generate Script Wizard w/SQL Azure option SQL Azure Migration Wizard / CodePlex
Useful for catching unsupported features in SQL Azure Moves data efficiently
Data-tier Application Component (DAC)
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
LOGICALTables, Views, Constraints,
SProcs, UDFs,
PHYSICALUsers, Logins,
Indexes
Future - DAC Deployment ProfileDeployment Requirements,
Management Policies, Failover Policies
Unit o
f Deplo
ym
ent
DAC Pack = Single unit (Package) for authoring, deploying, and managing the data-tier objects through the development 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 Deploying and Upgrading databases using DACs,
DAC (Data-tier Application) Packages
Generating, then modifying, the SQL DDL script
Loading in the data (bcp, SSIS, etc…)-OR- SQL Azure Migration Wizard
http://sqlazuremw.codeplex.com
Demo – Data into the Cloud
SQL Azure Migration Wizard
What Could Go Wrong?
Progress!
Success!
Scenario/Tool SSMA Generate Script Wizard
DACs SSIS BCP
Mysql and Access to SQL Azure
√ (Schema
and Data)
√ (Data Only)
Move SQL Schema √ √ √
Move Data √ √ √
Move Large Data √ √
Summary– Database Migration
Local and Cloud-Based
Simple Application – 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) Nothing to install, no SDK required!
Dem0 – SQL Azure application
**Update** Pricing
Edition WEB BUSINESS Bandwidth
Max 1 GB $ 9.99 / month n/a 10 cents in 15 cent 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
Storage Comparison
SQL Azure Tables Fully structured Strongly typed Relational (RDMS) Highly scalable
Windows Azure Tables Semi-structured Loosely typed Non-Relational (Not RDMS) Massively scalable
Added new Dynamic Management Views (DMVs) Allows return of state information for health monitoring &
performance tuning sys.dm_exec_connections , _requests , _sessions,
_tran_database_transactions, _active_transactions, _partition_stats
Added ability to move between Web or Business edition ALTER DATABASE database_name { MODIFY (MAXSIZE = {1 | 10}
GB) }
Increased idle session conn timeout (from 5 to 30 minutes) Improves experience while using connection pooling and other
interactive tools
Improved long running transaction algorithm Allows import and export of much larger amounts of data without
having to chunk your data
Added - Feb 2010
Added availability in 20 more countries (now 41 total) Complete list here Started with 21 countries, now doubled
Added support for MARS Simplifies the design process
Added support for ALTER (rename) Database Adds features found in RDMS to SQL Azure
Included support in Visual Studio 2010 to manage SQL Azure Allows direct management of SQL Azure databases via Server
Explorer Also found in SQL Server Management Studio
Added new labs feature - sandbox https://www.sqlazurelabs.com/
Added - April 2010
Added upper size limit of 50 GB Available June 28, 2010 Initial discount on upgrading maximum size
Added support for Spatial Data Types Support for Geography & Geometry data types and queries
Added support DataSync Service for SQL Azure Initially available in CTP via sqlazurelabs.com
Announced SQL Server Web Manager Web-based SQL Azure management tool Initially available as CTP in suimmer 2010
Added Access 10 support for SQL Azure Support from Office 2010 to SQL Azure
Recently Added (June 2010)
Database Engine
Relational Data
Management
Replication
Full Text Search
Integration Services
ETL Processing
Data Profiling
StreamInsight*
Complex Event
Processing
Analysis Services
Classic OLAP Data Manageme
nt
Data Mining
PowerPivot* Self Service
Analytics
Reporting Services
Managed Reporting
Self Service
Reporting
Embedded Reporting
Master Data
Services*
Master Data
Management
SQL Server 2008 R2 Editions
SQL Web Management and Administration (SWA) Partitioning Utility Profiler-like traces / deadlock graphs Geo-location and geo-redundancy Distributed query Security w/AD, WLID, etc Support for multiple levels of hardware and software
isolation BI features – SSRS, etc…
Opportunities and Futures
What’s Your Idea?
Windows Azure Platform – here Windows Azure Platform Training Kit – here
MSDN Development Center – here Team Blogs – SSDS and SQLAzure
Want to Know More?
Lynn LangitBlog: http://blogs.msdn.com/SoCalDevGalTwitter: @llangit
Contact Me