Our Blog

where we write about the things we love

10

May

What is SQL Azure?

SQL Azure is an important part of Microsoft’s Software as a Service (SaaS) cloud computing strategy. In reality, this offering is effectively a “database as a service” in which a cut-down version of Microsoft’s SQL Server relational database management system is available to provision, populate and use – all in the cloud.

In use, SQL Azure is a database server with similar functionality to that of on-premise SQL Server. It has been designed as a highly available database – behind the scenes, Microsoft maintains three consistent copies of a database at any one time. These replicated databases are held on separate physical nodes to ensure that a failure on one will not impact too greatly on any customer, as the system would failover to one of the other instances instantly.

To get started on SQL Azure, you need a Windows Live ID and a credit card. Additionally there is a free usage period of 90 days to get you started and help you determine whether this is for you.

Using SQL Azure

I have been using SQL Azure recently and am impressed with how quickly you can be up and running. Setting up follows a similar paradigm to on-premise SQL Server.

First, against your subscription, you can configure a server upon which you can create one or more databases. From the Windows Azure portal you can create new servers, new databases, and configure security, including user accounts and firewall rules. The firewall rules allow you to control the safe IP address ranges that you permit to access the database.

From the portal you can also launch the Management Console which is a web-based portal for managing your database that allows you to create new tables, views, indexes and stored procedures. Recently Microsoft added extra functionality to allow you to view performance statistics on database and CPU usage; you can also now view estimation plans against your queries.

Alternatively, you can use the connection details provided as part of the properties of the server and connect to your SQL Azure database using SQL Server Management Studio and use a familiar interface to manage your database environment. This is a great benefit, as it doesn’t require investment in new toolsets and allows users to use tools they are familiar with – they can even be used side-by-side with on-premise SQL Server databases.

Another feature I have been using recently is the Community Technical Preview of Azure Reporting Services which can be added as servers to your SQL Azure subscriptions.

Similar to SQL Server Reporting Services, reports can be developed in Visual Studio using a SQL Azure database as the data source and deploying to an Azure Report Server.

This is a great start to Microsoft’s roadmap into making cloud-based business intelligence available – currently there is no cloud-based version of Analysis Services available - and I am looking forward to seeing the extra functionality that is added in the future. In the meantime it is possible to deploy the MS BI stack in the cloud using an infrastructure-as-a-service offering. However, this method carries similar administrative overhead as an on-premise deployment.

SQL Azure vs. On-Premise SQL Server

Why would you choose SQL Azure over an on-premise deployment?

Ultimately, any decision differs from site to site and ultimately comes down to the size of the organisation, how mission critical and secure the data is, and what levels of access are needed to the database.

In terms of capital investment, because there is no need to purchase hardware and deploy and maintain servers, sizeable savings can be made just on the hardware alone. And because Microsoft is maintaining the hardware in their cloud, operational costs are also reduced.

Additionally, you just pay for the resources that you consume: the pricing structure is based around how much storage is used on a month by month basis and how much data is extracted by queries. The more you understand your databases in terms of how large they are and the rate that they grow, the easier it will be to forecast your usage over time and therefore be in a better position to calculate (and compare) your cost of ownership.

In the medium term, I believe that organisations will adopt a hybrid approach to cloud database services and continue to maintain their existing local on-premise databases, but may seek to maintain a copy of the database in the cloud that is synchronised with the local copy as part of their disaster recovery or business continuity procedures. Applications using the databases could be switched over to the cloud version and redeployed, and businesses could continue operating very quickly. Microsoft is already offering a preview of a web service called ‘SQL Azure Data Sync Agent’ which will make synchronisation between on-premise SQL Server and SQL Azure seamless and ensure integrity between the two sites.

Posted by: Stuart Birkbeck, Senior Consultant - Enterprise Applications | 10 May 2012

Tags: Azure, SQL Server


Blog archive

Stay up to date with all insights from the Intergen blog