连接SQL Server的Web应用迁移至Service Fabric：数据迁入Reliable Collections咨询

Hey there! Let's walk through how to migrate your SQL Server data into Service Fabric Stateful Services' Reliable Collections. I’ve tackled similar migrations before, so here’s a practical, step-by-step approach that’ll get you sorted:

Don’t skip this—getting your foundation right will save you headaches later:

• Map SQL tables to Reliable Collection types: Match your relational data to the right Service Fabric structures:
  • Use IReliableDictionary<TKey, TValue> for most tables with a clear primary key (this is the go-to for 90% of use cases)
  • Opt for IReliableQueue<T> if you’re dealing with ordered, process-once data (like a job queue)
  • Pick IReliableSortedDictionary<TKey, TValue> if you need ordered access to your dataset
• Build serializable data contracts: Create C# classes that mirror your SQL table schemas. Make sure to mark them with [DataContract] and each property with [DataMember]—this is non-negotiable for Reliable Collections to serialize/deserialize data correctly.
• Spin up a test cluster: Never migrate straight to production! Use the local Service Fabric dev cluster (via the SDK) or a small cloud test cluster to validate your migration logic before touching live data.

Your choice depends on whether you can tolerate downtime for your app:

Option A: Offline Migration (Simplest, For Downtime-Tolerant Apps) #

If you can take your old web app offline for a few hours, this is the fastest path:

• Shut down the old app: Ensure no new data is written to SQL Server during migration—this prevents partial or duplicate records.
• Batch-extract SQL data: Write a simple console app (or use SqlBulkCopy for speed) to pull data from SQL. For hundreds of thousands of rows, process batches of 1,000–5,000 records at a time to avoid memory overload.
• Write to Reliable Collections: Use the Service Fabric client to connect to your Stateful Service’s partition, then insert batches into the collection. Wrap each batch in a transaction for atomicity:

  using (var transaction = await stateManager.CreateTransactionAsync())
  {
      foreach (var record in dataBatch)
      {
          await reliableDictionary.SetAsync(transaction, record.PrimaryKey, record);
      }
      await transaction.CommitAsync();
  }
  

• Validate thoroughly: After migration, cross-check row counts between SQL and Reliable Collections, and spot-check random records to ensure data integrity.

Option B: Online Migration (Zero Downtime, For Mission-Critical Apps) #

If downtime isn’t an option, you’ll need to sync data incrementally:

• Initial bulk load: Do a one-time load of existing SQL data (like the offline method), but don’t shut down the old app. Record the last modified timestamp or SQL Server’s log sequence number (LSN) when this load finishes—this marks your starting point for incremental syncs.
• Track SQL changes: Enable SQL Server Change Tracking (or Change Data Capture) to log all inserts, updates, and deletes that happen after the initial load.
• Sync incremental changes: Build a lightweight service (either a console app or a Service Fabric Stateless Service) that polls the SQL change log at regular intervals, then applies those changes to Reliable Collections:
  • Inserts/Updates: Use SetAsync() (Reliable Dictionaries overwrite existing keys automatically)
  • Deletes: Use TryRemoveAsync()
• Cut over traffic: Once the sync is caught up (no pending changes left), switch user traffic from the old web app to your new Service Fabric app. You can then shut down the old app and sync service.

• Handle concurrency conflicts: You’ll run into TransactionAbortedException when multiple transactions clash. Add retry logic (use a library like Polly or a simple loop) to automatically retry failed transactions.
• Optimize for speed: Avoid single-row writes—batch operations are way more efficient. Also, make sure your Stateful Service has enough partitions to handle the load; split data using a partition key (like a hash of the primary key) to distribute work.
• Log everything: Add detailed logging to your migration tool—track rows processed, errors, and progress. In Service Fabric, use EventSource or integrate with Application Insights to monitor the health of your Reliable Collections.
• Reconcile data: After migration, run a reconciliation job that compares a sample (or all) of the data between SQL and Reliable Collections to catch any missing or corrupted records.

• Backup your new state: Take a full backup of your Service Fabric cluster’s state using the built-in backup/restore feature—better safe than sorry.
• Retire the old SQL database: Once you’ve confirmed the new app works flawlessly and all data is intact, you can shut down the old SQL database (or keep it as a backup for a few weeks just in case).

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