0

I need to migrate sql database into the cosmosdb. I want to know about the strategies, best practices and tools to use for migration or should I write my own migration logic.

I have read about a Azure Cosmos db migration tool and Azure datafactory. I am not sure which one to use.

also how should one proceed in migration. There can be many scenarios. For e.g.

  1. There may be case only few records are migrated and network disconnects.
  2. Another case can be suppose data is migrated and somebody again runs the migrations etc.
jarlh
  • 42,561
  • 8
  • 45
  • 63
Learner
  • 1,277
  • 3
  • 15
  • 34
  • From which dbms are you migrating? – jarlh May 25 '23 at 08:18
  • 2
    _Why_ are you migrating to cosmos db? Relational db's and cosmos db's are two very different things. Is there a particular reason you need to migrate? how many tables? how many records? – Nick.Mc May 25 '23 at 09:35
  • 1
    Yes @Nick.McDermaid earlier developer used sql db but in this case we need faster access and there is no need to be consistency . Consider we are storing blog posts and comments. Due to it`s large size it is taking too long to load. Moreover there is only single join that is with other table which we can keep inside a single document – Learner May 25 '23 at 14:28
  • @jarlh I need to migrate from on premise sql server 2019. I looked into Azure Cosmos db migration tool but It support query where as I need to arrange data with complex queries which I think wont be feasible for this tool. More over running this tool can create duplicate records in the database – Learner May 25 '23 at 14:32
  • I would worry less on "how", and more on "why", "what do I lose", "what do I win", "is it worth it". See also: https://stackoverflow.com/a/49486980/331325 – Imre Pühvel May 25 '23 at 19:38
  • ADF can help move the data. Cosmos DB's Data Migration Tool may also help. Before you get too far in your design, be sure you understand the concepts for modeling and partitioning data. This will ensure your design can scale. https://gist.github.com/markjbrown/114179b3d530abe0eaa25fb9ac54b609 – Mark Brown May 30 '23 at 12:47

2 Answers2

1

Our team has done a migration recently from SQL to Cosmos DB. Although Azure Data Factory is a valid option, we ended up building our own function with a service bus trigger. (Which we ran locally, resulting in barely any costs)

This way we got to know the SDK instead of having to learn to work with Azure Data Factory.

In our experiments it also became clear that complex data transformations were easier in a custom implementation. Especially with (deeply) nested objects.

Tom Bosmans
  • 63
  • 7
  • yes @Tom, I also developed my custom implementation. I also figured out early that custom data transformation is getting complex with each new requirement. – Learner May 31 '23 at 16:50
  • Hey @Tom what strategy did you follow for bulk update? – Learner May 31 '23 at 17:06
  • We used a queue function architecture to guarantee succes. We didn't bother too much about performance as our source db was only a couple of gb large. – Tom Bosmans Jul 04 '23 at 22:34
0

First identify the Azure Cosmos DB API to which Data load has to happen.

Try to use Azure Data Factory to load data using Copy Data Activity.

If need to apply customized logic, use Mapping Data Flow Activity with different transformations.

Reference Link: https://learn.microsoft.com/en-us/azure/data-factory/how-to-sqldb-to-cosmosdb

ShaktiSingh-MSFT
  • 314
  • 1
  • 4