Techfuse, a New Laptop, and How Microsoft Azure Helped Save the Day

24 04 2014

On Tuesday, April 22, I had the opportunity to speak at the Techfuse conference in Minneapolis. I was presenting a session on the differences between tabular and multidimensional models with a focus on the developer’s experience. My deck has tenTechFuse_logo  slides including title, references, and bio. The rest of the time is spent in Visual Studio building out equivalent models in using SSAS Tabular and SSAS Multidimensional.

The previous week, I was issued a new laptop from my company, a Dell 7440. This is a very nice machine and I had it all set for the presentation. About 11 AM (I was scheduled to speak at 1:15 PM) it occurred to me that I did not recall seeing a VGA port only HDMI. Next question, did the projectors at the convention center support HDMI? Answer, No. Now I had about an hour and a half to resolve this issue. Simple, I decided to head downtown and get the convertor from Office Depot. This was about 8 blocks away. I could do that and get some exercise in.

I took off at about 11:30. First, I stopped at Target, it was closer. No luck. So on to Office Depot. Keep in mind that Office Depot sells laptops like mine with only HDMI support and it stands to reason that they would have the converter. No such luck. I was able to get the HDMI converted to DVI, but that would not help as I later found out. They directed me to Radio Shack where I promptly picked up a DVI – VGA converter. Now I have three pieces that when strung together should support my needs. I headed back to the convention center and arrived with 30 minutes to spare. Working with the AV guy, we got it all plugged in only to still have it not work. Turns out you need a convertor to convert the digital signal to analog for use in the older projectors. Now what?

The moderator for my room offered me her laptop to use for the presentation. Which was AWESOME! So now I have a way to give the presentation, all ten slides. However, she did not have Visual Studio with SSDT for BI and SQL Server installed. Which was fine, because I didn’t expect her to.

Here is where Azure comes in. I had created a VM with SQL Server Tabular installed along with Visual Studio 2012 and the SQL Server Data Tools for BI. So, I firedth9CGBMYN6 up the VM right before I gave the presentation. I warned the crowd about what had happened and decided to push the demos to the end of the presentation so everyone could leave if nothing worked and all the material could be covered.

I was able to get into the VM, fire up Visual Studio. Since the demo was a live build of a tabular model and multidimensional model, I used a database I had created in SQL Azure as the data source and we built it the models live. Granted we were not able to do a complete multidimensional model because the database was not formatted as star schema, but it helped highlight the difference between what needs to be done prior to development. Overall it went very well (I think, surveys are forthcoming…). At the end of the day, without the work I had been doing in Azure I would not have been able to demo and it would have been a very short presentation.

Some lessons learned -

  • Be sure to have what you need to support presenting in a variety of scenarios. I should have made sure to have a converter prior to the conference as most convention centers and other facilities haven’t upgraded their projectors yet.
  • I will likely set up Azure VMs to support more demos. Just in case. It is always good to have a backup plan though a wireless connection would have painful to do that on.
  • Roll with it. Don’t give up, try to make the best of a bad situation. People understand things don’t always go perfectly. At the end of the day, I came to talk about multidimensional and tabular model development. I could have opened the floor up for discussion and did Q&A. Make the most of every situation.

Logging into Azure and Office365 with Different Accounts

14 03 2013

I have been doing some work on the Modern Apps Live! content that required me to use both an Office365 account and an Azure account.  My Azure work is currently associated with my Microsoft Account (formerly known as my Live ID).  On the other hand, I am working with a Microsoft Office365 account which I have been unable to tie to my current account at this point.

While I was working with Office365, I was trying to open my Azure account to get some storage info that I wanted to use.  It told me I was already signed in with a different user ID and it did not have access to my Azure portal as noted in the image below.


Obviously I did not want to sign out, so I started looking for options.  The IT Director at Magenic, Dave Meier, mentioned he was having the same issue with multiple Microsoft accounts. So, we determined this was somehow related to IE so I installed Google Chrome.  By using Chrome I was able to work around the issue.  However, Dave came across the following article regarding this change in behavior starting in IE 8  Turns out they changed how they manage sessions in that version.

imageThe article brings up a couple of options to work around.  One is using Alt-F-I to change the session.  Kind of annoying as I actually use my mouse (some of you keyboard junkies will like that solution).  So I used the shortcut option and created a shortcut for IE on my Windows 8 desktop which uses the switch –noframemerging.  I added this to my Taskbar, and voila, I have an easy way to open a new session browser which ignores my other session.  Keep in mind that you are essentially running two different sessions in IE which means that you will have two sets of credentials active.  Be sure to keep track of which is which.

Also, as a quick sanity test, Google Chrome works the same way.image  Even when I open a different Chrome instance, it keeps my session state. So, it appears the IE shortcut is a nice way to handle this issue.  Although, it is nice to know I can use a Chrome instance and and IE instance if that is my preferred method.

Add a comment if you try this in FireFox and want to chime in.  I really only want to use one browser, so Chrome is still one more than I want.

UPDATE:  You can also use InPrivate or Incognito modes accomplish the same thing.  Thanks to Rocky Lhotka ( T | B ) for pointing this out.

Join Me at Modern Apps Live! Las Vegas

5 02 2013


If you are familiar at all with Visual Studio Live! then you should check out this new conference.  At the Vegas conference, I am participating in this unique three-day event which is a series of sessions that build on each other as the conference progresses called Modern Apps Live! The goal is to do a end-to-end application build during which the presenters build the application as well as focus on Application Lifecycle Management, best practices, tools, and a variety of technologies.

I will be presenting on the database design and business intelligence components of the solution and would love to see you there.  To make this even sweeter, if you register with the following code, MVSPK3, you will qualify for one of the following offers:

  • The 5 day all-access Best Value Conference Package for just $1,595 – a savings of $500.00 off the standard price of $2,095! (*Includes pre and post conference events.)


  • The 3 day Modern Apps Live! Conference for just $1,295 – a savings of $400.00 off the standard price of $1,695

This is for new registrants only.  Make sure to use the code by February 27, 2013, to take advantage of the full discount.

Also keep in mind that you will be able to take advantage of the Visual Studio Live! sessions as well as this is a cohosted conference.

I hope to see many of you there!

PASS Summit 2012 Wrap Up

13 11 2012

Wow, what a week. Once again, PASS put on a great event that provided much in the way of events and training for the SQL Server community. If you followed my countdown you know some of what I love about PASS. Last year I blogged everyday but I did not do that this week. So, what was different for me? Well, for one I volunteered much more this year than last and I was privileged to speak twice. I spent more time meeting new people and catching up with friends and that was great as well. Enjoy my wrap up from my week.

Tuesday – Leadership Meetings, Welcome Reception, and some Karaoke

Before the event officially kicked off, I joined community leaders from around the world for a series of leadership meetings. First we had a meeting on SQL Saturdays which was an opportunity to see the immense growth of these free training events around the U.S. and throughout the world. What a great opportunity for SQL Server professionals to improve their skills and for those passionate about the community to improve their abilities by leading these events. Many ideas were shared among the team including a panel on how to effectively run a SQL Saturday on a tight budget.

Once that was completed, the Regional Mentors enjoyed a lunch together and an opportunity to share what we do to support the user groups in our regions. I particularly enjoyed the fact that I was able to spend some time with Regional Mentors from Germany, Holland, and Portugal. This highlighted further the international scope and reach of PASS. This was followed by the Chapter Leaders meeting. That meeting was held as a series of round tables that the chapter leaders could move through. I was working at the table focused on leadership with Ryan Adams ( B | T ) from the North Texas SQL Server User Group – NTSSUG. We had a number of good conversations around building leadership teams for user groups and what is needed to have an effectively led user group. Check out the NTSSUG site for the by-laws sample we discussed multiple times.

All of these meetings were followed up with the Welcome Reception, which I made a small portion of as I was trying to drop my backpack at my hotel and work my way back there. After the reception, I headed out to Bush Gardens with a number of others. During that time, Jes Borland ( T ) managed to get a microphone in my hand and I had my first round of karaoke. Yes, I actually did sing and had fun doing it. All-in-all, it was a good time had by all.

Wednesday – SQL Around the World, Microsoft Announcements, Tabular Models, and Magenic Team Dinner

This was the true kick off to the event. For many, they looked at the key note as the kick off. Before that even began, I was working in the Community Zone encouraging people to participate in the SQL Around the World community activity. It was a great game. You needed to find 10 people from 10 different countries and find out something interesting about them or their country. I found a dancer and someone who had ridden a cheetah as a kid. I also surprised someone from the Czech Republic when she mentioned her home town only to have me let her know I had been to her home town many years ago. It was a fun conversation. If you did this and have other cool stories let me know. It was amazing as well over 50 different countries were represented at PASS.

Next, Ted Kummert had the first keynote session of the day. His keynote was filled with announcements concerning SQL Server including the following:

  • Hekaton: the project code name for a new in-memory OLTP engine
  • The Columnstore Index will be updateable
  • Next version of PDW will be out in H1 2013
  • Polybase: allows you to query across multiple types of data sources such as SQL Server and Hadoop with T-SQL
  • DAX Queries will be able to query SSAS Cubes

He also highlighted some recent announcements related to the SQL Server stack:

  • Microsoft HDInsight Server CTP: Hadoop for Windows Server
  • Windows Azure HDInsight Service Preview: Hadoop for Azure
  • Power View and PowerPivot fully implemented in Excel 2013

After the keynote, I hit a session on BigData and Hive which was put on by SQL CAT and very informative. My big takeaway was to use EXTERNAL tables not INTERNAL tables when working with Hive. I then went to do final prep for my Tabular Model session. In this session, “Building a Tabular Model Database”, I present on what tabular and in-memory is, and then proceed to open up a Visual Studio project and create a database. I think it went well and the attendees seemed to enjoy the upbeat nature for an end of day session. The night wrapped up with dinner with the Magenic team (7 of us). Good chance to grow relationships across offices from around the country.

Thursday – Community Zone and DAX as a Query Language

Thursday was a fairly low key day for me. Once again I spent time in the Community Zone. I had the opportunity to talk with a few people on creating a user group in their area. As always, I like to see people interested in growing their local community.

I also attended Alberto Ferrari’s session on DAX. I think the biggest surprise to me was that you can now query DAX directly from SSMS. I am not sure that I am convinced that it is a full query language yet, but it is definitely closer. The key to it all is the EVALUATE expression which allows you to create the DAX query ironically in the MDX window. Here is just a taste of DAX as a query:



What I found interesting is that you can create columns, build measures, and perform many other operations against the tabular model using DAX. In the end, it will not increase the memory used as storage as it is all calculated. Look for some more on this in later blog posts as I delve more into the in-memory storage and usage when working with DAX.

Friday – More Community Zone, HDInsight, Paul White, and Window Functions

Last day. I spent more time in the Zone. I really did enjoy my time there as I continued to meet more people. I was even present when a contract was completed for the Shanghai user group. Very cool indeed. I then attended a session on HDInsight by Mike Flasco from Microsoft. This is very cool stuff as you can create simple Hadoop cluster on your desktop to test the technology. Microsoft and Hortonworks have done a great job of bringing Hadoop data into the Microsoft stack.

On my way to present my final session of the day and the conference, I stopped in for the second half of Allen White’s ( B ) optimization presentation. In a word (or two), mind-blowing! Wow, who knew that the optimizer did all those things? I was highly impressed and think he should look at a precon on the subject next year. Unlike some three hour presentations, he could have went longer as he was not stretching his content out. Nice work Paul. So, I got to follow that with a presentation on Window Functions in T-SQL. For the second time, I had the last slot of the last day. I think this presentation went well even though we were all worn out from a content-filled week. It was fun to try some ideas from the audience in the demos. That always makes for a more interesting demo. I will be doing a follow up post on what I learned from some of the attendees on the subject as well, proving once again this is a user community event. We all have something to contribute! (If you attended this session, you will find links to the blogs on the subject here.)

What’s Next?

Coming in April is the new Business Analytics conference in Chicago followed by the PASS Summit in Charlotte, North Carolina. Of course, your local user groups will continue to meet with regional SQL Saturdays sprinkled throughout the year as well. How will you participate and contribute in 2013? We look forward to seeing you all again, soon.

SQL PASS Summit Final Day (Sessions)

16 10 2011

Here is a summary of the final day of sessions I attended at PASS.

Vertipaq vs OLAP – Change Your Data Modeling Approach by Marco Russo

This session discussed the role of each data model type in your Business Intelligence platform.

The Many-to-Many Revolution 2.0 is being released which will cover BISM multidimensional and BISM Tabular.  It is available now from

The relational model for the multidimensional cube is a star schema.  It provides the best performance and functionality.

OLAP (Multidimensional) Vertipaq (Tabular)
Dimensional modeling Relational modeling
   Facts, dimensions    Tables
   Complex Relationships    Basic Relationships (1:N)
   Script    Calculated columns
   Powerful, Complex    Measures

OLAP and MDX is much more complex than Vertipaq and DAX.  That makes the barrier to entry or learning curve with DAX and Vertipaq much lower.

The dimensional model is designed to be faster and to store aggregations in the relational world.  For solutions with Vertipaq, is the datamart still required?  Can Vertipaq replace the dimensionally modeled datamart?

Marco went through a number of scenarios which show the difference in the performance between DAX, MDX, and SQL for aggregating and analyzing data.  At this point, it is too early to say that OLAP is old and will die.  We have two tools with different purposes and areas of strengths.  For instance, Vertipaq can handle distinct count operation much easier and faster than OLAP.  Marco keeps noting that Vertipaq and DAX are “fast enough” which seems to mean that expensive operations are expensive in both models Vertipaq and OLAP.

Key take away: Adapt the technology to the data model, not the data model to the technology.

PDW Architecture and Internals – Brian Walker

Only place you can put your software on PDW is on the Landing Zone.

Flow of data in PDW:
Source – Landing Zone Files – Data Loader – Compute Nodes

You can get up to 654 TB of backup space on the Backup Node.  Yes, that is 654 Terabytes!

The bread and butter of PDW is the Compute Nodes and Storage Nodes. Each compute node is running a SQL Server 2008 EE instance. SQL is the primary interface to the compute nodes.  TempDB lives on the Compute Node as well because it serves instance specific functions.

In the storage node, SQL Server file groups are created by the appliance to support a compute node.  This includes filegroups for distributed data, replicated data, and logs.

The Admin console is available through the web browser with tabs for Alerts, Sessions, Queries, Loads, Backup/Restores, Appliance State, Locks and Performance Monitor.

There are now add ins for System Center management with PDW.

PDW supports a hub and spoke architecture.  You can connect SQL Server and Analysis Services to PDW so you can offload data to different consumers.  You can treat the data warehouse on PDW as the single source of truth that can support analytics and data marts and related functionality in a spoke.

Integration with PDW:

  • SQL Server – Remote table copy from MPP to SMP
  • PDW ADO.NET drivers for SSAS, SSIS, .NET, SSRS

Data layout approaches that support Shared Nothing querying which is the ability for the appliance to request discreet query subsets from an originating query.

  • Replicated – full copy on each discrete PDW node (good for dimensions)
  • Distributed – uniformly distributed across all nodes (good for fact tables).  Uses hash keys to distribute the data across the storage nodes.
Data Warehousing: From SMP to MPP using SQL Server Parallel Data Warehouse – Dandy Weyn (Sr. Tech Project Mgr) and Brian Mitchell (Sr Premier Field Engineer)

Simplified Data Warehouse Offerings:

  • Enterprise Edition (software only)
  • Fast Track Data Warehouse (reference architecture) – includes software, configuration guidelines, and hardware;
  • BDW Appliance (integrated architecture)
  • Parallel Data Warehouse (DW appliance)

SQL Server Appliances:

  • HP Enterprise Data Warehouse Appliance (PDW)
  • Dell Parallel Data Warehouse Appliance (PDW)
  • HP Business Data Warehouse Appliance
  • HP Business Business Application Appliance (inc SharePoint)

DWLoader or SQL Server Integration Services can be used to load data into the PDW.  DWLoader will usually be the faster method because it has been optimized for PDW.

This is a Shared Nothing Architecture unlike Oracle RAC which is a shared everything architecture.

You need to choose your distribution key very well.  It will make sure that the table is properly distributed across nodes.

Largest SQL Azure Projects in the World & How Do They Scale – Kevin Cox, Michael Thomassy (SQLCAT)

Top stats:

  • Largest Sharded Database is 20 TB
  • Largest number of databases for 1 app is 500
  • Most users on 1 app is 3 million
  • 2k block Read, 8 node end-point – 34,000 ops/sec

SQL Server is the core DB Engine for SQL Azure.

Case Studies

Flavorous – Ticket Sales Vendor

This is a truly elastic solution.  They only keep databases up for the duration of the ticket sale.  (NOTE: Checkout Chaos Monkey).  All of the applications needed to be built with SQL Azure capabilities in mind.  They were able to handle 3 million tickets in 6 minutes including 150,000 tickets in 10 seconds.

Bing Games

Bing Game tracking and scoring system.  398 SQL Azure databases supporting 30 million unique users per month, 200,000 concurrent users with 99.975% uptime.

Why SQL Azure?

  • Faster than Azure Table Storage – for entities with large number of attributes
  • Very low learning curve – prototyped and written in less than 4 days with 1 developer
  • Testability – mostly compatible with SQL Server and easy to prepopulate with millions of records.

Sepia Labs –

They are now in the process of closing their on-premise data center and they are moving everything to the cloud.  This is a private social network for mobile phones.  (Side note from them: “Windows 7 is by far the easiest mobile platform to build on.”)  They picked Azure table storage and not SQL Azure except for some analytical data.

Why Table Storage?

  • Less expensive than SQL Azure
  • Speed approximately the same for small entities (very few columns; i.e. < 10)
  • High size limit – 100 TB total between Table, Blob store, Queue per storage account.

MYOB – Account Software ISV

They service small businesses with accounting solutions.  They chose the cloud because of the types of offerings they have.  This allowed them to provide massive scalability and high availability.  They scale by a sharding technique built by the developers.  They are considering Federations in SQL Azure for the future.

Federations in SQL Azure will allow you to do less development to support sharding within SQL Azure.  It is designed to help customers take care of the following:

  • Unlimited Scalability – distribute workload equally to all nodes, scale out which is not scale up.  Scale applications horizontally to many nodes and allow applications to dynamically deliver data.
  • Rich Elasticity – expand and contract without downtime.
  • Simplified Tenancy Model – whether you have multiple tenants per database, one tenant per database, or multiple databases per tenant.

Thus concludes my week at PASS Summit 2011.

SQL PASS Summit–Day 4 (Keynote)

13 10 2011

Bill Graziano kicked off the event today with the notice of the official “Kilt Day” at the Summit.  He also recognized the volunteers who make this happen.  Thanks to all volunteers which contribute to our local, national, and international SQL communities.  If you are interested in volunteering check out  Bill updated us on the financials, something I won’t be blogging about here.

Quentin Clark, Corporate VP SQL Server, was the keynote speaker from Microsoft.  He talked about the new release of SQL Server 2012.

  • Data – Managing data at the fundamental level; Knowledge – how to get insights out of information; between Data and Knowledge is Information – data cleansing and quality.
  • SQL Server 2012 is one of the biggest releases Microsoft has ever done.  !00s of new features in SQL Server 2012.

Quentin’s Fantastic 12 of SQL Server 2012

  1. Requred 9s & Protection – SSIS as server; HA for StreamInsight; SQL Server AlwaysOn
  2. Blazing Fast Performance – Performance Enhancements in RDBMS, SSAS, and SSIS; ColumnStore Index with Vertipaq
  3. Rapid Data Exploration – Power View + PowerPivot; Administration from SharePoint; Reporting Alerts
  4. Managed Self Service BI – same as 3
  5. Credible, Consistent Data – BI Semantic Model (BISM); Data Quality Services (DQS); Master Data Services (MDS)
  6. Organizational Compliance – Expanded Audit (User Defined, Filtering, HA); User-defined Server Roles
  7. Peace of Mind – Production-simulated Application Testing; System Center Advisor & Management Packs; Expanded  Support (Premier Mission Critical)
  8. Scalable Data Warehousing – SQL Server Appliances (optimized & pre-tuned including Dell PDW, HP Enterprise Data Warehouse Appliance, HP Business Decision Appliance); HW + SW + Support (Just add power); Choice of hardware
  9. Fast Time to Solution – same as 8, lots of appliances
  10. Extend Any Data, Anywhere – Greater Interoperability – new drivers for PHP, Java & Hadoop; ODBC Drivers for Linux & Change Data Capture (CDC) for SSIS & Oracle; Beyond relational: FileTable, 2D Spatial, Statistical Semantic Search (functionally will replace full text search)
  11. Optimized Productivity – SQL Server Data Tools (formerly “Juneau”); Unified across database and BI (no more BIDS); Deployment & Targeting Freedom
  12. Scale on Demand – AlwaysOn; Deployment Across Public & Private; Elastic Scale (using SQL Azure)

Azure Announcements

You will now be able to access Windows Azure storage from SSMS which can be used for SQL Azure backups.  You will also be able to “right-click” deploy from SSMS to SQL Azure.  A lot of integration between SQL Server and SQL Azure is coming in SQL Server 2012.

SQL Azure Federation is being released at the end of the year.  This will allow for larger and elastic databases to be deployed to SQL Azure.  This provides for a management tool to distribute or federate your application data across SQL Azure databases which allows you to manage cost and performance.

SQL Azure management site is now going Metro (WIndows 8) including live tiles.  I like it.  This too will be released at the end of the year.

SQL Azure will now be able to support 150 GB databases.

Available today – CTPs for SQL Azure Reporting and SQL Azure Data Sync.

There really are a plethora of really cool features and tools being released with SQL Server 2012.  Nice work SQL Server project teams.

SQL PASS Summit–Day 3 (Keynote)

12 10 2011
From the PASS president, Rushabh Mehta.

PASS has facilitated 430,000 hours of training and expansion into the global community.  These three days contain massive amounts of training and opportunities to network with other SQL peers, including over 800 first timers.  SQL Server MVP Deep Dives Volume 2 has been officially released here with the opportunity to get a bunch of autographs from the 55 MVP authors in the house.

Ted Kummert’s Keynote highlights:
  • “Some database vendors just decided to get into the cloud last week”
  • Growth of PASS is amazing! Over 240 chapters, 300 MVPs, 79,000+ members, and over 4000 attendees at the Summit.
  • SSAS in the cloud, coming soon?
  • Crescent is now PowerView.
  • SQL Server 2012 (aka Denali) will release in the first half of next year.
  • MS SQL Server Vision: “any data, any size, anywhere”
  • Big Data characteristics:  Large data volumes, tranditional and non-traditional data sources, new technologies and new economics and new insights.
  • They will be supporting Hadoop on Windows. By providing connectors to SQL Server and SQL Server Azure, by investing in a JavaScript Framework for Hadoop, drivers for Excel, by partnering with Hortworks,
  • From Denny Lee: HiveQL – the query language for working with Hadoop data. Hive ODBC Driver in PowerPivot – this driver will be available in the next month.
  • Announcing code name Data Explorer for data enrichment, which will be in SQL Azure Labs by end of year.
  • Data Explorer will be in SQL Azure Labs by end of year.  It allows you to do data classifications.  Looks very much like a variation of Excel.  It allows you to add data from external data in the Azure Marketplace which will recommend data based on the data you are analyzing.  Kind of looks like PowerPivot in the cloud, interesting.
  • Amir Netz was recently promoted to Technical Fellow at Microsoft – early developer of OLAP Services, SSAS and beyond.
  • Amir demos PowerView.  During the demo we discovered that Meet the Fockers was the top comedy.  Oh, the power of BI.  Great demo about this product.  He also announced that export to PowerPoint will be supported in this release.

Quite the whirlwind this morning, but some more cool BI stuff coming soon.  Looking forward to the rest of the day.

March Presentations Uploaded on SkyDrive

20 03 2011

I presented at three events in the past 10 days. 

  • Magenic Lunch & Learn – SSRS Training – Overview of SQL Server Reporting Services functionality.
  • Minnesota SQL Server User Group – SQL Azure – a good discussion on SQL Azure, it’s capabilities, and cost effectiveness for our solutions.
  • Techfuse 2011 – Performance Monitoring and Tuning with SSAS – a look at the query execution workflow and the tools to monitor and tune Analysis Services.

I hope these provide value to you.  Feel free to comment here with questions related to any of these presentations.

SQL Azure’s place in the data world (Part 3)

16 01 2011

In the first two parts of this topic, I discussed how data is managed in SQL Azure and what the cost would be to an organization.  In this installment, I wanted to propose some data solutions that might be a fit for a SQL Azure database.

Here is some quick history that is shaping my thoughts on this.  While at Magenic, my focus is on business intelligence solutions on the Microsoft SQL Server product stack.  Prior to returning to Magenic, I worked at XATA Corporation as the data architect for a hosted, SaaS solution serving the trucking industry.  So, out of this background, I base my first suggestion.  In SaaS (Software as a Service), customers often use tools provided by the vendor for specific tasks which tasks generate data.  Most SaaS solutions offer some form of reporting or query based analysis for the data that is collected.  However, some users require a greater level of interaction with the data.  The issue is that the data is stored in the SaaS vendor’s multi-tenant data store which is usually not conducive to having ad hoc queries run against it.  This has led to one of the most common solutions to this problem – export the data to customer.  The problem is that the customer must now host the data on premise and is often responsible for merging new data as it comes from the vendor.  In this solution, SQL Azure could act as the “go-between” between the multi-tenant data store and the customer.  This will allow the vendor to provide a value-added BI service that the customer can leverage in a number of ways including reports, Excel, and even Access.  The vendor can keep the data synchronized and the customer can leverage the data as needed.

Beyond the SaaS BI ad hoc solution, SQL Azure can be used to support development of solutions that require a shared data store without putting the data on each desktop.  In keeping with the concept of the cloud being “anywhere”, SQL Azure can also be used to support distributed solutions that require a SQL data store to function. 

Obviously, there are still issues with using SQL Azure as a primary, production data store due to the lower SLAs from Microsoft.  However, it is not too early to investigate creative ways that will help your business leverage a relational database in the cloud.  I would encourage you to check out the free trial options from Microsoft to experiment with the platform. 

SQL Azure’s place in the data world (Part 2)

26 12 2010

In Part 1, I discussed the synchronization of data and availability of data on Microsoft’s SQL Azure platform.  In Part 2, I would like to discuss the next set of questions related to using SQL Azure with your data solution.  Here are the questions I will address in this post:

  • How much data can be supported by SQL Azure?
  • Is it really cost effective?

How much data can be supported by SQL Azure?

The answer to this question is entirely dependent on how and, in some cases, who you ask.  SQL Azure currently only supports databases up to 50 GB in size.  Because this is subscription based, you will only pay for what you use or allocate.  I will delve into the subscription and their cost in the next section.  For our purposes, it is important to realize that any database up to 50 GB is supported by Microsoft.  This limitation leads to a couple of additional questions.  First, how is the size measured?  Keep in mind that you are managing by the database not the server, so the system databases are not included in this size.  Furthermore, Microsoft does not include the size of the log in this measurement.  So when you determining how much space you will need, focus on the size of the MDF file and be sure to check how much is actually in use.

The second question that comes to mind is what happens if you need more than 50 GB? While not supported directly, it is possible to distribute or to “shard” a larger database across multiple smaller databases.  Currently this is a custom solution, but Federation is coming.  With Federation, SQL Azure will natively support sharding a large database and be able to present the database as a single unit which will make development easier.  If you can’t wait for SQL Azure Federation, check out the SQL Server and SQL Azure Shard Library available on CodePlex (

So you can see that if you would like to support or scale out a larger database solution it is possible with SQL Azure.

Is it cost effective?

At the time of this writing SQL Azure subscriptions break out as follows (

Edition Max Size Cost per Month
Web Edition 1 GB $9.99
Web Edition 5 GB $49.99
Business Edition 10 GB $99.99
Business Edition 20 GB $199.98
Business Edition 30 GB $299.97
Business Edition 40 GB $399.96
Business Edition 50 GB $499.95

Microsoft also charges for data transfers.  Currently it is $0.10/GB for data in and $0.15/GB for data out.

For those of you who don’t have the privilege of working with software licensing, may not be able to appreciate the complexity of determining the cost effectiveness of the solution.  For comparison let’s review the MSRP cost for SQL Server Standard and Enterprise licenses.  (You check out other licensing options here:  SQL Server 2008 R2 Enterprise Edition costs $27,595/processor and SQL Server 2008 R2 Standard Edition costs $7,171/per processor.  These are the listed prices.  Customers can get a variety of discounts or other pricing options depending on their agreements with Microsoft including Software Assurance, Enterprise Agreements, and Volume Licensing.  In any case, there is is a significant license cost when ramping up a production SQL Server.

Besides licensing, you have to consider the cost of the hardware and personnel to maintain the servers.  By the time you have premium servers and a disk solution in place you could easily have spent around $100,000 for a simple production environment.  The next piece of the puzzle is related to additional manpower required to support the environment.

Now we have a bunch of numbers, but how can you compare solutions?  There are some significant differences between a SQL Azure solution and a SQL Server solution.  First, and most glaring to me, is the fact that SQL Azure is managed at the database level while SQL Server is managed at the server or instance level.  When you are trying to determine the cost-effectiveness for your application in either solution, you need to determine the number of databases required for your solution.

Next, you need to determine the cost of an on premise solution on a monthly basis so you can compare the solutions over an expected period of time.  Generally speaking, we expect hardware and software licenses to last around three to four years before an upgrade will be required for the solution.  In the case of SQL Azure, you have a known monthly cost with hardware and software licensing and related upgrades “included” in the cost.

Finally, when looking at SQL Azure, you might think that the fact it does not include Analysis Services or Integration Services in the license will make it cost-prohibitive in your solution.  However, since best practices dictate that separate servers be used for these services, they really do not factor into the cost-effectiveness of the solution.

So, what does this all mean?  It means you have some work to do to determine how cost-effective a SQL Azure solution will be for your application.  So let’s assume the following hardware/software solution to provide disaster recovery and high availability for your on premise database:

Component Cost factor Total
Dell PowerEdge with PowerVault disk – 2 Servers (2 proc/16GB) $65,000 with Rack $65,000
SQL Enterprise Licenses $25,000 / processor (Active/Passive Cluster) $50,000
Install and setup $100/hour $4,000
Total $119,000
$ Per month for 3 years $3305

As you can see, a simple on premise solution without considering the cost of maintenance and without considering the setup time will cost you around $3300 a month.  At this point, $500 a month plus data transfer costs should be less on SQL Azure for your database.  So, like me, you might think that dropping Enterprise to Standard licensing will make up the difference, but it still costs $2300 a month.  That means that you can get Enterprise capabilities with HA built in for less than standing up your own Standard environment.

Where this will break down is when your databases are larger than 50GB.  At that point you will have to scale both solutions and determine what is the more cost-effective solution for you.  You could reasonably use sharding to increase your space to 200 GB and still be cost-effective.  I admittedly did not think the cost savings would be that significant for SQL Azure.  Microsoft appears to be on the right track to provide reasonable pricing for SQL Azure.  I would be interested in hearing additional cost factors for on premise versus off premise database solutions.  (Please comment on this blog with additional factors you see that would impact the cost-effectiveness analysis of the SQL Azure solution.)

In the final blog entry on this topic, I will delve into solutions that make sense on SQL Azure and yes there are some cases for using SQL Azure sooner rather than later.


Get every new post delivered to your Inbox.

Join 731 other followers

%d bloggers like this: