萬盛學電腦網

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

SQL Server 建立資料庫三大法:GUI、T-SQL、SMO/PowerShell 完整教學

方法一:SSMS 圖形化介面(適合初學者、一次性操作)

  1. 展開 Object Explorer → Databases → 右鍵 New Database...
  2. General 頁籤:
    • Database name:MyAppDB
    • Owner:sa 或應用登入
    • Database files:邏輯名、檔案類型 (Rows/Log)、初始大小、自動成長 (建議固定 MB、非 %)、路徑(資料/記錄分離不同磁碟)、檔案群組 (PRIMARY / 自訂 FG)。
  3. Options 頁籤:
    • Collation:Chinese_Taiwan_Stroke_CI_AS (繁體筆畫不區分大小寫/重音) 或 Latin1_General_CI_AS。
    • Recovery model:FULL (生產) / SIMPLE (測試/可重建)。
    • Compatibility level:160 (SQL 2022) / 150 (2019) / 140 (2017)。
    • Containment type:NONE / PARTIAL (獨立資料庫)。
  4. Filegroups 頁籤:新增 FG_DATA、FG_INDEX、FG_BLOB 分離 I/O。
  5. 按 OK 或腳本圖示產生 T-SQL 存檔。

方法二:T-SQL 腳本(版控、自動化、CI/CD 必備)

CREATE DATABASE [MyAppDB]
  CONTAINMENT = NONE
  ON PRIMARY 
  ( NAME = N'MyAppDB_Data', FILENAME = N'D:\SQLData\MyAppDB_Data.mdf', SIZE = 512000KB, FILEGROWTH = 64000KB ),
  FILEGROUP [FG_INDEX]
  ( NAME = N'MyAppDB_Index', FILENAME = N'E:\SQLIndex\MyAppDB_Index.ndf', SIZE = 256000KB, FILEGROWTH = 32000KB ),
  FILEGROUP [FG_BLOB]
  ( NAME = N'MyAppDB_Blob', FILENAME = N'F:\SQLBlob\MyAppDB_Blob.ndf', SIZE = 102400KB, FILEGROWTH = 16000KB )
  LOG ON
  ( NAME = N'MyAppDB_Log', FILENAME = N'L:\SQLLog\MyAppDB_Log.ldf', SIZE = 102400KB, FILEGROWTH = 16000KB )
  WITH CATALOG_COLLATION = DATABASE_DEFAULT;
GO

ALTER DATABASE [MyAppDB] SET RECOVERY FULL;
ALTER DATABASE [MyAppDB] SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE [MyAppDB] SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON);
ALTER DATABASE [MyAppDB] SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [MyAppDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
ALTER DATABASE [MyAppDB] SET DELAYED_DURABILITY = DISABLED;
ALTER DATABASE [MyAppDB] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900);

方法三:PowerShell + SMO / DBATools(自動化佈署、IaC)

# 安裝模組
Install-Module -Name dbatools -Scope CurrentUser -Force

# 建立資料庫(含檔案群組、檔案、選項)
New-DbaDatabase -SqlInstance 'localhost' -Name 'MyAppDB' `
  -DataFile @{Name='MyAppDB_Data'; Path='D:\SQLData'; Size=500MB; Growth=64MB} `
  -LogFile  @{Name='MyAppDB_Log';  Path='L:\SQLLog';  Size=100MB; Growth=16MB} `
  -FileGroup @(
    @{Name='FG_INDEX'; File=@{Name='MyAppDB_Index'; Path='E:\SQLIndex'; Size=250MB; Growth=32MB}}
    @{Name='FG_BLOB';  File=@{Name='MyAppDB_Blob';  Path='F:\SQLBlob';  Size=100MB; Growth=16MB}}
  ) `
  -Collation 'Chinese_Taiwan_Stroke_CI_AS' `
  -RecoveryModel Full -CompatibilityLevel 160 `
  -EnableQueryStore -AutoCreateStatistics -AutoUpdateStatistics -AutoUpdateStatisticsAsync

# 驗證
Get-DbaDatabase -SqlInstance 'localhost' -Database 'MyAppDB' | Select-Object Name, RecoveryModel, CompatibilityLevel, FileGroups

進階配置清單(建庫後必做)

  • 啟用 Query Store(效能基線、計畫強制)。
  • 設定 MAXDOP:ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;(視核心數)。
  • Legacy Cardinality Estimation / Parameter Sniffing** 視負載關閉/開啟。
  • 啟用 CDC / Change Tracking** 需資料同步場景。
  • 設定信任資料庫:ALTER DATABASE [MyAppDB] SET TRUSTWORTHY OFF;(預設關閉)。
  • 備份策略** 立即建立完整備份 → 定期差異/記錄備份。

常見陷阱

  • ❌ 檔案放在 C 槽 / 同一磁碟 → I/O 爭用。
  • ❌ 自動成長用 % → 碎片化、不可預測延遲。
  • ❌ 未規劃檔案群組 → 後續無法線上移動大表/索引/LOB。
  • ❌ Collation 不統一 → 跨資料庫 JOIN/比較產生 Collation Conflict。

以 T-SQL 或 PowerShell 程式化建庫,納入版控與 CI/CD,才是專業 DBA/DevOps 的標準作法。

copyright © 萬盛學電腦網 all rights reserved