Policy-Based Management(VLF Kontrolü-Database Facet)
Policy-Based Management SQL Server 2008 ile gelen bir özelliktir. Sistemlerimizde istediğimiz kuralları koymamıza, standartlarımızı belirlememize ve kontrollerimizi otomatik olarak yapmamıza olanak sağlar. Örneğin stored procedure’lerimizin isimlerinin rakam ile başlamaması ya da veritabanı recovery modellerinin simple olmaması için kontrol amaçlı olarak policy oluşturabiliriz.
Bu makaleyi okumadan önce VLF sayısı nedir ve ne olmamalıdır sorusunun cevabını anlamak için “Vlf(Virtual Log File) count nedir” isimli makaleyi okumanızı tavsiye ederim.
Policy Based Management(PBM)’ı kavramak için bazı kavramları anlamanız gerekir.
Facet: PBM tarafından yönetilebilen bir özelliktir. Örneğin Database isminde bir facet vardır. Ve bu facet’ı kullanarak veritabanındaki log dosyasında vlf sayısını kontrol eden bir policy oluşturabilirsiniz.
Tüm facet’ların listesine aşağıdaki gibi SSMS üzerinden erişebilirsiniz. Facet detayı için üzerine çift tıklamalısınız.
Condition: ilgili facet’ların alt özelliklerinin belirlenen koşulu sağlayıp sağlamadığını kontrol eder.
Veritabanındaki vlf sayılarını kontrol etmek için bir policy oluşturalım.
Aşağıdaki gibi Management > Policy Management > Policies sekmelerinden New Policy diyoruz.
Karşımıza çıkan ekranda Policy’e bir isim veriyoruz ve Check Condition kısmından bir condition oluşturmamız gerekiyor.
New Condition’a tıklıyoruz.
Karşımıza çıkan ekranda Name kısmından condition’a bir isim veriyoruz.
Facet kısmından veritabanındaki log dosyasının vlf sayısını kontrol edeceğimiz için Database facet’ını seçiyoruz.
Expression kısmında … ‘ya tıklayarak aşağıdaki script’i Cell Value kısmına yapıştırıyoruz.
ExecuteSql('Numeric', '--variables to hold each ''iteration''
declare @query varchar(100)
declare @dbname sysname
declare @vlfs int
--table variable used to ''loop'' over databases
declare @databases table (dbname sysname)
insert into @databases
--only choose online databases
select name from sys.databases where state = 0
--table variable to hold results
declare @vlfcounts table
(dbname sysname,
vlfcount int)
--table variable to capture DBCC loginfo output
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version
declare @MajorVersion tinyint
set @MajorVersion = LEFT(CAST(SERVERPROPERTY(''ProductVersion'') AS nvarchar(max)),
CHARINDEX(''.'',CAST(SERVERPROPERTY(''ProductVersion'') AS nvarchar(max)))-1)
if @MajorVersion < 11 -- pre-SQL2012
begin
declare @dbccloginfo table
(
fileid smallint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = ''dbcc loginfo ('' + '''''''' + @dbname + '''''') ''
insert into @dbccloginfo
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
else
begin
declare @dbccloginfo2012 table
(
RecoveryUnitId int,
fileid smallint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = ''dbcc loginfo ('' + '''''''' + @dbname + '''''') ''
insert into @dbccloginfo2012
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
--output the full list
select vlfcount
from @vlfcounts
order by dbname
')
Script’in kaynağına https://gallery.technet.microsoft.com/scriptcenter/SQL-Script-to-list-VLF-e6315249 adresinde ulaşabilirsiniz.
Ve aşağıdaki gibi vlf sayısının 1000’den küçük olmasını istediğimiz için <1000 olarak set ediyoruz. Buradaki 1000 sayısı her veritabanı için geçerli değildir. Bu yüzden makalenin başında belirttiğim makaleyi okumalısınız.
Evaluation Mode kısmından On Schedule’ı seçerek policy hangi aralıklarla kontrol’ü yapacağını belirliyoruz ve daha sonra Enable kutucuğuna tıklıyoruz.
On Demand’ı seçersek, sadece policy’i çalıştırdığımızda kontrolleri yapar.
Server restriction kısmında server bazında bir koşulunuz varsa server bazında bir condition oluşturarak koşulunuzun kontrolünü yapabilirsiniz.
Belirli aralıklarla otomatik kontrol etmesini istiyorsak Schedule kısmından new diyoruz ve policy’nin kontrol’ü yapacağı sıklığı belirliyoruz. Biz aşağıda her gün 00:00:000’da bir kere çalışacak şekilde set ettik.
Policy’mizi bu şekilde oluşturduk. Manual olarak çalıştırmak için aşağıdaki gibi Evaluate diyoruz.
Facet özelliği yerine script kullandığımız için aşağıdaki gibi bir uyarı aldık. Policy’nin script içerdiğini ve sadece güvenilir bir kaynaktan çalıştırmamız gerektiğini söylüyor.
The policy: ‘VLF Sayısı’ contains scripts. You should only run policies from a trustworthy source.
Sağ alt taraftaki Evaluate tuşuna basarak policy’i uyarıya rağmen çalıştırıyoruz.
Ben kendi lokal’imde bu işlemi gerçekleştirdiğimde aşağıdaki gibi bir sonuç aldım. Gördüğünüz gibi veritabanlarının hiçbirinde vlf sayısı 1000’den fazla değilmiş.
View’e tıklayarak mevcut vlf sayılarını öğrenebilirsiniz.
Eğer herhangi bir veritabanı için fail olsaydı Instance üzerinde refresh yaptığınızda Instance’ın yanında aşağıdaki gibi mektuba benzeyen bir kutucuğun üstünde kırmızı renki x işareti çıkardı. Bunun sebebi instance üzerinde tanımlı olan ve hatalı biten bir policy’nin var olduğunun sql server’ın bize bildirmek istemesi. Bu işareti gördüğünüzde tanımlı policy’lere bakıp gerekliliklerini yerine getirmeniz gerekir.
Policy Based Management ile SQL Server üzerinde yapabileceğiniz bir çok kontrol vardır. Ve profesyonel bir veritabanı yöneticisi bence SQL Server’ın bize sunduğu bu özelliği bütün detaylarıyla kullanmalı. Policy Based Management ile yapabileceğiniz diğer kontrolleri sitemizdeki menüden MSSQL’in altındaki POLICY-BASED MANAGEMENT alt menüsünden erişebileceğiniz makalelerde bulabilirsiniz.
