探索SQL Server 2016关系数据迁移至Azure CosmosDB SQL API的方案

Hey there! Let's walk through the most practical methods to get your SQL Server relational data into Azure Cosmos DB SQL API, where you'll merge related tables (like Product and ProductSubCatalog) into individual documents as you described.

1. Azure Data Factory (ADF) – No/Low-Code Enterprise Solution #

This is my go-to for most production migrations, especially when you want to avoid writing custom code. Here's how to set it up:

• Step 1: Create a new ADF pipeline and add a Data Flow activity.
• Step 2: Configure your SQL Server source dataset. Instead of pulling a single table, use a custom SQL query to join your tables upfront:

  SELECT 
    p.ProductID, 
    p.Name, 
    p.ProductNumber, 
    p.MakeFlag, 
    p.FinishedGoodsFlag,
    ps.SubCatalogID,
    ps.SubCatalogName,
    ps.SubCatalogDescription
  FROM Product p
  INNER JOIN ProductSubCatalog ps ON p.ProductID = ps.ProductID
  

• Step 3: Use a Select transformation to clean up fields (rename, remove unnecessary columns) to match your desired document structure.
• Step 4: Set up your Cosmos DB sink dataset. Specify your target container, set the partition key to /ProductID (critical for performance), and choose your write mode (Insert or Upsert if you need to handle updates).
• Step 5: Run the pipeline and monitor progress via ADF's monitoring tab.

Bonus: ADF supports incremental migrations using change data capture (CDC) if you need to keep Cosmos DB synced with ongoing SQL Server changes.

2. Azure Cosmos DB Data Migration Tool (dt.exe) – Quick Fix for Small Datasets #

If you're working with a smaller dataset and prefer a command-line approach, this tool is perfect:

• Download the tool from the Azure portal (it's free).
• Run this command to migrate your joined data directly:

  dt.exe /s:SqlSource /s.ConnectionString:"Server=YOUR_SQL_SERVER;Database=YOUR_DB;Integrated Security=True" /s.Query:"SELECT p.ProductID, p.Name, p.ProductNumber, p.MakeFlag, p.FinishedGoodsFlag, ps.* FROM Product p INNER JOIN ProductSubCatalog ps ON p.ProductID = ps.ProductID" /t:DocumentDB /t.ConnectionString:"AccountEndpoint=https://YOUR_COSMOS_ACCOUNT.documents.azure.com:443/;AccountKey=YOUR_COSMOS_KEY;" /t.Collection:Products /t.PartitionKey:/ProductID
  

• This pulls the joined SQL results into Cosmos DB as individual documents, with ProductID serving as both the document ID and partition key.

3. Custom C# Code – For Complex Transformations #

If you need granular control over data shaping (like nested structures or custom business logic), a small console app with the Cosmos DB SDK is ideal. Here's a simplified example:

using Microsoft.Azure.Cosmos;
using System.Data.SqlClient;
using System.Text.Json;

// Initialize connections
var sqlConnString = "Server=YOUR_SQL_SERVER;Database=YOUR_DB;Integrated Security=True";
var cosmosConnString = "AccountEndpoint=https://YOUR_COSMOS_ACCOUNT.documents.azure.com:443/;AccountKey=YOUR_COSMOS_KEY;";
var cosmosClient = new CosmosClient(cosmosConnString);
var container = cosmosClient.GetContainer("YourCosmosDB", "Products");

// Pull joined data from SQL Server
using (var sqlConn = new SqlConnection(sqlConnString))
{
    sqlConn.Open();
    var query = @"SELECT p.ProductID, p.Name, p.ProductNumber, p.MakeFlag, p.FinishedGoodsFlag, ps.* FROM Product p INNER JOIN ProductSubCatalog ps ON p.ProductID = ps.ProductID";
    
    using (var cmd = new SqlCommand(query, sqlConn))
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // Build your document object (use a strong type class for production!)
            var productDoc = new
            {
                ProductID = reader.GetInt32(reader.GetOrdinal("ProductID")),
                Name = reader.GetString(reader.GetOrdinal("Name")),
                ProductNumber = reader.GetString(reader.GetOrdinal("ProductNumber")),
                MakeFlag = reader.GetBoolean(reader.GetOrdinal("MakeFlag")),
                FinishedGoodsFlag = reader.GetBoolean(reader.GetOrdinal("FinishedGoodsFlag")),
                SubCatalogID = reader.GetInt32(reader.GetOrdinal("SubCatalogID")),
                SubCatalogName = reader.GetString(reader.GetOrdinal("SubCatalogName"))
            };

            // Write to Cosmos DB (use Transactional Batch for better performance with bulk data)
            await container.CreateItemAsync(productDoc, new PartitionKey(productDoc.ProductID));
        }
    }
}

Key Considerations #

• Partition Key Selection: Always pick a key that distributes data evenly (like ProductID here) to avoid hot partitions and ensure fast queries.
• Document Size Limits: Cosmos DB allows documents up to 2MB. If your joined data exceeds this, consider nesting child data instead of flattening, or splitting into multiple related documents.
• Data Consistency: If SQL Server data changes during migration, use CDC with ADF or custom code to capture incremental updates and keep Cosmos DB in sync.
• Indexing: Cosmos DB automatically indexes all fields by default, but you can customize the index policy later to optimize for your specific query patterns.

内容的提问来源于stack exchange，提问作者Kalaiselvan