2023-04-22

Database

沒有任何防備，突然被指派為 SQL Server 管理員的求生指南

說明
1. Scripts
資料庫管理者的幫手
1. Ken Fisher’s Security Scripts
參考資料
相關連結

面對突如其來的職位指派，只能硬著頭皮面對了。但初來乍到一定要有指南在手，增加勝任指派的可能性 😎

說明

首先是盤點管理的資料庫伺服器與資料庫的範圍，第一步是先釐清 SQL Server 管理員週期性的工作，

每日作業：

確認備份情形 (Backup History)
確定排程作業的進行 (Agent Job Monitor)
監控系統資源和事件日誌 (Activity Monitor & ErrorLog)

每週作業：

執行索引重建和統計更新 (Indexes & Statistics)
檢查資料庫的完整性 (DBCC CHECKDB)

每月作業：

清理資料庫日誌和過期的備份 (Backup File & Hisotry Clean)
測試資料庫還原 (Restore Tests)

每季作業：

資料庫 Patch Update
評估和優化 SQL Server 的配置

Scripts

以下由 Kevin Hill 在 Getting Started with SQL Server Maintenance 課程中所分享的 Scripts 有助於批次在註冊伺服器上查詢所有的資料庫實體相關資訊：

Backup History

Use msdb; 
go
Select  
	backup_start_date, database_name, type,	backup_size/1024/1024/1024 as [backup size (GB)], 
	compressed_backup_size/1024/1024/1024 as [Compressed backup size (GB)]
	,Physical_device_name as [Backup location]	
from msdb..backupset join msdb..backupmediafamily bmf on backupset.media_set_id = bmf.media_set_id
where 	1=1
	--and type = 'D' 	-- valid options: D (Full), I (Differential), L (Transaction Log)
	and database_name = 'MyDatabase' --<<<<< change this
order by 	backup_start_date desc
--exec master.sys.xp_fixeddrives

Job History

Select 
	j.[name], 
	jh.step_name, 
	run_status, 
	run_date, 
	run_time, 
	run_duration, 
	[server]
From [msdb].[dbo].[sysjobhistory] jh
	join [msdb].[dbo].sysjobs j 
	on jh.job_id = j.job_id
Where 1=1 
	and run_status not in (1,4)
	and run_date > 20171110 -- Change date
	and [step_name] <> '(Job outcome)'
	-- and run_time > 60000
Order by 
	run_date desc, 
	run_time desc

SQL Server ErrorLog

Create Table #Errorlog 
	(Logdate datetime, 
	 ProcessInfo varchar(50), 
	 LogText varchar(5000))

--Dump all the things into the table
insert into #Errorlog
EXEC sys.xp_readerrorlog 
	0			-- Current ERRORLOG
	,1			-- SQL ERRORLOG (not Agent)

--Query just like you would anything else:
Select * 
from #Errorlog 
Where 1=1
	--and LogText like '(c) Microsoft Corporation%'
	and (LogText like '%Error%'	or LogText like '%Fail%'or LogText like '%deadlock%'
	)
	And Logdate > getdate() -2
	And LogText Not Like '%CheckDB%'
	And LogText not like '%35262%'
	And LogText not like '%35250%'

--Clean up your mess, you weren't raised in a barn!
Drop Table #Errorlog

Check Alerts

SELECT
	[name], 
	event_source, 
	last_occurrence_date, 
	last_occurrence_time, 
	occurrence_count
	--, * 
FROM 
	msdb..sysalerts
Where 1=1
	and last_occurrence_date >= 20181109
	and severity >= 16

SQL Server Services

Select 
	servicename, 
	status_desc, 
	service_account, 
	instant_file_initialization_enabled
From sys.dm_server_services

Drive Space

SELECT 
	RTRIM(name),
	groupid,
	filename,
	CAST(size/128.0 AS DECIMAL(10,2)),
	CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)),
	CAST([maxsize]/128.0 AS DECIMAL(10,2)),
	CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)),
	CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 
		AS DECIMAL(10,2))/CAST(size/128.0 
		AS DECIMAL(10,2)))*100 
		AS DECIMAL(10,2))
FROM sysfiles

Long Running Jobs

SELECT j.[name], jh.step_name, run_date, run_time
FROM [msdb].[dbo].[sysjobhistory] jh
  JOIN [msdb].[dbo].sysjobs j
  ON jh.job_id = j.job_id
WHERE 1=1 AND run_stats = 4
ORDER BY run_date desc, run_time desc

資料庫管理者的幫手

Ola Hallengren Maintenance Solution | GitHub
SQL-Server-First-Responder-Kit | GitHub
dbatools | GitHub
sp_WhoIsActive | GitHub

Ken Fisher’s Security Scripts

sp_SrvPermissions.sql
sp_DBPermissions.sql

參考資料

Accidental DBA | SQLSkills

說明