SQL Server 管理系列（第一期）：安装配置与基础架构规划

SQL Server 管理系列（第一期）：安装配置与基础架构规划

欢迎来到管理系列！开发系列我们聚焦于“怎么写 SQL”，管理系列则关注“怎么让 SQL Server 跑得稳、跑得快、跑得安全”。作为管理系列的开篇，我们从最基础的安装配置与架构规划开始。你是否遇到过：安装时一堆选项不知道选什么？内存配置不当导致服务器卡顿？日志文件无限增长撑爆磁盘？这些问题的根源，往往在于安装和配置阶段就埋下了隐患。这一期，我们从头开始，搭建一个生产级别的 SQL Server 环境。

一、安装前的规划

1.1 版本选择

场景	推荐版本	说明
生产环境（OLTP）	SQL Server 2019/2022 标准版	稳定可靠，功能完整
高并发/大数据量	SQL Server 2019/2022 企业版	在线索引、分区、压缩
开发/测试环境	Developer Edition	免费，功能同企业版
报表/数据分析	SQL Server 2022 + 列存储	分析性能大幅提升
云环境	Azure SQL Database	无需管理基础设施

版本差异关键点：

• 企业版独有：在线索引重建、表分区、数据压缩、资源调控器
• 标准版：最大 128GB 内存（SQL 2019+ 已提升到 128GB），24核
• 开发版：全功能，但不可用于生产

1.2 硬件规划公式

资源	规划原则	中小生产环境参考
CPU	核心数 = 并发查询数 / 4	8-16 核
内存	数据量 20%-50% + 操作系统 4-16GB	32-128GB
系统盘	100-200GB	200GB SSD
数据盘	当前数据量 × 1.5（预留增长）	500GB-2TB SSD
日志盘	数据盘的 30%-50%	200-500GB SSD
tempdb 盘	数据盘的 20%-30%	100-200GB SSD

磁盘分离最佳实践：

C: 系统盘 + SQL Server 程序
D: 数据文件（.mdf / .ndf）
E: 日志文件（.ldf）
F: tempdb 文件
G: 备份文件

1.3 安装前的 Windows 配置

# PowerShell 脚本：生产环境 Windows 配置

# 1. 电源管理（设置为高性能）
powercfg /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c

# 2. 禁用 Windows 更新自动重启
New-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU" `
    -Name "NoAutoRebootWithLoggedOnUsers" -Value 1 -PropertyType DWORD -Force

# 3. 设置虚拟内存（建议初始值 = 物理内存 × 1.5）
# 手动设置：系统属性 → 高级 → 性能 → 虚拟内存

# 4. 防火墙开放 SQL Server 端口（1433）
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# 5. 格式化磁盘分配单元大小为 64KB（重要！）
# 使用磁盘管理或 diskpart
# format fs=ntfs unit=65536 quick

二、SQL Server 安装详解

2.1 安装选项决策

安装选项	推荐选择	说明
实例类型	默认实例	单实例推荐默认，多实例用命名
服务器排序规则	Chinese_PRC_CI_AS	中文不区分大小写（根据业务）
身份验证模式	混合模式	兼容 Windows 和 SQL 登录
sa 账户	禁用	创建专属管理员账户
数据目录	D:\Data, E:\Logs, F:\TempDB	分开存放
分析服务	按需	无 BI 需求可不装
集成服务	按需	无 ETL 需求可不装
复制	按需	无同步需求可不装

2.2 静默安装（自动化部署）

<!-- ConfigurationFile.ini -->
[OPTIONS]
ACTION = "Install"
FEATURES = SQLENGINE, FULLTEXT, CONN
INSTANCENAME = "MSSQLSERVER"
SECURITYMODE = "SQL"
SAPWD = "复杂密码"
SQLSYSADMINACCOUNTS = "DOMAIN\DBA_Group"
SQLCOLLATION = "Chinese_PRC_CI_AS"
SQLUSERDBDIR = "D:\Data"
SQLUSERDBLOGDIR = "E:\Logs"
SQLTEMPDBDIR = "F:\TempDB"
SQLTEMPDBLOGDIR = "F:\TempDB"
SQLBACKUPDIR = "G:\Backup"

# 执行静默安装
.\SETUP.exe /ConfigurationFile=.\ConfigurationFile.ini /quiet

三、核心配置（安装后立即执行）

3.1 服务器内存配置（最重要！）

-- 查看当前内存配置
SELECT name, value_in_use 
FROM sys.configurations 
WHERE name LIKE '%memory%'

-- 设置最大内存（以 MB 为单位）
-- 公式：总内存 - 操作系统预留（4GB + 每4GB内存加1GB）
-- 示例：32GB 服务器 → 32 - 6 = 26GB = 26624MB
EXEC sp_configure 'max server memory (MB)', 26624
EXEC sp_configure 'min server memory (MB)', 4096  -- 预留最低内存
RECONFIGURE

-- 查看内存使用情况
SELECT 
    (physical_memory_in_use_kb / 1024) AS SQL_Physical_Memory_MB,
    (locked_page_allocations_kb / 1024) AS Locked_Pages_MB,
    (total_virtual_address_space_kb / 1024) AS Virtual_Memory_MB
FROM sys.dm_os_process_memory

3.2 CPU 与并发配置

-- 查看 CPU 配置
EXEC sp_configure 'max degree of parallelism'      -- 最大并行度
EXEC sp_configure 'cost threshold for parallelism' -- 并行成本阈值

-- 推荐配置（根据实际调整）
-- MAXDOP：8核以下 = 核数，8核以上 = 8
EXEC sp_configure 'max degree of parallelism', 8

-- 成本阈值：默认 5 太低，建议 50
EXEC sp_configure 'cost threshold for parallelism', 50
RECONFIGURE

-- 查看 CPU 使用情况
SELECT 
    scheduler_id,
    cpu_id,
    status,
    is_online,
    current_tasks_count,
    runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'

3.3 数据库文件配置

-- 创建数据库时指定文件和增长设置
CREATE DATABASE YourDB
ON PRIMARY 
(
    NAME = YourDB_Data,
    FILENAME = 'D:\Data\YourDB_Data.mdf',
    SIZE = 1024MB,
    MAXSIZE = 10240MB,
    FILEGROWTH = 1024MB  -- 不要用百分比！至少 512MB
)
LOG ON 
(
    NAME = YourDB_Log,
    FILENAME = 'E:\Logs\YourDB_Log.ldf',
    SIZE = 512MB,
    MAXSIZE = 20480MB,
    FILEGROWTH = 512MB
)

-- 修改现有数据库文件增长设置
ALTER DATABASE YourDB MODIFY FILE 
(
    NAME = YourDB_Data,
    FILEGROWTH = 1024MB
)

-- 添加多个数据文件（利用多磁盘 I/O）
ALTER DATABASE YourDB ADD FILE 
(
    NAME = YourDB_Data2,
    FILENAME = 'D:\Data\YourDB_Data2.ndf',
    SIZE = 1024MB,
    FILEGROWTH = 1024MB
)

-- 添加 tempdb 文件（核心优化！）
-- tempdb 文件数 = CPU 核心数（不超过 8）
USE master
GO
ALTER DATABASE tempdb MODIFY FILE 
(
    NAME = tempdev,
    FILENAME = 'F:\TempDB\tempdb.mdf',
    SIZE = 8192MB,
    FILEGROWTH = 1024MB
)

ALTER DATABASE tempdb ADD FILE 
(
    NAME = tempdev2,
    FILENAME = 'F:\TempDB\tempdb2.ndf',
    SIZE = 8192MB,
    FILEGROWTH = 1024MB
)
-- 重复添加，文件数 = CPU 核心数

3.4 数据库恢复模式设置

-- 查看当前恢复模式
SELECT name, recovery_model_desc 
FROM sys.databases

-- 生产环境（需要时间点恢复）：FULL
ALTER DATABASE YourDB SET RECOVERY FULL

-- 开发/测试环境：SIMPLE
ALTER DATABASE YourDB SET RECOVERY SIMPLE

-- 批量导入临时切换（注意会破坏日志链）
ALTER DATABASE YourDB SET RECOVERY BULK_LOGGED
-- 批量操作后立即切回 FULL 并做完整备份
ALTER DATABASE YourDB SET RECOVERY FULL
BACKUP DATABASE YourDB TO DISK = 'G:\Backup\YourDB_Full.bak'

四、安全基础配置

4.1 创建专用管理员账户

-- 禁用 sa（如果必须保留，设置超复杂密码）
ALTER LOGIN sa DISABLE

-- 创建 DBA 专属登录
CREATE LOGIN DBA_Admin WITH 
    PASSWORD = 'VeryComplex!@#123' MUST_CHANGE,
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON

-- 添加为 sysadmin
EXEC sp_addsrvrolemember 'DBA_Admin', 'sysadmin'

-- 创建应用专用登录（最低权限）
CREATE LOGIN AppUser WITH PASSWORD = 'AppP@ssw0rd'
-- 只授予需要的数据库权限
USE YourDB
CREATE USER AppUser FOR LOGIN AppUser
EXEC sp_addrolemember 'db_datareader', 'AppUser'
EXEC sp_addrolemember 'db_datawriter', 'AppUser'

4.2 网络配置

-- 查看当前网络配置
EXEC xp_readerrorlog 0, 1, 'listening'

-- 启用 TCP/IP（需使用 SQL Server 配置管理器）
-- 设置端口：1433（默认），可更改
-- 配置防火墙：允许端口入站

-- 隐藏实例（增加安全性）
USE master
GO
EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'HideInstance',
    REG_DWORD,
    1

五、备份策略初始配置

5.1 维护计划基础

-- 创建备份目录（需手动创建）
-- G:\Backup\Full\
-- G:\Backup\Diff\
-- G:\Backup\Log\

-- 完整备份（每周日）
BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\Full\YourDB_Full_YYYYMMDD.bak'
WITH INIT, COMPRESSION, STATS = 10

-- 差异备份（每天）
BACKUP DATABASE YourDB 
TO DISK = 'G:\Backup\Diff\YourDB_Diff_YYYYMMDD.bak'
WITH DIFFERENTIAL, INIT, COMPRESSION

-- 日志备份（每小时）
BACKUP LOG YourDB 
TO DISK = 'G:\Backup\Log\YourDB_Log_YYYYMMDD_HHMM.trn'
WITH INIT, COMPRESSION

5.2 自动化备份脚本

-- 创建存储过程自动备份所有用户数据库
CREATE PROC dbo.sp_BackupAllDatabases
AS
BEGIN
    DECLARE @dbname VARCHAR(100)
    DECLARE @backupPath VARCHAR(500) = 'G:\Backup\'
    DECLARE @date VARCHAR(10) = CONVERT(VARCHAR, GETDATE(), 112)
    DECLARE @sql NVARCHAR(MAX)
    
    DECLARE db_cursor CURSOR FOR
        SELECT name FROM sys.databases 
        WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
          AND state = 0  -- ONLINE
          AND is_in_standby = 0
    
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbname
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 完整备份
        SET @sql = 'BACKUP DATABASE [' + @dbname + '] 
                    TO DISK = ''' + @backupPath + @dbname + '_' + @date + '.bak''
                    WITH INIT, COMPRESSION'
        EXEC sp_executesql @sql
        
        FETCH NEXT FROM db_cursor INTO @dbname
    END
    
    CLOSE db_cursor
    DEALLOCATE db_cursor
END

六、初始性能基线采集

-- 创建性能基线表
CREATE TABLE DBA_PerformanceBaseline (
    BaselineID INT IDENTITY PRIMARY KEY,
    CollectionTime DATETIME DEFAULT GETDATE(),
    CounterName VARCHAR(200),
    CounterValue BIGINT,
    InstanceName VARCHAR(100)
)

-- 采集关键性能计数器
INSERT INTO DBA_PerformanceBaseline (CounterName, CounterValue, InstanceName)
SELECT 
    counter_name,
    cntr_value,
    instance_name
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Buffer cache hit ratio',
    'Page life expectancy',
    'Lazy writes/sec',
    'Total Server Memory (KB)',
    'SQL Compilations/sec',
    'SQL Re-Compilations/sec',
    'User Connections'
)

-- 查看基线（定期对比）
SELECT 
    CounterName,
    AVG(CounterValue) AS AvgValue,
    MIN(CounterValue) AS MinValue,
    MAX(CounterValue) AS MaxValue
FROM DBA_PerformanceBaseline
WHERE CollectionTime > DATEADD(day, -7, GETDATE())
GROUP BY CounterName
ORDER BY CounterName

七、安装配置检查清单

安装前检查

• 操作系统版本兼容性（Windows Server 2016+ / Linux）
• 硬件资源评估（CPU、内存、磁盘）
• 磁盘分离方案（数据、日志、tempdb、备份）
• 防火墙端口开放（1433、1434）
• Windows 更新策略（避免自动重启）

安装中配置

• 选择正确版本（Standard/Enterprise/Developer）
• 混合身份验证模式
• 数据目录路径正确
• tempdb 文件数 = CPU 核心数
• 排序规则正确（Chinese_PRC_CI_AS）

安装后配置

• max server memory 正确设置
• MAXDOP 和并行阈值配置
• 数据库文件增长设置（固定大小，非百分比）
• 恢复模式设置（生产用 FULL）
• 备份策略创建
• 维护计划创建
• 性能基线采集
• 监控告警配置

八、核心总结

知识点	核心要点
磁盘分离	数据、日志、tempdb、备份分盘存放
内存配置	max server memory = 总内存 - 4-16GB
tempdb 优化	文件数 = CPU 核心数，均匀分布
文件增长	固定大小增长（512MB/1024MB），禁用百分比
恢复模式	生产 FULL，开发 SIMPLE
安全配置	禁用 sa，使用 Windows 或专用账户
备份策略	全备 + 差备 + 日志备份
性能基线	安装后立即采集，用于对比

一句话记住本期内容：

生产环境安装 SQL Server 的核心是“规划先行”——磁盘分离、内存留足、tempdb 多文件、文件定长增长、安全最小权限、备份早配置。

下一期预告

安全加固——保护你的数据资产

• 最小权限原则：登录、用户、角色的正确配置
• 行级安全与动态数据脱敏
• 透明数据加密（TDE）
• SQL 注入防护（数据库层面）
• 审计与合规性配置

---

📌 本文配置适用于 SQL Server 2016-2022。实际生产环境请根据硬件和业务特点调整参数。

管理系列持续更新中，点击关注不错过第二期。