Having worked with both SQL Server and Cosmos DB (more the former), the following are the biggest pain points I've experienced with each.
- Complicated to make it work. There are 100+ different ways to authenticate, for example. All this complicated User-Role mapping, Managed Identity, Multiple Activity Result Sets, blah blah.
- Syntax is bulky and complicated, even for simple things. Inserting test data means I have to examine the schema, triggers, constraints, etc. of multiple tables, then insert into each one in a specific order, sometimes temporarily dropping constraints, etc.
- Lots of annoying things about working with it locally. Like if you have both SQL Server 2016 and Sql Server 2012 installed, you have to figure out which one is localhost, and stuff like that.
- "Schema-less" is not as practical as it sounds. The business entities you're representing with documents will change over time, so now you have to come up with a strategy to handle missing/extra properties on your documents.
- Document sizes are capped at 2MB. This means I can't represent one-to-many foo-bars in documents unless I'm sure there aren't too many bars with each foo.
- The "DB" itself is not infinitely scalable. You can scale up Collections finitely (up to 10k requests/second) and scale them out infinitely. But you have to come up with a strategy and write code to create new collections on-the-fly and balance the load. I don't understand how this is any different than scaling SQL Server by creating new databases.
- Simple plug-and-play
- I can define an entity in JSON and it can be arbitrarily large and I can insert it to this DB
- I can query entities of a specific type (I don't require that it needs to be fast)
- I can keep throwing entities at it and it will do any scaling automatically
Last edited by a moderator: