[Udemy] 課堂筆記 – SQL Server Administratipn Part1

Hits: 1037

工作上遇到了很多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 DBread 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會記錄所有資料庫發生的事情，因此很容易膨脹

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 your CREATE 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)
    • 驗證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的備份，在不同的位置
    • file

1-30. Differential backup

  • 跟t-log backup的差別?
    • diff backup只紀錄與上次full backup的差異
    • t-log backup會把所有異動通通保留下來
  • 藉由diff backup，可以快速地還原到最新狀態
    • file
    • 根據上圖，還原步驟為：
      • 先還原第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

  • 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

  • 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
    • file
  • SQL Server Agent點右鍵>多伺服器管理
    • 設定主伺服器與目標伺服器
    • file
  • 完成之後，在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(資料表按右鍵>權限>搜尋使用者>下方的權限找到選取>點選資料行權限)
    • file

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安全性的概念

  1. 備份在遠端，不要備份在sql server的本機端

  2. 安裝windows server與sql server的更新

  3. 先在local測試，再部署到production

  4. 移除內建的BUILTIN\Administrators group

    SQL server 2012版以後已移除此功能，避免安全性問題。參考

  5. 建議使用windows驗證，而非sql驗證

  6. 將每個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

  7. Disable all features via the SQL server configuration manager that are not in use

  8. 把sa帳號取消啟用，並起重新命名

  9. Remove the BUILTIN\Administrators Windows Group from a SQL

  10. 善用server roles, permission來給予使用者最低需求的權限

  11. Hide all DBs from logins

    USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO
Leave a Comment on [Udemy] 課堂筆記 – SQL Server Administratipn Part1SQL, 學習筆記
Tags ,

About the Author

swchen

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

You may also like these