替代Linked Server的等速方案：解决CLR类型数据迁移问题

Great question—this is a common pain point when dealing with CLR types and linked servers, and switching to OPENROWSET is absolutely the right call to keep your stored procedure-based framework intact with minimal changes. Here's how to approach this step by step:

1. Validate OPENROWSET Support for CLR Types #

First, confirm your SQL Server environment can handle CLR types via OPENROWSET. Using the MSOLEDBSQL or SQLNCLI11 driver (avoid outdated drivers) will properly serialize/deserialize geography and other CLR types between your OLTP and staging servers. You don’t need custom type conversion logic here—OPENROWSET passes these types natively as long as your target staging table has matching geography columns.

2. Replace Linked Server References with Parameterized OPENROWSET #

Since your framework generates SQL dynamically based on target OLTP tables, swap out linked server syntax like:

SELECT * FROM [LinkedOLTP].[OLTP_DB].[dbo].[TargetTable]

With a parameterized OPENROWSET template. Wrap this in dynamic SQL to maintain your framework’s generic nature, pulling table names and connection config from your existing setup.

Example Dynamic SQL in Your Stored Procedure #

Here’s how to adapt your auto-generated query logic:

-- Assume these are existing parameters/variables in your framework
DECLARE @OLTPServer NVARCHAR(100) = 'OLTP-SERVER-01'
DECLARE @OLTPDB NVARCHAR(100) = 'OLTP_DB'
DECLARE @TargetTable NVARCHAR(256) = 'dbo.TargetTable'
DECLARE @StagingTable NVARCHAR(256) = 'BI_Staging.dbo.Staging_TargetTable'

-- Build OPENROWSET query (use integrated security or encrypted credentials)
DECLARE @OpenRowSetQuery NVARCHAR(MAX) = CONCAT(
    'INSERT INTO ', @StagingTable, '
     SELECT * 
     FROM OPENROWSET(
         ''MSOLEDBSQL'',
         ''Server=', @OLTPServer, ';Database=', @OLTPDB, ';Trusted_Connection=YES;'',
         ''SELECT * FROM ', @TargetTable, '''
     ) AS src'
)

-- Execute the generated migration query
EXEC sp_executesql @OpenRowSetQuery

3. Centralize Connection Configuration (Keep Framework Generic) #

Avoid hardcoding connection strings by storing OLTP connection details (server name, authentication mode, credentials) in a config table (e.g., MigrationFramework.Configurations). Your framework can pull these values at runtime, just like it does for target table names. This keeps your setup flexible and avoids duplicate code changes.

4. Enable Required SQL Server Configuration #

OPENROWSET requires the Ad Hoc Distributed Queries advanced option to be enabled. Run this once on your staging server (work with your DBA to align with security policies):

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

5. Minimal Framework Modifications #

• Keep existing table discovery logic: Your current code that identifies target OLTP tables, validates schema matches, etc., can stay exactly as is. Only modify the part that generates the source query (replace linked server with OPENROWSET).
• Retain error handling: If your framework has retry logic, logging, or schema mismatch checks, those don’t need changes—just wire them to the new OPENROWSET-generated query.

Key Considerations #

• Security: If using SQL authentication, avoid plaintext credentials. Use SQL Server Credential objects to store encrypted credentials, and reference them in your OPENROWSET connection string.
• Performance: For large tables, add a WHERE clause to pull incremental data (e.g., WHERE LastUpdated > @LastMigrationDate) just like you would with linked servers. OPENROWSET supports filtered queries natively.
• Future-proofing: If your OLTP tables add new CLR types later, as long as your staging table has matching columns, OPENROWSET will handle them without extra code changes—just like your original framework.

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