One of the topics we like to come back to on this blog is Azure Mobile Services (ZUMO) - and rightfully so, because that’s a terrific service, capable of smoothly fueling your application’s backend in a hassle-free and scalable manner.
One thing you might have noticed about ZUMO though, is that pretty much all the online tutorials and materials related to it will show you how to work with it from scratch. One of the unknown facts about it, is that, with some slight modifications, you can actually plug in your existing SQL Server (or rather SQL Azure) database (provided you have earlier migrated it to Windows Azure of course), and serve it for your application utilizing the power of Azure Mobile Services.
Let’s a have a look at how you’d configure that.
Prerequisites for using existing database with Azure Mobile Services π
We will need a couple of things for this demo to work:
- Obviously, we’d need an Azure Mobile Services account - if you don’t have one, get a free trial at http://windowsazure.com
- It would be good if you had an existing sample SQL DB on Azure already - so that you can point ZUMO to it. If you need instructions on how to move a DB to SQL Azure check my post from a few months back
- your DB tables that you want to expose via Azure Mobile Services need to have primary key of int type and the primary key column needs to be called id (small caps matter!). If you have different primary key approach, you’d need to convert that in order to proceed
Creating a new service and pointing to existing DB π
When you create a new ZUMO service you get a choice of DBs. Instead of telling ZUMO to create a new one, you choose an option “use an existing db instance,” and in the next screen select the DB you’d like to expose via ZUMO.
Once the service has been created, the way ZUMO works, is that it will look for a DB with a schema name that matches the service identifier - for example, if your service is called testservice, the schema will be called the same - testservice. If you used dashes (i.e. my-test-service), then the schema name should use underscores (my_test_service).
So you should head over to SQL Azure DB management, choose the DB server where your database is located, and change its schema to be compatible with the newly created Azure Mobile Service.
Suppose, you, like me, have a simple dbo database:
You can create a new schema and transfer tables to it:
CREATE SCHEMA my\_new\_service;
ALTER SCHEMA my\_new\_service TRANSFER dbo.product;
ALTER SCHEMA my\_new\_service TRANSFER dbo.color;
And now you should have:
But where is my stuff?? π
Everything seems great, but if you head over to your service’s Data tab under Azure Management Portal, it still shows no data - and that’s OK.
ZUMO will not detect the tables automatically, you still have to add them, except this time, when you add them, they will not be created (since they are already there, under the correct schema as well) - all this operation would do, is it would link your existing tables to the service.
Obviously the name needs to match the name of the existing tables. Let’s bring our Color and Product tables.
We can now see the same data that we have in the database:
appearing in ZUMO (after refresh)
We can also go ahead and simply query the service using a Web Request. In this case, I’m using Postman to make a REST API request to the Color table:
Summary π
All in all, nothing too complicated at all! As long as you are willing to swallow a little annoyance of having to use primary keys of int and key name id, you can very easily use existing DBs with Azure Mobile Services.
This should also be helpful in development, when you can develop against a local SQL DB and have the production version use ZUMO direcly.
