MySQL to Azure SQL Database Migration

I’ve been experimenting lately with Azure and wanted to kick the tires of SQL Database with a real dataset that I have. It turns out that this is pretty straightforward, but I had to collect information from a number of sources. Here are my notes – hope they are helpful:

  1. You’ll need a Windows machine to run SQL Server Management Studio (SSMS) and an Azure account with your target SQL Database provisioned.  There is a great walk through on how to do that written up in the Azure documentation.  Follow the steps there to get SSMS connected to your Azure db instance.   
  2. Install the MySQL ODBC Connector if you haven’t already.
  3. Install the SQL Server Migration Assistant for MySQL (SSMA).   Note: You actually need to install both it and an extension pack that are bundled in the same zip file you download.
  4. Run SQL Server Migration Assistant for MySQL.   It’ll prompt you for a license, which requires jumping back to the Microsoft site, and moving this license file to a path that it gives you.
  5. Click “Connect to MySQL” in the top left corner.  Point SSMA to your MySQL instance and connect.   SSMA will introspect the instance and give you a list of the databases you can connect to.   Check off the database you’d like to migrate.
  6. Click “Connect to SQL Azure”.   You’ll need your SQL Database server’s instance name from the Azure Management Portal, which should look something like this:  kcpnnc4bcy.database.windows.net and your user name and password for the db instance that you created in step 1.   Check off the database you created.
  7. Ok, we’ve connected the two databases.  Expand the node for your MySQL database and check off the items that you want migrated.
  8. SSMA can do a dry run of the schema conversion and produce a report.   Do this by right clicking the “Tables” node on your MySQL connection and selecting “Create Report.”   I had 70 odd warnings to work down through – all were slight collation and schema differences that I could safely ignore – but check them all anyway.
  9. It takes three steps to get your data migrated.   Right click your MySQL “Tables” node again and click “Convert Schema.”   This will create the schema on your Azure SQL Database.  Check that over and make sure it looks good to go.
  10. Right click your new Azure SQL Database and select “Synchronize with Database”.   This force you to enter credentials again for both databases and then will synchronize schemas and indexes.
  11. Finally, right click the “Tables” node for your MySQL database and select “Migrate Database”.   Enter your credentials again and SSMA will jump into migrating the data.

Walkthrough: Ruby on Rails on Windows Azure

I setup Ruby on Rails on a new shiny Windows Azure VM instance today and I thought I’d share this walkthrough on how to get yourself up and running.

First off, you’ll need a virtual machine running one of the Linux distributions. To do that, go to your Windows Azure management console and click “+ New” -> “Virtual Machine” -> “From Gallery” -> “Ubuntu Server 12.04”.

Move to page #2 and name the machine (I called mine “frontend”) and choose a username and password. On page #3, choose a DNS name for the url that you’ll use for this app (you’ll need it down below, so make a note of it). On page #4, just accept the defaults.

After a minute or so of provisioning work, your VM should come up and go green.

Using your favorite SSH program, attach to a shell on the machine by using the login credentials you created previously and the DNS name you assigned above.

ssh <username>@<dns-address>.cloudapp.net

Entering your password should drop you to the Linux command line in your VM.

Ok, we’re ready to get started! The first thing we need to install is RVM (Ruby Version Manager). You can do that like this:

curl -L get.rvm.io | bash -s stable

This should download and setup rvm. You only then need to source rvm’s script to get all of the paths loaded into the environment.  Look in the output of the last command for the appropriate command for you (mine given as reference below).

source /home/testapp/.rvm/scripts/rvm

Next, we need to find out what dependencies we have for installing ruby. rvm has a command for that.

rvm requirements

Copy the requirements and prepend them with a sudo to install them (below are my dependencies as of this writing for reference):

sudo /usr/bin/apt-get install build-essential openssl libreadline6 libreadline6-dev curl git-core zlib1g zlib1g-dev libssl-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt-dev autoconf libc6-dev ncurses-dev automake libtool bison subversion

Now that we have the ruby version manager and dependencies, installing Ruby is easy. I’ll use 1.9.3 here, which is the current stable version:

rvm install 1.9.3

This will take some time to complete. Afterwards, we’ll want to make this the default version as well:

rvm use 1.9.3 --default

Next, we’ll install RubyGems, the package management app for Ruby:

sudo apt-get install rubygems

and then install the Ruby on Rails gem using RubyGems:

sudo gem install rails

Finally, we need a javascript runtime for Rails. I’ll install that here by installing node.js to keep it simple.

sudo apt-get install python-software-properties
sudo apt-add-repository ppa:chris-lea/node.js
sudo apt-get update
sudo apt-get install nodejs npm

All of the installation work should now be completed. Let’s create a test application to try it all out:

cd ~
rails new testapp
cd testapp
rails server

This fires up the Rails WEBrick development server with the default port binding (3000), which you can open up to the world from the Azure virtual machine dashboard for this machine. Click “endpoints” at the top, select “Add Endpoint” at the bottom, and add a TCP endpoint with the name “web” and public/private ports of 3000, and, voila, that should be it. You should have a running Ruby on Rails application on Azure — typing http://<name of your app>.cloudapp.net:3000/ in your browser should bring up the default Rails home page.

Using the development WEBrick server is obviously not production ready, but given that this is real honest to goodness Ubuntu machine, there are existing walkthroughs for getting passenger and NGINX setup that can help you through that.

Windows 8 applications with Ember.js

I’ve previously talked about how painful platform proliferation and fragmentation is for small startups.   It is an immense burden for us to keep our applications at parity across platforms when we have to implement and test everything 3 times across web, Android, and iOS.   So, as I consider adding a Windows 8 app to the mix, I’ve prioritizing spending some time (out of sheer survival) to experiment on how we can do this in a manner that we can better share common components and logic without stooping to the lowest common denominator.   I want to take full advantage of the Metro UI elements but share as much as possible with our web app.

In parallel, I’ve also been experimenting with ember.js and been very pleased with it.   ember.js itself provides a great separation of concerns in classic MVC style with easy data binding support.  Ember Data provides a very nice persistance layer for REST endpoints.   And Ember Route Manager keeps your app transition logic untangled and efficiently moves your app between view states, only tearing down/building the views that are disappearing/appearing in the new state.

And so, inspired by Windows 8’s addition of a first class HTML5+Javascript development model and the success of LinkedIn’s hybrid mobile application, I dove in and built a skeletal Windows 8 application with ember.js last night that I wanted to share with the community. I think the approach is highly promising – but I’d love feedback on where I ended up.

I’ll walk through the points around Ember Route Manager that might be less clear.  I’d recommend you clone my github project to follow along in Visual Studio:

git clone git@github.com:timfpark/windows8-emberjs.git

The app is structured as a normal Windows 8 HTML5+JS app, with default.html as the entry point for the application.   As a single page app, it pulls in all of the javascript libraries, views, and controllers it’ll need here and initialize the application in main.js.  It also defines all of the Handlebars templates it’ll use as part of the Ember application.   A real world scale application should break these out into individual template files and use require.js for the javascript files, but I’ve included them all inline here for simplicity.

routes.js is where we define how our views all fit together via Ember Route Manager.  This defines a top level view state called ‘desserts’ with two substates called ‘index’ and ‘show’ and an initialState of ‘desserts.index’.   We define views that should  be associated with each state and its important to note that the higher level states will also be included.   So for desserts.index, you get both the view for ‘desserts’ and the view for ‘index.’   You can also add logic for when you enter and exit a view state and handle any setup needed for the view, as we do in the ‘show’ enter function where we load the selected desert for the view.


EmberApp.ContentRouteManager = Ember.RouteManager.extend({
 enableLogging: EmberApp.config.enableRouteLogging,
 initialState: 'desserts.index',

 desserts: Ember.ViewState.create({
   route: 'desserts', // static route
   view: Ember.View.create({ templateName: 'app/templates/desserts/main' }),

   index: Ember.ViewState.create({
     route: 'index', // static route
     view: EmberApp.DessertsIndexView
   }),

   show: Ember.ViewState.create({
     route: 'show/:id', // dynamic route
     view: EmberApp.DessertShowView,
     enter: function(stateManager, transition) {
       this._super(stateManager, transition);
       EmberApp.dessertController.loadDessert(stateManager.params.id);
     }
   })
 })
});

EmberApp.routeManager = EmberApp.ContentRouteManager.create();
EmberApp.routeManager.start();

The views and controllers are typical ember.js, so I won’t dwell on them. One interesting detail is in DessertListViewItem.js.

EmberApp.DessertListViewItem = Ember.View.extend({
    content: {},
    click: function (event) {
        EmberApp.routeManager.set('location', 'desserts/show/' + this.getPath('content.id'));
    }
});

I’m using the event here to transition to the detail view for the selected dessert. To do this, we simply tell the route manager which view state we’d like to end up in and it takes care of transitioning us there in the most efficient manner possible by walking the state tree. This means that in the ‘desserts.index’ -> ‘desserts.show’ transition that the ‘desserts’ view at the top level will not be replaced but only the ‘index’ view for a ‘show’ view.

I’ve kept the sample simple by not bringing Ember Data into the mix but a real app would obviously implement against a store instead of stub data (I wrote a previous entry on how to do that).

Ok, so we have implemented a simple Windows 8 application with Ember.js.  So what?

Back up now and look at the application broadly.   What could be reused by a web application?   The answer is:  a whole heck of a lot.   There is some Windows 8 initialization code in main.js and a real application should take advantage of native Metro UI controls (like WinJS.UI.ListView for our dessert picker) in their templates.  That’s ok — we can isolate those pieces in a metro specific template while reusing the view logic, the controller logic, the model logic, probably a lot of the routing logic, and maybe even much of HTML if we have high CSS-fu.

This isn’t write once, run everywhere.   And it shouldn’t be – we all know that doesn’t work.  You should take advantage of the native elements on a platform to match user expectations of how things work.

But this isn’t “write once, write it all again, maintain it all in parallel” either.   Its genius that Microsoft included HTML+JS in Windows 8.   It makes developing Windows 8 apps cleaner, easier, and more reusable for other platforms while still enabling developers to leverage native UI controls.

Let me know what you think.   I’m happy to add details if you find anything unclear.  Pull requests highly appreciated if you have ideas on how to improve what I’ve started.

I, for one, welcome our javascript overlords

Today’s news about LinkedIn’s HTML5 + Javascript driven iOS app tied together a bunch of trends in my head and, as my former British colleagues would say, made the penny drop for me.

LinkedIn has not just dabbled in HTML5 + Javascript but written nearly their whole application in it and done so with high enough fidelity to leave people guessing which parts were native.  This should make all of us sit up and take notice.

The trend in web apps is obviously towards client centric frameworks like the maturing backbone.js, the emerging ember.js, and the experimental Meteor that provide ever increasing interactivity and connectivity against server backends that are increasingly API and real time oriented.

The trend in mobile is not so positive but clear.   It is literally all some small startups can do to stay on top of maintaining two native apps for Android and iOS.   If Windows Phone pulls itself into the mix as a credible 3rd platform (and I have no doubt Microsoft is willing to go home in a body bag before quitting that quest), it will be a backbreaker and something is going to have to give.

What I realized today is that what is going to give is native.   App developers will increasingly forgo developing natively and are going to go hybrid, leveraging Javascript they already have for their web app and using that where possible across their mobile apps.   Mobile platforms and Javascript engines are finally performant enough, Javascript frameworks are rich and mature enough, and there is so much pain in the app world around developing and fixing everything N times (especially tricky protocol level stuff) that we have reached a breaking point.

The future is Javascript and LinkedIn’s app shows us that the future is here.  And Microsoft, against all the recent trends, seems to have read this correctly and is the best positioned to take advantage.

Windows 8 has a first class HTML5 + Javascript development model that doesn’t force you to re-implement everything and enables you to leverage platform capabilities.   You can bring your Javascript implementation of your API client for your backend (and your HTML5 templates if you so desire) but still take advantage of hardware accelerated transitions, tiles, and other previously only native features (or native through some lowest common denominator interface like PhoneGap that doesn’t clear the Turing test).  Its not hard to see Microsoft bringing this model to phones in Windows Phone 8.

Obviously Chrome OS also has its eyes on this, which is great, but its hard to see how that desktop platform meshes with Android and Google has nothing like Metro that helps app developers scale form factors (nor an installed base of a billion PCs).   Apple has clearly bet native (and this strategy probably makes sense in terms of locking in developers to their platform) but also lacks a coherent development model between desktop and mobile.   I suspect these both will change as the take up of this approach accelerates.

Perhaps this has been obvious to others – or perhaps I have been too pessimistic.   This has always felt like it was the future but today was the first time that we experience was truly viable for me.

It can’t happen fast enough for me.  I’m sick of planning, developing, and fixing the same app and bugs at least three times in entirety.

I, for one, welcome our Javascript overlords.

Getting started with Ember Data and Rails

I’ve recently become smitten by ember.js.   As a new framework, however, there isn’t a lot of documentation yet around some of the supporting elements.  I had a bit of a struggle with getting Ember Data working against Rails and thought I would write up this walkthrough to help out other folks working through the same issues.

I’m going to skip the Rails setup.   I’m also going to assume that there is an existing scaffold for a resource named Posts with one field called title and that you’ve set up the basics of an ember application in your Rails app.

First, you need to add Ember Data to your app/javascripts/application.js file if you haven’t already:

//= require  ember
//= require  ember-data

Next, create the Ember Data store in your Ember app.js file (I place these at ~/app/assets/javascripts/ember in my ember.js projects):


App = Ember.Application.create();
window.App = App;

App.store = DS.Store.create({
 adapter: DS.RESTAdapter.create({ bulkCommit: false }),
 revision: 3
});

The adapter tells Ember Data that your endpoints are REST endpoints.   You can write an adapter for endpoints that work differently than the JSON endpoints automatically generated with Rails’ scaffolding but thankfully we don’t need to do that.   That said, there is one one caveat:  we need to return the JSON for our Post resource wrapped in a top level key of the name of the model.   This is the gotcha that stumped me for a few hours.   To do this, open up post_controller.rb in your Rails app and edit the index method to wrap the response in a top level element with the plural of your model:


# GET /posts
# GET /posts.json
def index
  @posts = Posts.all

  respond_to do |format|
    format.html # index.html.erb
    format.json { render json: { posts: @posts } }
  end
end

That completed, let’s build our post model.   Create a post.js file in your Ember models directory:


App.Post = DS.Model.extend({
  title: DS.attr('string')
});

App.Post.reopenClass({
  url: 'post'
});

What this does is tell Ember Data that you have a model Post with one field “title” and that you can find the REST endpoint for the index of such models at “/posts”.   Ember Data automatically pluralizes the endpoint so notice that we provided the singular here.

Ok, we have our model.   On to the controller.   Create a postController.js in the Ember controllers directory:


App.postsController = Em.ArrayController.create({
  content: App.store.findAll(App.Post)
});

What this controller does is automatically load all of the posts at instantiation using the Ember Data store that we created previously.   Easy huh?

With the controller created, let’s create a view that uses it starting with the template.   Create template/posts/index.hjs and add the following  template to it.


<ul>
   {{#each post}}
      <li>{{title}}</li>
   {{/each}}
</ul>

This keeps it simple – it displays all the posts in a list.

Ok, so we have our template.   Let’s create the view that hooks the template up to data.

Create views/posts/indexView.js:


App.PostIndexView = Em.View.extend({
  templateName: 'ember/templates/posts/index',
  postBinding: 'App.postsController'
});

The view is responsible for defining the template to use and what data should be bound to it.   Here we are using the template we created earlier and also using Ember’s Binding suffix magic to specify that post should be bound to the content of the postsController that we wrote earlier.

Ok – we have all the Ember elements together so the last step we need to do is replace the Rails app/views/posts/index.html.erb boilerplate with:

<script type="text/x-handlebars">
  {{view App.PostIndexView}}
</script>

Then you should be able to do a refresh on http://localhost:3000/posts and see your posts rendered in all their client side framework glory!