microsoft sql azure development best practices n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft SQL Azure Development Best Practices PowerPoint Presentation
Download Presentation
Microsoft SQL Azure Development Best Practices

Loading in 2 Seconds...

play fullscreen
1 / 37

Microsoft SQL Azure Development Best Practices - PowerPoint PPT Presentation


  • 215 Views
  • Uploaded on

Required Slide. SESSION CODE: COS305. Microsoft SQL Azure Development Best Practices. Rick Negrin Lead Program Manager SQL Azure Microsoft Corporation. Agenda. Overview Architecture Development Debugging Deployment Management Summary. Overview. The Windows Azure Platform.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Microsoft SQL Azure Development Best Practices' - jacob


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft sql azure development best practices

Required Slide

SESSION CODE: COS305

Microsoft SQL Azure Development Best Practices

Rick Negrin

Lead Program Manager

SQL Azure

Microsoft Corporation

agenda
Agenda
  • Overview
  • Architecture
  • Development
  • Debugging
  • Deployment
  • Management
  • Summary
the windows azure platform
The Windows Azure Platform
  • Developer Experience
  • Use existing skills and tools

Relational data

Management

platform

Compute

Storage

Management

AppFabric

Flexible APIs

Connectivity

Access control

Information Marketplace

Reporting & BI

Billing & Payments

microsoft sql azure
Microsoft SQL Azure
  • Familiar SQL Server relational model
  • Uses existing APIs & tools
  • Friction free provisioning and reduced management
  • Built for the Cloud with availability and scale

Relational Database as a Service for the Windows Azure Platform

Focus on combining the best features of SQL Server running at scale with low friction

sql azure database
SQL Azure Database

Highly scaled out relational database as a service

  • Relational database service
    • SQL Server technology foundation
    • Highly symmetrical
    • Highly scaled
      • Database “as a Service” – beyond hosting

SQL Azure Database

(Windows Azure Compute)

Browser

SOAP/REST

HTTP/S

Astoria/REST - EDM

HTTP/S

  • Customer Value Props
    • Self-provisioning and capacity on demand
    • Symmetry w/ on-premises database platform
    • Automatic high-availability and fault-tolerance
    • Automated DB maintenance (infrastructure)
    • Simple, flexible pricing – “pay as you grow”

App Code

(ASP.NET)

App Code

(ASP.NET)

Windows Azure Compute

T-SQL (TDS)

T-SQL (TDS)

SQL Server Reporting Server

(on-premises)

SQL Azure Database

MS

Datacenter

- AD Federation (LiveId /.NetSvcs ACS)

service provisioning model
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

Database

application topologies
Application Topologies

SQL Azure access from within

MS Datacenter (Azure compute)

SQL Azure Access from outside

MS Datacenter (On-premises)

SQL Azure Access from within and outside MS Datacenter (On-premises & Azure Compute)

SQL Server

App code/ Tools

App code/ Tools

Application/ Browser

SOAP/REST

HTTP/S

Astoria/REST - EDM

HTTP/S

Windows

Azure

SQL Azure

Data Sync

Windows

Azure

App Code

(ASP.NET)

App Code

(ASP.NET)

T-SQL (TDS)

App Code

(ASP.NET)

App Code

(ASP.NET)

T-SQL (TDS)

T-SQL (TDS)

Windows

Azure

SQL Azure

SQL Azure

SQL Azure

Code Near

Code Far

Hybrid

unit of multi tenancy
Unit of multi-tenancy

Account

  • Account
    • ISV provides software to End customer
    • End customer has full control and responsibility over Azure components, direct billing relationship with MS
    • Typically for ISVs that do not want to run a service
  • Server/Subscription level
    • ISV manages owns the account (provisioning and billing)
    • Customer owns administration of the server and databases
    • Common for ISVs providing development platforms
  • Database level
    • ISV owns account and administration
    • Each customer gets its own DB(s) but typically does not manage it, simplest programming model
    • Common case for most ISVs where the data per customer > 500 MB or the biz model allows it
  • Sub-database level
    • ISV packs multiple customers into a single DB (may have many DBs though)
    • Common case where average data per customer is small < 50k

Server

Database

Sub-Database

partitioning when do i need it
Partitioning, when do I need it?
  • Partitioned Data
  • Partitioning based on Application Requirements (IOPS)
  • Partitioned Data
  • Partitioning based on Application Requirements (IOPS, Storage or both)

High

Transactional Requirements

  • Partitioned Data
  • Partitioning Based on Application Requirements (Storage)
  • Single Database
  • No Partitioning

Low

Low

High

Storage Requirements

develop locally
Develop Locally
  • Developing on a local SQL Express instance has some advantages
    • Easy to get started, you already know how to do it!
    • Full fidelity with the designer and debugging tools
    • Reduces latency when using local Azure development tools
    • Reduces bandwidth and databases costs for development
  • Some caveats
    • Remember to alter your VS build settings to switch the connection string when deploying
    • Use tools (like SQLAzureMW) to keep you within the supported SQL Azure features
    • Always test in SQL Azure before deploying to production
connecting to sql azure
Connecting to SQL Azure
  • SQL Azure connection strings follow normal SQL syntax
  • Applications connect directly to a database
    • “Initial Catalog = <db>” in connection string
    • No support for context switching (no USE <db>)
    • Some commands must be in their own batch
      • Create/Alter/Drop Database & Create/Alter/Drop Login, & Create/Alter USER with FOR/FROM LOGIN
  • Encryption security
    • Set Encrypt = True, only SSL connections are supported
    • TrustServerCertificate = False, avoid Man-In-The-Middle-Attack!
  • Format of username for authentication:
    • ADO.Net:Data Source=server.database.windows.net;User ID=user@server;Password=password;...
  • Setup your firewall rules first!
connections use pooled connections
Connections: Use Pooled Connections
  • Increases efficiency by removing re-login
  • // When pooling, use connection and return immediately
  • // Do not hold for a long time – pool ensure fast turnaround
  • // one second use
  • using (SqlConnection conn = new SqlConnection(…))
  • {
  • conn.Open();
  • using (SqlCommandcmd = conn.CreateCommand())
  • {
  • cmd.CommandText = …;
  • }
  • }
  • using (SqlConnection conn = new SqlConnection(…))
  • {
  • conn.Open(); …
resilient connection management
Resilient Connection Management
  • Connections can drop for variety of reasons
    • Idleness (greater than 30 minutes)
    • Throttling
      • Long running transactions > 5 minutes
      • Resource Management
    • Database failover
      • Hardware failure
      • Load Balancing
      • Upgrade
  • What to do on connection failure?
    • Wait, then retry if it is a transient failure
    • Change your workload if throttled, i.e. break up your transaction
resilient connection

Resilient Connection

NameTitleCompany

DEMO

short transactions

Short Transactions

NameTitleCompany

DEMO

select into temp tables
SELECT INTO temp tables
  • SELECT *
  • INTO #Destination
  • FROM Source
  • WHERE [Color] LIKE 'Red‘
  • To work around this you need to create your destination table then call INSERT INTO. Here is an example:
  • CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max), [Color] nvarchar(10))
  • INSERT INTO #Destination(Id, [Name], [Color])
  • SELECT Id, [Name], [Color]
  • FROM Source
  • WHERE [Color] LIKE 'Red';
tracking connectivity problems
Tracking Connectivity Problems
  • Each session assigned a unique ‘sessionId’
    • Tracks session state and service errors
  • Retrievable from CONTEXT_INFO()
    • Save this with each new connection
  • Also comes back with *some* error messages
  • If you need support, support personnel will greatly appreciate that value
sessionid helper pattern
SessionId Helper Pattern

Guid? sessionId = null;

using (SqlConnection conn = new SqlConnection(…))

{

// Grab sessionId from new connection

using (SqlCommandcmd = conn.CreateCommand())

{

conn.Open();

cmd.CommandText =

"SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())";

sessionId = new Guid(cmd.ExecuteScalar().ToString());

}

// continue processing

...

}

other techniques
Other Techniques
  • DMVs
    • sys.dm_exec_*
      • Connections, query_plan, query_stats, requests, sessions, sql_texttext_query_plan
    • sys.dm_tran_*
      • active_transactions, database_transactions
  • Test Connectivity button
  • Future features
    • Profiler-like trace
    • Deadlock graphs
deployment options
Deployment Options
  • Generate Script Wizard
    • Produce a SQL script compatible with SQL Azure, Schema and/or data
    • Great for initial deployment
  • SQLAzureMW
    • Useful for catching unsupported features in SQL Azure
    • Moves data efficiently
    • Unofficially supported

Data-tier Application Component

Schema

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

LOGICAL

Tables, Views, Constraints, SProcs, UDFs,

PHYSICAL

Users, Logins, Indexes

Unit of Deployment

Future - DAC Deployment Profile

Deployment Requirements, Management Policies, Failover Policies

sql azure firewall
SQL Azure Firewall
  • What is it?
    • A way to restrict access to your database
  • How does it work?
    • UI
    • Programmatic access
  • Common scenarios
    • I need my development machine to be able to access it
    • I need access from my laptop when I am not in the office
    • I want to restrict access to only my application running in Windows Azure
summary
Summary
  • Many SQL Server patterns apply to SQL Azure
    • Use SQL best practices wherever possible
  • Think carefully about your level of multi-tenancy and need for partitioning
  • Make your code robust, use retry logic for your connections
  • Develop locally
related content

Required Slide

Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session.

Related Content
  • Breakout Sessions
    • DAT209 – What’s New in Microsoft SQL Azure
    • COS305 – Microsoft SQL Azure Development Best Practices
    • COS311 – Migrating Applications to Microsoft SQL Azure
  • Interactive Sessions
    • COS07-INT – Using SQL Azure as a Datahub to connect SQL Server and Silverlight clients
    • COS13-INT – Database Performance in a Multi-tenant Environment
  • Hands-on Labs
    • COS04–HOL – Introduction to Microsoft SQL Azure
track resources

Required Slide

Track PMs will supply the content for this slide, which will be inserted during the final scrub.

Track Resources
  • Team Blog – http://blogs.msdn.com/sqlazure
  • MSDN Dev Center – http://msdn.microsoft.com/azure/sqlazure
  • SQL Azure Labs – http://www.sqlazurelabs.com
  • Follow us on Twitter - @sqlazure

Windows Azure Platform Training Kit - http://bit.ly/dgvMv0

track resources1

Required Slide

Track PMs will supply the content for this slide, which will be inserted during the final scrub.

Track Resources
  • Join a user group and network with peers to Connect, Share and Learn
    • Become a PASS Member (Professional Association for SQL Server) - www.sqlpass.org
    • Attend the PASS Summit 2010 – largest SQL Server conference http://www.sqlpass.org/summit/na2010
  • Join the SQL Server conversation on Facebook, Twitter and YouTube
    • Find out what top SQL Server bloggers are saying on the Community Tab
    • Learn about applications running on SS08 R2 on the Application Showcase Tab
  • Ask questions and get answers, straight from SQL Server experts
    • Visit the Microsoft SQL Server Forums - http://social.technet.microsoft.com/forums/en-us/category/sqlserver
track resources2
Track Resources
  • Windows Azure, Microsoft SQL Azure, Windows Azure platform AppFabric: www.windowsazure.com/techedus
  • Learn More: Visit the Windows Azure Boot Camp, Room 396
resources

Required Slide

Resources

Learning

  • Sessions On-Demand & Community
  • Microsoft Certification & Training Resources

www.microsoft.com/teched

www.microsoft.com/learning

  • Resources for IT Professionals
  • Resources for Developers
  • http://microsoft.com/technet
  • http://microsoft.com/msdn
slide34

Required Slide

Complete an evaluation on CommNet and enter to win!

slide35

Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st

http://northamerica.msteched.com/registration

You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

slide36

© 2010 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.