Recently I decided to embark on a personal project I had on my mind for a while now. My intention has been to write a personal finance app that would manage things like invoices, timesheets, and expenses, and allow me to easily generate the reports and keep track of the information I need.
Previously I have been keeping track of this in a spreadsheet stored on Live Mesh, but recently I have had a need to store and present the data in a much more intuitive form, and allow for greater connections, ie Invoices can be easily marked as paid without having to manually add the value to my current balance when it happens.
Since I have a need to be able to access this information from any computer, in at least two physically separate locations, I need to keep the system in the cloud somewhere, with easy access with an internet connection, and as a bonus if possible, be able to edit whilst offline and sync when connection is restored.
Since my webserver is an apache host, I found that SQL Azure would be perfect for me, at least for now. (ie. Until the pricing comes into effect and I can decide if $9.95 per month is worth it for my needs.
It is quite easy to get going with SQL Azure, you just need an invite code (register and you should get one really quickly, I believe there is no more waiting list anymore) and a Windows Live ID. Once you enter your code, you are provisioned with a server which you can add databases to (up to 5 for the CTP) and use just like you would a MSSQL 2008 instance.
Now here is where I encountered some issues. Normally I would set the server up in the VS2008 server explorer and begin creating the database using the tools there, however unfortunately since SQL Data Services (SDS) does not support the entire set of features that MSSQL supports, this simply gives me an error, and further reading reveals I need to use the sqlcmd tool, write my own app, or with some configuration I can use the management tool that comes with MSSQL 2008. For reference, sqlcmd also only comes with MSSQL 2008, and so it just depends how comfortable you are with T-SQL, as sqlcmd will work out of the box with SDS, whilst the management tool will not.
Once you have your details (Get them from the connection string) you can login to your SDS database using sqlcmd and begin issuing SQL statements to your database. This is where I begin to setup my tables, (of course I pre-plan them) and then its just a matter of using the database as you normally would in your application, either the long manual way, using LINQ to SQL, using the ADO.Net Entity Framework, or any of the other ORM libraries/tools out there.
This is probably the easiest of the Azure services to get started with, and quite powerful for connected desktop applications.
I plan to write more about the app I am writing, since it is also a learning experience for me when it comes to the in depth aspects of WPF, and also database access using LINQ2SQL or the Entity Framework. (I am undecided over which to use)
If anyone knows of any better tools to manage the SDS databases, especially tools that don’t require me to install MSSQL2008, then please let me know in the comments.