# 1. 硬件配置

— 选择合适的CPU：根据工作负载选择多核、高频率的CPU
— 配置足够的内存：通常为物理内存的80%-85%
— 使用高性能存储：如SSD，配置适当的RAID级别
— 确保网络带宽：配置足够的网络带宽，减少网络延迟

# 2. 数据库配置

— 合理设置数据库文件：
CREATE DATABASE fgedudb
ON PRIMARY (
NAME = ‘fgedudb_data’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_data.mdf’,
SIZE = 100GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10GB
)
LOG ON (
NAME = ‘fgedudb_log’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_log.ldf’,
SIZE = 20GB,
MAXSIZE = 200GB,
FILEGROWTH = 5GB
);
GO

— 优化tempdb配置：
ALTER DATABASE tempdb MODIFY FILE (
NAME = ‘tempdev’,
SIZE = 2GB,
FILEGROWTH = 512MB
);
GO

# 3. 高可用性配置

— 配置Always On可用性组：
CREATE AVAILABILITY GROUP [fgedu_ag]
WITH (DB_FAILOVER = ON, AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR DATABASE [fgedudb]
REPLICA ON
N’FGEDU1′ WITH (
ENDPOINT_URL = N’TCP://fgedu1.fgedu.net.cn:5022′,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
),
N’FGEDU2′ WITH (
ENDPOINT_URL = N’TCP://fgedu2.fgedu.net.cn:5022′,
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
);
GO

# 4. 安全配置

— 启用透明数据加密：
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘FgEdu123!@#’;
GO

CREATE CERTIFICATE fgedu_tde_cert WITH SUBJECT = ‘FGEDU TDE Certificate’;
GO

USE fgedudb;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE fgedu_tde_cert;
GO

ALTER DATABASE fgedudb SET ENCRYPTION ON;
GO

# 5. 监控配置

— 创建扩展事件会话：
CREATE EVENT SESSION [fgedu_performance_monitor] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text)
WHERE ([duration] > 1000000)
),
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text)
WHERE ([duration] > 1000000)
)
ADD TARGET package0.event_file(
SET filename=N’\\fgedu1\\monitoring\\fgedu_performance_monitor.xel’,
max_file_size=(100),
max_rollover_files=(5)
)
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=ON,
STARTUP_STATE=ON
);
GO

ALTER EVENT SESSION [fgedu_performance_monitor] ON SERVER STATE = START;
GO