如何使用Azure SQL存储冷热数据？能否自动迁移Azure SQL订单表中冷数据至独立数据库？

Great question! Let's tackle your two Azure SQL cold/hot data management questions with practical, actionable solutions tailored to your IsOrderActive flag scenario.

Azure SQL offers several native approaches to segregate hot (active) and cold (inactive) orders while keeping querying straightforward for your application:

Partitioned Tables (Single Database) #

This is my go-to for scenarios where you want to keep all data in one database but leverage different storage tiers for cost optimization. You'll use the IsOrderActive flag to split the table into partitions mapped to separate file groups (each tied to a storage tier):

1. Create a partition function to split data based on the boolean flag:

   CREATE PARTITION FUNCTION pf_OrderActive (bit)
   AS RANGE LEFT FOR VALUES (0); -- Splits data into <=0 (cold) and >0 (hot)
   

2. Create a partition scheme to map partitions to file groups. First, make sure you have two file groups: one for hot data (e.g., FG_Hot on Premium/General Purpose local SSD) and one for cold data (e.g., FG_Cold on General Purpose blob storage for lower cost):

   CREATE PARTITION SCHEME ps_OrderActive
   AS PARTITION pf_OrderActive
   TO (FG_Cold, FG_Hot); -- Maps IsOrderActive=0 to FG_Cold, 1 to FG_Hot
   

3. Bind your Orders table to the partition scheme. For a new table:

   CREATE TABLE Orders (
       OrderId INT PRIMARY KEY,
       IsOrderActive BIT,
       OrderDate DATETIME,
       -- Add other order columns here
   ) ON ps_OrderActive(IsOrderActive);
   

   For an existing table, rebuild the clustered index to apply partitioning:

   CREATE CLUSTERED INDEX CI_Orders_OrderId
   ON Orders(OrderId, IsOrderActive)
   WITH (DROP_EXISTING = ON)
   ON ps_OrderActive(IsOrderActive);
   

Benefits: No application changes needed—queries automatically route to the correct partition. Hot data stays on fast storage for low latency, while cold data uses cheaper storage.

Elastic Queries (Cross-Database) #

If you prefer to split hot and cold data into separate databases (e.g., a high-performance hot DB and a low-cost cold DB), use elastic queries to unify access:

1. Set up an external data source in your hot database pointing to the cold database:

   CREATE DATABASE SCOPED CREDENTIAL ColdDB_Credential
   WITH IDENTITY = '<cold-db-username>', SECRET = '<cold-db-password>';
   
   CREATE EXTERNAL DATA SOURCE ColdOrdersDB
   WITH (
       TYPE = RDBMS,
       LOCATION = '<cold-db-server>.database.windows.net',
       DATABASE_NAME = 'ColdOrders',
       CREDENTIAL = ColdDB_Credential
   );
   

2. Create an external table in the hot DB that mirrors the cold DB's Orders table structure:

   CREATE EXTERNAL TABLE dbo.ColdOrders (
       OrderId INT PRIMARY KEY,
       IsOrderActive BIT,
       OrderDate DATETIME,
       -- Match all columns from your Orders table
   ) WITH (
       DATA_SOURCE = ColdOrdersDB
   );
   

3. Query both datasets together seamlessly:

   -- Get all active orders from hot DB + inactive from cold DB
   SELECT * FROM dbo.Orders WHERE IsOrderActive = 1
   UNION ALL
   SELECT * FROM dbo.ColdOrders WHERE IsOrderActive = 0;
   

Benefits: Full cost isolation (cold DB can use Basic/Standard tier), and you can scale each database independently.

Yes, you can absolutely automate this process! Here are the most reliable methods:

Azure Data Factory (ADF) #

ADF is the low-code/no-code solution for scheduled data movement:

• Create a pipeline with two core activities:
  1. Copy Data: Configure it to pull rows where IsOrderActive = 0 from your source hot DB and insert them into the target cold DB. Use incremental copy (e.g., filter by a last modified date or track changes) to avoid reprocessing data.
  2. Stored Procedure: Run a T-SQL script in the source DB to delete the rows that were successfully copied (wrap this in a transaction to ensure consistency).
• Add a trigger (daily/weekly, or event-based) to run the pipeline automatically.

Pros: Visual interface, built-in error handling, and support for complex transformations if needed.

Azure Automation Runbooks #

For script-based automation, use Azure Automation with PowerShell or Python:

• Create a Runbook that connects to both databases and executes the transfer logic:

  -- Example T-SQL to run in the script (wrap in PowerShell's Invoke-SqlCmd)
  BEGIN TRANSACTION;
  
  -- Insert cold orders into target DB
  INSERT INTO ColdOrders.dbo.Orders
  SELECT * FROM HotOrders.dbo.Orders WHERE IsOrderActive = 0;
  
  -- Delete copied orders from source DB
  DELETE FROM HotOrders.dbo.Orders WHERE IsOrderActive = 0;
  
  COMMIT TRANSACTION;
  

• Schedule the Runbook to run at your desired interval.

Pros: Full control over the script logic, ideal for custom business rules.

Azure Functions #

If you need serverless, event-driven automation (e.g., trigger when IsOrderActive changes to 0):

• Create a Timer Trigger Function (scheduled) or use Change Data Capture (CDC) to detect when orders become inactive.
• Use ADO.NET or Entity Framework in the Function code to transfer the affected rows to the cold DB.

Pros: Pay-per-use pricing, easy integration with other Azure services, and support for custom code.

Key Considerations #

• Always use transactions to ensure data consistency during transfer.
• If your Orders table has foreign keys, plan to transfer related data (e.g., order items) alongside the orders.
• Add validation steps (e.g., row count checks) to verify that data was transferred correctly.

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