This is a very broad question...

We are in the process of migrating our application to highly-available cloud architecture.

We are being asked to evaluate using CosmosDB as a solution for globally distributed active-active database.

We believe it would be a daunting task to migrate our established platform with very large and complex database schema and MSSQL dependencies to CosmosDB.

Does anybody have any experience or advice on what it would take to ditch MSSQL and move to CosmosDB? All our data access code is based on LLBLGen 5.1 Adapter Model.

Hoping there is some "magic" I don't know about . . . but thinking this is a huge development effort.

I think the question you should ask yourself is: what problems does CosmosDB solve that are not solved with SQL Server?

CosmosDB isn't cheap, and although it comes with consistency guarantees above 'eventual', things aren't as instant as on an RDBMS, it's a different kind of system. The API is able to understand a SQL dialect, but don't expect a relational db, it's a document database (so it's suitable for storing derived models defined on top of the entity model, llblgen pro can generate transition code for that using the DocDB target framework for a derived model, so you can fetch queries from the entity model using RDBMS and project them to documents and then store them in your documentdb).

But most important is the question I mentioned at the start: why move to cosmosdb in the first place? 'Big data' isn't really an argument in that debate btw, Stackoverflow runs on a single sql server database, chances are your app has less data/activity.

(so iow: I'd also look into SQL Azure if you can, or VMs with SQL Server in a private VM network on Azure, using a topology similar to what you're using now on-prem).

The business requirement is multi-region active-active redundancy.

Currently Microsoft AzureSQL does not support this, which is why they are bringing up different DBMS such as CosmosDB and Spanner.

We could set up a MSSQL Always-On configuration, but they believe the latency and bandwidth restrictions would make this impractical if the two servers are in different regions.

All our code is built on MSSQL so it would be a major development effort to support a different database. Spanner might be a more viable option given it is relational, but I'm not sure if there are any plans for LLBLGen to support.

I'm with you--the best plan forward is to stick with MSSQL in one form or another.

Thanks very much for your reply.

DaveR wrote:

The business requirement is multi-region active-active redundancy.

Currently Microsoft AzureSQL does not support this, which is why they are bringing up different DBMS such as CosmosDB and Spanner.

Not all availability is equal

Please check: https://docs.microsoft.com/en-us/azure/cosmos-db/consistency-levels

It says (for the Strong, the linearizability consistency): "Azure Cosmos DB accounts that are configured to use strong consistency cannot associate more than one Azure region with their Azure Cosmos DB account. " Which is logical, I mean, how to guarantee linearizability when a write in a datacenter in Texas is guaranteed to be committed before a read in HongKong?

This means that if you trade Consistency for Availability (and your requirement looks like it wants to do that), there are options for a regular RDBMS as well, e.g. a hybrid solution where you have reads on a document DB and writes on an RDBMS and replicate the write data using projections to documents (generated e.g. through a derived model so it's easy to maintain that. llblgen pro has that built in ).

We could set up a MSSQL Always-On configuration, but they believe the latency and bandwidth restrictions would make this impractical if the two servers are in different regions.

All our code is built on MSSQL so it would be a major development effort to support a different database. Spanner might be a more viable option given it is relational, but I'm not sure if there are any plans for LLBLGen to support.

Not in the near future (as in: v5.5 or v5.6) but things can change. The big question I think is what cosmosdb will do in the near future. The EF team has done some preliminary work on supporting cosmosdb but only the SQL part, and considering documentdb (it's predecessor) didn't get enough traction, it's to be seen whether it's feasible to support it or if it stays a niche product.

I'm with you--the best plan forward is to stick with MSSQL in one form or another. Thanks very much for your reply.

It's indeed a complex situation you're in. Rewriting is very costly and perhaps not needed: I think if you start with investigating which areas need read/write access globally and which areas can do with read only access and at what consistency level (keep in mind that CAP with network partitioning practically a given results in the choice of either CP or AP), you can perhaps refactor the code which uses only readonly access to use a relaxed consistency using DB, e.g. cosmosdb or other (low consistency levels automatically give you high availability) and keep things with read/write & high consistency on mssql, e.g. by implementing a service at a higher level in the application so the database usage can still be centralized and you don't need a lot of data replication (as the application in another area of the world uses a local UI/web app but a centralized service)

Good luck