SQL Azure Use Case: Department Application Data Store

This is one in a series of posts on when and where to use a distributed architecture design in your organization's computing needs. You can find the main post here: http://blogs.msdn.com/b/buckwoody/archive/2011/01/18/windows-azure-and-sql-azure-use-cases.aspx


Most organizations use a single set of enterprise applications where employees do their work. This system is designed to be secure, safe, and to perform well. It is attended and maintained by a staff of trained technical professionals. But not all business-critical work is contained in these systems. Users who have installed software such as Microsoft Office often set up small data-stores and even programs to do their work. This is often because the system is “bounded” - the data or program only affects an individual’s work.

The issue arises when an individual’s data system is shared with other users. In effect the user’s system becomes a server.


There are many issues with this arrangement. The “server” in this case is not secure, safe and does not perform well at higher loads. It may have design issues, and is normally cannot be accessed from outside the office. Users might even install insecure software to allow them to access the system when they are on the road, adding to the insecurity. When the system becomes unavailable or has a design issue, IT may finally become aware of the application’s existence, and it’s criticality.


There are various options to correct this situation. The first and most often used is to bring the system under IT control, re-write or adapt the application to a better design, and run it from a proper server that is backed up, performs well, and is safe and secure. Depending on how critical the application is, how many users are accessing it, and the level of effort, this is often the best choice.

But this means that IT will have to assign resources, including developer and admin time,licenses, hardware and other infrastructure and development resources, and this takes time and adds to budget.

If the application simply needs to have a central data store, then SQL Azure is a possible architecture solution. In smaller applications such as Microsoft Access, data can be separated from the application - in other words, the users can still create reports, forms, queries and so on and yet use SQL Azure as the location of the data store. A minimal investment of IT time in designing the tables and indexes properly then allows the users to maintain their own application. No servers, installations, licenses or maintenance is needed.


Another advantage to this arrangement is that the user’s department can set up the subscription to SQL Azure, which shifts the cost burden to the department that uses the data, rather than to IT.

Microsoft Access is only one example - many programs can use an ODBC connection as a source, and since SQL Azure uses this as a connection method, to the application it appears as any other database. The application then can be used in any location, not just from within the building, so there’s no need for a VPN into the building - the front end application simply goes with the user.

Another option for a front-end is to have the power-user create a LightSwitch application. More of a pure development environment than Microsoft Access, it still offers an easy process for a technical business user to create applications. Pairing this environment with SQL Azure makes for a mobile application that is safe and secure, paid for by the department. Interestingly, if designed properly, the data store can serve multiple kinds of front-end programs simultaneously, allowing a great deal of flexibility and the ability to transfer the data to an enterprise system at a later date if that is needed.


Example process of moving a Microsoft Access data back-end to SQL Azure: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=375

More information on LightSwitch: http://www.microsoft.com/visualstudio/en-us/lightswitch