Connecting an Existing Database to Windows Azure Mobile Services

With all the excitement over Windows Azure Mobile Services, one of the questions we get frequently is how to connect Mobile Services to an existing database.  It turns out that this isn’t very hard to do but may require some changes to your database.  Today I’ll walk you through taking a database hosted locally in SQL Server, pushing into Windows Azure SQL Database, and connecting it using a Mobile Service.  If you’d like the quick explanation with out all the pretty pictures and moving from a local DB to Windows Azure, you can check out Paul Batum’s answer on the MSDN forums (I didn’t find this until after I had figured out all of the below).

In order to use Mobile Services, you’ll need a Windows Azure account.  You can sign up for a free trial here.  While in preview, Mobile Services stay free even when the 3 month trial of the other features of Windows Azure expires.

Changing our Local Database

Let’s start by loading our local database and looking at the changes necessary.  You can follow along by creating our sample database on your own server.  You can use this script to do so.  This database is meant to store the data for a blog website.  Specifically, it has a table to store Users and a table to store entries.  It’s a very simple database but it will suffice for what we want to do. 

Notice that the tables begin with “dbo”.  This is the schema of our database.  When a table is created in Mobile Services, it sets the schema to be the name of the mobile service.  Later in this walkthrough, we’ll create a mobile service named “fakeblog” so we’ll create a new schema here to match that.  We can accomplish this in two ways: through the UI of SQL management Studio, or executing a query.  I’ve linked to instructions for creating the schema through the UI.  Once that is done you’ll need to go into design mode on each table and change it’s Schema property.  Alternatively, you can execute this SQL script:

One thing to point out is that you’ll need to have CREATE SCHEMA permissions for this to work.  With that done, there is one more change we need to make to both tables.  If you look at the tables, you’ll see that both of them have a column named “id”. 

This is great because Mobile Services REQUIRES that each table have a column named “id”.  However, we also need this column to be a Primary Key column (in addition to it being a non-null Identity) field.  We can solve this with a quick script:

Now our DB is ready to be ported to Windows Azure SQL Database.

 Moving the database to the cloud

Thankfully, SQL Server Management Studio 2012 has a wizard to easily make this happen.  In SQL Server Management Studio, right click on your database and Tasks and select Deploy Database to SQL Azure.  The first screen that will appear (if this is the first time you’ve used it) will be informational and will explain what you’re doing.  The next screen will look like this:

Clicking the “Connect” button will launch the usual connection wizard you see when first launching SQL Server Management Studio.  For this to work, you’ll need to have already created a database in the Windows Azure portal as well as granting firewall access.  Once in the portal, select your database and click the “Manage” button at the bottom:

When you do that, the first thing that happens is the server checks your IP address to see if it’s on the list of IPs that are allowed.  You’ll see two popups show up at the bottom of the screen to add your current IP, go ahead and click yes on each.

Once you’ve done that, you’ll be able to connect to your SQL Database server from your local SQL Server Management Studio client.  Go back to the Deploy Database to SQL Azure wizard and connect to the server (you can click a link to view the credentials in the same screen in the portal).  When you tap through, you should see the operation start and after a few seconds it should complete.

Connecting your mobile service

Now let’s return to the Windows Azure portal and set up our new mobile service.  Once you’re in, go to + New at the bottom and select Compute -> Mobile Service -> Create.  Make sure you enter a name that matches the schema you created for your database tables earlier (in our case fakeblog):

At the next screen in the wizard, select the database you uploaded and enter the credentials for it (these will be the creds for the database server):

Now, go into your mobile service and select the Data tab at the top.  When you do so, you’ll see that you don’t have any tables.  This happens because we need to “tell” mobile services about the tables we already have.  Go to the + Create button at the bottom when on the Data tab and enter the names of your tables (Users and Entries for our fakeblog db).  After you create the tables, they will initially show up in your table list and it will show 0 records:

However, if you click into a table you’ll see the records that were previously inserted (by the create script) into your database:

Now you can access your database from anything connected to mobile services.

Conclusion

Today we saw that it isn’t that difficult to put an existing database into a Mobile Service.  This can also be done if you already had a database in SQL Database as well as long as you keep in mind the changes necessary for a table to work with Mobile Services (the id column and the schema).  Additionally, not every data type is supported in Mobile Services at this time.  For example as we saw when uploading images into Mobile Services, binary data types don’t currently work.  For more information on the supported data types, take a look at the CUD section of this article on Dinesh’s website.