# 1. 数据库设计

# 创建数据库
CREATE DATABASE fgedudb
ON PRIMARY (
NAME = ‘fgedudb_data’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_data.mdf’,
SIZE = 100GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10GB
),
FILEGROUP [fg_data] (
NAME = ‘fgedudb_data_1’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_data_1.ndf’,
SIZE = 100GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10GB
),
FILEGROUP [fg_index] (
NAME = ‘fgedudb_index’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_index.ndf’,
SIZE = 50GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5GB
)
LOG ON (
NAME = ‘fgedudb_log’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_log.ldf’,
SIZE = 20GB,
MAXSIZE = 200GB,
FILEGROWTH = 5GB
);
GO

# 创建表
CREATE TABLE dbo.fgedu_users (
user_id INT PRIMARY KEY IDENTITY,
user_name NVARCHAR(50) NOT NULL,
email NVARCHAR(100) NOT NULL,
phone NVARCHAR(20) NOT NULL,
address NVARCHAR(200) NOT NULL,
create_time DATETIME DEFAULT GETDATE(),
update_time DATETIME DEFAULT GETDATE()
) ON [fg_data];
GO

CREATE INDEX IX_fgedu_users_email ON dbo.fgedu_users(email) ON [fg_index];
GO

# 2. 高可用性设计

# 创建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

# 配置可用性组监听器
ALTER AVAILABILITY GROUP [fgedu_ag]
ADD LISTENER N’fgedu_ag_listener’ (
WITH IP ((N’192.168.1.100′, N’255.255.255.0′)),
PORT = 1433
);
GO

# 3. 存储设计

# 配置存储
# 使用RAID 10存储数据文件
# 使用RAID 1存储日志文件

# 4. 网络设计

# 配置网络
# 使用专用网络进行复制
# 配置网络负载均衡

# 1. 性能评估

— 检查数据库性能
SELECT
object_name(object_id) AS table_name,
cntr_value AS page_count
FROM sys.dm_db_partition_stats
WHERE index_id < 2 ORDER BY page_count DESC; GO -- 检查索引使用情况 SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name, i.type_desc AS index_type, s.user_seeks + s.user_scans + s.user_lookups AS total_accesses, s.user_updates AS updates FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE s.database_id = DB_ID('fgedudb') ORDER BY total_accesses DESC; GO -- 检查等待统计信息 SELECT wait_type, wait_time_ms, signal_wait_time_ms, wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms, waiting_tasks_count FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC
TOP 10;
GO

# 2. 架构优化

— 优化索引
— 识别并删除未使用的索引
SELECT
OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name,
i.type_desc AS index_type
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID(‘fgedudb’)
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND i.type_desc != ‘CLUSTERED’;
GO

— 优化查询
— 识别高CPU查询
SELECT
qs.query_hash,
qs.total_worker_time / qs.execution_count AS avg_cpu_time_ms,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.execution_count,
st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_time_ms DESC
TOP 10;
GO

— 优化存储
— 检查数据库文件大小
SELECT
name,
physical_name,
size * 8 / 1024 AS size_mb,
max_size * 8 / 1024 AS max_size_mb,
growth * 8 / 1024 AS growth_mb
FROM sys.database_files;
GO

— 优化高可用性
— 检查可用性组状态
SELECT
ag.name AS availability_group,
ar.replica_server_name,
ars.role_desc,
ars.operational_state_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO

# 1. 架构概述
# 采用三层架构：
# – 数据层：SQLServer Always On Availability Groups
# – 应用层：Web应用服务器
# – 表示层：客户端应用

# 2. 数据层设计

# 主数据中心
# – 2节点Always On Availability Group
# – 1节点异步复制到灾备中心

# 灾备数据中心
# – 1节点接收异步复制
# – 定期测试故障转移

# 3. 存储设计

# 主数据中心
# – 存储区域网络(SAN)：RAID 10
# – 数据文件：多文件组，按功能分区
# – 日志文件：独立存储，RAID 1

# 4. 网络设计

# 主数据中心
# – 生产网络：用于应用访问
# – 复制网络：用于Always On同步
# – 管理网络：用于管理操作

# 5. 监控与管理

# 监控系统
# – SQL Server Management Studio
# – System Center Operations Manager
# – 自定义监控脚本

# 管理流程
# – 定期备份：每日全量备份，每小时差异备份，每15分钟事务日志备份
# – 定期维护：每周重建索引，更新统计信息
# – 定期评估：每月进行性能评估和架构审查

# 6. 安全设计

# 认证与授权
# – Windows身份验证
# – 基于角色的访问控制
# – 最小权限原则

# 数据加密
# – 透明数据加密(TDE)
# – 列级加密
# – 传输加密

# 审计
# – 数据库审计
# – 登录审计
# – 操作审计