MyAppDBsa 或應用登入Chinese_Taiwan_Stroke_CI_AS (繁體筆畫不區分大小寫/重音) 或 Latin1_General_CI_AS。FULL (生產) / SIMPLE (測試/可重建)。160 (SQL 2022) / 150 (2019) / 140 (2017)。NONE / PARTIAL (獨立資料庫)。FG_DATA、FG_INDEX、FG_BLOB 分離 I/O。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);
# 安裝模組
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
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;(視核心數)。ALTER DATABASE [MyAppDB] SET TRUSTWORTHY OFF;(預設關閉)。以 T-SQL 或 PowerShell 程式化建庫,納入版控與 CI/CD,才是專業 DBA/DevOps 的標準作法。