Windows Azure SQL Notes

  • Article

I am at the Azure Firestarter event in Redmond today and just heard David Robinson from the SQL team give an overview of SQL Server for Azure.  Here are my notes; slides and sample code are to be posted later and I will update the post with them when they are.

SQL Azure - David Robinson

  • Goal is to convince you that there is no difference between SQL Server and SQL Azure.
  • First called "SQL Data Services for Azure" at PDC 2008.
    • Looked a lot like the Azure table storage that Brad just talked about.
    • Feedback - why not expose SQL Server?
  • Done that - extended the SQL Server platform to the cloud.
    • Rich ecosystem of tools: BI, reporting, VS integration, …
    • Goal -> same SQL Server you have on premise you now have in the cloud.
  • Difference between hosted DB and DB as a service
    • SQL Azure combines best of both.
    • Nothing to install, nothing to maintain, nothing to patch.
    • Every 8 weeks provide new features as a service update
      • Service Update 2 deployment started today. Expect it to complete by Friday (4 days).
    • Use existing tools - management studio, visual studio - all just work
      • Need November 2009 CTP of SQL Server 2008 R2 to manage Azure SQL Services
    • Automation manages servers in the data centers.
  • Key customer scenarios for SQL Azure v1
    • Departmental collaboration apps
      • Low concurrency, cyclical usage apps
      • Corp IT doesn't manage the application and isn't aware of it
      • Provides better management
    • Data hubs
      • Consolidate multiple data sources and allow access from anywhere
    • Packaged LOB applications
      • ISVs extending their on-premise offering to be cloud hosted
  • Architecture
    • Provisioning
      • Listen for connections on Port 1443, monitor and bill.
      • Gateway to SQL Azure
    • Scalability and Availability fabric
      • Provides load balancing, management, etc.
      • Provides automatic backup replicas
      • Have a scale unit - fully redundant rack of machines.
        • Puts your primary and backup on different scale units.
        • Upgrade:
          • Failover a scale unit
          • Update the scale unit with new service bits
          • Put scale unit back in rotation
    • In between - SQL Server
  • Use existing client libraries
    • ADO.NET, ODBC, PHP
    • Client libraries are pre-installed in Azure roles
    • Support for ASP.Net controls
    • Clients can connect directly to the database
      • Cannot hop across DBs (no USE command)
  • Management
    • All the "pain in the butt" things that DBAs don't want to be bothered with (the log file is running out of space) are handled automatically.
    • All the application-specific things like query tuning, index creation still managed by DBAs.
    • Support "code far" scenario (where code and DB are not co-located) but will generally work best when you insure your DB and code are co-located in a single Azure DC
      • Unless you do special work to do caching locally.
  • No way to do a backup of a regular SQL DB on premise and restore to a SQL Azure instance.
    • Working on this.
    • Now have to write a script to create the DB and indices, etc. then use BCP to import data.
  • In VS 2010 - new project type "Data Tier Application"
    • Supported with SQL Azure and on-premise
    • Captures data tier requirements (e.g. machine type assumptions, usage patterns) in addition to basic create table/etc. scripts.
    • Compiles to a DACPAC - like an MSI for a database.
    • Can deploy a DACPAC on-premise or to the cloud.
      • Click on Properties on the Data Tier Application in VS and go to Deploy tab.
      • Change deployment location in Destination Connection String to a SQL Azure database and will deploy there.
      • Build and then Deploy to send to SQL Azure.
      • Q: What about changes?
        • A: You have the ability to deploy just changes for on-premise.
          • Saves the DACPAC you deployed in the master DB. When you deploy upgrades, it will compare the DACPAC against the actual live schema to make sure they match and then figures out the changes to make.
          • Not yet available for SQL Azure - for now a manual process. KB article on how to do this.
  • Uses regular SQL Server security model
  • New features announced at MIX 10:
    • 50 GB SKU
      • Now in beta (preview) - must sign up by emailing engagesa@microsoft.com
      • General availability with SU3 of SQL Server Azure in June 2010
      • Pricing TBA
    • MARS - Multiple Active Row Sets
      • Multiple SQL batches on a single connection
    • Spatial Data in the Cloud
      • Available in SU3 (June)
      • Spatial data allows for geographic and mapping data in SQL.
      • All features that on-premise supports for spatial data available in SQL Azure
      • Example/ demo:
        • have a table with geo coordinates for every gas station in the U.S.
        • Use Bing Maps API to figure route from A to B.
        • Go to SQL Azure DB to get all gas stations from the table within 1000 meters of the map route calculated by Bing Maps
        • Add those as pins on the Bing Map and display to users.
  • Q: How do you maintain app compatibility when you update underneath the app
    • A: Goal is to maintain 100% compatibility. Do extensive testing. Focusing on minor changes, not adding new features like data types.
  • Q: Can you run a linked server to SQL Azure
    • A: You can add a SQL Azure instance as a linked server if you have an on-premise SQL Server.
    • You cannot link two SQL Azure servers.
  • Q: What about distributed transactions?
    • A: Not supported in SQL Azure.
  • Q: Can I run managed code and stored procedures in a SQL Azure instance?
    • A: Today, no. Spatial is the start of enabling that.
    • You will never be able to run arbitrary code on SQL Azure.