Category Archives: Cloud Computing

Mar 25

Is performance an issue with SQL Server on Azure?

Posted on by

pasturecloudsI inherited the development of a web app that was meant to run on Azure, using SQLAzure as the data store. Right from my first contact with the app, it was clear that it had performance issues. When running from a local SQL Server performance could be deemed as endurable, but it was totally unacceptable on Azure, where it took 3 times longer.  A performance analysis pointed out database access as the main factor, but there were other things that could be improved upon as well.

The identification of the performance issues coincided with some changes in requirements, that led to the decision to take a totally different approach, regarding the way data the app needed was to be handled. This new approach was motivated mainly by the need to support the new requirements, but it was also meant to see if it could help with the performance issues. A model to represent the data was developed and implemented on a SQL Server database. This model was tested and improved upon, up to a point where performance, in a worst case scenario, was deemed acceptable, when implementing it on a local database server. The model requires some calculations to be performed, and this is done through a stored procedure. Each of the stored procedure queries was optimized for performance, considering the overall goal of minimizing the time needed to execute the stored procedure and not the individual queries themselves. This involved, for example, leaving out some indexes that could improve individual queries, but contributed to the degradation of the overall performance. As stated before, we arrived at a point where performance in any of our local SQL Server 2012 databases was deemed good enough.

Having solved the performance issue with local instances of SQL Server 2012, we set to see how SQL Azure, the intended data store, handled the new way to process the data. We rebuilt the entire database, populated it with the exact same data we have in our local testing scenario and tested the new stored procedure. Performance was much worse – the time needed to complete the stored procedure in the worst case scenario was almost 3 times the time it took to execute it in any of our local servers. Let me be clear here – we are not even talking about time needed to retrieve data from the database – it’s just the time needed to process records inside the database and storing the results in a database table.

Trying to determine if SQL Azure was to blame for this, we decided to compare the performance in different hosting scenarios: Azure SQL VM, a standard hosting offering running SQL Server 2008 Web edition and Amazon RDS. The testing started with Azure SQL VM. Using the Azure Virtual Machine preview, we created several instances of a VM running SQL Server 2012 SP1 Evaluation edition, on Windows Server 2008 R2 SP1. To rule out the virtual machine size as a factor, we had a VM of each size – small, medium, large and extra large (an extra large machine offers 8 computing cores and 14 GB of memory). On all these VMs, used with their pre-configured settings, performance was pretty consistent and didn’t really change much, when compared with SQL Azure. The execution time for the stored procedure was very similar in all VM sizes – and too high in all of them.

We then tried the traditional hosting solution, a Windows 2K3 server running SQL Server 2008 Web edition on two Xeon processors with 4 GB of RAM. Surprisingly, or maybe not, performance was pretty similar to the one in SQL Azure and Azure SQL VM. Almost believing that somehow, hosted solutions of SQL Server were not up to the task, we decided to try Amazon RDS. We had never tried Amazon’s cloud solution before, so we had to check the options offered and create a new account. There are multiple options regarding VM features and size and we decided to test a Large DB Instance – a VM running SQL Server 2008 R2 with 7.5 GB of memory and 4 ECUs (2 virtual cores with 2 ECUs each – 1 ECU, according to Amazon Web Services is equivalent to a 1.0-1.2 GHz 2007 Opteron or Xeon processor). Setting up the VM was as easy as on Azure and a few minutes afterwards, I was creating the database and uploading the test data. Once this was completed, the test stored procedure was executed multiple times … and execution times were less than half, on average, than those from Azure and almost as good as with our local SQL Server instances.

All this testing was both reassuring and worrying. For one, it’s clear that there are cloud based offerings that can provide performance that is pretty similar to what can be obtained from in house SQL Server solutions. For our case, however, it seems that Azure is not one of those. We still need to decide what we will do, but the cost / performance factor for Azure based SQL Server solutions is not looking too good. Right now SQL Azure is the only non preview offer and its cost is rather high, for databases larger than 100 MB. Azure SQL VM won’t be a reality until this summer, but while it may provide a more cost effective solution, it’s not clear that it can be a competitive offer, performance wise. Of course, we are considering a rather specific use case, but the overall performance before we changed our model was not that good either and this experience, while not definitive in anyway, does seem to raise the question – are Azure based SQL Server solutions good enough, performance wise? The answer is not clear right now, but it is a bit unsettling to find a competitor’s cloud offerings a better choice than Microsoft’s, on a Microsoft product as relevant as SQL Server.

Dec 14

Rising and Dying Clouds

Posted on by

This week saw the official announcement of the death of a tool that I have used extensively since its launch – Windows Live Mesh. In an email sent to the remaining Live Mesh users, Microsoft stated that “it makes sense to merge SkyDrive and Mesh into a single product for anytime and anywhere access to files”, so Live Mesh will be retired on February 13, 2013.

Ever since Live Mesh was initially released, Microsoft has been steadily improving its cloud offerings. One of those that I now use regularly is the Team Foundation service, available on tfs.visualstudio.com. TFS is an Application Lifecycle Management (ALM) service, meant for developers. Of course, there is also Office365, soon to become a lot more used, even by regular users, with the upcoming release of Office 2013 and its new subscription based purchasing model. SkyDrive, however, is the most relevant piece of Microsoft’s cloud strategy, in what regular users are concerned.

SkyDrive was revamped a few months ago and although the free storage offered was reduced to just 7 GB (for new users, as older users who took advantage of a timed offer can have 25 GB free), tiered, paid storage additional options are now available. One of the problems with SkyDrive was the difficulty to access it from a computer or other computing devices, other than from a browser, that is. The first sign of change in this area, was the excellent Windows Phone software, that integrated SkyDrive very well, allowing seamless access to photos, Office and other documents stored on SkyDrive. This easy access to SkyDrive was “ported” to Windows 8 where, like in Windows Phone 7.x and now Windows Phone 8, a Microsoft account can be used. Office 2013 takes good advantage of this easier access. While until now, OneNote was the single Office app that could easily use SkyDrive as the place to store, share and work cooperatively on OneNote documents, with Office 2013, the same ability to work cooperatively on documents stored on SkyDrive was extended to Word, Excel and Powerpoint. This new Office 2013 SkyDrive integration is really good and will make the cooperative work on Office documents a lot easier – at work, we have started to use it extensively, with great satisfaction and success.

The issue of SkyDrive integration with Windows Explorer (File Explorer in Windows 8) was meant to be addressed by the SkyDrive app. However, the two versions of this app, the initial one, released a few months ago, and a very recent update, when compared to Windows Live Mesh, are rather poor. Windows Live Mesh was (is) an excellent software tool. Among other features, it allowed syncing of folders, freely chosen by its users, between different computers and even different users. It also allowed the synchronization to be done with the “cloud” – a 5GB storage space provided in SkyDrive and reserved exclusively to this purpose.

Live Mesh did not require a preset folder organization – it allowed the sharing any folder, regardless of location and that folder could be mapped, in the destination computer, wherever desired. Through this freedom of mapping of the shared folders location, Live Mesh did not impose a way to organize one’s folder structure, it simply adapted to each user’s preferred drive organization scheme. That, and the fact that when synchronizing between computers located in a local network, Live Mesh did not use the internet to transfer the files, using the local network instead, were two of the signs of a cleverly, smartly designed software tool (we really don’t have enough of these!).

In what concerns file synchronization, the SkyDrive app fares much worse than Live Mesh. For users like me, who have relied on Live Mesh to share files with others, the app does not allow access to files that were initially shared by others. To do that, you still need to use your browser. On the other hand, when mapping shared folders in computers different from those where the sharing was initiated, there is no freedom to choose the destination path – everything goes under a root SkyDrive folder. You can choose where, in your local file system, this root folder goes, but that’s all that can be done. There is no way to map each individual folder, as it was possible with Live Mesh.

Of course, the SkyDrive app has some advantages over Live Mesh, the most relevant being the ability to allow access from Windows Explorer (or File Explorer, in Windows 8) to all of the available SkyDrive storage space, instead of just the previous 5 GB. For new users of the free service, however, this will just mean 2 extra GB, so not that much, actually, but still an advantage. To me, that’s probably the only advantage of the app, compared to Live Mesh. Yes, there are SkyDrive apps on other platforms, but I really couldn’t care less about it. I’m all in on Microsoft’s ecosystem – I own a Windows Phone, my computers run Windows in various flavors, from Vista to 8 (which I quite enjoy, by the way), I use Office and I develop using Visual Studio.

At this time, where the use of cloud based services and storage is on the rise, and where the advantages of that use are becoming clearer, the announced death of Live Mesh is bad news. It’s very hard to accept a degradation of functionality, once you get used to a better way. I can see no technical reasons for the Windows SkyDrive app not to offer the same file sharing and syncing abilities of Live Mesh. The SkyDrive app, though it has improved in its latest version, is still lacking. It is like Microsoft’s urge to change and adapt to fight Apple’s and Google’s competition has caused it to rush things and the end result are initial versions of products are that are, in some cases, glaringly lacking. Windows RT is a case in point, as the Metro side of Windows 8 seems to be, as the desktop versions of SkyDrive are. Different scales, of course, but can they be signs of the same problem?

It’s possible that a future version of the SkyDrive app will improve on the current limitations. I am not holding my breath, though. I will just have to find a replacement, to keep syncing my files as I want to. The good thing is that there are competing cloud alternatives (my own favorite backup tool, Acronis True 2013 offers sync functionality, which I need to check properly) and maybe there can be some advantages of not putting all my cloud based eggs in Microsoft’s basket.