SQL Server 基础架构与安装配置

1.1 SQL Server 整体架构概述

SQL Server 是微软出品的企业级关系型数据库管理系统（RDBMS），自 1989 年发布以来，已演进为集 OLTP、数据仓库、分析服务、报表服务于一体的完整数据平台。与 MySQL 的插件式存储引擎不同，SQL Server 采用单一存储引擎设计，通过协议层、关系引擎层、存储引擎层三层协作提供数据服务。

1.1.1 逻辑架构层次

┌─────────────────────────────────────────┐
│           客户端应用程序                 │
│   (SSMS / ADS / .NET / ODBC / JDBC)    │
└────────────────┬────────────────────────┘
                 │ TDS 协议 (TCP 1433)
┌────────────────▼────────────────────────┐
│         SQL Server 实例（Instance）      │
│  ┌──────────────────────────────────┐  │
│  │        协议层（SNI）             │  │
│  │  TCP/IP · Named Pipes · VIA     │  │
│  └──────────────┬───────────────────┘  │
│  ┌──────────────▼───────────────────┐  │
│  │       关系引擎（Relational Engine）│  │
│  │  解析器→代数化→优化器→执行器     │  │
│  └──────────────┬───────────────────┘  │
│  ┌──────────────▼───────────────────┐  │
│  │       存储引擎（Storage Engine）  │  │
│  │  Buffer Pool · Lock Manager      │  │
│  │  Transaction Manager · Log Mgr  │  │
│  └──────────────┬───────────────────┘  │
└─────────────────┼───────────────────────┘
                  │ Win32 / Linux I/O
          ┌───────▼────────┐
          │  物理文件       │
          │ .mdf .ndf .ldf │
          └────────────────┘

1.1.2 实例与数据库的关系

一台主机可安装多个 SQL Server 实例（Instance），每个实例相互隔离，独立管理自己的数据库集合。

-- 查询当前实例名称
SELECT @@SERVERNAME AS InstanceName,
       @@VERSION   AS SQLServerVersion;

-- 查看实例下所有数据库
SELECT name,
       state_desc,
       recovery_model_desc,
       compatibility_level
FROM   sys.databases
ORDER BY name;

1.1.3 物理存储结构：页与区

SQL Server 所有数据的最小存储单位是页（Page），大小固定为 8 KB。8 个连续的页组成一个区（Extent）（64 KB）。

┌──────────── 区（Extent = 64KB）────────────┐
│ Page1(8KB) Page2(8KB) Page3(8KB) Page4(8KB) │
│ Page5(8KB) Page6(8KB) Page7(8KB) Page8(8KB) │
└─────────────────────────────────────────────┘

页头（96字节）
├── PageID
├── LSN（日志序列号）
├── CheckSum
└── 行偏移数组
    ├── 行数据区（可变长度行）
    └── 空闲空间

页类型： - 数据页（Data Page）：存储用户表的实际行数据 - 索引页（Index Page）：存储 B+Tree 索引节点 - IAM 页：追踪区分配情况 - PFS 页：追踪页的空间使用率

1.2 安装部署与环境配置

1.2.1 Windows Server 安装（推荐生产环境）

前置准备：

# 检查 .NET Framework 版本（需 4.6+）
[System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory()

# 调整 Windows 电源计划为高性能
powercfg -setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c

# 禁用 Windows 自动更新（生产数据库机器）
Set-Service -Name wuauserv -StartupType Disabled

# 格式化数据盘（建议 64KB 分配单元大小）
# 在磁盘管理中或使用 diskpart 完成

静默安装参数示例（SQL Server 2022）：

setup.exe /Q ^
  /ACTION=Install ^
  /FEATURES=SQLENGINE,REPLICATION,FULLTEXT ^
  /INSTANCENAME=MSSQLSERVER ^
  /SQLSYSADMINACCOUNTS="DOMAIN\SQLAdmins" ^
  /SQLCOLLATION=Chinese_PRC_CI_AS ^
  /SQLBACKUPDIR="E:\SQLBackup" ^
  /SQLUSERDBDIR="D:\SQLData" ^
  /SQLUSERDBLOGDIR="F:\SQLLog" ^
  /SQLTEMPDBDIR="G:\TempDB" ^
  /SQLTEMPDBLOGDIR="G:\TempDB" ^
  /SQLTEMPDBFILECOUNT=8 ^
  /SQLTEMPDBFILESIZE=512 ^
  /SQLTEMPDBFILEGROWTH=512 ^
  /SQLTEMPDBLOGFILESIZE=128 ^
  /IACCEPTSQLSERVERLICENSETERMS ^
  /UPDATEENABLED=FALSE

最佳实践：将数据文件、日志文件、TempDB 分别放在不同物理磁盘，避免 IO 竞争。

1.2.2 Linux（Ubuntu 22.04）安装

# 1. 导入 Microsoft GPG 密钥
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

# 2. 添加 SQL Server 2022 存储库
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list)"

# 3. 安装 SQL Server
sudo apt-get update
sudo apt-get install -y mssql-server

# 4. 运行初始配置向导
sudo /opt/mssql/bin/mssql-conf setup
# 选择 Edition（建议选 Developer 用于学习）
# 输入 SA 密码（复杂度要求：大小写+数字+特殊字符，最少8位）

# 5. 验证服务状态
systemctl status mssql-server

# 6. 安装命令行工具 sqlcmd
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo apt-get install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# 7. 连接验证
sqlcmd -S localhost -U SA -P 'YourStrong@Passw0rd' -Q "SELECT @@VERSION"

1.2.3 关键配置参数（mssql-conf / 注册表）

# Linux mssql-conf 常用配置
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 8192     # 限制最大内存 8GB
sudo /opt/mssql/bin/mssql-conf set network.tcpport 1433          # TCP 端口
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true          # 启用 SQL Agent
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1             # 启用 Always On
sudo /opt/mssql/bin/mssql-conf traceflag 1118 1117 on             # TempDB 优化标志

-- Windows 端：通过 sp_configure 修改服务器配置
-- 开启高级选项
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- 最大服务器内存（MB），留 10-15% 给 OS
EXEC sp_configure 'max server memory (MB)', 24576;  -- 24GB

-- 最小服务器内存（MB）
EXEC sp_configure 'min server memory (MB)', 4096;

-- 最大并行度（MAXDOP），通常设置为物理核心数的一半
EXEC sp_configure 'max degree of parallelism', 8;

-- 并行查询代价阈值（Cost Threshold for Parallelism）
EXEC sp_configure 'cost threshold for parallelism', 50;

-- 优化 Ad-hoc 查询缓存
EXEC sp_configure 'optimize for ad hoc workloads', 1;

RECONFIGURE WITH OVERRIDE;

-- 验证当前配置
SELECT name, value, value_in_use, description
FROM   sys.configurations
ORDER BY name;

1.2.4 排序规则（Collation）选择

排序规则决定了字符的存储、排序和比较规则，安装后极难更改，需提前规划。

-- 查看所有支持的排序规则（含中文）
SELECT *
FROM   fn_helpcollations()
WHERE  name LIKE 'Chinese%';

-- 常用中文排序规则说明
-- Chinese_PRC_CI_AS  : 简体中文，不区分大小写，区分重音（最常用）
-- Chinese_PRC_CS_AS  : 简体中文，区分大小写，区分重音
-- Chinese_PRC_CI_AI  : 简体中文，不区分大小写，不区分重音

-- 在数据库级别修改排序规则（仅影响新创建的列）
ALTER DATABASE [YourDB] COLLATE Chinese_PRC_CI_AS;

-- 检查数据库和列的排序规则
SELECT DB_NAME()                AS DatabaseName,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DBCollation;

SELECT t.name AS TableName,
       c.name AS ColumnName,
       c.collation_name
FROM   sys.tables t
JOIN   sys.columns c ON t.object_id = c.object_id
WHERE  c.collation_name IS NOT NULL;

1.3 系统数据库详解与 TempDB 优化

1.3.1 五大系统数据库职责

-- 查看 master 库中的登录名
SELECT name, type_desc, create_date, is_disabled
FROM   master.sys.server_principals
WHERE  type IN ('S', 'U', 'G')
ORDER BY name;

-- 查看 msdb 中最近 7 天的备份记录
SELECT bs.database_name,
       bs.backup_start_date,
       bs.backup_finish_date,
       bs.type,
       CAST(bs.backup_size / 1048576.0 AS DECIMAL(10,2)) AS SizeMB,
       bmf.physical_device_name
FROM   msdb.dbo.backupset bs
JOIN   msdb.dbo.backupmediafamily bmf
    ON bs.media_set_id = bmf.media_set_id
WHERE  bs.backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY bs.backup_start_date DESC;

1.3.2 TempDB 深度优化

TempDB 是 SQL Server 中竞争最激烈的系统资源，所有用户共享，常见瓶颈包括： - SGAM/GAM/PFS 页闩锁争用：大量并发分配临时对象 - 版本存储增长：快照隔离（RCSI）产生大量行版本

核心优化策略：多文件并行分配

-- 查看当前 TempDB 文件数和大小
SELECT name,
       physical_name,
       CAST(size * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB,
       CAST(max_size * 8.0 / 1024 AS DECIMAL(10,2)) AS MaxSizeMB,
       growth,
       is_percent_growth
FROM   tempdb.sys.database_files;

-- 最佳实践：数据文件数 = min(逻辑CPU核心数, 8)
-- 所有数据文件大小相等（消除不均匀分配）
-- 关闭自动增长或设置固定增量

-- 示例：添加 TempDB 数据文件（共 8 个，每个 512MB）
USE tempdb;
ALTER DATABASE tempdb
  ADD FILE (NAME = 'tempdev2', FILENAME = 'G:\TempDB\tempdev2.ndf', SIZE = 512MB, FILEGROWTH = 256MB),
  ADD FILE (NAME = 'tempdev3', FILENAME = 'G:\TempDB\tempdev3.ndf', SIZE = 512MB, FILEGROWTH = 256MB),
  ADD FILE (NAME = 'tempdev4', FILENAME = 'G:\TempDB\tempdev4.ndf', SIZE = 512MB, FILEGROWTH = 256MB),
  ADD FILE (NAME = 'tempdev5', FILENAME = 'G:\TempDB\tempdev5.ndf', SIZE = 512MB, FILEGROWTH = 256MB),
  ADD FILE (NAME = 'tempdev6', FILENAME = 'G:\TempDB\tempdev6.ndf', SIZE = 512MB, FILEGROWTH = 256MB),
  ADD FILE (NAME = 'tempdev7', FILENAME = 'G:\TempDB\tempdev7.ndf', SIZE = 512MB, FILEGROWTH = 256MB),
  ADD FILE (NAME = 'tempdev8', FILENAME = 'G:\TempDB\tempdev8.ndf', SIZE = 512MB, FILEGROWTH = 256MB);

-- 修改现有 tempdev 文件大小保持一致
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 512MB, FILEGROWTH = 256MB);

-- 监控 TempDB 空间使用
SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS FreeSpaceMB,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS VersionStoreMB,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS UserObjectsMB,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS InternalObjectsMB
FROM   sys.dm_db_file_space_usage;

-- 找出占用 TempDB 最多的会话
SELECT s.session_id,
       s.login_name,
       t.user_objects_alloc_page_count * 8 AS UserObjKB,
       t.internal_objects_alloc_page_count * 8 AS InternalObjKB,
       r.text AS QueryText
FROM   sys.dm_db_session_space_usage t
JOIN   sys.dm_exec_sessions s ON t.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(
    (SELECT TOP 1 sql_handle FROM sys.dm_exec_requests r2
     WHERE r2.session_id = t.session_id)) r
WHERE  t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count > 0
ORDER BY (t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count) DESC;

1.4 管理工具实战

1.4.1 SSMS 核心功能

SQL Server Management Studio（SSMS） 是 SQL Server 最主要的图形化管理工具。

常用快捷键：

注册服务器（管理多实例）：

-- 通过 SSMS 的"注册服务器"功能，可以集中管理多个实例
-- 在中央管理服务器（CMS）上注册所有子服务器后，可以跨服务器批量执行查询

-- 示例：查询所有注册服务器的版本（在 CMS 中执行）
SELECT @@SERVERNAME, @@VERSION;

1.4.2 sqlcmd 命令行客户端

# 基本连接
sqlcmd -S <server>\<instance> -U <user> -P <password> -d <database>

# 使用 Windows 身份验证
sqlcmd -S MyServer -E

# 执行 SQL 文件
sqlcmd -S MyServer -E -i "C:\Scripts\deploy.sql" -o "C:\Scripts\deploy.log"

# 执行内联查询
sqlcmd -S MyServer -E -Q "SELECT @@VERSION" -W -s ","

# 在脚本中使用变量
sqlcmd -S MyServer -E -v DBName="AdventureWorks" -Q "USE $(DBName); SELECT COUNT(*) FROM Sales.Orders"

# 批量导出数据（bcp 工具）
bcp "SELECT TOP 1000 * FROM AdventureWorks.Sales.SalesOrderHeader" queryout "C:\Export\orders.txt" -S MyServer -T -c -t"," -r"\n"

# 批量导入数据
bcp AdventureWorks.dbo.ImportTable IN "C:\Data\import.txt" -S MyServer -T -c -t"," -r"\n" -F2

1.4.3 PowerShell DBA 脚本

# 安装 SqlServer PowerShell 模块
Install-Module -Name SqlServer -Force -AllowClobber

# 导入模块
Import-Module SqlServer

# 查询数据库列表
Get-SqlDatabase -ServerInstance "MyServer" | 
    Select-Object Name, Status, Size, RecoveryModel |
    Format-Table -AutoSize

# 执行 T-SQL 查询
Invoke-Sqlcmd -ServerInstance "MyServer" -Database "master" -Query "
    SELECT TOP 10 
        DB_NAME(database_id) AS DBName,
        COUNT(*) AS OpenTransactions
    FROM sys.dm_tran_active_transactions
    GROUP BY database_id
"

# 批量备份所有用户数据库
$server = "MyServer"
$backupPath = "E:\SQLBackup"
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

Get-SqlDatabase -ServerInstance $server | 
    Where-Object { $_.Name -notin @('master','model','msdb','tempdb') -and $_.Status -eq 'Normal' } |
    ForEach-Object {
        $dbName = $_.Name
        $backupFile = "$backupPath\$dbName`_$timestamp.bak"
        Write-Host "备份 $dbName → $backupFile"
        Backup-SqlDatabase -ServerInstance $server -Database $dbName -BackupFile $backupFile -CompressionOption On
    }

# 检查磁盘空间（对所有注册实例）
$instances = @("Server01", "Server02", "Server03")
foreach ($inst in $instances) {
    Invoke-Sqlcmd -ServerInstance $inst -Query "
        SELECT SERVERPROPERTY('MachineName') AS Host,
               volume_mount_point,
               CAST(available_bytes/1073741824.0 AS DECIMAL(10,2)) AS FreeGB,
               CAST(total_bytes/1073741824.0 AS DECIMAL(10,2)) AS TotalGB
        FROM sys.dm_os_volume_stats(1, 1)
    "
}

1.4.4 系统健康检查脚本

-- ============================================================
-- SQL Server 实例健康检查（每日运维必备）
-- ============================================================

-- 1. 实例基本信息
SELECT  SERVERPROPERTY('ServerName')        AS InstanceName,
        SERVERPROPERTY('ProductVersion')    AS Version,
        SERVERPROPERTY('Edition')           AS Edition,
        SERVERPROPERTY('Collation')         AS Collation,
        SERVERPROPERTY('IsClustered')       AS IsClustered,
        SERVERPROPERTY('IsHadrEnabled')     AS IsAlwaysOnEnabled;

-- 2. 查看等待统计（识别瓶颈）
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    CAST(wait_time_ms / 1000.0 AS DECIMAL(10,2))         AS WaitTimeSec,
    CAST(signal_wait_time_ms / 1000.0 AS DECIMAL(10,2))  AS SignalWaitSec,
    CAST(wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS PctTotal
FROM   sys.dm_os_wait_stats
WHERE  wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE',
    'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_MONITOR','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLAGENT_ALERT_SET',
    'SQLAGENT_LOCK_MONITOR','SQLAGENT_NOTIFY','SQLAGENT_WAIT_ON_OPER',
    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','WAITFOR','XE_DISPATCHER_WAIT',
    'XE_TIMER_EVENT','BROKER_EVENTHANDLER','CHECKPOINT_QUEUE',
    'DBMIRROR_EVENTS_QUEUE','SQLAGENT_STEPS_THREAD','TRACEWRITE',
    'XE_DISPATCHER_JOIN')
ORDER BY wait_time_ms DESC;

-- 3. 检查数据库文件空间
SELECT  DB_NAME(vfs.database_id) AS DatabaseName,
        mf.name                  AS LogicalName,
        mf.physical_name,
        mf.type_desc,
        CAST(vfs.size_on_disk_bytes / 1048576.0 AS DECIMAL(10,2)) AS FileSizeMB,
        CAST(FILEPROPERTY(mf.name,'SpaceUsed') * 8.0 / 1024 AS DECIMAL(10,2)) AS UsedMB
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN    sys.master_files mf ON vfs.database_id = mf.database_id
                           AND vfs.file_id = mf.file_id
ORDER BY vfs.database_id, mf.file_id;

-- 4. 当前阻塞链
SELECT  r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000 AS WaitSec,
        SUBSTRING(t.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS CurrentSQL,
        s.login_name,
        s.host_name,
        s.program_name
FROM    sys.dm_exec_requests r
JOIN    sys.dm_exec_sessions s  ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE   r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

1.5 小结

下一章：T-SQL 查询基础