SQL Server 2014 MDS部署：寻求替代自建REST API的外部数据推送方案

Great question! Since you're working with SQL Server 2014's Master Data Services (MDS) and need to expose a REST endpoint for external systems to push data to the staging area, you don't have to jump straight into building a fully custom REST API from scratch. Here are some practical alternatives to consider:

1. Wrap the Existing MDS WCF API with a Lightweight REST Proxy #

MDS's built-in WCF API already handles all the heavy lifting for interacting with staging areas—you can just add a thin REST layer on top of it to translate REST requests into WCF calls.

• How to implement: Use ASP.NET Web API (compatible with .NET Framework, which works seamlessly with SQL Server 2014) to create endpoints that map to MDS WCF operations. For example, a POST /api/mds/staging endpoint could accept JSON data, convert it into the required MDS staging objects, then call the StagingService.CreateStagingBatch method from the MDS WCF client.
• Example snippet:

  // Simplified example of a Web API controller action
  [HttpPost]
  public IHttpActionResult PushToStaging(StagingDataModel data)
  {
      // Initialize MDS WCF client
      var stagingClient = new StagingServiceClient();
      var batch = new StagingBatch
      {
          BatchTag = $"ExternalPush_{DateTime.UtcNow:yyyyMMddHHmmss}",
          StagingRecords = data.Records.Select(r => new StagingRecord { ... }).ToList()
      };
      var result = stagingClient.CreateStagingBatch(batch);
      stagingClient.Close();
      
      return Ok(new { BatchId = result.BatchId, Status = result.Status });
  }
  

• Pros: Reuses MDS's native staging logic, minimizes custom code, and avoids reinventing validation/error handling that MDS already provides.
• Cons: Requires familiarity with MDS's WCF API structure, and you'll still need to write some glue code.

2. Use SSIS with a REST Endpoint Listener #

If your team already uses SQL Server Integration Services (SSIS), you can leverage it to create a REST-aware pipeline that directly feeds data into MDS's staging area.

• How to implement:
  • Set up a REST endpoint listener (using SSIS's Script Task or a custom script component) to receive incoming data from external systems.
  • Use MDS's built-in SSIS components (like the MDS Staging task) or direct SQL queries to write the received data into MDS's staging tables.
  • Trigger the MDS staging batch processing via SSIS (either by calling the mdm.udpStagingBatchSave stored procedure or using the MDS API).
• Pros: Leverages existing ETL skills and tools, no need to build a separate API service, and integrates smoothly with other data workflows.
• Cons: Less flexible for complex REST request patterns (like custom filters or real-time validation), and SSIS is better suited for batch-oriented data rather than high-volume real-time requests.

3. Directly Write to MDS Staging Tables via a Lightweight REST Service #

MDS's staging area is built on top of standard SQL Server tables (e.g., stg.<EntityName>_Leaf, stg.<EntityName>_Consolidated). You can build a simple REST service that writes directly to these tables and triggers the staging process.

• How to implement:
  • Create a REST service (using ASP.NET Web API, Node.js, or Python—whichever fits your environment) that accepts data in your desired format.
  • Validate the data against MDS's staging table schema (ensuring required columns, correct data types, etc.).
  • Insert the data into the appropriate staging table, then call the mdm.udpStagingBatchSave stored procedure to initiate the MDS import process.
• Example SQL call:

  EXEC mdm.udpStagingBatchSave
      @User_ID = 'your-service-account',
      @Version_ID = 1, -- Replace with your MDS version ID
      @BatchTag = 'ExternalPush_202405201430',
      @Batch_ID = NULL, -- Returns the new batch ID
      @Return_ID = 0;
  

• Pros: Bypasses the WCF layer for potentially better performance, gives you full control over data handling, and works well if you need to customize the staging logic.
• Cons: Requires deep knowledge of MDS's staging table structure and stored procedure parameters, and you'll need to handle validation, error logging, and transaction management yourself.

Final Recommendations #

• If you want the fastest path with minimal custom code, go with Option 1 (WCF wrapper)—it's the most straightforward way to expose REST while using MDS's native functionality.
• If your team is already invested in SSIS for data workflows, Option 2 (SSIS-based) is a natural fit.
• If you need maximum control or performance, Option 3 (direct table access) is worth exploring, but be prepared to handle more of MDS's internal logic.

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