Does SQL Azure have Allure?

image I love SQL Server 2008 spatial functionality. Not only because it has effectively killed off the middle-man GIS database software, but it even sticks to OGC standards allowing the same data and tables to be used in nearly all GIS software. However I have some reservations about the use of SQL Azure for GIS applications.

FAQ

How is SQL Azure Database different from working with a hoster using SQL Server?

Developers do not have to install, setup, patch or manage any software. High Availability and fault tolerance is built-in and no physical administration of hardware, storage or servers is required.

If you have a good hosting solution then the same benefits apply (well maybe not the “self-healing” also mentioned). The only real difference I can tell is that you pay a lot more, to be able to scale more easily. Its unlikely your Local Planning Mapping system is suddenly going to need to scale up to meet the expectations of millions of new users, no matter how great the system looks.

Update: Many thanks to David Chou for his clarifications on this – see comments below. In essence a SQL Azure database uses three replicas of your database with a layer on top that makes it appear to the application and user as a single instance. It is this new architecture that allows for scalability, and the self-healing (which still makes me think of Tekken’s Yoshimitsu).

Windows Azure and SQL Azure SLAs are independent of our on-premises Microsoft licensing agreements.

So if you do plan to continue standard “earth” development, you’ll still be paying SQL Server licences. For most government departments and large businesses there will always be some data that needs to be stored locally.

For Developers

Currently you can’t bring your existing on-premises Windows Server, SQL Server to Windows Azure, SQL Azure.

So it doesn’t seem possible to develop database applications locally and then upload all your views, stored procedures, functions and data in one go to Azure. It appears this only applies to the server software (see comments) – I’d imagine Enterprise level funcitonality such as mirroring or replication. The actual tables, stored procedures, and views will be fine to import.

SQL Server Management Studio will be able to access the online databases, but I would be interested to see how quickly a result set from a query would be returned. Would this data be returned locally and slowly, or would it work in a similar way to Remote Desktop by returning images from the server?

Cheque Please

The pricing structure is as follows:

  • Web Edition:  Up to 1 GB relational database = $9.99 / month
  • Business Edition:  Up to 10 GB relational database = $99.99 / month
  • Data transfers = $0.10 in / $0.15 out / GB – ($0.30 in / $0.45 out / GB in Asia)*

image

I’ve just completed a GIS project using SQL Server 2008, with around 60 different layers. The database is around 15 GB (this stored procedure returns disk space on a table by table basis). Uploading this data would be cheap enough at only $1.50, but would take hours. It was far easier to go round to the hosts with an external hard-drive, than upload from our network.

Where things get scary for a GIS developer is considering the data transfers out of a system as soon as the Microsoft spatial data types get involved.

The amount amount of bytes sent over the network in SQL Server for each query can be viewed using Client Statistics. Selecting 100 records from one of my tables resulted in 14 KB of data transfer.  Selecting 100 records and their associated polygons resulted in over 10 times more traffic at 151 KB (here’s an online calculator for bit/kilobyte conversions).

image

Whilst I presume it is possible to set up a WMS server such as MapServer in Azure, it is unclear whether the “data out” costs apply to data that leaves the cloud or to any data that leaves the database (thanks to Ed Katibah for more details – charges only apply to data leaving Azure sub-region (Western Europe is a sub-region). In this second scenario it could well be game over. It is easy to imagine spiralling costs if you wish to display a few dynamic vector layers, and the database takes a hit on every user’s zoom or pan.

It’s (not) all about the Data(base)

The target market for SQL Azure in GIS however may not be individual applications, but rather business to business spatial services. Datasets used for geolocation and navigation could be stored in one place with an API that could then be called by any number of web applications. These “clients” could then be charged a premium on top of Azure’s costs for the additional services. Heavy use of results caching would mitigate the price of data transfer.

Unfortunatley for Microsoft it appears once again Google already has a big chunk of this (spatial) market wrapped up. For free, or $10,000 if charging for your service.  Another example is James Fee’s WeoGeo. I presume by James’s quote about getting your ducks in a row his is clearly a business model that could benefit from SQL Azure. Both examples differ from the SQL Azure model of selling database services in that they both provide data as part of a cloud solution.

There are still many unknowns on how this will develop over time, and who will benefit from Azure. Its nice to know the spatial functionality is out there in the cloud, but for the majority of web mapping applications the risk of sky-high costs may keep it there. Case studies and early-adopter’s experiences will hopefully help paint a clearer picture. Microsoft’s main competitor could well be Microsoft. SQL Server 2008 Express is free (however with a 4GB limit), and includes both spatial functionality and Reporting Services. If you need to scale up it seems simpler to change your licence rather than your entire mindset.



6 views shared on this article. Join in...

  1. Ed Katibah says:

    Good discussion. I’ve talked with some of our SQL Azure folks and they had the following comments: “[The] Data transfer rate issue is clearly not a problem between the DB and the app as long as they are in Windows Azure. Apps using SQL Azure that aren’t in Windows Azure will pay additional amounts but that is [a] less common [scenario] due to network latency [considerations]. Usually people end up putting some local cache DB like SQL [Server] Express [in Windows Azure].”.

    Here is information from our web site: Data Transfer Details: “Our data transfer rates are determined by the region in which your solution is deployed. Data transfers between Azure Services located within the same sub region are not subject to charge. Data transfers between sub regions are charged at normal rates on both sides of the transfer. A sub region is the lowest level geo-location that you may select to deploy your applications and associated data.

  2. David Chou says:

    Great post. And we’re glad to see that you find value in using SQL Server for GIS applications. Although a few clarification points may help with understanding SQL Azure better and how it can be best used.

    1. SQL Azure is a multi-tenant, scale-out relational database service. It’s not “hosted SQL Server” a-la-SaaS style. The implementation today, in a nutshell, allows you to interact with the SQL Azure application, but each logical database is actually managed as 3 separate replicas underneath the SQL Azure application, which then use SQL Server as the relational database engine. The SQL Azure application then load-balances and synchronizes the incremental changes across those 3 replicas. To you it’s looks like one SQL Server database over the Internet but SQL Azure uses this strategy to ensure resiliency and scalability. It’s not the same as any hosting solution anywhere else.

    2. The “Currently you can’t bring your existing on-premises Windows Server, SQL Server to Windows Azure, SQL Azure” comment was intended to point out that developers cannot bring server software to deploy into Windows Azure and SQL Azure; as you can tell from the first point above – Windows Azure and SQL Azure aren’t hosted servers. Thus, from a development model perspective, Windows Azure and SQL Azure actually do support current applications and databases created locally and then uploaded into Azure. In fact, for SQL Server, all your views, stored procs, functions, and data, can all go directly into SQL Azure. There are some differences but today we’re at about 98% feature parity and are working towards 100%.

    3. Similar to your observations, and as Ed Katibah pointed out, SQL Azure isn’t ideally suited as a replacement for a local SQL Server database. This also goes back to what does cloud computing represent? Many people think cloud is simply someone else’s data center that runs the same things for rent. In our case, Windows Azure and SQL Azure, as they are implemented as a different type of technology, they’re not simply hosted versions of software that you have locally. And thus, they’re ideally suited for different types of workloads and application scenarios. However, some laws of physics still apply, as an application that uses a relational database as a back-end will continue to operate better having the database local as opposed to over the Internet. And that means many applications that use SQL Azure will also be more ideally suited to be hosted in Windows Azure as well. Other scenarios also evolve around data sharing leveraging data integration techniques.

    Just my thoughts. :) Best! -David (blogs.msdn.com/dachou)

  3. geographika says:

    @Ed Katibah
    Thanks Ed. Good to know that its the data that leaves the cloud that is counted. I presume that a WMS MapServer could be set up as an Azure application after reading this post.

    I guess the sub-regions also help government departments from keeping within data legislation rules.

    • Dalma says:

      Hi,1) Go to backofficeplus > Storefront > Reviews > Search by koewyrdEnter the search criteria to find review by subject or review content.When you find the review click Delete link.2) You can add pIdProduct variable to the body part of call sendMail( )Also you can query the database to obtain product name, sku, etc.3) You can do that from backofficeplus > Storefront > Reviews > View Pending[]

  4. geographika says:

    @David Chou
    Your technical summary makes far more sense than the marketing pages! I’ve updated my post to include your comments.

    With regards to where cloud computing could be useful, hosting the mapping engine and database in Azure could provide a powerful, scalable back-end for many different client map viewing applications.

  5. Douglas says:

    Hi,First of all try to comment or romvee the lineon error resume nextin the comersus_backoffice_createHTMLPagesExc.asp file to check if a trapped error appears.If you cannot see any error, maybe you need to use two queries instead one, to retrieve the products info.Currently you have the query select * from products.You should use an additional query to retrieve few product values like price, sku, description.In the other query you should retrieve the others fields like imageURL, details, etc.Note that these modifications require advanced asp skills.If you are not a developer, contact your sales representative with your order number to send you the modified script.[]

Leave a Reply

Your email address will not be published. Required fields are marked *

Comment

You may use these tags : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>