Simple app to migrate data from CosmosDB to SQL Server
View the Project on GitHub EitanBlumin/CosmosDb-to-MSSQL-Migration
This is a simple command-line program that migrates data from Azure CosmosDB to a Microsoft SQL Server database.
In this page:
Edit the appsettings.json file to specify the necessary parameters, as described below.
The CosmosDB section in the settings affects the retreival of data from your Cosmos DB source.
sourceEndPoint is the end-point URI of your Cosmos DB account.
It can be found in the Overview page of your Cosmos DB account in the Azure portal.
It can also be found in the Keys page of your Cosmos DB account.
Example: https://my-cosmosdb-account.documents.azure.com:443/
sourceAuthKey is either the Primary Key or Secondary Key of your Cosmos DB account.
You can find your keys in the Keys page of your Cosmos DB account in the Azure portal.
Example: ASdiodhasjda2...Ajddndf==
sourceDatabase is the source database name of your Cosmos DB account.
Your Cosmos DB databases can be found in the Data Explorer or Browse pages of your Cosmos DB account in the Azure portal.
sourceCollection is the source collection name of your Cosmos DB account.
Your Cosmos DB collections can be found under each database in the Data Explorer or Browse pages of your Cosmos DB account in the Azure portal.
sourceQuery is the SQL Query to be used for retrieving the relevant data from your Cosmos DB collection.
For example: SELECT c.Field1, c.Field2, c.Field3 FROM c
sourceMaxCountPerFetch is the maximum number of rows to fetch per each iteration from your Cosmos DB account.
It affects the number of maximum buffered items, and the maximum fetched items.
Choosing the right configuration number in this setting should be affected by your Cosmos DB configured throughput (based on RUs).
This can be affected by your Cosmos DB account scale, found in the Scale page of your Cosmos DB account in the Azure portal.
Click to learn more about Cosmos DB Scaling throughput.
The SQLServer section in the settings affects the insertion of data into your target SQL Server database.
targetHost is the host address of your SQL Server instance destination.
For example: my-azure-sql-db.database.windows.net
You can also specify an IP address. For example: 168.192.10.11
If your SQL Server instance uses a TCP port other than 1433, it should be specified after a comma.
For example: my-sql-server-host.acme.com,12345
Specifying a named instance: my-sql-server-host\myNamedInstance
targetDatabase is the name of your SQL Server database destination.
To find your list of databases in your SQL Server instance, you can run the following SQL query:
SELECT name FROM sys.databases
targetUsername is the SQL Authentication login name to be used for authenticating with your SQL Server.
targetPassword is the password of your SQL Authentication login name to be used for authenticating with your SQL Server.
rowsPerChunk specifies the number of rows to fetch from the Cosmos DB source before inserting them as a chunk into the SQL Server destination.
mergeProcedure is a stored procedure to run after the insertion of each chunk of data into the SQL Server destination.
This parameter is optional. If you do not want such a procedure to be executed, please specify null as its value.
useBulkCopy is a boolean parameter specifying whether to use SqlBulkCopy or not.
If true, you must specify the targetTable parameter must be specified.
If false, will use a stored procedure with a Table-Valued-Parameter instead.
In such case, the targetProcedureTVP parameter must be specified.
targetTable is the name of the table into which you want to save the data using SqlBulkCopy.
If useBulkCopy is false, the targetTable parameter is optional.
truncateTargetTable is a boolean parameter specifying whether to truncate the targetTable staging table before beginning the migration process.
If useBulkCopy is false, the truncateTargetTable parameter is optional.
targetProcedureTVP is the name of a stored procedure that accepts a Table-Valued-Parameter as input.
This procedure must accept a table-valued-parameter with the fields matching the fields specified in the FieldsToCopy section.
If useBulkCopy is true, the targetProcedureTVP parameter is optional.
The FieldsToCopy parameter is a string array used for specifying the list of fields to copy from Cosmos DB to SQL Server.
If a specified field is not returned in the Cosmos DB results, it will be sent as an empty string instead.
Example:
"FieldsToCopy": [
"Field1",
"Field2",
"Field3"
]
TBA
TBA
TBA
TBA
TBA