Bài giảng Kiến trúc cài đặt cơ sở dữ liệu - Chương 9: Policy-based management (PBM)
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (307.36 KB, 22 trang )
GV Phi Loan - Khoa CNTT – HUI
1
Policy-Based Management
• Policy-Based Management is a system for
managing one or more instances of SQL
Server 2012.
•T ̀ ̀
̀ ̀
̀ ̀ ̀
̀ ̀
̀
̀
̀ ̀
̀ a SQL
“
̀
̀
GV Phi Loan - Khoa CNTT – HUI
2
T
ng 1
• Cơng ̀́̀
D
̀M
̀
̀
̀
• Administrator
̀N ̀
̀
policy ̀
p
GV Phi Loan - Khoa CNTT – HUI
̀
̀̀P
̀
̀
̀
̀
̀
̀̀
̀ ̀ ̀ ̀
̀
̀ ̀ i DB mail.
̀ ̀
̀
̀
̀
̀ ̀
̀
̀
̀
̀
̀C
̀
̀ ̀̀
̀
̀ ̀ ̀
̀
̀ ch
3
T nh
ng 2
• Quy
c cho DB AdventureWorks ̀ ̀ ̀
̀
̀̀
̀ ̀
̀ ̀ ̀ ̀
́W ̀M ̀
̀
̀ ̀ ̀ ̀
̀
̀
̀
y.
• Khi
̀
̀̀
̀ ̀ ̀
̀
̀ ̀ ̀ ̀
̀
̀
̀
̀
̀
̀C ̀
̀
̀
̀ ̀
̀ ̀
̀
̀
̀
̀
̀ ̀ ̀ ̀
̀ i.
GV Phi Loan - Khoa CNTT – HUI
4
B
a PBM
• Policy management
–́
̀
̀ c policy
• Explicit administration
–́
̀
̀ ̀ ̀
̀ ̀
̀
̀ ̀
theo theo policy khơng
̀
̀ ̀
̀ ̀tn
• Evaluation mode
GV Phi Loan - Khoa CNTT – HUI
5
Evaluation mode
• B n evaluation mode:
– On demand ̀
̀ ̀
̀
̀ ̀
̀
̀
̀ ng
– On change: prevent ̀ ̀
̀ ̀
̀DDL̀
̀ ̀
̀ ̀
m policy
– On change: log only ̀ ̀
̀ ̀
̀
̀
̀ ̀
̀ ̀
̀ ̀ ̀
̀ i
– On schedule ̀ ̀
̀ ̀
̀“QL̀“
̀
́
̀ ̀ ̀
̀ ̀
̀ ̀policy.
GV Phi Loan - Khoa CNTT – HUI
6
M
•
•
•
•
PBM managed target
PBM facet
PBM condition
PBM policy
GV Phi Loan - Khoa CNTT – HUI
7
PBM managed target
•L ̀ ̀
̀
PBM nh :
̀E
̀
̀
̀ ̀
i
– Instance of the SQL Server Database Engine
– Database, table, hay index.
•T ̀ ̀
̀
̀ ̀
target hierarchy.
GV Phi Loan - Khoa CNTT – HUI
̀
̀
nh
8
Policy-Based Management facet
• M ̀ ̀
̀
̀ c property.
• “ ̀
̀ ̀ ̀
nh 1 facet
–C ̀ ̀
̀ ̀ ̀
̀
̀ ̀
̀
̀
̀
̀
̀
̀
̀ ̀
̀
̀
̀ ̀
i 1 facet
̀
̀
̀
̀
̀
̀
• A target type can implement one or more
management facets, and a management
facet can be implemented by one or more
target types.
GV Phi Loan - Khoa CNTT – HUI
9
Policy-Based Management condition
•L ̀ ̀
̀
facet.
GV Phi Loan - Khoa CNTT – HUI
̀
̀
̀
̀
̀
̀
̀
̀
̀ ̀
̀
̀
̀
̀
i1
10
PBM policy
•P
̀
̀
̀
̀ ̀
̀
̀ ̀
̀
̀
̀
̀
̀ ̀schedule.
•M ̀
̀ ̀ ̀ ̀
a 1 condition.
•P
̀ ̀
enabled hay disabled.
•C ̀
̀
̀ ̀ ̀trong msdb
GV Phi Loan - Khoa CNTT – HUI
11
Effective policies
•P
̀ ̀
̀
̀
̀
̀
̀
̀
̀
̀ ̀
n sau:
̀
̀
–P
̀ ̀enabled.
–T
̀
̀ ̀ ̀
̀ ̀
̀
–T
̀ ̀ ̀
̀ ̀
̀ a target
tuân theo 1
̀
̀ ̀
̀
̀ y.
GV Phi Loan - Khoa CNTT – HUI
12
T
policy
•C
̀PBM̀
̀
̀ ̀
̀ ̀
̀ ̀ p
•Đ ̀
̀
̀ ̀
̀
̀ a
facet.
•Đ ̀
̀
̀
̀
̀ ̀̀
evaluation mode.
•K
̀ ̀
̀
̀ ̀“QL̀“
̀ ̀
̀
̀ i policy khơng?
GV Phi Loan - Khoa CNTT – HUI
13
V
1
• Scenario: We will create an on demand
policy to ensure that all the databases
have the Auto Shrink option set to True.
By default, a database that is created has
Auto Shrink set to False
GV Phi Loan - Khoa CNTT – HUI
14
V
1
• B ̀T ̀
i condition
• B ̀T ̀
i policy
– Đ t tên: Check auto shrink
–C
̀
̀ ̀Database options
–B ̀
c: @autoshrink = true
– Đ t tên: AutoShrinkPolicy
–C
̀
̀
̀ ̀
̀
– X ̀ nh target: every database
–E
̀M
̀ ̀on demand
GV Phi Loan - Khoa CNTT – HUI
̀ o
15
V
1
• B ̀P
–N
̀M
̀
̀
̀policy.
̀
̀ ̀P
̀́ “
P
̀ nh Evaluate
–K ̀
̀
̀ ̀ ̀
̀ ̀ ̀DB̀
̀
̀
̀
̀ ̀
̀ y
– Đ ̀C“DL̀
̀
̀
̀
̀
̀
̀C“DL̀ ̀ ̀
̀ t apply
GV Phi Loan - Khoa CNTT – HUI
̀̀
̀ ̀
̀
16
V
2
• Scenario: The scenario is ensuring that
each user defined Stored Procedure
created on the server is prefixed with
̀L ̀
̀ ̀
̀ ̀
GV Phi Loan - Khoa CNTT – HUI
17
V
2
• B ̀T ̀
i condition
• B ̀T ̀
i policy
– Đ t tên:CheckProcName
–C
̀
̀ ̀Stored Procedure
–B ̀
̀ N
̀LIKÈ
– Đ t tên: ProcPolicy
–C
̀
̀
̀ ̀
̀
̀ o
–X ̀
̀
̀ n every stored procedure in every
database
–E
̀M
̀ ̀OnChange: Prevent
–Đ ̀
̀
̀ ̀Enable
GV Phi Loan - Khoa CNTT – HUI
18
V
2
• B ̀P
–T ̀
C“DL̀
̀M
̀ ̀
̀
̀
̀
̀
̀policy.
̀
̀
̀
̀ ̀ ̀
CREATE PROCEDURE TestProcPolicy
AS BEGIN
PRINT 'HELLO WORLD !'
END
̀K ̀
̀ ̀
̀ i
GV Phi Loan - Khoa CNTT – HUI
19
L
• Đ ̀
̀
̀
̀ ̀
nhau nh :
– Table
– Name
–M P
• E
̀
̀
̀
̀
̀
̀
̀
̀ ̀
c
̀N
̀
̀̀ ̀
̀
̀on schedule)
GV Phi Loan - Khoa CNTT – HUI
̀
̀
̀
̀
̀
̀
̀ ̀ ̀
̀ ̀ ̀
̀ ̀
̀
̀
20
Tutorial 1: Administering Servers by
Using Policy-Based Management
• />
• Lesson 1: Create and Apply an Off By
Default Policy
• Lesson 2: Create and Apply a Naming
Standards Policy
GV Phi Loan - Khoa CNTT – HUI
21
Tutorial: Evaluating Best Practices by
Using Policy-Based Management
• />
• Lesson 1: Evaluate Best Practices on an
On-Demand Basis
• Lesson 2: Evaluate Best Practices Policies
on a Scheduled Basis
• C:\Users\Phi Loan\Documents\SQL
Server Management Studio\Policies
GV Phi Loan - Khoa CNTT – HUI
22
