Connect:    



Event Calendar

Some useful links

Online Courses

Articles


A software architect, Azure expert, and former Microsoft evangelist, Mike Benkovich dedicates huge amounts of his time to helping his fellow developers and burgeoning programmers learn about new technologies and platforms. Mike’s website equips developers with tips and resources to help them get to grips with technologies including cloud, data and devices, and he produces online courses covering areas like Azure enterprise development and serverless computing. Mike is also a chronic sharer of puns, so head over to his Twitter feed if you’re after a laugh (or a groan).

BenkoBLOG by Tags


Blog Roll...
Conferences
Regional User Groups

Blog

Setting a schema for the database in Azure Mobile Services

@MikeBenkovich 01/12/2015

I’ve been working on a project using Xamarin.Forms and Azure Mobile Services for a while, and one issue I came across that wasn’t entirely clear is how do you work with an existing database, yet support updates to the tables and be able to deploy to different environments (like Test, QA and Prod)? Hopefully I can shed some light with this post.

Azure Mobile Services is a feature rich cloud service that promises to take care of some of the complexities of building connected mobile apps. It supports things like 3rd party federated identity, push notifications, logging and more. With the original node.js release it supported JavaScript configuration for the CRUD operations against a table along with a dynamic schema that adapts to the request received via REST. In the .NET release they replace node.js with the ability to roll your own logic in C# and handle the data interaction with WebAPI type controllers. It requires a bit more work, and handling the database changes can be confusing.

Fortunately I’ve found a couple articles that help illustrate how this is done (on MSDN), but it doesn’t specify how to handle ongoing updates. To make it work for my scenario I either needed a way to support data model changes to a .NET backend mobile service, but have a consistent schema name when I move between environments. In the second article they show how to enable code-migrations, and to replace the default database initializers, by using the NuGet package manager and modifying code in the WebApiConfig.cs file. The steps were:

  1. Use NuGet Package Manager to Enable-Migrations
  2. Add a starting migration
  3. Update the WebApiConfig.cs file to use a DbMigrator to update the context instead of calling the default initializer

    public
    static class WebApiConfig
    {
    public static void Register()
    {
    // Use this class to set configuration options for your mobile service
    ConfigOptions options = new ConfigOptions();

    // Use this class to set WebAPI configuration options
    HttpConfiguration config = ServiceConfig.Initialize(new ConfigBuilder(options));

    // *** BENKO: Enable database migrations for the service
    //Database.SetInitializer(new MobileServiceInitializer());
    var migrator = new DbMigrator(new Configuration());
    migrator.Update();
    }
    }
  4. Test local and confirm it’s working

I added a couple additional changes to set the schema name for my app in the MobileServiceContext.cs file (in the Models folder).


protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// *** BENKO: This is what sets the schema name to the service name...
string schema = ServiceSettingsDictionary.GetSchemaName();
schema = "mySchema";
if (!string.IsNullOrEmpty(schema))
{
modelBuilder.HasDefaultSchema(schema);
}

modelBuilder.Conventions.Add(
new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
"ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));
}

I also need to make sure to create the schema on my database instance in SQL Azure and grant rights to the service user account. You can get the user account using SQL Server Mgmt tool (expand the users of the database node, or by running a SQL Script to select name from the SysLogins table of the master db. Once you have it you will need to create the schema an grant rights to it. Assuming the service user is ABC123Login_myServiceUser, the script looks like this:



create schema mySchema

-- Grant specific access rights to use based on Schema
GRANT
SELECT, INSERT, UPDATE, DELETE,
ALTER, CONTROL, EXECUTE,
REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION
ON SCHEMA::[mySchema]
TO [abcdefghijLogin_democityUser]

When you publish the mobile service it will attempt to update the database using the schema provided. If there are error you can use the service’s logs to figure out what’s missing. By specifying the schema name instead of using the service’s name I’m able to deploy to multiple environments but integrate this data with my other applications.

Happy Coding! (originally posted on www.benkotips.com)

Technorati Tags: ,,,