Hits: 973
工作上遇到了很多SQL Server的問題,google半天也還是一知半解,因此決定系統性的上個課,把資料庫的底層知識給補起來
1-1. 講解什麼是domain
1-2. 講解什麼是VM
講解的原因是課堂會要操作SQL Server 2012,需要一個虛擬環境來安裝 > 本課程使用VM, Windows Server 2012, SQL Server 2012 Enterprise Edition
1-17. Introduction to System Databases
每個instance都會有基本的4個DB:
Master
,Model
,Msdb
,Tempdb
Master
- file locations of user databases
- login accounts
- server configuration settings
- linked servers information
- startup stored proceduses
Model
- template databases that is copied into a new databases(新建DB時的預設設定,比如mdf/ldf大小、是否自動成長…etc)
- options set in model will be applied to new databases
- used to create tempdb every time the server starts
Msdb
- support SQL Server Agent
- SSMS
- DB mail
- service brooker
- history and metadata information
- backup and restore history for DB(
msdb.dbo.backupset
) - history for SQL agent jobs
--列出各資料庫的備份紀錄
use msdb
select type, database_name, name from msdb.dbo.backupset database_name order by name
/*
type:
D = 完整備份
I = 差異備份
L = 交易紀錄備份
*/
Tempdb
- shared resource by SQL server all users
- temporary objects, worktables, online index operations cursors, table variables, and snapshot isolation version store
- it is recreated every time that the server is restarted
- can not backup and restore > other DB
Reporting Services DB
- ReportServer only available if you have installed
- ReportServerTempDB available if you have installed
- you can choose whether to install when you install the SQL server instance
Replication System DB
- available when you configure replication
Resources DB
read only, hidden, contains system informations can not backup must copy paste the file
1-18. SQL server data and log files
- 什麼是data / log files
- log如何增長?如何限制增長?
- 備份與還原的模式
- Virtual Log File Growth
What is database file?
- 就是存放實體資料表、預存程序的地方
- bb
What us log file?
- record all changes to the databse
- record changes sequentially
- all data is written to the transaction log file first before commited to tha data files
- key object needed to restore db
- used for log shipping, database mirroring, replication
- transaction log file size issue
- backup not occuring while simple recovery mode
- big transaction cause log full
1-19. Detail transaction log ATM example
- 首先說明為何ldf容易暴漲?以ATM交易為例,提領100元為範例
- mdf
- 現有500元,提領100元,餘額400元
- 只記錄目前的餘額是400元,中間發生的過程都由ldf紀錄
- ldf
- 插卡
- 讀取卡號
- 讀取密碼
- 現有500元
- 提領100元
- 餘額400元
- 由於ldf會記錄所有資料庫發生的事情,因此很容易膨脹
- mdf
1-20. Auto growth and sizing of transaction log
-
要把ldf變小,只能使用交易紀錄備份
-
Virtulal Log File
-
Auto growth 與 fragementation
- 越頻繁的auto growth,會造成越多的virtulal log file, 越容易造成fragementation
- auto growth建議使用實體的增長(如5mb, 10mb…),不要用百分比,容易造成太頻繁的auto growth
- 建議使用pre-size的ldf
-
實驗
- auto: mdf file = 3MB auto growth = 1MB, ldf file = 1MB auto growth = 1%
- auto2: mdf file = 1000MB auto growth = 1000MB, ldf file = 100MB auto growth = 100MB
- 分別匯入12萬比資料到auto與auto
- auto非常的破碎,一直持續的增加mdf與ldf的大小
- auto2完全不破碎,因為已經有預先設好(
pre-sized
)的ldf大小了
- 如何觀察fragementation?
- 方法1: 對著DB按右鍵>報表>標準報表>Disk Usage
- 方法2: 執行語法
USE [master]
GO
BEGIN TRY
IF (SELECT CONVERT(INT,value_in_use) FROM sys.configurations WHERE NAME = 'default trace enabled') = 1
BEGIN
DECLARE @curr_tracefilename VARCHAR(500);
DECLARE @base_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1;
SET @curr_tracefilename = REVERSE(@curr_tracefilename);
SELECT @indx = PATINDEX('%\%', @curr_tracefilename) ;
SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc';
SELECT
--(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1,
ServerName AS [SQL_Instance],
--CONVERT(INT, EventClass) AS EventClass,
DatabaseName AS [Database_Name],
Filename AS [Logical_File_Name],
(Duration/1000) AS [Duration_MS],
CONVERT(VARCHAR(50),StartTime, 100) AS [Start_Time],
--EndTime,
CAST((IntegerData*8.0/1024) AS DECIMAL(19,2)) AS [Change_In_Size_MB]
FROM ::fn_trace_gettable(@base_tracefilename, default)
WHERE
EventClass >= 92
AND EventClass <= 95
--AND ServerName = @@SERVERNAME
--AND DatabaseName = 'myDBName'
AND DatabaseName IN ('auto','auto2') --Modify DB name here
ORDER BY DatabaseName, StartTime DESC;
END
ELSE
SELECT -1 AS l1,
0 AS EventClass,
0 DatabaseName,
0 AS Filename,
0 AS Duration,
0 AS StartTime,
0 AS EndTime,
0 AS ChangeInSize
END TRY
BEGIN CATCH
SELECT -100 AS l1,
ERROR_NUMBER() AS EventClass,
ERROR_SEVERITY() DatabaseName,
ERROR_STATE() AS Filename,
ERROR_MESSAGE() AS Duration,
1 AS StartTime,
1 AS EndTime,
1 AS ChangeInSize
END CATCH
1-21. Types of recovery models
每個DB都有下列三種復原模式
- Simple(簡易)
- 不支援log備份
- transaction log會自動進行truncation(不需要透過log備份來釋放空間)
- production database不建議使用simple recovery mode
- Full(完整)
- 支援log備份
- 不會對transaction進行自動truncation,需要透過備份來清除空間
- 概念是:把.ldf裡面的log file,移轉到備份檔(bak)內,來清除ldf的空間
- 公司產品建議使用Full recovery model
- Bulk-logged(大量紀錄)
- 支援log備份
- 不會對transaction進行自動truncation,需要透過備份來清除空間
1-22. Function fn_dblog to view inside a transaction log
- 透過
fn_dblog
來觀察DB,在插入1000筆資料的前後,其DB的log狀態select count (*) from fn_dblog (null, null)
- 在full recover model下,透過
backup log
,將committed transaction備份到.trn檔案中,並且成功的縮小log file
[重點]
1. 定期使用完整備份與差異備份,將資料備份好
2. 定期使用交易紀錄備份,把完成的交易紀錄備份至.trn檔中
3. 如此即有完整的資料備份,也可控制ldf檔案大小
1-23. What is virtulal log file (VLF)
- 每個實體的記錄檔都是由數個虛擬記錄檔(Virtulal Log File; VLF)組成
- SQL2012以前版本可以透過以下規則來估算VLF數量
- 1.每一檔案少於 64 MB 內部產生4 VLFs
- 2.每一檔案大於 64 MB 並小於等於 1 GB 內部產生 8 VLFs
- 3.每一檔案大於 1 GB 內部產生 16 VLFs
- 檢查每個DB的log file中有多少VLF
DBCC LOGINFO
- 查詢出來的每一列都是1個VLF
- 對於DBA來說,可以藉由評估資料可能的大小量,來設定DB的起始大小,避免過多的VLF產生,造成log破碎化降低效率
1-24. Create a SQL database
本章在講解如何透過GUI與指令來建立DB,除此之外,重要的是如何決定DB資料檔起始大小?log檔起始大小?auto growth容量大小?
- 基本上看經驗
- 講者提出的建議(假設一年的資料量是100G的話)
- 資料檔起始大小設定為資料量的2-3倍(200-300G)
1-25. The importance of Tempdb
-
What is Tempdb?
- Automatically created system database.
-
purpose
- Creation global/local temporary tables.
- Temporary stored procedures
- Table variables
- Use the tempdb for sorting index when using the
SORT_IN_TEMPDB
in yourCREATE INDEX
statement to off load the main database files, thus improving performance. - MARS(Multiple Active Result Sets)
- Snapshot isolation and read-commited snapshot isolation
- Very busy database that needs attention
-
Can not
- Drop
- Backup and restore
- Change recovery model
- Create multiple file groups
- Detach
-
Can
- Place data and lof file separately
- Place the files on the fatest IO subsystem possible
- Pre-size auto grow on data and log file
- Create multiple data files for tempdb according to the formula: one data file per physical or virtual CPU core
- keep the sizes of files equal
1-26. Attach and detach a database
附加(attach)與卸載(detach)通常是為了清出空間、調整server表現所用的功能
-
舊版的sql server可能要先把DB調成single user才可detach
- 位置:右鍵點選資料庫>>選項>>狀態>>限制存取>>從MULTI_USER改為SINGLE_USER
-
user可能會被斷開,可使用指令把user重新建立連接
EXEC sp_change_users_login 'Update_One', 'Bob', 'Bob'
1-27. Introduction to backups
- 重要的備份觀念
- 要從還原策略下手,來思考備份計畫
- 公司可以忍受多大的資料損失?(年/月/日/小時/分/秒…etc)
- 當資料掛掉時,可以忍受多久的還原時間?
- 備份的種類
- 完整備份(full backup):最完整的備份,包含所有資料表、sp、結構等,是snapshot的概念
- 差異備份(differential backup): 以完整備份為基礎,跟完整備份有差異的備份
- 交易紀錄備份(transaction log backup): 紀錄每個交易異動的備份
- 只複製備份(copy-only backup)
- file backup
- partial backup
- 備份要放哪?
- 理想上,要跟production server分開放
- 要放多久的備份檔?
- 理想上,1個月的onsite, 3個月的offsite,但還是取決於公司的決定
1-28. Full DB backup
- 重要觀念:不要在server忙碌時進行備份作業
- 細部設定
- 若已有同樣的備份時,要作什麼事?
- append: 直接在現有的備份尾端附加上新的備份(此為預設指令,
TSQL = with no init
) - overwrite: 把原檔直接覆蓋(
TSQL = with init
)
- append: 直接在現有的備份尾端附加上新的備份(此為預設指令,
- 驗證backup可行性(valid)
- 進行總和檢查碼(checksum)
- 壓縮備份
- 若已有同樣的備份時,要作什麼事?
1-29. Transactional log backup
- 要進行交易紀錄備份,需要先將recovery model調為full
- 必須要先進行full backup才可進行transaction log backup
- 檢查backup file
RESTORE HEADERONLY FROM DISK = 'path\.bak'
- Backup Type = 1, 2, 5 -> Full, T-Log, Diff
- 1個bak檔,可同時存放full, t-log的備份,在不同的位置
1-30. Differential backup
- 跟t-log backup的差別?
- diff backup只紀錄與上次full backup的差異
- t-log backup會把所有異動通通保留下來
- 藉由diff backup,可以快速地還原到最新狀態
- 根據上圖,還原步驟為:
- 先還原第1列的full backup
- 在還原第7列的diff backup
- 最後還原第8列的t-log backup
- 這樣,資料庫就還原到最新狀態了
- 呈上,diff backup通常用在很大的DB,當作快速還原DB狀態使用,若是小DB,使用t-log已很足夠
1-31. Introduction to restore
概念講解而已,前面都講過了
1-32. Restore db with GUI
- 1個bak有1次full backup(t0)與兩次t-log backup(t1, t2)的話,我可以選擇只還原到t0, t1, t2,但我不能選擇還原t0與t2,在還原時一定得按照順序
1-33. Restore db using differential backup
- 用差異備份還原時,不用照順序,只要用第一次的完整備份+最後一次的差異備份,即可
- 因為差異備份是逐漸累積的,所以可以只用最後一次的差異備份,即可還原到最新狀態。
- 比較大的DB可用差異備份,小的DB可用log備份(效率問題)
1-34. Recovery mode with tail log backup
-
講解recovery state
- with recovery
- 當完成完整備份後,沒有其他要還原的備份(差異or交易紀錄),即可選擇此選項,告訴SQL server你已經完成所有的還原
- with norecovery
- 當完成完整備份後,還要還原其他備份(差異or交易紀錄)時,要選擇此選項,告訴SQL srever你還有其他要還原的備份檔
- with standby
- with recovery
-
tail log backup
- 在交易紀錄備份執行完成後,若還有其他的DB異動,那得先執行tail log backup,指令上要注意下列兩點
with no truncate (交易紀錄不要截斷)
: 不要把交易紀錄備份完成後的insert刪除copy only
: 不把t-logs的序列截斷,保持t-logs的序列性- 要注意DB不能在使用中(???)
- 此時的DB狀態是Restoring,要使用指令,逐步還原(full, diff, t-log, tail t-log)
- 注意在full, diff, t-log, tail的還原時都要使用
with norecovery
,在tail t-log的還原時要使用with recovery
- 在交易紀錄備份執行完成後,若還有其他的DB異動,那得先執行tail log backup,指令上要注意下列兩點
-
tail log backup的使用時機(參考官方文件)
- 要還原線上的資料庫,要先執行結尾備份搭配
with norecovery
選項
- 要還原線上的資料庫,要先執行結尾備份搭配
1-35-37. Backup using maintenance plan, tasks
沒什麼特別的,就靠UI操作就好,介紹一下工具箱
- 壓縮資料庫供作(shrink database): 壓縮資料庫,
Don't Do IT
- 重建索引工作(rebuild index)
- 更新統計資料工作(update statistics)
- 通知操作員工作(notify operator task): DB mail
- 紀錄清除工作(history cleanup): 清除msdb的歷史資料
1-38. Don’t shrink a database
為何不要?
- 會把頁面(pages)重新排列
- index會不被保留,會被重建,導致破碎化,要解決,就是要重重建index
alter index col_name on table_name rebuild
- 千萬不要放在sql server agent jobs內,會導致嚴重的效率問題
- 反而會導致DB大小增加
實驗
壓縮前
- DB總大小 -> 26MB,包含下列兩張表
- table 1 -> 1500 rows, no index, 14MB
- table 2 -> 1500 rows, with index, 12MB
- 碎片化(table2) -> 0.4
- 檢查指令:
select avg_fragementation_in_percent from sys.dm_db_index_physical_stats (db_id (DB_name), object_id (table_name), 1, null, 'limited')
- 檢查指令:
壓縮後
- DB總大小(未重建索引前) -> 15MB
- 碎片化(table2) -> 99.6
- DB總大小(重建索引後) -> 41MB
- 因為rebuild index是個log event
1-39. The importance of using SQL server agent
- 就是執行排程作業的地方(backup, clean, rebuild index, execute query, alter, email…etc)
- job, schedule, operator, alert
- multi server administration(master/slave)
1-40. Create a simple backup job using SQL server agent
沒什麼重點
1-41. The importace of DBCC CheckDB
使用DBCC checkDB
確認資料庫的備份檔,以及使用該檔案還原後的資料庫,是否真的完整無誤
最好要把每個production DB都進行一次DBCC checkDB
,確保正確無誤(若有錯誤,請修part 2的課,會教你如何修復)
1-42. Using SQL server agent with multiple steps
1-43 & 44. Setting up SQL database mail
- 設定operator
- 設定mail server
1-45. Setting up alerts severity errors from 17 to 25
詳細請見另一篇文章
1-47. Central management with SQL server agent
- 使用multi server administration達到集中(centralized)同步管理多server的功能
- create server group
- 可能會需要變更機碼
實作步驟
- View>Registered Servers
- SQL Server Agent點右鍵>多伺服器管理
- 設定主伺服器與目標伺服器
- 完成之後,在SQL Server Agent裡面新增multi-server jobs後,只要在主伺服器有變更,其他伺服器亦會同步變更(例如新增DB)
1-48. Installing free VMware pro player for visualization
(略)
1-49. Introduction to SQL server security
- 講解Domain、SQL server、連線、帳號、登入、權限的概念
- 權限控管基本的可以作到決定user可否登入、可否看到DB、可否看到table、甚至是看到table內的欄位
- 登入flow: Windows user -> SQL login name -> SQL user name -> DB|table|column(這裡是非常彈性的控管區)
- 可同步參考我的另外一篇文章
1-50. Demonstration of users logins role
- 示範1-48的登入flow
- 選擇column是否可被select(資料表按右鍵>權限>搜尋使用者>下方的權限找到選取>點選資料行權限)
1-51. Managing security with T-SQL
use [AdventureWorks2012]
--1. 先建立Tom這個可連線到SQL Server的使用者(windows驗證)
create login [DBA\Tom]
from windows
with default database = [master]
go
--2. 再建立Tom的使用者,可連線到SQL DB
create user [Tom]
from login [DBA\Tom]
with default_schema = [dbo]
go
--3. 給予Tom權限,可查詢特定資料表
grant select on [HumanResources].[Department]
to [Tom] as [dbo]
要注意的是,SQL login與SQL user是可各別存在的,刪除時要記得通通刪除才可
use [master]
drop login [DBA\Tom]
drop user [Tom]
1-52. Selecting authentication or mixed mode security.
驗證方式有兩種:windows與mixed(windows + SQL server)
Windows驗證
- disables SQL server authentication by default(
sa
account will disable). - 在windows server主機有個驗證token,創建user後,即可與sql server驗證
mixed驗證
- 強迫你設定sa帳戶的密碼(必須為強式密碼)
1-53. Accessing SQL server using SQL login
示範如何使用SQL驗證來登入SQL server
- 可以不用在windows server下設定對應的帳號
- 要確保在server的安全性(security)設定下是mixed驗證模式
- SQL驗證可在user mapping設定下,設定這個login user能操作的DB與給予的權限(aka
roles
, 參考我的另一篇文章)
1-54. Understanding SQL server roles
- Roles allow the DBA to manage permissions more efficiently
- 本節是新增一個db_roles,來控制兩個user,只能查詢兩張table
- 可參考我的另一篇文章
1-55. Managing SQL server roles via TSQL
- !!! Plan the role first !!!
1-56. SQL Server roles demonstration
- 可參考我的另一篇文章
1-57. Managing SQL server permissions
權限操作的三種語法:GRANT
, DENY
, REVOKE
- GRANT
- 給予權限
- DENY
- 拒絕權限
- 會覆蓋GRANT的權限,也會拒絕繼承而來的權限
- REVOKE
- 移除權限(還原到最初)
列出特定DB下所有user所具有的權限
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
JOIN
--All objects
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
--Only roles
roleprinc.[type] = 'R' AND
--Only public role
roleprinc.[name] = 'public' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]
1-58. Best SQL server practice security
分享一些DB安全性的概念
-
備份在遠端,不要備份在sql server的本機端
-
安裝windows server與sql server的更新
-
先在local測試,再部署到production
-
移除內建的BUILTIN\Administrators group
SQL server 2012版以後已移除此功能,避免安全性問題。參考
-
建議使用windows驗證,而非sql驗證
-
將每個user的權限文件化,紀錄曾經給予的權限變化
詳細指令可參考1-57或是下列這裡
SELECT name AS Login_Name,TYPE, type_desc AS Account_Type FROM sys.server_principals WHERE TYPE IN ('U', 'S', 'G') ORDER BY name, type_desc
-
Disable all features via the SQL server configuration manager that are not in use
-
把sa帳號取消啟用,並起重新命名
-
Remove the BUILTIN\Administrators Windows Group from a SQL
-
善用server roles, permission來給予使用者最低需求的權限
-
Hide all DBs from logins
USE MASTER GO DENY VIEW ANY DATABASE TO PUBLIC GO
Comments