LANSA's support for databases in the Microsoft Azure Cloud and how to Shard in LANSA

Date:9 October 2014
Product/Release:Visual LANSA - V13 and above
Abstract:LANSA supports SQL Server, Oracle and Sybase Adaptive Server Anywhere databases in the Microsoft Azure cloud to the extent that those databases are available for use in Microsoft Azure. If a sharded database is required we recommend using a third party tool like Scale Arc iDB. Azure SQL Database is not supported.
Submitted By:LANSA Technical Support

This article is a review of Azure SQL Database (hereinafter termed "SQL Azure").

Microsoft supports SQL Server and SQL Azure in the Microsoft Azure Cloud. Oracle databases may also be run in Microsoft Azure. Sybase Adaptive Server Anywhere may not be a standard offering, but it may be implemented using IaaS - installing and maintaining the database yourself. These databases are all LANSA supported platforms with specific versions listed in the Supported Platforms document. For this purpose, LANSA's support is no different whether its on a physical machine in-house or a virtual machine in the Cloud.

But what about SQL Azure? LANSA does not support SQL Azure because it has a subset of features compared to SQL Server and there are third-party solutions that offer a superior solution to SQL Azure. When SQL Azure is a single database the best transaction isolation level possible is Dirty Reads. We do not recommend this level. This is because SQL Azure does not support sp_bind_token to bind connections together.

Elastic Scale is essentially the same as the previous Azure SQL Database feature of Federation - both are sharding technologies. Unfortunately, they are not compatible though there is some migration possible. Any investment in Federation may not be transferrable.

The main reason to use SQL Azure is to improve performance by sharding the database. Microsoft have named this Elastic Scale. Elastic Scale requires application code changes. Elastic Scale features are only available in ADO.NET. LANSA Uses ODBC to access databases and so it cannot make sure of Elastic Scale. Not only are application code changes required, but the application must be aware of what keys the sharding is based on. Thus it is hard coded. An application written in LANSA would need to declare what the sharding keys are, requiring new File attributes. Also each database request would need to stipulate whether its to be executed against a shard or across all shards.

Also note that Elastic Scale does not work with Identity columns, and LANSA uses Identity columns on every table. To implement Identity columns would require generating the unique identifiers ourselves which is almost certainly going to take longer to execute than inbuilt Identity columns.

There are very good alternatives to using SQL Azure in the Microsoft Azure Cloud. To achieve higher database throughputs its possible to put a sharding/cluster layer in between SQL Server and LANSA. One to look at is Scale Arc iDB (http://www.scalearc.com/product/). This may be added into a Microsoft Azure Stack in a fault tolerant manner including automatic failover. And an advantage of using such a product is that you can switch to Oracle if you need to. This is all achieved with no code changes whatsoever.

The choice of Cloud to run Windows machines should be opened up to more than just Microsoft Azure. Except for SQL Azure, there are no features of Microsoft Azure that are not available elsewhere. Other Cloud vendors support Windows virtual machines. A notable and competitive example is Amazon Web Services which has more features in its Cloud offering.

Any customer considering SQL Azure, as an external database only, should contact their local LANSA representative to make their needs known to LANSA and to get an update on LANSA's latest support status and plans.

In all cases when you are trying various or new types of deployments it is always crucial to test thoroughly before distributing your package.