萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

根據 SQL 腳本升級客戶端資料庫結構:三代方案演進與工具化實戰

軟體交付中,將開發端已驗證的新版資料庫結構 (含新表、欄位變更、索引、預存程序、約束) **無損遷移** 至客戶端含資料的舊庫,是典型的「結構同步」難題。本文回顧三代方案演進,從腳本生成→工具化→增量同步,揭示自動化演進邏輯。

方案一:全量腳本重建 + 資料遷移 (粗暴但有效)

  1. SSMS → `Tasks → Generate Scripts` → 產生完整建庫腳本 (Schema + Data)
  2. 客戶端:舊庫改名 `OldDB_Bak` → 執行新腳本建 `NewDB` → `INSERT INTO NewDB..Table SELECT * FROM OldDB_Bak..Table`

**缺點**:停機時間長、大資料量遷移慢、需大量暫存空間、序列/Identity 值衝突處理複雜。

方案二:表級重命名 + 選擇性遷移 (工具化雛形)

-- 核心邏輯:舊表改名 → 跑新建表腳本 → 資料映射搬移
EXEC sp_rename 'dbo.OldTable', 'OldTable_Bak';
-- 執行新建表腳本 CREATE TABLE dbo.OldTable (...)
INSERT INTO dbo.OldTable (Col1, Col2, NewCol3)
SELECT Col1, Col2, DEFAULT  -- 新欄位補預設值
FROM OldTable_Bak;

-- 輔助查詢
SELECT name FROM sys.procedures;  -- 列舉預存程序
SELECT constraint_name, table_name FROM information_schema.table_constraints; -- 列舉約束

**進展**:縮小遷移單元至表級,可平行處理,但仍需手工處理約束/預存程序/觸發器的 Drop/Create。

方案三:增量結構同步 (現代化、自動化、可持續)

核心邏輯:比對 → 生成差異腳本 → 安全執行

-- 1. 讀取目標資料庫現有結構
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TargetTable';

-- 2. 解析來源腳本 (正則/TSQL 解析器) 獲取目標結構
-- 3. 比對差異:
--    • 新增欄位 → ALTER TABLE ADD COLUMN ...
--    • 類型變更 (相容擴大) → ALTER TABLE ALTER COLUMN ...
--    • 類型縮小/不兼容 → 藍綠重建 (新表→同步→切換)
--    • 刪除欄位 → 標記待人工確認 (防誤刪資料)

-- 4. 處理程式碼物件
-- 預存程序/函數/視圖/觸發器:DROP IF EXISTS → CREATE (或 ALTER)
-- 約束:依類型 (PK/FK/UK/DF/CK) 產生對應 DDL

約束前綴命名規範 (自動化識別關鍵)

類型前綴範例
Primary KeyPK_PK_Order_OrderID
Foreign KeyFK_FK_Order_Customer_CustID
Unique KeyUK_UK_User_Email
DefaultDF_DF_Order_CreateTime
CheckCK_CK_Order_Amount_Positive

工具化關鍵技術棧

  • Schema Compare:SSDT (Visual Studio)、Redgate SQL Compare、ApexSQL Diff、dbForge Schema Compare
  • Migration Framework:
    • **Flyway / Liquibase** (版本化遷移腳本 V1__Init.sql, V2__AddCol.sql)
    • **DbUp** (C# 嵌入式腳本執行器)
    • **EF Core Migrations / FluentMigrator** (代碼優先)
  • CI/CD 整合:Azure DevOps / GitHub Actions / Jenkins 階段執行 `sqlpackage /Action:Publish /SourceFile:dacpac /TargetConnectionString:...`

遺留問題與演進方向

  • ❌ **舊欄位未自動刪除** (需人工審核,防業務誤刪)
  • ❌ **資料轉換邏輯** (如 `VARCHAR`→`INT` 需清洗) 尚未自動化
  • ❌ **複雜重構** (拆表、合表、正規化) 需人工設計遷移腳本
  • 🔮 **未來**:結合 `SQL Server Data Tools (SSDT)` 專案 + `SqlPackage.exe` 發布 + `Pre/Post Deployment Scripts` 處理資料轉換,實現 **狀態式聲明部署** (Desired State Configuration)

從「手工跑腳本」到「工具比對同步」再到「聲明式持續部署」,資料庫結構變更管理正走向成熟的 DevOps 化。核心原則:**版控一切、小步提交、自動化驗證、可回滾**。

copyright © 萬盛學電腦網 all rights reserved