1 / 53

Introduction to Developing With SQL Azure

Introduction to Developing With SQL Azure. Jeff Mlakar Database Architect Ernst & Young. About me. Computer Science and Mechanical Engineering degrees from the Engineering College at the University of Michigan (pause for “ Boo”s from Ohio audience)

arich
Download Presentation

Introduction to Developing With SQL Azure

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Developing With SQL Azure Jeff Mlakar Database Architect Ernst & Young

  2. About me • Computer Science and Mechanical Engineering degrees from the Engineering College at the University of Michigan (pause for “Boo”s from Ohio audience) • Started in SQL Server 2000,C++,Started in .NET 1.1,BizTalk 2002 • Currently work as a Database Architect at accounting firm of Ernst & Young. • (Title has been referred to as Database Architect, SQL Lead Designer/Data Modeler, Senior Application Engineer, … so you tell me) • Work on applications to Steward/Manage/ReportOn data across the global firm. • Blog: www.jeffmlakar.com , twitter: @JeffMlakar

  3. PURPOSE To introduce you to working with Cloud databases in SQL Azure by working to accomplish a specific goal.

  4. THE GOAL Create a Tool in Management Studio that can upload/retrieve common TSQL scripts to/from an online repository.

  5. Agenda • About your speaker • What is the Cloud? • Windows Azure Platform Overview • SQL Azure overview • DEMO: Creating an Azure database • DEMO: Creating your own SSMS AddIn • DEMO: Developing in the Cloud • DEMO: Connecting to and using your Cloud DB • Show end product • SQL Azure Pricing • SQL Azure News

  6. What is Cloud Computing? A Service approach to computing that’s about scaling, abstraction by the vendor, and consumption-based pricing.

  7. Think “Electricity” • Powering your building is like running your website / database

  8. Why use the Cloud? 2 reasons: • Scale to and pay for what you use. • Only worry about the stuff you know well.

  9. Allocated IT-capacities Load Forecast Not Enough Power Too Much Power IT CAPACITY ActualLoad TIME

  10. Load Forecast Capacity on Demand IT CAPACITY ActualLoad TIME

  11. Cloud Services Infrastructure-as-a-Service “PaaS” “IaaS” “SaaS” Platform-as-a-Service Software-as-a-Service Consume GMail Build Microsoft Host Amazon

  12. Cloud Services Infrastructure (as a Service) Platform (as a Service) In-House Software Software (as a Service) You manage Applications Applications Applications Applications You manage Data Data Data Data Runtime Runtime Runtime Runtime Managed by vendor Middleware Middleware Middleware Middleware You manage Managed by vendor O/S O/S O/S O/S Virtualization Virtualization Virtualization Virtualization Managed by vendor Servers Servers Servers Servers Storage Storage Storage Storage Networking Networking Networking Networking

  13. Windows Azure PlatformPaaS

  14. Windows Azure Platform • Scalable compute and storage • Automated service management • Familiar tools, technologies, languages • Relational storage for the cloud • Consistent development model • Automated database management • Connect existing apps to the cloud • Connect through network boundaries • Easily control authorization to apps

  15. Storage Options in Windows Azure Platform: • Windows Azure Storage Instance: non-relational storage by means of services that use • BLOBS • Tables • Queues(cheap, but have to programmatically handle Referential Integrity, Transactions, Performance, etc…) • SQL Azure (more expensive that #1, but gives us relational storage with powerful and robust database engine we all know and love)

  16. SQL AzureDatabase as a Service

  17. History • First introduced in Community Technology Preview (CTP) as SQL Server Data Services (SSDS) • SSDS was non-relational (similar to current Windows Azure Storage instances) and was an altogether new approach to persisting data. • Customers complained. They wanted: • Relational engine • Easy way to Migrate existing apps • To use the skill set they already have. • Exit SSDS. Enter SQL Azure. (Yay! A Database!)

  18. SQL Azure DatabaseThe first and only true database as a service Subtitle color Managed Service Scale On Demand Innovate Faster • Build cloud-based database solutions on consistent relational model • Leverage existing skills through existing ecosystem of developer and management tools • Database utility; pay as you grow • Business-ready SLAs • Enable multi-tenant solutions • World-wide presence • Easy provisioning and deployment • Auto high-availability and fault tolerance • No need for server or VM administration

  19. SQL Azure Physical Architecture Apps Tools ODBC ADO.NET TDS SQL Azure Services Connection Routing Billing Provisioning Server Server Server SQL Server SQL Server SQL Server SQL Azure Fabric SQL Azure Fabric SQL Azure Fabric Infrastructure Database 1 Database 2 Database 3

  20. Database Replicas (Hooray for HA!) ! Single Database Multiple Replicas Replica 1 Single Primary Replica 2 DB Replica 3 Replica 4

  21. Logical vs. Physical Administration • Service handles physical management • Automatically provides HA “out of box” • Transparent failover in case of failure • Load balancing of data to ensure SLA • SQL Azure focus on logical administration • Schema creation and management • Query optimization • Security management (Logins, Users, Roles)

  22. Azure Code/Data Architecture Options: Near Far

  23. Azure Code/Data Architecture Options: (cont.) From Windows Azure From Outside Microsoft Datacenter From Windows Azure & Outside Microsoft Datacenter SQL Server App Code / Tools App Code / Tools Application / Browser SQL Azure Data Sync Microsoft Datacenter MicrosoftDatacenter Microsoft Datacenter Windows Azure Windows Azure SQL Azure SQL Azure SQL Azure Code Near Code Far Hybrid

  24. demo DEMO TIME!Create a SQL Azure Server and Database

  25. Steps to Create a SQL Azure Database • Get a free trial through: http://www.microsoft.com/windowsazure/free-trial/ Includes one 1 GB DB free for 90 days. Charges begin after that. • Go your portal at http://windows.azure.com • Select Database -> Your Subscription -> Create Server. • Create firewall rules. DONE!

  26. Steps to Create a SQL Azure Database • Select Create Database

  27. demo Create an SSMS AddInStep-by-Step

  28. Steps to Create an SSMS AddIn • Open Visual Studio. • File->New Project->Other Project types->Extensibility-> Visual Studio AddIn • Go through Wizard and select “Yes” for creating a Command Bar UI • Change Debugging from VS to SSMS by going to the Project Properties, under “Debug”. • Remove Working Directory and Command Line • Change Start External Program toC:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

  29. Steps to Create an SSMS AddIn (cont.) • While you’re in Properties, go to Build and Select “Register for COM interop” • Delete “.Addin” files • Add a Setup Project. File->Add->New Project->Other Project Types->Setup Proj • Right-click Setup Proj and Add Project Output. Select the primary output of your AddIn project • Select primary output, go to Properties->Register. Change from vsdrpDoNotRegister to vsdrpCOM

  30. Steps to Create an SSMS AddIn (cont.) • Add Registry Keys to Setup Proj by right-clicking and going to View-Registry. Add:[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Shell\AddIns\AddInName.Connect]"LoadBehavior"=dword:00000001 • Add dlls (What make it possible)

  31. Steps to Create an SSMS AddIn (cont.) • Add DLLs:- SqlWorkbench.Interfaces.dll - Microsoft.SqlServer.SqlTools.VSIntegration.dll From: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE • Open Connect.cs and add “using Microsoft.SqlServer.Management.UI.VSIntegration” • Comment out DTE2 _applicationObject and declaration; that interface is not supported in SSMS AddIn • Replace _applicationObject with ServiceCache.ExtensibilityModel

  32. Steps to Create an SSMS AddIn (cont.) 15) Locate OnConnection and make connectMode Startup Before: After:

  33. Steps to Create an SSMS AddIn (cont.) • In Setup Proj, right click Detected Dependencies and hit Refresh • In the resulting list, right-click ieframe.dll and Click “Exclude”. It’s not supported.

  34. Now we’re ready to sayHello World

  35. Steps to Create an SSMS AddIn (cont.)

  36. Steps to Create an SSMS AddIn (cont.) • Build your AddIn project and Setup Project. • Run the msi you built and Start Management Studio(After you’re installed once, you can just hit F5 to run changes)

  37. Make a more complex App

  38. Add a User Control Create a Windows User Control “MainWindow” and launch it like so:

  39. If you need to create an update: • In Setup Project Properties, • make sure “RemovePreviousVersions” is set to true • Increment Version • Select “Yes” when prompted to update ProductCode • Correct update installer to function correctly for COM reg • Download “Orca.exe”, a database table editor for creating and editing Windows Installer packages. • Open the resulting msi in Orca and open table “InstallExecutionSequence” • Find Entry “RemoveExistingProducts” and give it a value that falls between InstallValidate and InstallInitialize (ie 1450 if InstallValidate is 1400 and InstallInitialize is 1500)

  40. demo Developing in the Cloud

  41. Database Manager

  42. demo Connecting to and using your Cloud DB

  43. Use fully-qualified server name and SQL Server Authentication to connect in Connection String

  44. demo Bail! Drop DB and Server. No more charges!

  45. New(er) Stuff • SQL Server Reporting Services • Data-tier Application (DAC) framework v2.0 (CTP) • Sparse Columns • Inbound data is FREE as of June! • Federations

  46. Not supported • Change Data Capture • Data Auditing • Data Compression • Extended Events • External Key Management / Extensible Key Management • FILESTREAM Data • Integrated Full-Text Search • Large User-Defined Aggregates (UDAs) • Large User-Defined Types (UDTs) • Performance Data Collection (Data Collector) • Policy-Based Management • Resource Governor

  47. Not supported (cont.) • SQL Server Replication • Transparent Data Encryption • Common Language Runtime (CLR) and CLR User-Defined Types • Database Mirroring • Service Broker • Table Partitioning • Typed XML and XML indexing is not supported. The XML data type is supported by SQL Azure. • Extended Stored Procedures • Extended PropertiesSee General Guidelines & Limitations:http://msdn.microsoft.com/en-us/library/ff394115.aspx

  48. Useful Tools • SQL Azure Data Sync • Based on Microsoft Sync Framework • Easy way if you want to sync data across data centers or from Cloud to On-Prem • SQL Azure Migration Wizard • Analyze a DB or a script for Azure compliance • http://sqlazuremw.codeplex.com/

  49. So what does this COST?

  50. Summary SQL Azure provides a highly available cloud database service. • Infrastructure in managed for you • Scale On Demand • Use your existing skill set to innovate faster

More Related