七、高可用性
7.1 Always On 可用性组
-- 创建端点
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
);
-- 创建可用性组
CREATE AVAILABILITY GROUP AG_SalesDB
WITH (
AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
FAILURE_CONDITION_LEVEL = 3,
HEALTH_CHECK_TIMEOUT = 30000
)
FOR DATABASE SalesDB
REPLICA ON
'SQLServer1' WITH (
ENDPOINT_URL = 'TCP://SQLServer1:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
),
'SQLServer2' WITH (
ENDPOINT_URL = 'TCP://SQLServer2:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
-- 创建可用性组监听器
ALTER AVAILABILITY GROUP AG_SalesDB
ADD LISTENER 'SalesDB_Listener' (
WITH IP (('10.0.0.100', '255.255.255.0')),
PORT = 1433
);
-- 查看可用性组状态
SELECT
ag.name AS AGName,
ar.replica_server_name,
rs.role_desc,
rs.operational_state_desc,
rs.connected_state_desc,
rs.synchronization_health_desc
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
INNER JOIN sys.dm_hadr_availability_replica_states rs
ON ar.replica_id = rs.replica_id;
-- 手动故障转移
ALTER AVAILABILITY GROUP AG_SalesDB FAILOVER;
7.2 日志传送
-- 配置日志传送（在主服务器上）
-- 设置备份作业
EXEC sp_add_log_shipping_primary_database
@database = 'SalesDB',
@backup_directory = '\\backup_server\LogShipping',
@backup_share = '\\backup_server\LogShipping',
@backup_job_name = 'LSBackup_SalesDB',
@backup_retention_period = 72,
@monitor_server = 'MonitorServer',
@monitor_server_security_mode = 1;
-- 添加辅助服务器
EXEC sp_add_log_shipping_primary_secondary
@primary_database = 'SalesDB',
@secondary_server = 'SecondaryServer',
@secondary_database = 'SalesDB_Standby';
-- 查看日志传送状态
SELECT
primary_database,
secondary_server,
secondary_database,
last_backup_file,
last_copied_file,
last_restored_file,
last_restored_latency
FROM msdb.dbo.log_shipping_monitor_primary;
八、SQL Server Agent 与作业
8.1 作业管理
-- 创建作业
USE msdb;
GO
EXEC sp_add_job
@job_name = 'Daily Database Maintenance',
@enabled = 1,
@description = '每日数据库维护作业',
@notify_level_eventlog = 2,
@owner_login_name = 'sa';
-- 添加作业步骤
EXEC sp_add_jobstep
@job_name = 'Daily Database Maintenance',
@step_name = 'Backup Database',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE SalesDB TO DISK = ''D:\Backups\SalesDB.bak'' WITH COMPRESSION',
@on_success_action = 3,
@retry_attempts = 2,
@retry_interval = 5;
EXEC sp_add_jobstep
@job_name = 'Daily Database Maintenance',
@step_name = 'Update Statistics',
@subsystem = 'TSQL',
@command = 'EXEC sp_updatestats',
@on_success_action = 1;
-- 创建作业计划
EXEC sp_add_schedule
@schedule_name = 'Daily at 2AM',
@freq_type = 4, -- 每天
@freq_interval = 1,
@active_start_time = 20000; -- 2:00 AM
-- 关联作业和计划
EXEC sp_attach_schedule
@job_name = 'Daily Database Maintenance',
@schedule_name = 'Daily at 2AM';
-- 添加作业通知
EXEC sp_add_jobserver
@job_name = 'Daily Database Maintenance',
@server_name = '(LOCAL)';
-- 启动作业
EXEC sp_start_job @job_name = 'Daily Database Maintenance';
-- 查看作业历史
SELECT
j.name AS JobName,
jh.step_name,
jh.run_date,
jh.run_time,
jh.run_duration,
jh.run_status
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE j.name = 'Daily Database Maintenance'
ORDER BY jh.run_date DESC, jh.run_time DESC;
8.2 警报与操作员
-- 创建操作员
EXEC msdb.dbo.sp_add_operator
@name = 'DBA Team',
@enabled = 1,
@email_address = 'dba@company.com',
@pager_address = '1234567890@provider.com',
@weekday_pager_start_time = 80000,
@weekday_pager_end_time = 180000;
-- 创建严重性警报
EXEC msdb.dbo.sp_add_alert
@name = 'Severity 16 Error',
@message_id = 0,
@severity = 16,
@enabled = 1,
@include_event_description_in = 1,
@notification_message = '严重性16错误发生',
@job_name = 'Handle Error';
-- 创建性能条件警报
EXEC msdb.dbo.sp_add_alert
@name = 'Low Disk Space',
@performance_condition = 'LogicalDisk|Free Space|C:|<|1024',
@enabled = 1,
@notification_message = 'C盘剩余空间不足1GB';
-- 创建错误号警报
EXEC msdb.dbo.sp_add_alert
@name = 'Deadlock Occurred',
@message_id = 1205,
@severity = 0,
@enabled = 1;
-- 添加通知
EXEC msdb.dbo.sp_add_notification
@alert_name = 'Severity 16 Error',
@operator_name = 'DBA Team',
@notification_method = 1; -- 电子邮件
九、安全与权限
9.1 登录与用户
-- 创建 Windows 登录
CREATE LOGIN [DOMAIN\UserName]
FROM WINDOWS
WITH DEFAULT_DATABASE = master;
-- 创建 SQL Server 登录
CREATE LOGIN AppUser
WITH PASSWORD = 'StrongP@ssw0rd',
DEFAULT_DATABASE = SalesDB,
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON;
-- 创建数据库用户
USE SalesDB;
CREATE USER AppUser FOR LOGIN AppUser;
CREATE USER DomainUser FOR LOGIN [DOMAIN\UserName];
-- 创建无登录用户
CREATE USER GuestUser WITHOUT LOGIN;
-- 包含数据库用户（SQL Server 2012+）
CREATE USER ContainedUser
WITH PASSWORD = 'StrongP@ssw0rd',
DEFAULT_SCHEMA = dbo;
-- 修改登录
ALTER LOGIN AppUser WITH PASSWORD = 'NewP@ssw0rd';
ALTER LOGIN AppUser WITH DEFAULT_DATABASE = SalesDB;
ALTER LOGIN AppUser WITH CHECK_POLICY = OFF;
ALTER LOGIN AppUser ENABLE;
ALTER LOGIN AppUser DISABLE;
-- 删除登录
DROP LOGIN AppUser;
9.2 权限管理
-- 服务器角色
EXEC sp_addsrvrolemember 'AppUser', 'sysadmin';
EXEC sp_addsrvrolemember 'AppUser', 'securityadmin';
EXEC sp_addsrvrolemember 'AppUser', 'serveradmin';
EXEC sp_addsrvrolemember 'AppUser', 'setupadmin';
EXEC sp_addsrvrolemember 'AppUser', 'processadmin';
EXEC sp_addsrvrolemember 'AppUser', 'diskadmin';
EXEC sp_addsrvrolemember 'AppUser', 'dbcreator';
EXEC sp_addsrvrolemember 'AppUser', 'bulkadmin';
-- 数据库角色
EXEC sp_addrolemember 'db_owner', 'AppUser';
EXEC sp_addrolemember 'db_securityadmin', 'AppUser';
EXEC sp_addrolemember 'db_accessadmin', 'AppUser';
EXEC sp_addrolemember 'db_backupoperator', 'AppUser';
EXEC sp_addrolemember 'db_ddladmin', 'AppUser';
EXEC sp_addrolemember 'db_datawriter', 'AppUser';
EXEC sp_addrolemember 'db_datareader', 'AppUser';
-- 创建自定义角色
CREATE ROLE SalesManager;
GRANT SELECT, INSERT, UPDATE ON Sales TO SalesManager;
GRANT EXECUTE ON sp_GetSalesReport TO SalesManager;
EXEC sp_addrolemember 'SalesManager', 'AppUser';
-- 授予对象权限
GRANT SELECT ON Employees TO AppUser;
GRANT INSERT, UPDATE ON Employees TO AppUser;
GRANT DELETE ON Employees TO AppUser WITH GRANT OPTION; -- 允许传递权限
GRANT EXECUTE ON sp_GetEmployeeDetails TO AppUser;
GRANT REFERENCES ON Employees(EmployeeID) TO AppUser;
-- 拒绝权限
DENY DELETE ON Employees TO AppUser;
-- 撤销权限
REVOKE DELETE ON Employees FROM AppUser;
-- 查看权限
SELECT
pr.name AS PrincipalName,
pr.type_desc AS PrincipalType,
pe.permission_name,
pe.state_desc,
o.name AS ObjectName
FROM sys.database_permissions pe
INNER JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.objects o ON pe.major_id = o.object_id
WHERE pr.name = 'AppUser';
9.3 数据加密
-- 透明数据加密（TDE）
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssw0rd';
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate';
BACKUP CERTIFICATE TDECert
TO FILE = 'D:\Certificates\TDECert.cer'
WITH PRIVATE KEY (
FILE = 'D:\Certificates\TDECert.key',
ENCRYPTION BY PASSWORD = 'CertP@ssw0rd'
);
USE SalesDB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE SalesDB SET ENCRYPTION ON;
-- 查看加密状态
SELECT
db_name(database_id) AS DatabaseName,
encryption_state_desc,
percent_complete
FROM sys.dm_database_encryption_keys;
-- 列级加密
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyP@ss';
CREATE CERTIFICATE ColumnCert
WITH SUBJECT = 'Column Encryption Certificate';
CREATE SYMMETRIC KEY ColumnKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE ColumnCert;
-- 创建表并加密列
CREATE TABLE CustomerData (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
CreditCard VARBINARY(MAX)
);
-- 加密数据
OPEN SYMMETRIC KEY ColumnKey
DECRYPTION BY CERTIFICATE ColumnCert;
INSERT INTO CustomerData (CustomerID, CustomerName, CreditCard)
VALUES (
1,
'张三',
EncryptByKey(Key_GUID('ColumnKey'), '1234-5678-9012-3456')
);
-- 解密数据
SELECT
CustomerID,
CustomerName,
CONVERT(NVARCHAR, DecryptByKey(CreditCard)) AS CreditCard
FROM CustomerData;
CLOSE SYMMETRIC KEY ColumnKey;
