Relational Database to Cosmos DB


A customer fired off a few questions to me today regarding their move from on-premises SQL Server to Cosmos DB. I thought I would share those questions and answers.

How can we handle relational DBMS?

Cosmos DB is not a relational database. You cannot just take your relational database and expect it to run in Cosmos DB. You could move tables of data into Cosmos, but not the relational aspects of your existing data structures. If you are thinking of re-modelling your data as a set of documents rather than in a relational way, then you could use the DocumentDB model within Cosmos. I recommend this as a good read: - https://docs.microsoft.com/en-us/azure/architecture/guide/technology-choices/data-store-overview

Does Cosmos Db have different data types?

Cosmos is a multi model database which means it supports Graph, Documents, Key-Values and Column-Family. The data types that are supported, if applicable, depend on the model you are using. Documents for instance use JSON, so will support all types supported in JSON.

Does it have any APIs to load data from different source systems like Excel, CSV, txt, XML etc. Or do we have to write our own importer?

Importing data for use with the Table API or Graph API is not supported at this time; however, there are a few mechanisms available to you, have a look here https://docs.microsoft.com/en-us/azure/cosmos-db/import-data

Can we create store procedure or functions and schedule them as jobs to perform actions automatically?

DocumentDB does support stored procedures which are represented by server-side JavaScript as detailed here https://docs.microsoft.com/en-us/azure/cosmos-db/programming. For other database models, you could use Azure Functions (https://azure.microsoft.com/en-us/services/functions/) in an event driven model or based of a schedule for a light-weight app. Alternatively you could build this tier in .Net or most popular programming languages using these Quick starts https://docs.microsoft.com/en-us/azure/cosmos-db/

How we can manage different type of security for example read only?

In addition to the two master keys for the Cosmos DB account, there are two read-only keys. These read-only keys only allow read operations on the account. Read-only keys do not provide access to read permissions resources. If you did not want to give out your keys, then you can create Users and grant Permissions to resources. Cosmos DB also encrypts your data at rest by default. (https://docs.microsoft.com/en-us/azure/cosmos-db/database-encryption-at-rest)

Can we create data views?

Using a NoSQL database normally means that you have denormalised your data, so I would need to see what you were using the View for to see if it is required

Can we create partitions to manage data?

Cosmos DB provides containers for storing data called collections (for document), graphs, or tables. Containers are logical resources and can span one or more physical partitions or servers. Partition management is fully managed by Azure Cosmos DB, and you do not have to write complex code or manage your partitions. Cosmos DB containers are unlimited in terms of storage and throughput.

How much data we can store in one JSON document?

I believe the current limit is around 2Mb per document, if you need more, there are some binary formats you can use, or you can have a master document with an array of child document id's.

If a part of data stored in JSON is not properly formatted, can we ready other data available in same JSON?

Using a client API, you should only be able to write valid JSON to Cosmos DB. The format of an attribute should not impact any query as long as it is a valid document.

How much data Cosmos DB can store?

At the moment, there are no upper limits of Cosmos DB, it is a global scale database in which throughput and storage can be scaled to handle whatever your application requires. If you have any questions about the scale, please send email to askdocdb@microsoft.com.

 


Comments (0)

Skip to main content