17-20 OCTOBER 2011

DURBAN ICC

SQL Azure ToolsFor Developers and DBAs@LlewellynFalco & @LynnLangit

Session: DAT311

Session Objectives and Takeaways• Session Objective(s):

– See and understand the breadth of tools for SQL Azure– These tools include the following:

• Windows Azure Portal• SQL Server Management Studio• Visual Studio (also SQL Server Data Tools)/ LightSwitch• OData, WCF Data Services

• Session Takeaway:– SQL Azure has a variety of tools – Use the right tool for the task(s)

Note: we are covering RELATIONAL cloud database options here only. For information about non-relational cloud data structures - (i.e. NoSQL, etc…) see this link

Extending SQL Server to the Cloud

Symmetry with Traditional RDBMS

Enterprise-class “scale-up” data platform

Multiple hardware platforms and deployment options

Deploy as dedicated server in customer datacenter, VM or 3rd party

Typically licensed per-server or per processor

Distributed “scale-out” database service

Abstracts OS and hardware management

Low-friction provisioning, multi-tenant

Automatic high-availability and data replication

Pay-as-you-go and commitment offers

Familiar T-SQL relational modelSupport existing code libraries and protocolsEcosystem of familiar development and management tools

SQL Azure Database

Cloud relational database based on SQL Server engine

Use same tools, data access frameworks, T-SQL based language

Global datacenters

High Availability & Redundancy

– Reads are completed at the primary– Writes are replicated to a quorum of secondaries

Single LogicalDatabase

Multiple PhysicalReplicas

Single Primary

MultipleSecondaries

Repl

ica

1Re

plic

a 2

Repl

ica

3

DB

SQL Azure PortalSilverlight-based tools

demo

SQL Azure Portal

Database Management Portal• Logon

Database information

Database Schema Management

Manage Server

Import / Export

Import/Export Concepts

SQL Azure Data Sync

SQL Server

Application

SQL Azure

Application

SQL Server

Application

On-Premises

SQL Azure

Application

SQL Azure

Application

SQL Azure

ApplicationCloud

• Scale-out via multiple copies of data

• E.g. Separate reporting & OLTP workloads; multiple Web sites

• Geo-located web applications• Use with Windows Azure Traffic

Manager• Hybrid applications; one-way publish or

two-way sharing• Multiple locations (e.g. branch office, retail

offices); share data between locations and/or aggregate data in cloud

SQL Azure Data Sync (CTP)• No-Code Sync Configuration

– Configure and manage using Windows Azure Portal– Define data to be synchronized and locations– Choose how often data is synchronized– Specify optional filters

• Full Data Synchronization Capabilities– Two-way sync of same data, as well as one-way sync

• Conflict Handling– Detect and resolve conflicts caused by the same data being changed in multiple

locations

SQL Azure Reporting & Data Sync Previews

Data Sync in Action

SQL Azure Data Sync

demo

SQL Azure Reporting Beta

SQL Server Management Studio

SQL Server --Connection Info--Query Execution--DACPAC

demo

About SQL Azure & SSMS 2008 R2 SP1

Be sure to update to SP1 - here

Visual Studio 2010 SP1Included tools--Server Explorer--Entity Framework--DACPAC--Refactoring--Intellisense

demo

SQL Server Data Tools in Visual Studio• Can target SQL Azure (version targeting)• Server Explorer

– Can Snapshot project (database)– Can Schema compare– Supports entity framework

• Improved Intellisense– Error syntax highlighting– Easier refactoring– Easier debugging (including stored procs)

SSDT Architecture

SQL Server Data Tools Preview Release

demo

SQL Server Data ToolsFormerly codename “Juneau”

SSDT in Visual Studio• Server Explorer

– New Node –> SQL Server• Connect to SQL

Azure• Connect to SQL

Server• LocalDB

• Can work online or offline

Table Designer

Offline ProjectsNew Project Type

--Set Source connection--Set Target project--Set Import settings--Creates Off-line Project

SSDT Schema Compare

Refactoring• Find All Dependencies• Rename with Preview• Intellisense

Version Targeting & Publishing

More About SSDT• Table Designer• Schema Compare• Refactoring (find all references & go to definition)• SSDT project (to create objects)• Debug/test w/local DB• Publish (sync and/or apply incremental updates)

Visual Studio Light Switch

demo

Visual Studio Light Switch• Beth Massi blog

– Azure and Light Switch – here

Other tools• PowerShell• Command-line

• SQLCMD• bcp

• CodePlex tools• SQL Azure Migration Wizard

• 3rd party tools• Cerebrata, RedGate (www.queryanywhere.com), Quest

3rd Party Tools

Download Migration Tools – hereDownload Migration Wizard - here

• Oct 2011– SQL Azure Reporting Final CTP– SQL Azure Data Sync Final CTP

• Open to all via Windows Azure Management Portal• Commercial Release Q1 2012

• Q4 2011– Max database size increased to 150 GB– Updated Azure Management Portal– SQL Azure Federations– Refresh of SQL Server Data Tools – Refresh of SQL Server Management Studio

SQL Azure Futures

Session Objectives and Takeaways• Session Objective(s):

– See and understand the breadth of tools for SQL Azure– These tools include the following:

• Windows Azure Portal• SQL Server Management Studio• Visual Studio (SQL Server Data Tools)/ LightSwitch• PowerShell, Command-Line, Open Source, 3rd Party

• Session Takeaway:– SQL Azure has a variety of tools – Use the right tool for the task(s)

Team Resources• Windows Azure Platform Blog - here• MSDN SSDT section• MSDN Forum – here• SSDT Team Blog - here • Tip: Watch information from SQLPass – here • Oct 11-14, 2011 in Seattle

Resources

• Intro To TDD Class (4 Day)• Legacy Code• SQL Server (Dev, BI)• SQL Azure

www.Develop.com

TeachingKidsProgramming.org• Do a Recipe Teach a Kid (Ages 10 ++)• Microsoft SmallBasic Free Courseware (recipes)

Contact Information

@LlewellynFalcohttp://LlewellynFalco.Blogspot.comhttp://www.approvaltests.com

@LynnLangithttp://www.LynnLangit.com

Submit your session evaluation for a chance to win!

Sponsored by MVA

http://microsoftvirtualacademy.com

Creating

the futuretogether