The first step to query optimization is determining the efficiency of the query. In SSMS, the Execution Plan graphically shows the cost of excuting a query. To see the Execution Plan, first toggle on “Include Actual Execution Plan” by selecting Query > Include Actual Execution Plan or from the toolbar as shown below:
Then when you execute the query, you will also be presented with an Execution Plan tab besides the Results and Messages tabs:
A good resource on reading Execution Plans is the MSDN resource here.
In the above query I have made the most basic mistake – reading more data than will be required, it is rare we will need to return all the columns in a table, it’s also rare that we will need every row in a table (so always use WHERE to minimize the rows that are read) .
The TSQL SETSTATISTICS command will monitor the execution of a query and then provide statistics on either the time taken to execute the query or the I/O expense of the query.
To test the time of the query execution use the Command SET STATISTICS TIME ONE at the start of a query. This will then output the parse, compile and execution times for the query to the Messages tab:
To test the I/O expense of the query execution use the Command SET STATISTICS IO ONE at the start of a query. This will then output the IO performance for the query to the Messages tab:
To learn about using this data to optimize the performance of you SQL Azure queries a great resource is Simple Query tuning with STATISTICS IO and Execution plans .]]>
Overall Stability, Security and Performance : A-
Azure definitely confounded some of its harsher critics by registering a very good track record for uptime, performance and most important – security. Since its launch there have been no major security issues and no large outages. I’ve run Azure since April and the only issue was two short periods (under one hour) of sluggish performance while OS patches were being applied, it seems that most users experiences have been similar to mine.
SQL Azure : D
While the performance of SQL Azure has been good, I can only register my bitter disappointment at the progress of adding features. I noted that probably the biggest weakness of SQL Azure at launch was the lack of any backup facility, we were promised two backup functions (continuous and clone) with one to appear in the first half of 2010 but still no sign of anything, no sign of encryption or compressions either. The features that have been added can only be described as basic – such as a 50GB sized database, or the ability to automatically upgrade to a larger database size (although we still have to execute a TSQL Alter statement for this).
SQL Azure itself is a solid product but it is still too expensive and lacking in even the basic features SQL Server users require.
Windows Azure Features : C-
Azure is still not heavy in terms of features, which is fine for a product is its first year so it would be merited a B- or C+ had it not been for the omission of .NET 4.0 support (6 months and counting since .NET was released). Unfortunate for Microsoft the last year has been one of heavy innovation for its main rival – AWS. Most notably, AWS Simple Notification Services (SNS) allows AWS users to send notifications via several formats (even email and SMS) to alert a user of an application of an event (this is a heavily requested feature on Azure but no plans have been confirmed to add it).
Azure Tooling : C+
.NET developers are used to best in class dev tools and so we’d expect great tooling for deploying to Azure. We now have an vastly improved Visual Studio experience which allows for direct deployment to Azure (which is great since the Azure developer’s portal is still slow and generally a poor user experience). We can also directly connect to SQL Azure databases to view and interact with database objects from SSMS 2008 R2 and generate scripts to create an SQL Azure database. But this is pretty much the minimum we would expect using a Microsoft environment.
In the minus column the Azure portal is still slow and lacking in features, monitoring of the cost is very basic and the are no tools for monitoring the various running instances of an Azure service. Also, migration tools are pretty lacking – the SQL Azure migration is good to troubleshooting a migration to SQL Azure but won’t be able to solve many of the incompatibilities. No tool exists for migration an ASP.NET app (there are several tutorials on migration in which the process looks relatively simple but there are enough gotchas to make the migration of a reasonably sized ASP.NET app a real headache).
Pricing : D
I’ve given a D for pricing as it is the one area where improvement could easily have been made. Probably the most persistent complaint about Azure is the high cost of the entry. With a single SQL Azure database and only a single compute instance an Azure plan will cost $60 – $90 per month depending on any discounting given if you are a member of BizSpark or MSDN etc. Even worse if you need to rely on Azure’s 99.95% uptime SLA you are required to have 2 compute instances which will comfortably bring the cost to over $100 (imagine your hosting company informing you that you needed a second server if you wanted to have good uptime ). This is a relatively high barrier to entry for small developers who are building new apps which will only initially use a fraction of the small compute instance and the 1GB SQL Azure database allocation. AWS meanwhile now offers an ultra-small instance which costs only $15 per month and in addition they are offering a full free year to new users.
I would have given an E except for the fact that Azure pricing matches AWS whilst offering a lot more. The Azure platform handles all the OS patching, scaling and security without the need for user intervention. AWS by contrast with its infrastructure as a service model merely provides the OS and offers some tools the user can implement (such as the elastic load balancer) to manage scaling, patching and security updates is left to the user.
Overall the grades might not have been too high but it is definitely the grade for stability, security and performance which is the most important since a poor performance would surely mean the death of the Azure platform and the only one area where Azure should definitely have been better is in pricing. We are definitely lacking in tools and features (especially for SQL Azure) but since it is just a year we shouldn’t be too demanding – the real test of Azure will be whether it can innovate over the next 1 -2 years.]]>
The TSQL command structure below provides will produce a copy of a database:
CREATE DATABASE database_destination_name AS COPY OF [source_server_name.]database_source_name
Note that this TSQL syntax allows for copying to a different Azure server. This is not necessary to protect the data from a disaster recovery point of view since the data is replicated across 3 geographic locations, but it might be required if you wish to copy the database to a different admin account or perhaps a different Azure account that would be billed separately to the source database account. If the database is copied to a new server the exact same login/password pair executing the command must exist on both the source server and destination server.
The copying process will not result in any downtime for the source database, and the copy will be a full copy complete with transactional data (note that this will be the transaction data as at the end time of the copying process not the start time of the copying process).
To monitor the copying process and determine if there were any copying errors you can query the sys.dm_database_copies view. The below command will retrieve all the data regarding the copying process:
Select * from sys.dm_database_copies
A major drawback to using Database Copy for backup is cost – each copy/backup will be charged as a new SQL Azure database.]]>
Note that Azure deployment is not available in Beta 1, but is promised in a subsequent Beta.
See this LightSwitch Tutorial for details on developing and deploying a simple LightSwitch app.]]>
Currently, there are 22 CDN locations:
In addition there is a flat charge of $0.01 per 10,000 transactions.
The CDN works by caching content at its 20 worldwide ‘edge’ locations, the first time that a file is requested at an edge location it must be transferred from Azure Storage and will thus incur Azure Storage costs in addition to the CDN pricing.
Azure CDN pricing is broadly comparable to Amazon’s competing CloudFront CDN, except that CloudFront prices transactions at $0.0075 per 10,000 and also offers discounts for larger data transfers (down to $0.03 for 1,000TB per month.
The update is available now in in the AppFabric LABS environment.
There is an undoubted need for a hybrid architecture for many larger corporations since migrating existing apps to the cloud is not a simple as lot of demos show and there is a perception (whether real or not) that the data is less secure on the cloud. Enter hybrids apps – maintain the data on premise or consume on-premise apps from a cloud service.
Of course it is possible to communicate between on-premise data sources or apps and cloud-based apps using SOAP/REST communication protocols, however there are two major obstacles – discovering the service endpoints (since these may change due to dynamically assigned IPs) and navigating through firewalls. These problems can be overcome by allowing apps to selectively open ports which is inherently insecure, and using relay systems that sit between the firewall and the apps and act as a bridge, thee systems tend to be very complicated and hard to implement.
The Azure Service Bus attempts to solve this issue by proving a service which allows applications which need to communicate with eachother to register with it. The requesting app is given a Service Bus endpoint to communicate with the data source/service app. Essentially the services are provided by service apps run behind the firewall, and the connection endpoints are provided by the Azure Service Bus. It should be noted that the Service Bus allows communication with non-.NET services , so Linux/UNIX hosted apps can register with the Service Bus and be consumed by .NET apps.
Security is provided by the Azure AppFabric Access Control, which applies user-defined rules to ensure security when an app claims tokens via the STS service provided by the Access Control.
Thus the Service Bus can be used to build hybrid apps which span both on-premise and cloud services.]]>
The standard compute instances sizes are very similar on both platforms – Small (1 core processor, 1.7GB RAM) , Large (4 core processors, 7GB RAM), Extra Large (8 core processors, 15GB RAM ). The only differences being that Azure offers slightly larger drive space on the Large and Extra Large instances, on the Large instance Azure provides 1TB of space vs 0.85TB on AWS , and on the Extra Large instance Azure offers 2TB vs 1.8TB on AWS. However this is probably a minor issue to most cloud platform users who will be more interested in the persistent storage offered (see below).
Azure includes a Medium instance which provides 2 core processors, 3.5GB RAM, 500GB hard disk and is a useful bridge between the Small and Large instances for growing applications.
In addition, Amazon offers a variety of larger instances. High Memory instances provide 17GB – 64GB of memory and High CPU Instances provide up to 20 ‘compute units’ (which equates to 20 cores of processing power).
Pricing for compute instances is identical on AWS and Azure.
Compute instance pricing on AWS and Azure:
Small : $0.12 per hour
Medium : $0.24 per hour (Azure only)
Large : $0.48 per hour
Extra Large : $0.96 per hour
(NB : All AWS pricing is based on Windows instances, Linux based instances are typically 30% cheaper)
Amazon is currently running a pilot program to transfer Windows Server licenses to EC2. Under this program, pricing for the EC2 instances drops 30% to the Linux EC2 levels. See full details of the Windows Server Mobility program.
The basic pricing models for both AWS and Azure are very similar, however in addition to the standard pricing model Amazon offers Reserved Instances. A reserved instance involves payment of a one time up-front fee that ‘reserves’ an instance which is then available at a lower hourly cost. Instances can be reserved for one or three year terms and pricing varies according to instance size and length of the reservation.
For an assessment of the pricing impact I have amortized the upfront payment equally over the term of the reservation (assuming 12 months usage at 730 hours per month). For example, the one time fee to reserve a Small Instance is $227.5 and thereafter usage on the instance is charged at $0.05 , thus assuming full utilization of the instance the $227.5 payment is $0.026 over the year (227.5 / 12/ 730) and the total hourly cost of the instance is $0.076 (a saving of 37% over the normal charge of $0.12 per hour). If the instance was reserved for three years the charge would be $350 and the final cost would be $0.0.61 per hour.
Amazon AWS Reserved Instance Pricing (Assuming Full Utilization) :
Small : $0.076 (1y term) , $0.061 (3yr term)
Large : $0.304 (1y term) , $0.253 (3yr term)
Extra Large : $0.608(1y term) , $0.507 (3yr term)
All charges are per hour
Amazon also offers spot pricing, which is hourly updated pricing for instances that cannot be booked for any period longer than an hour which is essentially live market pricing for instances. This is likely only to have very niche appeal for apps which can be run at any time – large batch processing tasks could conceivably use this pricing service.
Cloud apps need to use persistent storage for files (such as images, videos etc) which are then referenced in the app. Both Amazon’s S3 (Simple Storage S0lution) and Azure Storage price storage at $0.15 per GB, however S3 offers discounts for very large data amounts stored (for example, $0.140 per GB for 50TB – 400TB of storage).
Transactions on both Aaure and AWS are billed at $0.01 per 10,000 transactions, it should be noted there is a slight difference in this as S3 only charges for requests for stored items whereas Azure charges for inward storage transactions as well.
For the Azure CDN versus Amazon’s CloudFront, see our separate article on Azure CDN Pricing .
Under traditional hosting this would be called bandwidth but the convention for cloud providers is to use the term Data Transfers.
F0r both Azure and AWS, Data Transfer is charged at $0.10 per GB of inbound traffic and $0.15GB per GB for outbound traffic. Azure pricing is constant for all volumes whereas AWS provides discounts for large volumes of outbound traffic – $0.11 per GB for > 40TB , $0.09 per GB for >100TB , $0.18 per GB for >150TB.
Prior to June 30, 2010 AWS offers free inbound data transfer and Azure offers free inbound off-peak data transfer.
Database offerings for Azure and AWS are structured very differently and for that reason are not directly comparable. Amazon’s AWS does not offer per database plans and fro SQL Server you can only use a SQL Serve license to install a full instance of SQL Server on an EC2 instance , or you can use the free SQL Server Express. Azure, by contrast, only offers individual databases of the SQL Server based SQL Azure.
Amazon offers relational database (MySQL) instances which are comparable to EC2 instances with a full MySQL instance installed (and can therefore have numerous databases).
SQL Azure databases are priced at $0.014 per hour for a database with 1GB of storage and $0.14 per hour for a 10GB database. A 50GB database for SQL Azure has been announced and will be available from June 2010.
By contrast, Amazon’s Small Relational Database Instance has 1.7GB RAM and a single core processing unit which is charged at $0.11 per hour. Storage is charged at $0.1 per GB per month and an instance can have between 5GB and 1TB of storage.
AWS database instances can scale all the way up to the Quadruple Extra Large DB Instance which has 64GB RAM , with 26 compute units and 1TB storage.
Both Amazon and Azure offer non-relational databases which are considered persistent storage and charged accordingly (see above).
The obvious question any user will have is what will be the total cost to run the app on the cloud. To this end Microsoft has two offerings which provide a package of resources available at a single monthly charge (overages are charged at the standard rates). By contrast Amazon only offers al-la-carte pricing.
The Azure Development Accelerator Core is charged at $59.95 per month for a six month subscription and provides:
A comparable setup on Amazon would be $95 if the EC2 instance is not reserved, $61.5 if the instance is reserved for one year and $52 if the instance is reserved for three years.
The Azure Development Accelerator Extended is charged at $109.95 per month and is identical to the Accelerator Core package with a 10GB SQL Azure database included, and this package therefore doesn’t lend itself to a comparison with Amazon AWS.
Across all the services the pricing for Azure and Amazon AWS is very similar, however there are three key differences :
NB In this review I have ignored services which have no natural equivalent such as the Azure AppFabric services, or the Azure Asian bandwidth charges for Azure instances location in Asia (AWS instances do not have a geolocation feature).]]>
Here are my observations in no particular order:
I will keep this review updated as I use Azure more and the platform matures, please do get in touch if you have any comments or experiences on Azure.]]>