sql azure for socalcodcamp

Lynn Langithttp://blogs.msdn.com/SoCalDevGal

twitter - @llangit

Ike Ellishttp://EllisTeam.blogspot.com

twitter - @EllisTeam1

January 2010

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

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

App Code

(ASP.NET)

App Code(ASP.NET)

TS

QL T

DS

SQL Azure

WindowsAzure

Code Near

App code/ Tools

SQL Azure

Code Far

Hybrid D

ata

Syn

c

SQL Azure

SQL Server App code/ Tools

App Code

(ASP.NET)

App Code(ASP.NET)

T-S

QL /

TD

S

TS

QL T

DS

WindowsAzure

Database Replicas

Replica 1

Replica 2

Replica 3

DB

Single Logical Database

Multiple Physical Replicas

Single Primary

Logical vs. Physical

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

Customer Evidence

Demo – SQL Azure Portal

Demo – DB Manager - Tables

Demo – DB Manager – Stored Procs

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 Azure Database Access

Web Portal(API)

Your App

Change Connection String

ADO.NET, ODBC, PHP (NOT OLE DB) Client libraries pre-installed ASP.NET controls

Clients connect directly to ONE database Cannot hop across DBs (no USE) May need to include

<login>@<server> Use familiar tools Use connection pooling for

efficiency

Connection Model

Firewall Rules

DB Manager Portal (Silverlight) SQL Server Management

Studio 2008 R2 Visual Studio 2010 / DACPACs SQLCMD SQL Azure as source data

SSRS, SSIS, SSAS 2008 R2 SharePoint, Excel, PowerPivot

2010

Demo – Connect via tools

SSMS 2008 R2 – Cloud vs. On Premise

Query Optimizer and Client Statistics can be used with SQL Azure data

Feature not yet implemented Physical layer – Microsoft handles it

Commodity vs. Business Multi-tenant

We must play nice together

Compatibility Overview

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 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

http://enzosqlshard.codeplex.com/

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

Tables (joins) , Views , Stored Procedures, Triggers

Indices Index Management Statistics Management

Spatial data Local Transactions

Supported T-SQL

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 Variables

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 Profiler / Trace Flags

Generating, then modifying, the SQL DDL script

Loading in the data (bcp, SSIS, etc…)-OR- SQL Azure Migration Wizard

http://sqlazuremw.codeplex.com

Demo – SQL Migration Wizard

SQL Migration Wizard

What Could Go Wrong?

Progress!

Success!

Remove unsupported features, such as…

Migrating By Hand

USE DATABASE ON PRIMARY

(Filegroups) Extended Properties NOT FOR REPLICATION

Various Index options (WITH) Windows logins Unsupported XML features Remove DEFAULT NEWSEQUENTIALID() Encrypted Stored Procedures No Heap tables (all tables must have clustered indices) UDTs

Advanced Migration

Use Scripts and/or Tools bcp SSIS 3rd Party

Guidance from SQLCAT team

Whitepapers Presentations

3 month release cycle Active CTPs at sqlazurelabs.com

OData Data Sync

Sign up for 2011 betas on Windows Azure portal SQL Azure Reporting Services

Roadmap

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 Support for HierarchyID data types (tree-like database structures)

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 summer 2010

Added Access 10 support for SQL Azure Support from Office 2010 to SQL Azure

Added - June 2010

Added Database copy capabilities Same server or cross server CREATE DATABASE DB2A AS COPY OF Server1.DB1A Monitor via sys.dm_database_copies

Increased MSDN documentation for common programming scenarios (with code examples)

Enhanced Project Houston Supported in multiple data centers View and StoredProc designers added

Added - August 2010

Improved Lightweight Silverlight Management Portal at windows.azure.com

Announced Betas SQL Azure Reporting Services SQL Azure Federation SQL Azure Data Sync v 2

Added - Nov 2010

Partitioning Utility / 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

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

Ike EllisBlog: http://EllisTeam.blogspot.comTwitter - @EllisTeam1

Contact Us

TeachingKidsProgramming.org

• Do a Recipe Teach a Kid (Ages 10 ++)• Free Courseware (recipes) Microsoft

SmallBasic