Redesign web application > Move from SQL-server to CosmosDB (or not)

[Mike Jansen]

I try to find as much information as possible about CosmosDB but have not found my answer yet.

We have a (kind of) straight forward web application (Classic ASP, Javascript, MS SQL server). We have to move/redesign this application to Azure (possibly .Net). We are not sure to use CosmosDB. Our current sql database is (of course) relational and uses a lot of stored procedures. 

Why should we use, or not use, CosmosDB? Price is also something we need to consider.

Can someone help us out to make the best decision?

[#2 · Accepted solution]

So this is a pretty broad question and tough to answer. The frustrating and truthful answer is "it depends" but to be clear though you can be successful with both.  

I have several CosmosDB implementations behind me and I'm a big proponent but in my experience to be successful it does take a mental shift from the traditional relational normalized data models that has been ingrained into the industry (and our heads) for the last 30 years.  I still work regularly with traditional relational databases but there are several aspects of CosmosDB that continually lead me to frequently recommend it.

• Serverless - I'm not maintaining a server, worrying about CPU and memory, worrying about storage, worrying about disk IO, worrying about redundancy, etc.  Huge overall reduction in devops efforts and costs.  Allows me to concentrate more on app and not the infrastructure.
  
  
• Development Model - I find I'm more efficient working with a simple JSON Data Model and the CosmosDB (DocumentDB) API over the equivalent traditional database development whether that be Entity Framework or more legacy stored procedure heavy solutions.  Mind you still have to plan\design your data model well (partition keys, etc).
• Costs -  In our experience so far we've felt pretty strongly that we've had significant costs saving over traditional Azure SQL instances that would provide us the equivalent performance and redundancy (not even counting more abstract operational and devops costs). 

Saying that I've had some frustrations and challenges - standing out in my mind:

• Aggregates - CosmosDB has limited support for aggregates that is REALLY challenging.  They say they do but they don't support common aggregates such as distinct and group by (yet). If you need to have aggregates this could be a headache for you.
• Case Sensitive - Queries are all case sensitive requiring extra work or having canonicalized versions of common search fields (e.g. lower case, etc)
• Lookup Values - Establishing a pattern I liked for things like lookup values (think dropdowns) and managing those values.

You also have extra considerations with migrating legacy data. If you are thinking of a "life and shift" to the cloud or if you have a lot of business logic in your stored procedures then you may have a lot of work ahead of you and it may be worthwhile to stay with SQL Server.  You'll also have to think about migrating your existing data. Some application are just naturally more relational and belong in SQL.

I really suggest you pull down some sample projects off of github and get a feel for the development experience. THere is a local emulator so you can do it for free.  Experiment a little on the impact on your data model and how you query and use your data. If you have a "close" data model you can plug it into a cost calculator and get some general costs - running some sample queries will also give you some RU costs for those queries which might help you refine those estimates even further. 

Hope this helps a little. Good luck!

[#3]

Hi @Joshua Carlisle thanks a lot. This give me a good start!

[#4]

I did some digging today for examples. I just want an simple example. Create a demo CosmosDB and use plain javascript/jquery to access the data. Is there an example out there which I can rebuild myself?

I did find this example: https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-nodejs-application

But I would like to do something like this:

  		$.ajax({
    		url: _rootSiteURL + "/_api/search/query?querytext='blabla',
    		method: "GET",
    		headers: {"accept": "application/json;odata=verbose"},
			success: function(xData, request){
blabala.....

If this is even possible..

[#5]

Most of the Javascript examples you'll find will be using Node.js on the server. CosmosDB does have a REST interface that could technically be used on the client side from javascript\jquery but that would not be a best practice by any means and I would highly discourage it - you have no safe place to store your credentials.  Your CosmosDB calls should be made from a trusted location and should not be considered for use as an API layer being called directly from some javascript in a browser. 

e.g. Client -> API Host -> CosmosDB 

There are a lot of options for that REST API layer on Azure ranging from your traditional API built in Node (Javascript) or ASP.NET to newer serverless offerings such as Azure Functions but in the end the reason you can't find examples is because you shouldn't do it :) 

Cheers,

Josh

[#6]

Hi @Joshua Carlisle 

I'll take your advice ;)

I did find some kind of "solution" meanwhile but also noticed it would not be safe:

var settings = {
  "async": true,
  "crossDomain": true,
  "url": "https://test.documents.azure.com:443/dbs/tempdb/colls/tempcoll/docs/WakefieldFamily",
  "method": "GET",
  "headers": {
    "Accept": "application/json",
    "x-ms-version": "2016-07-11",
    "Authorization": "{{authToken}}",
    "x-ms-date": "{{RFC1123time}}",
    "Cache-Control": "no-cache",
    "Postman-Token": "7146132a-75b2-39e2-001d-afeafb4e0d5d"
  },
  "data": "{\r\n    query: \"SELECT * FROM c\",\r\n    parameters: []\r\n}\r\n"
}

$.ajax(settings).done(function (response) {
  console.log(response);
});

I guess I have to sort out node.js. It's kind of new for me but that keeps me busy.

Just for testing I'll try to use a classic ASP connection first and then try to rebuild it to node.js.