Sponsored by:

Professional Association for SQL Server

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

twitter - @llangit

DATA “on”

An end-to-end solutions for working with custom Twitter Data Feeds using the Windows Azure

Platform

Windows Azure Platform

Compute: Virtualized compute environment

Storage: Durable, scalable, & available storage

Management: Automated, model-driven

management

Database: Relational processing and services for

structured/unstructured data – Data Marketplace

Service Bus: General purpose application bus

Access Control: Rules-driven, claims-based

access control

Windows Azure Platform

Data Storage ChoicesRe

sour

ces

Dedicated

Shared

Low HighControl

SQL Azure (RDBMS) Virtual DB serverResource 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

• Setup Accounts– Twitter account / hashtag (#24HOP)– SQL Azure Server with one Database– SQL Azure Reporting CTP

• Use Tools– Azure portal – SQL Server 2008 R2 Tools

• SSMS on premise with SSRS / Report Builder 3.0• Business Intelligence Development Studio

– SQL Azure Migration Wizard (CodePlex)– Visual Studio 2010 Ultimate– Excel 2010 with Power Pivot

Development Process

Using SQL Azure Services

Power Pivot

Report Viewer Control

SQL Azure Reporting

SQL Azure

Twitter Data

Power Pivot

Report Viewer Control

SQL Server Reporting

SQL Server

Twitter Data

Getting the Twitter Data

• General API information - http://dev.twitter.com/• Search API examples - http://dev.twitter.com/doc/get/search

• Containing a hashtag - http://search.twitter.com/search.atom?q=%2324HOP• From a user – http://search.twitter.com/search.atom?q=from%3Llangit• Mentioning a User - http://search.twitter.com/search.atom?q=%40Llangit

• Refresh (get latest results – JSON syntax)• "refresh_url":"?since_id=9290798834&q=i"

• Consider adding a ‘login to Twitter button’• Required for public Twitter clients

SQL Azure Server Portal

SQL Portal for Database

• We used ADO.NET and ODBC (OLE DB n/a at this time)– Client libraries pre-installed in Azure roles– Support for ASP.NET controls

• Best practices for connecting– Connect directly to ONE database (no USE)– May need to include <login>@<server>– Encrypt login information– Use Retry logic (can use connection library) – Use connection pooling for efficiency

Connection Model

RDMS Features Used

• Tables, indexes and views• Stored Procedures• Triggers• Constraints• Table variables • Session temp tables• Spatial datatype

SSMS 2008 R2

SQL Migration Wizard

COPY Database Command

DacPac

SQL Azure Data Sync Service

• Scale-out read or read/write• Geo replication of data• Edge network data distribution• Content delivery networks• sqlazurelabs.com

Sync Group

SQL Azure

SQL Server

SQL Azure

On-Premises Applications

Data Sync - Demo

Table Order in Sync

Tracking Objects Created

• Tracking Tables• On Source / on Destination

• Triggers• Stored Procedures

OData Support (Labs)

ATOM PUB JSON HTTP

https://www.sqlazurelabs.com

Open Data Protocol

Odata Query & Results

• Microsoft applications– Connectivity into BI applications

• SSRS, SSIS, SSAS

– Connectivity into Office 2010 applications• Excel, Excel PowerPivot, SharePoint, etc…

• Custom applications– Can use Windows Azure – not required– Can connect via any client with valid connection string

Application Integration with SQL Azure

• BIDS SQL Server 2008 R2• Report Builder 3.0

Report Development

SQL Server Report Builder 3.0

SQL Server BIDS 2008 R2

SQL Azure Reporting CTP

• 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

Microsoft Application Integration

Tools – Visual Studio 2010

Report Viewer Control – ‘out of box’ or customized

• Two UX versions – WinForms or WebForms• Two processing modes – Local or Remote (Azure)• Pass credentials• Customize UX

Report Viewer Control

Report Viewer Control Customization

• Current Betas– SQL Azure Reporting Services– Odata– SQL Azure Data Sync v 1

• Other Future Betas– SQL Azure Data Sync v 2 – SQL Azure Integrations Services – SQL Azure Analysis Services– SQL Azure Federation

Future - SQL Azure CTPs

VOTE for the FUTUREhttp://www.mygreatwindowsazureidea.com/pages/34685-sql-azure-feature-voting

• Windows Azure Platform – here• Windows Azure Platform Training Kit – here • MSDN Development Center – here • Team Blogs – SQLAzure

Want to Know More?

Lynn LangitBlog: http://blogs.msdn.com/SoCalDevGal

Twitter: @llangit

Contact Information

Teaching High School Girls T-SQL• Dating Game Database• T-SQL queries – hour 1 (WHERE), hour 2 (JOIN)• Artifacts on CodePlex, can use SQL Azure to host

• http://datinggame.codeplex.com

TeachingKidsProgramming.org

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

Thank you to our sponsor

Professional Association for SQL Server

May 11-13, Orlando, FL

Oct 11-14, Seattle, WA

Save 25%: Register by April 12th

www.sqlpass.org/sqlrally

Register by March31st: save 40% and have the chance to win a cruise to Alaska!

www.sqlpass.org/summit