Azure SQL DB Usage Visualization with Power BI

It’s taken a week longer then I had hoped, but I’m finally ready for my next installment of my “blog more” effort. As many of you are aware, I’m not a data person. However, there’s no ignoring the fact that applications are either a conduit for, or generator of data. So it’s inevitable that I have to step out of my comfort zone from time to time. What’s important is to grab these opportunities and learn something from them.

The most recent example of this was when I had a partner that was faced with the deprecation of the Azure SQL DB Web/Business pricing tiers. Their solution is cost sensitive and when they looked at the Azure portal’s recommendations for the new tiers for their many databases, they had a bit of sticker shock.

With some help from folks like Guy Haycock of the Azure SQL team and a good starting blog post on SQL DB usage monitoring, the partner was able to determine that the high tier recommendations were due to their daily database backup processes. Since the new SQL tiers offer point in time restore my partner didn’t need to do their own backups and as a result their actual needs were much lower then what the portal suggested. Saving them A LOT of money.

To identify what they really needed, the partner did was pull the SQL diagnostic view data down into Excel, and then visualized the data there. But I thought there had to be an even easier way to not just render it once, but produce an on-going dashboard. And to that end, started looking at Power BI.

Which is what this post is about.

The Diagnostic Management Views

The first step is to understand the management views and how they can help us. The blog post I mentioned earlier refers to two separate managements views the “classic” sys.resource_stats view which displays data for up to 14 days with 15 minute averages and the new sys.dm_db_resource_stats which have 1 hour of data with 15 second averages.

For the “classic” view, there are two schemas: the web/business version, and the new schema. The new schema, is very similar to the new sys.dm_db_resource_stats schema which is what I’ll focus on for our examples in this blog post. The key difference is that resources_stats is located in the Azure SQL DB “master” database, and dm_db_resource_stats can be found in the individual databases.

There are four values available to us: cpu, data, log writes, and memory. And each is expressed as a percentage of what’s available given the database’s current tier. This allows us to query the data directly to get details:

SELECT * FROM dm_db_resource_stats

Or if we want to get a bit more complex, we can bundle values together per minute and chart out the mix, max, and average per minute:

    distinct convert(datetime,convert(char,end_time,100)) as Clock,
    max(avg_cpu_percent) as MaxCPU,
    min(avg_cpu_percent) as MinCPU,
    avg(avg_cpu_percent) as AvgCPU,
    max(avg_log_write_percent) as MaxLogWrite,
    min(avg_log_write_percent) as MinLogWrite,
    avg(avg_log_write_percent) as AvgLogWrite,
    max(avg_data_io_percent) as MaxIOPercent,
    min(avg_data_io_percent) as MinIOPercent,
    avg(avg_data_io_percent) as AvgIOPercent
FROM sys.dm_db_resource_stats
GROUP BY convert(datetime,convert(char,end_time,100))

Using the new dm_db_resource_stats view my experiments consistently got back 256 rows (64 minutes) which is a nice small result set. This gives you a good “point in time” measure of a databases resource usage. If you use the classic resource_stats view (which you query at the master db level), you can get back a few more rows and will likely want to filter the result set back on appropriate database_name.

Since the values represent a percentage of the available resources, this lets us determine how close to the database tier cap our database is operating. Thus allowing us to determine when we may want to make adjustments.

Power BI visualization

With a result set figured out, we’re ready to start wiring up the PowerBI visualizations. For simplicity, I’m going to use the online version of Power BI. It’s an online database, so why not an only reporting tool. Power BI online is available for free, so just head over and sign up. And while this is a sort of “getting started with” Power BI article, I’m not going to dive into everything about the portal. So if you’re new to it, you may need to poke around a bit or watch a couple of the tutorials to get started.

After logging into Power BI, start by getting connected to your database.


Note: You’ll want to make sure that your SQL Database’s firewall is allowing connections from Azure services.

On the next page, select Azure SQL Database, and click connect. Then fill in the server name (complete with part) and the database name. But before you click on next, select “Enable Advanced Options”.


This is important because Power BI online does not see the system/diagnostic views. So we’re going to use the queries we were working with above, to get our data. Just paste the query into the “Custom Filters” box.

Another important item to point out before we continue is that since we’re using an Azure SQL DB, we can’t have a refresh interval of less than 15 minutes. If you need more frequent updates, you may need to use Power BI Desktop.

With this form completed, click on “Next” and provide the Username and Password for your database and click on “Sign in”. Since our query is fairly small, the import of the dataset should only take a few seconds and once done, we’ll arrive at a dashboard with our SQL database pinned to it. Click on the database tile and we get a new, blank report that we can start to populate.

Along the right side of our report, select the “Line Chart” visualization and then check AvgCPU, MaxCPU, and MinCPU in the field list. Then drag Clock field to the Axis field. This setup should look something like this:


If done properly, we should have a visualization that looks something like…


From there you can change/resize the visualization, change various properties of it (colors, labels, title, background, etc…) by click on the paint brush icon below visualizations. You can even add other visualizations to this same report by clicking away in the whitespace and adding them like so..


It’s entirely up to you what values you want to display and how.

So there you have it

Not really much to it. The hardest part was figuring out how to connect Power BI online to the management view. I like that the Power BI desktop tool calls it a “query” and not a “filter. Less confusion there.

If I had more time, it would be neat to look at creating a content sample pack (creation of content packs is not self-service for the moment) for Azure SQL DB. But I have a couple other irons in the fire, so perhaps I can circle back on that topic for another day. J So in the interim, perhaps have a look at the SQL Sentry content pack (Note: This requires SQL Sentry’s Performance Advisor product which has a free trial).

Until next time!

A JavaScript based Windows 10 UWP & Notification Hub

Here we are, back at the blog again as promised. Admittedly, this is a week or so later then I had planned, but… best laid plans and of mice and men and all that. I’ll blame three straight weeks of travelling to visit with partners for the missed target. But all that aside, I am back to share something I’ve been thinking about blogging on for over a year. Namely Azure’s Notification Hub.

Notification Hub has bounced around a bit. First it was under the Web Apps banner, then finally landed under the Service Bus brand. Regardless of which area it falls under, I think it’s a highly underappreciated service. Perhaps this will help change that perception in some small way.


In short, Notification hub is a way to centralize your notifications across multiple platforms. Its provides you with a single API interface from which you can send notifications to a myriad of platforms (iOS, Android, Kindle, Windows, etc…). In doing this, the service handles negotiations with the platform specific APIs and also helps deal with the various throttling limits that are in place for each.

As if this weren’t enough, Event Hub (EH) also provides several higher level functions.

Device Registry – a single location that you can use to store details and metadata about individual devices. The service monitor for and purge expired device registrations. Saving you from being kicked off the notification service of various providers.

Templates – the capability to define templates that can be used to map a single message to different platforms and notification message formats.

Tags – meta-data attributes that can be used to help you target the recipient(s) of a given message. Allowing you to target a single device, a group of devices, or all devices as you see fit.

Each of these topics could be grounds for its own post or possibly even a series of posts. But today I’m interested in one specific topic, using NH to send notifications to a Windows 10 UWP (Universal Windows Platform) application written in HTML5/JS.

To get this all to work, we have to perform the following steps:

  1. Define the application in the Windows Store to and configure the Windows Notification Service (WNS)
  2. Configure the Notification Hub to talk with the application’s WNS endpoint.
  3. Have the app discover its “channel uri” which is used by the Windows Notification Service (WNS) to send notifications to the app.
  4. Using that URI, ask the Notification Hub to a unique device ID
  5. Using the device ID, register the device with the Notification Hub

So let’s get to it…

The Windows Store, WNS, and Notification Hub

To do notifications, we need to start by setting up WNS, and this requires us to declare our application at Windows Dev Center:

After logging in, click on “Dashboard” then select “Create a new app”. You’ll need to provide a name for your app (that must be unique) so it can be reserved for your use. With the app created we then click on “Services->Push notifications”. The app should already be configured for push notifications by default, but we need to get the credentials so we can configure the Notification Hub.

In the first section on the Windows Push Notification Services, locate the “Live Service site” link in the second paragraph and click it. This routes you over to the app settings for the new app. We want to capture and record two values here, the Package SID and Client secret as shown below…



Note: the values contained above are not valid. So please don’t even try. :)

Now we’ll switch over to the Azure Management Portal (classic) and create a new Notification Hub. Select the “+” at the bottom and do App Services -> Service Bus -> Notification Hub -> Quick Create. Provide a hub name, where its located, and a unique namespace for it.


After a few minutes (even faster if you’re re-using an existing namespace), the hub will be created and ready for configuration. We’ll click on its “configure” tab and enter in the Package SID and Client Secret from above.


Save this change, and switch back to the dashboard for the Notification Hub, and get its connection information for the DefaultFullSharedAccessSignature.


This use of the “full” permissions, can be a security issue. But I’ll take more about that in the next section.

With these steps done, we’ve declared a store application, created a Windows Notification Service endpoint for it, and associated the WNS with our Notification Hub. Its start to start building the app.

The Solution

Last time I worked with Notification Hub, was in September of 2014 with a Windows 8.1 app written in Angular. Since that time I’ve been actively trying to change my development habits and learn more JavaScript. So when another partner approach me about their HTML5/JavaScript application, I wanted to make another go at getting Notification Hub to work, this time with JavaScript. The challenge I soon found out was that we (Microsoft), still don’t have many materials available for building UWPs with JavaScript.

I want to fix that. To that end I’m creating a GitHub repository where I’ll be putting my explorations in creating UWPs and hopefully others will either fork this effort and perhaps even contribute back to it. I’ll start with what I hope is a fairly solid Notification Hub example.

I started by creating a Visual Studio 2015 solution that included a JavaScript Blank Universal Windows App, and a C# based ASP.NET Web API. Yeah, I know I said I’m trying to learn more about JavaScript, so I could have built the API in Node, but one battle at a time. I’m putting both in the same solution for convenience since I can then debug both side by side in the same VS instance.

The reason I created two projects was simple, security. I could have let the application directly interact with the Notification Hub api. However, this would have required me to put the shared access key for the hub into the app itself. But by placing it into a server side API, it helps protect the key from being pulled out of the app and put into malicious use.


With the solution in place, I started by building an API side first. I’ll create a single API controller that will have two methods. There will be a POST method that accepts a channel URI and returns a Notification Hub Device ID, and a PUT method that takes the Device ID and other values and registers it for notifications. You could put both these into a single operation. But there may be times where you just want to update the registration (say adding/updating a tag). So I prefer this two step process.

We start by creating a model that will be used by the controller. This class will be used when the controller is activated to help initialize a Notification Hub client. The class looks like this..

    public class Notifications
        public static Notifications Instance = new Notifications();

        public NotificationHubClient Hub { get; set; }

        private Notifications()
            //get properties that have NH connection info
            string nhConnString = Properties.Settings.Default.NotificationHubConnectionString;
            string nhName = Properties.Settings.Default.NotificationHubName;

            // create a NH client 
            Hub = NotificationHubClient.CreateClientFromConnectionString(nhConnString, nhName);

You’ll note that this code depends on two settings. Right-click on the project and add those now. The ConnectionString value we captured when we set up the notification hub. And the hub name we specified when we set up the hub. For this code to compile, we also have to make sure we use nuget to add the Microsoft Azure Notification Hub package to our project and a couple of using clauses so that everything resolves properly.

We’ll then add the controller that will contain our Notification Hub registration API. we start by adding a private NotificationHubClient variable and then populate this variable using the model we created earlier.

    public class RegisterController : ApiController
        private NotificationHubClient hub;

        public RegisterController()
            hub = Notifications.Instance.Hub;

For the post method, I start by define the request object. I create an object that is my request payload, having a single parameter that is the “channel URI”.

        public class regGetRegistrationId
            public string channeluri { get; set; }

With this, we can define the POST api method. This method will accept the channel URI (aka handle), and try to find an existing device ID for this URI in the Notification Hub device registry. If we find a match, we’ll return its device ID, and it not, we’ll create a new one. When checking existing devices, we do this in a loop to help make sure we don’t have multiple registrations using the same channel URI (which could result in the app getting the same notification multiple times).

        public async Task<HttpResponseMessage> Post([FromBody]regGetRegistrationId request)
            string newRegistrationId = null;
            string channelUri = request.channeluri;

            //todo: validate the URI is for domain

            // make sure there are no existing registrations for the channel URI provided
            if (channelUri != null)
                var registrations = await hub.GetRegistrationsByChannelAsync(channelUri, 100);

                foreach (RegistrationDescription registration in registrations)
                    if (newRegistrationId == null)
                        newRegistrationId = registration.RegistrationId;
                        await hub.DeleteRegistrationAsync(registration);

            if (newRegistrationId == null)
                newRegistrationId = await hub.CreateRegistrationIdAsync();

            return Request.CreateResponse(HttpStatusCode.OK, newRegistrationId);

Moving on to the PUT method for actually registering the device the app is running on, I again start by declaring my request payload/contract. This one has the device ID from the previous call, the platform we want to register with, the handle (in WNS, this is the Channel URI), and a set of tags that can be used for targeting notifications.

        public class DeviceRegistration
            public string deviceid { get; set; } // the device ID
            public string platform { get; set; } // what platform, WNS, APNS, etc... 
            public string handle { get; set; } // callback handle for the associated notification service
            public string[] tags { get; set; } // tags to be used in targetting notifications

Finally, we have the API method itself. It takes the payload, reformats the details into what’s needed for the Notification Hub SDK, and performs the registration. If the device is already registered, this would update/overlay the registration with new values.

        public async Task<HttpResponseMessage> Put([FromBody]DeviceRegistration deviceUpdate)
            RegistrationDescription registration = null;
            switch (deviceUpdate.platform.ToLower())
                case "mpns":
                    registration = new MpnsRegistrationDescription(deviceUpdate.handle);
                case "wns":
                    registration = new WindowsRegistrationDescription(deviceUpdate.handle);
                case "apns":
                    registration = new AppleRegistrationDescription(deviceUpdate.handle);
                case "gcm":
                    registration = new GcmRegistrationDescription(deviceUpdate.handle);
                    throw new HttpResponseException(HttpStatusCode.BadRequest);

            registration.RegistrationId = deviceUpdate.deviceid;
            registration.Tags = new HashSet<string>(deviceUpdate.tags);
            // optionally you could supplement/override the list of client supplied tags here
            // tags will help you target specific devices or groups of devices depending on your needs

                await hub.CreateOrUpdateRegistrationAsync(registration);
            catch (MessagingException e)

            return Request.CreateResponse(HttpStatusCode.OK);

In this example, we’re taking whatever tags were handed to our API method. In reality, the API may handle this type of update. Perhaps designating specific tags based off of information about the user of the app, or settings the user has defined in the app itself.

At this point, you could use a tool like Fiddler to test the API directly. You can monitor the notification hub dashboard in the Azure management portal to make sure operations are succeeding. There’s a bit of delay from when actions show up on the dashboard after being performed (seems like 5-10 minutes for the graph across the top, but an hour or so for the device registration count across the bottom). So don’t expect immediate results if you’re doing this type of testing. I’d suggest just firing off a couple REST requests to ensure your code doesn’t throw any obvious examples and then get back to coding up the rest of the app.

The client side JavaScript code

With the API in place, we can start working on the JavaScript side of things. The bulk of the code can be found in a stand-alone object I created called notification.js. This object has a single method named registration. Its a bit long, so we’ll look at it step by step.

First up, we need to look to see if the app was run previously and saved its channel URI.

        // check and see if we have a saved ChannelURI
        var applicationData = Windows.Storage.ApplicationData.current;
        var localSettings = applicationData.localSettings;

        var savedChannelURI = localSettings.values["WNSChannelURI"];
        //savedChannelURI = "re-register"; // uncomment this line to force re-registration every time the app runs

Note the last line, if uncomment it can over-write whatever the saved value was. A channel URI can change and usually won’t last more than 30 days. So the recommendation is that you get it, and save it, and only re-register with the Notification hub if it changes. If you un-comment this line of code, you can run the app over and over again while testing/debugging your code. Just make sure you remove it after you’re done.

Next, we’re going to get a new channel URI by using the Windows UWP API.

        // get current channel URI for notifications
        var pushNotifications = Windows.Networking.PushNotifications;
        var channelOperation = pushNotifications.PushNotificationChannelManager.createPushNotificationChannelForApplicationAsync();

        // get current channel URI and check against saved URI
        channelOperation.then(function (newChannel) {
            return newChannel.uri;
        }).then(function (currentChannelURI) {
            // do stuff here!

This code sets up a Push Notification client and returns a promise for when the operation is complete, This returns the URI which is then passed to the “then” promise for additional operation. Its inside that promise that the real work is done.

We start by checking to see if the channel URI we just received is any different then the one we have saved.

    if (!savedChannelURI || savedChannelURI.toLowerCase() != currentChannelURI.toLowerCase()) {

And if not, we’ll start making our calls to our rest API, starting with the call to get a device ID

                // get a Notification Hub registration ID via the API
                    type: "post",
                    url: "http://localhost:7521/api/register",
                    headers: { "Content-type": "application/x-www-form-urlencoded" },
                    responseType: "text",
                    data: "channeluri=" + currentChannelURI.toLowerCase()
                }).then(function (getIdSuccess) {

If this completes successful, inside of its “then” function, we set up the parameters for the call to the second API, passing it the device/registration ID we received back.

                    // strip the double quotes off the string, we don't want those
                    var deviceId = getIdSuccess.responseText.replace(/['"]/g, '');
                        console.log("Device ID is: " + deviceId);

                    // create object for notification hub device registration
                    // tag values used are arbitrary and could be supplemented by any
                    // assigned on the server side
                    var registrationpayload = {
                        "deviceid"  : deviceId,
                        "platform"  : "wns",
                        "handle"    : currentChannelURI,
                        "tags"      : ["tag1", "tag2"]

Note that I’m stripping of the quotes that came at the beginning and end of my deviceId. I then use it and other values to construct the remainder of the PUT request payload. Which I can now call…

                    // update the registration
                        type: "put",
                        url: "http://localhost:7521/api/register/",
                        headers: { "Content-type": "application/json" },
                        data: JSON.stringify(registrationpayload)
                        function (registerSuccess) {
                            console.log("Device successfully registered");

                            // save/update channel URI for next app launch
                            localSettings.values["WNSChannelURI"] = currentChannelURI;
                        function (error) {

Its inside the “then” for this second call that I save the new URI for the next time the app launches. It should also be noted that I’m not really doing any error handling in these samples and that in a production quality app, you really should have a few retries in this (since it will fail if there’s no network connection).

With this object and its method declared, we can now wire it up to the application. I put it in its own JavaScript file so it could be easily reused by another project, so we’ll want to add it to the default.html page of our new app.

    <!-- UWP___JavaScript references -->
    <link href="/css/default.css" rel="stylesheet" />
    <!-- TAG: #notificationhubjs -->
    <script src="/js/notifications.js"></script> <!-- Notification Hub Integration -->
    <script src="/js/default.js"></script>

Note hose this was inserted before the default.js file. This helps ensure that the object is available to default.js which is the entry point for my application. Inside the default.js we’ll add the code to access our new method.

    // TODO: This application has been newly launched. Initialize your application here.

    // TAG: #notificationhubjs			    
    uwpNotifications.registerChannelURI(); // register the app for notifications

By placing this in the block of the default.js after the “newly launched” I’ll ensure this code is called when the app is launched, but not each time the app is resumed. Which is my intent. Now all that remains is to associate my app with the app I created in the store.

Store Association & testing.

Fortunately, since we’ve already registered our application with the store, visual studio makes this step really easy. Right-click on the UWP application in the solution explorer, and select Store -> Associate App with the Store.


You’ll get walked through a wizard that will log you into the store and let you select an existing app, or create a new one. Select the app you created above, and associate it with your app.

Note: If you are using a public repository such as GitHub, don’t check in your code after making the association. By associating the project with the store, you are altering the application manifest and adding in a file that contains store specific details. I recommend doing this final stage/testing in a separate branch to allow you to more easily merge back changes without bringing over those details.

With this change done, we’re can run the app and test our notifications. Start by first launching the Web API in debug mode. I found it was easiest to set that project to my start-up project. Next, run the UWP application in debug mode. You’ll find you can set breakpoints in both apps and they’ll get hit. Which makes doing basic debugging of the code a lot easier.

Once you know the app has launched and appears to have registered with the Notification Hub properly, we can go back to the Windows Management portal and use the notification hub to test our application. Be sure to set the platform to “Windows” and the notification type to “Toast”.


If we’ve done everything correctly, you should receive a toast notification. What’s even better, is that if you stop debugging, you can still send the notification. This is one of the things that makes notifications awesome. Your app doesn’t even need to be running for you to still send notifications to it. And with Windows 10, you can even allow the user to take actions on those notifications. But that’s likely a subject left for another day.


So hopefully this has helped stitch a few topics together in a way that’s helpful. As I mentioned earlier, I’ve put all this code up on GitHub for you to borrow and reuse if you see fit. Over time I hope to grow that project, so if you want to skip to the parts covered in this blog, just clone the repository, and look for the tag #notificationhubjs.

Until next time!


Introduction to the Service Fabric

I’ve spent most of the last 7 years focused on the cloud. During that time I’ve worked with customers of all sizes in a myriad of industries to adopt cloud platforms and build solutions they can then provide to their customers. If I’ve learned nothing else, it’s that continuous innovation is necessary and EVERY cloud solution could be improved.

By now, you’re hopefully familiar with the concept of DevOps. This blending of developer and IT pro responsibilities is at the center of many cloud infrastructures. Unfortunately, most approaches still don’t offer a good way to separate these two fairly disparate viewpoints. They either require the developer to learn too much about infrastructure, or the IT Pro needs to know too much about building deployment scripts. The challenge is to satisfy the unique needs of both audiences, while still allowing them to stay focused on their passions.

What we need is a platform the developer can build applications for that can scale, provide resiliency, and be used to easily deliver performant, stateful, and highly available services. This same platform also needs to be something that can be deployed either in the cloud as a managed service, or set up on-premises using existing compute resources. It also needs to be something that can allow the IT Pro to worry less about the applications/services that are on it, and more about keeping the underlying infrastructure healthy.

To that end, I’m pleased to talk about our newest innovation, Microsoft Azure Service Fabric. But before I dive into exactly what Service Fabric is, let’s talk about the road that’s led us here.

Infrastructure as a Service

A natural evolution from managed infrastructure, it’s easy to argue that IaaS simply built upon the hypervisor technologies we’d already been using for years. The only real difference here was the level of automation taking care of common tasks (such as relocating virtual machines in case of hardware failure). This solution also had the benefit that to a degree, you could stand up your own private IaaS cloud.

The issue here however was that many of the old problems still remained. You had to manage the virtual machines, patching/upgrading them. And if you deployed an application to this type of infrastructure, there was still a lot of work to be done to make it resilient in case of a failure. Especially in scenarios where the application had any type of state information that you didn’t want to lose.

When you add in common tasks like rolling upgrades, application version management, etc… IaaS really starts to look like its predecessor. IaaS did reduce the need to manage hardware, but still didn’t address how we build highly scalable, resilient, solutions.


Linux (and you could easily argue that more specifically, Docker) brought us containerization. Containers are deployed into machines (either physical or virtual), as self-contained, mostly isolated components. This allowed for individual services to be quickly and easily combined into complex solutions. The level of automation available pushed the underlying machines further back on the stack. So while the underlying shortcomings of IaaS still exist, the automation allows us to work about it even less.

In addition to the composable nature of container based solutions, this technology also offered the advantage of less bloat. If you used virtual machines to isolate your services, you have the overhead of a full operating system for each instance. Containers are much lighter weight, sacrificing a degree of resource isolation to save resources overall.

Unfortunately, this approach is still about putting components on infrastructure and not really about applications that comprise them. So many of the problems with application lifecycle management (ALM) that we saw with IaaS, still exist. And while there are solutions that can be layered on top of containerization to help manage some of this. But these add even more complexity.

Platform as a Service

PaaS, be it Microsoft Azure Cloud Services, or solutions like Heroku, tried to solve some of this by pushing the underlying OS so far into the background that it nearly vanished. At least until something went wrong. And unfortunately, in the cloud there’s only one absolute, failure is inevitable.

Don’t get me wrong. I still love the promise of platform as a service. It is supposed to give us a place to deploy applications where we can depend on the platform to take care of the common tasks like scalability, failover, rolling upgrades. Unfortunately, in most cases PaaS solutions fell a bit short of their goals. Version management was mostly non-existent and if you wanted things to be truly resilient, you needed to externalize any state information which required a dependency on externalized caches or data stores.

Another key challenge is that PaaS adoption was often done using traditional n-tier architecture patterns. The result was that you would design a system comprised of components for the different layers, then deploy them as individual pieces. While you could scale the number of copies/instances of the solution components based on needed capacity, this pattern still often leads to wasted resources as each instance is often under-utilized.

Enter the Service Fabric

We (Microsoft) watched customers and often times ourselves struggle with these problems. And more importantly, we watched what was being done to overcome them. At the same time, we were also learning from the solutions we were building. Over time, common patterns became visible and the solutions for them were industrialized. It’s out of these learnings that we began to craft the Service Fabric.

The goal of Service Fabric, as the name applies, is to provide a framework that allows services to be easily stitched together into complex solutions. It will manage the services by helping them discover and communicate with each other while also helping them maintain a healthy state. The services could be a web application, a traditional service, or even some executable.

The fabric intends to specifically solve the following challenges:

–          Provide a platform into which services are deployed. Reducing the need to worry about the underlying virtual machine(s)

–          Increase the density of deployed services to decrease latency without increasing complexity

–          Manage deployed services, providing failover, version management, and discoverability

–          Giving services a way to coordinate actions such as data replication to increase resiliency when state needs to be maintained

And while we’re just now announcing this new offering, we’ve already tried the waters with this approach ourselves. Existing Azure Services such as Service Bus Event Hubs, Document DB, and the latest version of Azure SQL Database are being delivered on Service Fabric.

What is the Service Fabric

Service Fabric is a distributed systems platform that allows developers building build services and applications to avoid complex distributed infrastructure problems and focus instead on implementing their workloads and business logic while adding scalability and reliability. It also greatly reduces the burden on application administrators and IT Operators by implementing simple workflows for provisioning, deploying, patching, and monitoring services. Providing first class support for the full application lifecycle of cloud applications for initial deployment to eventual decommissioning.

At the heart of these buzzwords is the concept of a Service Fabric Cluster. A cluster is a collection of servers (physical or virtual) that are stitched together via the Service Fabric software. (did you see what I did there grin) Individually, these servers are referred to as “nodes”. The Service Fabric software keeps track of each node and the applications that have been deployed to the cluster. Its monitoring that allows the fabric to detect when a node fails, and move the instances of an application to another node. Thus providing resiliency for your application.

One of the interesting things about the Service Fabric is that it’s headless.  The Service Fabric software on each node synchronizes with the instances running on other nodes in the cluster. In essence, keeping track of the state of its own node, while also sharing that state with the other nodes. Those nodes in turn share their state back out. This is important because when you want to know anything about the cluster, you can simply query the Service Fabric management API on any node and you’ll get the same answer.

When you deploy an application to the cluster, a notification is sent to one of the nodes in the cluster. This node in turn looks at what it knows of the other nodes, and determines where to place the various parts of the application. Notifications are then in turn sent to the selected nodes so they can take the appropriate actions.

But before we go to deep in this, let’s look at a Service Fabric Application.

Service Fabric Application Model

Service Fabric operates on the notion that applications are composed of services. But the term ‘service’ is used fairly loosely. A service could be an application listening for a request, or a console application that runs in a loop, performing some action. A service is really just a process that you want to run.

Services in turn, are composed of three parts: code, config, and data. Just as you would expect applications and services to be versionable, Service Fabric allows you to version these components. This is extremely powerful because now you can deploy updates of any of these components independently of the others.

The service acts as a containerized, functional unit. It can be developed and revised individually, start/run independently, and scaled in isolation from other services. But most importantly, the services, when acting together, form the higher level applications that are needed for today’s cloud scale solutions.


This separation provides several advantages. First off, we can easily upgrade and scale individual instances of the services as needed. Another key advantage for Service Fabric is that we can deploy as many applications into the cluster as its resources (cpu, memory, etc…) can support. This can even be multiple, different versions of the same application or service. But each is managed separately.

But this isn’t the end of the story. This example was fairly simple because the services in question are not stateful. And a solution doesn’t have to get very complex because the need to store state arises.

Stateless vs Stateful Services

As mentioned earlier, a key failure with most cloud solutions is that they attempt to follow a traditional “n-tier” architecture. The result is that you have clear separation of concerns and externalized data stores. This in turn introduces both complexity and latency since the solution traverses various artificial boundaries such as remote calls to read/write data, or introducing more moving pieces such as a caching layer.

To address this issue, Service Fabric has two types of services: stateless and stateful. A stateless service is entirely self-contained and didn’t do anything besides write some output to the console. But the type of service, a stateful service, has something that stateless services lack. A stateful service, as its name implies can store date and more importantly, leverage the Service Fabric to replicate that data across multiple instances of the service. This speeds up access because we can now durably store information within the fabric cluster and since the data replicated, we don’t sacrifice resiliency. We also simplify the solution by reducing its dependency on external services. Any one of which could impact the availability of our application.

Stateful Service Fabric services (say that 5 times fast), work because they leverage the cluster for discoverability and communication. Each Stateful Service should run at least three instances. The cluster will select one of these copies to be the primary, and the others will be secondary replicas. The instances of the service then leverage the Service Fabric to keep the replicas in quorum. As transactions occur against the primary, they are sent over and applied to each of the replicas.

This works because when you request an endpoint for the service, the fabric will automatically route the request to the elected primary. When changes occur (inserts, updates, deletes), these transactions are then replayed on the secondary replicas. Each replicate will store its current state either in memory, locally on disk, or optionally remotely (although this approach should be used with caution for the reasons we mentioned earlier).

Internally, the stateful service leverages another Service Fabric concept, a distributed collection. Its these collections that actually do the task of storing any data and working with the Service Fabric to ensure replication is performed. The service container provides an activation point for the collection, while simultaneously providing a process that can host any service endpoints that allow for interaction with the collection. What’s important to note here is that the only part of any application in the cluster that can interact with a given collection is its hosting service.

Now some stateful services may operate just fine using a single set of primary/secondary instances. But as demand increases, you may need to scale out the service to allow for even greater throughput. This is where “partitions” come into play. When the service registers the distributed collection with the Service Fabric, it defines the number of partitions and the key ranges for those partitions.

It should be stressed that the partition is a logical construct only. You could have a distributed collection that’s running on a single primary that has 10,000 partitions. That same collection could be spread across 10, 20, or more primary instances. It’s all based on your demand. How this is done is a bit more of an advanced topic, so we’ll leave that for another time.

PaaS for the new generation of cloud solutions

So that is it for this introduction. We’ve barely scratched the service of what this framework can help you accomplish. So we’re hoping you’ll join us for some of the other sessions we have planned that explore various aspects of Service Fabric more deeply.

Thank you, and until next time!

An Anniversary, and a restart

This month my 3rd anniversary at Microsoft (start date was October 15th, 2012). Three years working for a company I believe in, focused on a topic I’m passionate about (cloud), and working with many great partners and fellow geeks along the way. Its been a great experience and one I hope till continue for some time to come. I’ve been able to explore new opportunities and stretch myself a bit. This has sometimes proven successful, sometimes not. But as with all things, I learned a lot.

One thing that has suffered, is this blog. Its been almost 8 months since my last post. Part of this been due to “the demands of the service” and part has been a lack of me feeling I really had anything to share. I was learning, but it was mostly focused on skills I hadn’t previously picked up (JavaScript, AngularJS, DocDB, etc…). In these, I wasn’t sure I really had much to contribute. So I focused on the partners/projects in front of me and let the “evangelism” side of my job slide a bit. I haven’t even been doing much speaking.

This month, I intend to start changing this. To get back to my desire to help others and “spread the word”. All this while I help the partners I’m assigned to and my colleagues. Back on July 1st, I moved to the Commercial ISV team. This means that I have a portfolio of partners I’m focused on (4 large software vendors that are focused on providing solutions that in some way serve local, state, or federal government). I’m also focused on Windows 10 UWP, Office 365, and of course Microsoft Azure. What you can expect to see from me over the course of the next few months are topics like the Azure Resource Monitor, Windows 10 UWP with Cortana and Azure Services integration (really keen to play with the new Azure AD B2C stuff), Windows containers, and Media streaming. IOT may also come up, but despite it being such a key industry focus, isn’t high on my list. If things to really well, I may even have some Hololens stuff to share by next summer. crossingfingers

That’s really all I have to say for the moment. But look for more soon (already working on a posts around DocDB and Azure SQL DB performance analysis w/ Power BI). Meanwhile, look for me on the twitters and let me know if there’s something you’re interested in hearing more about. Otherwise, I’ll be up-skilling and prepping to pass the Azure Architecture exam.

Until next time!




SAS, it’s just another token

Note: Please bear with me, I authored this post in Markdown. :)

I’ve been trying to finish this post since September of 2014. But I kept getting distracted away from it. Well this lovely (its 5 degrees Fahrenheit here in Minnesota) Saturday morning, it IS my distraction. I’ve been focused the last few weeks on my new love, the Simple Cloud Manager Project, as well as some internal stuff I can’t talk about just yet. Digging into things like Ember, Jekyll, Broccoli, GitHub Pages, git workflows, etc… has been great. But it’s made me keenly aware of how much development has leapfrogged my skills as a Visual Studio/.NET centric cloud architect. With all that learning, I needed to take a moment and get back to something I was a little more comfortable with. Namely, Azure services and specifically Service Bus and Shared Access Signatures (SAS).

I continue to see emails, forum posts, etc… regarding to SAS vs ACS for various scenarios. First off, I’d like to state that both approaches have their merit. But something we all need to come to terms with is that at their heart, both approaches are based around a security token. So as the name of this blog article points out, SAS is just a token.

What is in a SAS token?

For the Azure Service Bus, the token is simply a string that looks like the following


Within this string, you see a set of URL encoded parameters. Let’s break them down a bit…

SharedAccessSignature – used to identify the type of Authorization token being provided. ACS starts with “WRAP”

sr – this is the resource string we’re sharing access to. In the example above, the signature is for anything at or under the path ““

sig – this is a generated, HMAC-SHA256 hash of the resource string and expiry that was created using a private access key.

se – the expiry date/time for the signature expressed in the number of seconds since epoch (00:00:00 UTC on January 1st, 1970)

skn – the policy/authorization rule who’s key is was used to generate the signature and who’s permissions determine what can be done

The token, or signature, is created by using the resource path (the url that we want to access) and an expiry date/time. A HMAC-SHA256 hash using the key of a specify authorization policy/access rule is then generated off of those parameters. In its own way, using the policy name and its key is not that different then using an identity and password. And like an ACS token, we have an expiry value that helps ensure the token we receive can only be used for a given period of time.

Generating our own Token

So the next logical question is how to generate our own token. If we opt to use .NET and have the ability to leverage the Azure Service Bus SDK, we can pull together a simple console application to do this for us.

Start by creating a Console application, and adding some prompts for a parameter to it so that the main method looks like this…

static void Main(string[] args)
Console.WriteLine("What is your service bus namespace?");
string sbNamespace= Console.ReadLine();

Console.WriteLine("What is the path?");
string sbPath = Console.ReadLine();

Console.WriteLine("What existing policy would you like to use to generate your signature?");
string sbPolicy = Console.ReadLine();

Console.WriteLine("What is the policy's secret key?");
string sbKey = Console.ReadLine();

Console.WriteLine("When should this expire (MM/DD/YY HH, GMT)?");
string sbExpiry = Console.ReadLine();

Console.WriteLine("Press any key to exit...");

The first parameter we’re going to capture and save is the namespace we’re wanting to access without the “” part. Next, is the path to the Service Bus Entities we want provide access to. This can be a specific entity such as a queue name or as I mentioned last time, a partial path to grant access to multiple resources. Then we need to provide a named policy (which you can set up via the portal), and one of its secret keys. Finally, you will specify when this signature will need to expire.

Next, we need to transform the expiration time that was entered as a string into a Timespan (how long does the SAS need to ‘stay alive’. We’ll insert this right after we read the expiration value…

// convert the string into a timespan...
DateTime tmpDT;
bool gotDate = DateTime.TryParseExact(sbExpiry, "M/dd/yy HH", enUS, DateTimeStyles.None, out tmpDT);
if (!gotDate)
Console.WriteLine("'{0}' is not in an acceptable format.", sbExpiry);

Now we have all the variables we need to create a signature, so it’s time to generate it. For that, we’ll use a couple classes contained in the .NET Azure Service Bus SDK. We’ll start by adding it to our project using the instructions available on MSDN (so I don’t have to retype them all here).
With the proper references added to the project, we add a simple using clause at the top..

using Microsoft.ServiceBus;

Then add the code that will create the SAS token for us right after the code that created out TimeSpan.

var serviceUri = ServiceBusEnvironment.CreateServiceUri("https", sbNamespace, sbPath).ToString().Trim('/');
string generatedSaS = SharedAccessSignatureTokenProvider.GetSharedAccessSignature(sbPolicy, sbKey, serviceUri, expiry);

And there we have it. A usable SAS token that will automatically expire after a given period of time, or that we can revoke immediately by removing the policy on which its based.

But what about doing this without the SDK?

Lets start by looking at what the Service Bus SDK is doing for us. Fortunately, Sreeram Garlapati has already written some code to generate a signature.

static string CreateSasToken(string uri, string keyName, string key)
// Set token lifetime to 20 minutes. When supplying a device with a token, you might want to use a longer expiration time.
DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
TimeSpan diff = DateTime.Now.ToUniversalTime() - origin;
uint tokenExpirationTime = Convert.ToUInt32(diff.TotalSeconds) + 20 * 60;

string stringToSign = HttpUtility.UrlEncode(uri) + "n" + tokenExpirationTime;
HMACSHA256 hmac = new HMACSHA256(Encoding.UTF8.GetBytes(key));

string signature = Convert.ToBase64String(hmac.ComputeHash(Encoding.UTF8.GetBytes(stringToSign)));
string token = String.Format(CultureInfo.InvariantCulture, "SharedAccessSignature sr={0}&sig={1}&se={2}&skn={3}",
HttpUtility.UrlEncode(uri), HttpUtility.UrlEncode(signature), tokenExpirationTime, keyName);
return token;

This example follows the steps available at this SAS Authentication with Service Bus article. Namely:
– use the time offset from UTC time January 1st, 1970 in seconds to set when the SAS should expire
– create the string to be signed using the URI and the expiry time
– sign that string via HMACSHA256 and the key for the policy we’re basing our signature on
– base64 encode the signature
– create the fully signed URL with the appropriate parameters

With the steps clearly laid out, its just a matter of converting this into the language of your choice. Be it javascript, objective c, php, ruby… it doesn’t really matter as long as you can perform these same steps.

In the future, its my sincere hope that we’ll actually see something in the Azure Service Bus portal that will make this even easier. Perhaps even a callable API that could be leveraged.

But what about “Connection Strings”

This is something I’ve had debates about. If you look at most of the Service Bus examples out there, they all use a connection string. I’m not sure why this is except that it seems simpler because you don’t have to generate the SAS. The reality is that the connection string you get from the portal works much like a SAS, except that is lacks an expiry. The only way to revoke a connection string is by revoking the policy on which its based. This seems fine, until you realize you only get a handful of policies per entity to creating hundreds of policies to be used by customers is a tricky proposition.

So what are you to do when you want to use a SAS, but all the examples use a connection string? Lets start by looking at a connection string example. First with the connection string.


This string has several parameters:

sb – the protocol to be used. In this case its ‘sb’ which is Service Bus shorthand for “use AMQP”.

namespace – the URL we’re trying to access

SharedAccessRuleName – the policy we’re using

SharedAccessKey – the policy’s secret key

The common approach is to put this string into a configuration setting and with the .NET SDK, load it as follows…

EventHubClient client = EventHubClient.Create(ConfigurationManager.AppSettings["eventHubName"]);


string connectionString = CloudConfigurationManager.GetSetting("Microsoft.ServiceBus.ConnectionString");
var namespaceManager = NamespaceManager.CreateFromConnectionString(connectionString);

These are common examples you’ll see using the connection string. But what if you want to use the SAS instead… For that, we go up a level to the Service Bus MessagingFactory. Both of the examples above abstract away this factory. But we can still reach back and use it.

We start by creating the URI we want to access, and a SAS token object:

Uri runtimeUri = ServiceBusEnvironment.CreateServiceUri("sb", , string.Empty);
TokenProvider runtimeToken = TokenProvider.CreateSharedAccessSignatureTokenProvider());

Alternatively, we can create the token provider using a policy and its secrete key. But here we want to use a SAS token.

Now its just a matter of using the message factory to create a client object…

QueueClient sendClient = mf.CreateQueueClient(qPath);

Simple as that. A couple quick line changes and we’ve gone from a dependency on connection strings (ick!), to using SAS tokens.

So back to SAS vs ACS

So even with all this in mind, there’s one argument that gets brought up. The SAS tokens expire.

Yes, they do. But so do ACS and nearly all other claims based tokens. The only real difference is that most of the “common” security mechanisms support a way of renewing the tokens. Be this asking the user to log in again, or some underlying bits which store the identity/key and use them to request new tokens when the old is about to expire. The reality is that this same pattern needs to be implemented when you’re doing a SAS token.

Given what I’ve shown you above, you can now stand up your own simple token service that accepts a couple parameters (identity/key), authenticates them, selects the appropriate policy and URL for the provided identity, and then creates and returns the appropriate SAS.

The client then implements the same types of patterns we can already see for things like mobile notification services. Namely to store tokens locally until they’re about to expire. When they are approaching their expiry, reach back out using our credentials and ask for an updated token. Finally, use the token we been provided to perform the required operations.

All that’s really left up to you is to determine the expiry for the token. You can have one set value for all tokens. You may also opt to have tokens for more sensitive operations expire faster. You have that flexibility.

So until next time, enjoy SAS’s. Just please…. don’t be afraid of them.

SimpleFileFetcher for Azure Start up tasks

I know some of you are waiting for more news on my “Simple Cloud Manager” Project. Its coming, I swear! Meanwhile, I had to tackle one fairly common Azure task today and wanted to share my solution with you all.

I’m working on a project where we needed an Azure PaaS start-up task that would download a remote file and unzip it for me. Now there’s several approaches out there of varying degrees of complexity. Powershell, native code, etc… But I wanted something even simpler, a black box that I could pass parameters too that required no additional assemblies/files to work. To that end I sat down and spent about 2 hours crafting the “Simple File Fetcher”.

The usage is fairly simple: simplefilefetcher -targeturi:<where to download the file from> -targetfile:<where to save it to>

You can also optionally specify a parameter that tells it to unzip the file, ‘-unzip’, and another that will make sure downloaded file is deleted, ‘-deleteoriginal’.

I spent most of the 2 hours looking at and trying various options. The final product was < 100 lines of code and now that I know how to do it, would only likely take me 10-20 minutes to rebuild (most of that spend debugging the argument parsing). So instead of boring you all with an explation of the code, I’ll just share it along with a release build of the console app so you can just use it. :)

Until next time!

Azure Tenant Management

Hey everyone. I’m writing this post from Redmond, WA. I’m in here on the Microsoft campus this week to meet with some of my colleagues and explore options for my first significant open source project effort. We’re here to spend three days trying to find ways to manage the allocation of, and monitor the billable usage of Azure resources. There are two high level objectives for this effort:

  • Allow non-technical users to provision and access Azure resources without the need to understand Azure’s terminology and even granting direct access to the underlying Azure subscription
  • Monitor the billable usage of the allocated resources, and track against a “cap”

On the surface this seems pretty simple. That is, until you realize that not all the Azure services expose individual resource usage, and of the ones that do, they all do it separately. It gets even more complicated when you realize that you may need to do things like have users “share” cloud services and even storage accounts.

Couple examples

So let’s dive into this a little more and explore a couple of the possible use cases I’ve heard from customers.

Journalism Students

Scenario: We have a series of journalism students that will be learning to provision and maintain a online content management system. They need someplace to host their website, upload content, and need GIT integration so they can version any code changes they make to their site’s code. The instructor for the course starts by creating an individual web site for them with a basic CMS system. The student will then access this workspace as they perform their coursework.

Challenges: Depending on the languages they are using, we may be able get by Azure Web Sites. But this only allows us up to 10 “free” web sites, and what happens to the other students. Additionally, students don’t know anything about the different SKUs available and just want things that work, so do we need to provide “warm-up” and auto-scaling? Additionally, since the instructor is setting up the web sites for them, we need a simple way for the instructor to get the resources provisioned, and give the students access to it without the instructor needing to even be aware of the Azure subscriptions. We also need to track compute and bandwidth usage on the individual web site.

Software Testers

Scenario: A small company has remote testers that perform quality assurance work on software. These workers are distributed but need to remote into Windows VMs to run tests. Ideally, these VMs will be hosted “in the cloud”, and the company wants a simple façade whereby the workers can select which software they need to test and then provision a virtual machine for them. The projects being tested should be “billed back” for the resources used by the testers and the testers work on multiple projects. Additionally, the testers should be able to focus on the work they have to do, not how to manage and provision Azure resources.

Challenges: This one will likely be Azure Virtual Machines. But we need to juggle not only compute/bandwidth, but track impact on storage (transactions and total amount stored) as well. We also need to be able to provision VMs from a selection of customer gallery images and get them running for the testers, sometimes across subscriptions. Finally, we need to be aware of challenges with regards to VM endpoints and cloud services if we to maximize the density of these VMs.

Business Intelligence

Scenario: Students are learning to use Oracle databases to analyze trends. The instructor is using the base Oracle database images from the Azure gallery but has added various tools and sample datasets to them for the students to use. The students will use the virtual machines for various labs over the duration of the course and each lab should only take a few hours.

Challenges: If these VMs were kept running 24×7, it would costs thousands of dollars per month per student. So we need to make sure we can automate the start and stop of the VMs to help control these costs. And since the Oracle licensing fees appear as a separate charge, we need to be able to predict these as well based on current rates and the amount of time the VM was active.

So what are we going to do about it?

In short, my plan is to create a framework that we will release via open source to help fill some of these gaps. A simple, web based user interface for accessing your allocated resources, some back end services that monitor resource usage and track that against quotas set by solution administrators. Underneath all that, a system that allows you to “tag” resources as associated with users or specific projects. If all goes well, I hope to have the first version of this framework published and available by the end of March, 2015 that will focus on Azure Web Sites, IaaS VMs, and Azure Storage.

However, and this is the point of my little announcement post, we’re not going to make you wait until this is done. As this project progresses, I plan to regularly post here and in January we’ll hopefully have a GIT repository where you’ll be able to check out the work as we progress. Furthermore, I plan to actively work with organizations that want to use this solution so that our initial version will not be the only one.

So look for more on this in the next couple weeks as we share our learnings and plans. But also, let me know via the comments if this is something you see value in and what scenario you may have. And oh, we still don’t have a name for the framework yet. So please post a comment or tweet @brentcodemonkey with your ideas. J

Until next time!


Get every new post delivered to your Inbox.

Join 1,286 other followers