New Azure Mobile Services Samples

Hi Folks,

I have to say that I’m really impressed by the rate at which the Windows Azure Mobile Services team is rolling out updates and samples. In particular, it’s good to see a new sample for using Geolocation, given how common a requirement that is in mobile apps today. It’s worth checking out the following:

·         New Code Samples page on WindowsAzure.com

·         Updated WindowsAzure.com Tutorials and Resources page that includes new tutorials and related videos from the new Windows Azure Mobile Services channel 9 series

·         A bunch of new Windows Store + Mobile Services scenario-based samples:

o    Geolocation sample end to end using Windows Azure Mobile Services

o    Enqueue and Dequeue messages with Windows Azure Mobile Services and Services Bus

o    Capture, Store and Email app Feedback using Windows Azure Mobile Services

o    Upload File to Windows Azure Blob Storage using Windows Azure Mobile Services

o    Create a Game Leaderboard using Windows Azure Mobile Services

Nice!

How full is my Windows Azure SQL Database?

While the level of compatibility of Windows Azure SQL Databases is high, there are a number of things that need to be dealt with differently, compared with how they are done with on-premises SQL Server.

An example of this, today I needed to know how full one of my databases was. I wanted to know how much space I had used but also to know what the limit was. My first attempt was the usual system views such as:

SELECT * FROM sys.database_files;

But in the Azure environment, that returns:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.database_files'.

So that wasn't going to help. I had also tried the new sys.resource_stats and sys.resource_usage views also to no avail. There is a good article that provides details of which views do and don't work on which versions here. Conor Cunningham also posted early last year about how some of these are supposed to work. (For more info on Conor and Windows Azure SQL Database in general, see the podcast that I recorded with him recently).

Michael Wood pointed me to a post from Ryan Dunn that showed how to get the used size in total, and for each database object. (For more info on Ryan, see the SQL Down Under podcast that I recorded with him back when SQL Data Services first appeared). Tom LaRock also explained that in his post here. The view that I needed to use was the sys.dm_db_partition_stats view.

For an overall space usage total, the following query helps:

SELECT SUM(reserved_page_count) * 8.0/1024 AS DatabaseMB
FROM sys.dm_db_partition_stats;

For a breakdown by individual object, the following query (a tidied up version of what I've found in the posts) helps:

SELECT o.name AS ObjectName, 
      
SUM(reserved_page_count) * 8.0 / 1024 AS SizeinMB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.sysobjects AS o
ON ps.object_id = o.id
GROUP BY o.name
ORDER BY SizeinMB DESC;

There is also a really good Azure article here that describes the use of these along with info on bandwidth monitoring, etc.

But the final thing I wanted to know is what the limit was for a given database. Sanjay Nagamangalam (from the SQL Server team) came to the rescue by pointing out a post from Walter Berry. It mentions that you can get the maximum size from an extended database property. The query below that I've adapted from it, shows the current limit:

SELECT CAST(DATABASEPROPERTYEX('PopkornKraze_DW' , 'MaxSizeInBytes') AS integer) 
      
/ 1024 / 1024 AS DatabaseLimitInMB;

(Note that PopkornKraze_DW was the name of my database in this test). The final thing that I might need is a query that shows how full my database is as a percentage, so if we combine them, it's just:

SELECT CAST((SELECT SUM(reserved_page_count) * 8.0 / 1024
             FROM sys.dm_db_partition_stats) * 100
            / (SELECT CAST(DATABASEPROPERTYEX('PopkornKraze_DW' , 'MaxSizeInBytes') AS integer)
            / 1024 / 1024) AS decimal(10,2)) AS PercentageUsage;

If you do happen to exceed the size limit, SQL Exception 40544 is thrown.

You can modify the database size (and edition) by executing:

ALTER DATABASE PopkornKraze_DW MODIFY (EDITION='BUSINESS', MAXSIZE=10GB);

You appear to be able to change the size while others are connected but changing the edition terminates existing connections.

Hope that helps someone.

 

Nice set of updates to Azure over the last few days – Data Sync now in the HTML Portal – Updates to the CLI

Scott Guthrie posted about the Azure-related changes that have happened over the last few days.

Of particular interest to me was that Data Sync was now in the new HTML portal, and that the Azure store now works in a bunch more countries.

Generally I like the newer HTML portal but I'm still finding that it applies different validation rules to SQL passwords than Windows Azure SQL Database itself does. That's a pain as I still have to use the older portal.

Regardless, here is the list of changes:

  • Mobile Services (job scheduler support, Europe Region Support, Command Line Support)
  • Web Sites (scale improvements, integrated source control)
  • SQL Data Sync (support in the new HTML portal)
  • ACS Management (support in the new HTML portal)
  • Media Services (job and task management, blob storage support, reserved compute)
  • Virtual Network enhancements
  • Subscription Filtering Support
  • Windows Azure Store (now available in more countries)
  • Glenn Bock also posted a few days back about changes to the CLI that help with automation of site and virtual machine creation.

    Windows Azure SQL Reporting – Great to see a dramatic price decrease for lower volume users

    I was really excited about Windows Azure SQL Reporting being released. That lasted until I saw the pricing. If I just deployed a server and didn't use it, the price was 89c per hour for up to 300 reports per hour. What many people didn't understand is that the price applied whether or not you used the reporting server.

    That meant that the minimum price for deploying a server was 89c x 24 hours x 365 days = $7796.40 per year.

    That price was way more expensive than just deploying an Azure VM and purchasing a standard edition license for SQL Server to put on it. And the standard edition license had way more functionality, apart from the high availability provided by WASR.

    At every session where I showed the product, the feedback was the same: "Love the idea, but have you seen the price?".

    Many of us complained loudly (internally) to Microsoft about this, and I'm pleased to say that I just got an email that covers dramatic price reductions.

    From February 1, 2013, the price drops to 16c per hour (all USD) for up to 30 reports per hour.

    That means the minimum price for deploying a server is 16c x 24 hours x 365 days = $1401.60 per year, as long as you don't exceed 30 reports per hour. The pricing still isn't where I'd like to see it, but it's way more palatable now.

    If you've been put off by the pricing of Windows Azure SQL Reporting, it's time to take another look at it.

    SQL Down Under Show 51 – Guest Conor Cunningham – Now online

    Late last night I got to record an interview with Conor Cunningham.

    Most people that know Conor have come across him as the product team wizard that knows so much about query processing and optimization in SQL Server. Conor is currently spending quite a lot of time working on Windows Azure SQL Database, which we used to know as SQL Azure.

    I'm still trying to think of a good way to say "WASD". I suppose I'll pronounce it like "wassid". Windows Azure SQL Reporting is easier. I think it just needs to be pronounced like "wazza" with a very Australian accent.

    In the show, we've spent time on the current state of the platform, on dispelling a number of common misbeliefs about the product, and hopefully on answering most of the common questions that seem to get asked about it. We then ventured into Federations, Data Sync, and Reporting.

    You'll find the show (and previous shows) here: http://www.sqldownunder.com/Resources/Podcast.aspx

    Enjoy!

    PS: For those that like transcripts, we've got the process for producing them much improved now and the transcript should also be up within a few days.

    SQL Azure DB size limit increased to 150GB

    Nice to see the increase in maximum database size on SQL Azure kicked up to 150GB.

    In most enterprises I go into, there are a few databases that wouldn't fit but now the vast majority of databases would fit in SQL Azure.

    Also included in the November release are federations and an updated management portal.

    More info here: http://msdn.microsoft.com/en-us/library/windowsazure/ff602419.aspx