SQL Server Tüm Veritabanlarında Otomatik Audit Oluşturmak

SQL Server’da bir Instance üzerinde tüm veritabanlarında, tüm kullanıcıların yapmış olduğu belli işlemleri kaydetmek isteyebilirsiniz. Bunun için Audit özelliğini kullanmanız gerekir. Audit hakkında detaylı bilgi almak için SQL Server Audit Oluşturmak isimli makaleyi okuyabilirsiniz. Bir instance üzerinde çok sayıda veritabanı olduğunda bunları manuel biçimde oluşturmak oldukça zaman alacaktır. Ayrıca bazı şeyleri gözden kaçırma ihtimali de oluşturacaktır. Bu nedenle TSQL ile tüm veritabanlarında bunları oluşturmak daha efektif olacaktır. Bununla ilgili olarak yazdığım Script aşağıdadır. Ben örnek olarak Audit adlarını veritabanı adlarıyla birebir aynı aldım. Filepath olarak Data dosyalarının default path’ini belirledim. Maximum dosya boyutu olarak 10 MB ve Maximum dosya sayısı olarak 1 belirledim. Siz kendi ihtiyaçlarınıza göre bu değerleri şekillendirebilirsiniz.

DECLARE @DatabaseName NVARCHAR(1000)
DECLARE @sqltext NVARCHAR(MAX)
DECLARE AuditCursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN (SELECT name FROM sys.server_audits ) AND database_id > 4
OPEN AuditCursor
FETCH NEXT FROM AuditCursor INTO @DatabaseName
WHILE @@FETCH_STATUS=0
BEGIN
SET @sqltext=
'CREATE SERVER AUDIT '+ @DatabaseName +'
TO FILE 
(	FILEPATH = N''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA''
	,MAXSIZE = 10 MB
	,MAX_FILES = 1
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)'

EXEC sp_executesql @sqltext

SET @sqltext=
'USE [' + @DatabaseName +'] 
CREATE DATABASE AUDIT SPECIFICATION ['+ @DatabaseName +'] FOR SERVER AUDIT [' + @DatabaseName + ']'

EXEC sp_executesql @sqltext

SET @sqltext=

'USE [' + @DatabaseName +'] 
DECLARE @AuditScript TABLE (ID INT IDENTITY,AuditScript NVARCHAR(MAX))
DECLARE @ID INT=1
DECLARE @sqltext NVARCHAR(MAX)
INSERT @AuditScript
 
SELECT ''ALTER DATABASE AUDIT SPECIFICATION ['' + DB_NAME() + ''] ADD (UPDATE ON DATABASE::['' + DB_NAME() + ''] BY [''+ name + ''])'' 
FROM sys.database_principals 
WHERE type=''S'' AND name NOT IN (''guest'',''INFORMATION_SCHEMA'',''sys'')

UNION ALL

SELECT ''ALTER DATABASE AUDIT SPECIFICATION ['' + DB_NAME() + ''] ADD (DELETE ON DATABASE::['' + DB_NAME() + ''] BY [''+ name +''])'' 
FROM sys.database_principals 
WHERE type=''S'' AND name NOT IN (''guest'',''INFORMATION_SCHEMA'',''sys'')

WHILE @ID<=(SELECT COUNT(*) FROM @AuditScript)
BEGIN
SET @sqltext=(SELECT AuditScript FROM @AuditScript WHERE ID=@ID)
EXEC sp_executesql @sqltext
SET @ID=@ID +1
END

SET @sqltext=N''ALTER DATABASE AUDIT SPECIFICATION ''  + DB_NAME() + '' WITH (STATE=ON) ''
EXEC sp_executesql @sqltext
'

EXEC sp_executesql @sqltext

SET @sqltext='ALTER SERVER AUDIT [' + @DatabaseName + ']  WITH (STATE=ON)'
EXEC sp_executesql @sqltext

FETCH NEXT FROM AuditCursor INTO @DatabaseName
END
CLOSE AuditCursor
DEALLOCATE AuditCursor

UNION ve UNION ALL

SQL Server’da birden fazla sonuç kümesini birleştirmemiz gerektiğinde UNION veya UNION ALL işlemleri kullanılır. Mesela internet satışları ile mağaza satışları farklı tablolarda olan bir veritabanı düşünelim. Toplam satışları listelemek için bu iki tablonun birleşimi gerekir. UNION ve UNION ALL operatörlerini kullanırken dikkat etmemiz gereken hususlar şu şekildedir.

1. Bütün sonuç kümelerindeki sütun sayısı aynı olmalıdır.
2. Sıra olarak birbirine karşılık gelen sütunların veri tipleri birbirleriyle uyumlu olmalıdır.

UNION ile UNION ALL arasındaki tek fark UNION ALL ifadesi iki sonuç kümelerini direk alt alta eklerken, UNION ifadesi aynı olan kayıtlardan sadece bir tanesini getirir. Eğer birleşim için kullanacağımız sonuç kümelerinde ortak kayıtlar yoksa UNION ile UNION ALL aynı sonucu getirir. Aralarındaki performans farkını makalenin sonunda açıklayacağız.

UNION ve UNION ALL ile ilgili örneklere geçelim.

Öncelikle 2 adet örnek tablo oluşturalım.

CREATE TABLE Deneme 
(ID INT IDENTITY,
 Ad NVARCHAR(20),
 Soyad NVARCHAR(50),
 Tarih DATE)

INSERT Deneme
VALUES ('Hakan','Gürbaşlar','2017-01-01'),('Nurullah','Çakır','2017-01-01'),('Faruk','Erdem','2017-01-01')

CREATE TABLE Deneme2 
(ID INT IDENTITY,
 Ad NVARCHAR(20),
 Soyad NVARCHAR(50),
 Tarih DATE)

INSERT Deneme2
VALUES ('Hakan','Gürbaşlar','2015-01-01'),('Nurullah','Çakır','2016-01-01'),('Faruk','Erdem','2017-01-01')

İlk olarak UNION ile UNION ALL arasındaki sonuç farkını görelim.

SELECT Ad,Soyad
FROM Deneme

UNION ALL

SELECT Ad,Soyad
FROM Deneme2

SELECT Ad,Soyad
FROM Deneme

UNION

SELECT Ad,Soyad
FROM Deneme2

Bu örnekte görüldüğü gibi UNION kullanımıyla tekrarlayan kayıtlar birer kez getirildi.

Farklı sayıda kolona sahip iki sonuç kümesini birbirine bağlamayı denediğimizde ise aşağıdaki gibi bir hata alırız.

SELECT Ad,Soyad,ID
FROM Deneme

UNION ALL

SELECT Ad,Soyad
FROM Deneme2

Sorguyu biraz değiştirelim. İlk sorguda Ad,Soyad ; ikinci sorguda Soyad,Ad sırasıyla getirelim. Veri tipleri aynı olduğu için UNION işleminde hata ile karşılaşmayacağız.

SELECT Ad,Soyad
FROM Deneme

UNION ALL

SELECT Soyad,Ad
FROM Deneme2

İlk sorguda Ad,Soyad,ID ; ikinci sorguda Ad,Soyad,Tarih alanlarını kullanarak UNION işlemini yaptığımızda ise veri tipi uyuşmazlığından kaynaklı bir hata alırız.

SELECT Ad,Soyad,ID
FROM Deneme

UNION ALL

SELECT Ad,Soyad,Tarih
FROM Deneme2

Tarih alanlarının sadece yıl kısımlarını alarak UNION işlemini yapalım.

SELECT Ad,Soyad,YEAR(Tarih)
FROM Deneme

UNION ALL

SELECT Ad,Soyad,YEAR(Tarih)
FROM Deneme2

İki sorguda ortak kayıt yoksa UNION ile UNION ALL aynı sonucu getirir demiştik. Peki bu iki sorgunun performans değerleri nasıl?

SELECT Ad,Soyad
FROM Deneme
WHERE Ad='Hakan'

UNION ALL

SELECT Ad,Soyad
FROM Deneme2
WHERE Ad='Nurullah'

---------------------------

SELECT Ad,Soyad
FROM Deneme
WHERE Ad='Hakan'

UNION 

SELECT Ad,Soyad
FROM Deneme2
WHERE Ad='Nurullah'

Yukarıdaki iki sorguda da ortak kayıt olmadığı için sonuç aynı olacaktır. Çalışma planlarını incelediğimizde UNION işleminde Distinct Sort işlemi yani tekrarlayan kayıtları temizleme işlemi yapıldığı için Sorgu maliyeti neredeyse 3 katına çıkıyor. Bu nedenle de ortak kayıtların olmadığına emin olduğumuz durumlarda mutlaka UNION ALL kullanmalıyız.

SET DATEFORMAT

05/07/2017 şeklinde yazılan bir tarih sizce 5 Temmuz tarihini mi yoksa 7 Mayıs tarihini mi belirtir? Ya da 01-02-03 tarihi hangi yılı, hangi ayı, hangi günü belirtir? Uygulamalarımızda tarih formatını SQL Server üzerinde değiştirmemiz gereken durumlar olabilir. Bu değişikliği yapmak için ilgili session’da SET DATEFORMAT ifadesini çalıştırmamız gerekir. Böylece yıl, ay, gün karışıklığı olmadan istediğimiz tarihi kendimize uygun olan formatta yazabiliriz.

SET DATEFORMAT yazdıktan sonra dmy, dym, mdy, myd, ydm veya ymd ifadelerinden birini kullanmamız gerekir. d harfi günü, m harfi ayı, y harfi yılı temsil eder.

SET DATEFORMAT ymd

DECLARE @date DATETIME='01-02-03'
PRINT @date

SET DATEFORMAT ydm

SET @date ='01-02-03'
PRINT @date

SET DATEFORMAT dmy

SET @date ='01-02-03'
PRINT @date

Yazımı aynı olan bir tarihi ilk formatta 3 Şubat 2001, ikinci formatta 2 Mart 2001, üçüncü formatta 1 Şubat 2003 olarak ekrana yazdırdık. 01-02-03 yazdığımız zaman, yıl olarak 2000’li yılları bize verdiğini görüyoruz. 50 ve sonraki yıl değerleri için 1900’lü yılların geldiğini göreceğiz.

Siz de bu şekilde tarihlerinizin yazımını ayarlayabilirsiniz.

Yedeği Alınmamış Veritabanlarının Mail ile Bildirilmesi

Yedekleme işlemi tüm veritabanı sistemlerinde kritik bir rol oynamaktadır. Yedeği alınmamış bir sistemin yaşayacağı olası bir sorun, sistemin tamamen çökmesine ya da ciddi oranda veri kaybına yol açabilir. Bu nedenle yedekleme işleminin düzenli olarak gerçekleştirilmesi gerekir. Yedekleme işlemlerini otomatik olarak gerçekleştirmek için Maintenance Plan kullanabilirsiniz.

Konuyla ilgili olarak Maintenance Plan Kullanarak Full Backup Almak , Maintenance Plan Kullanarak Differential Backup Almak , Maintenance Plan Kullanarak Log Backup Almak isimli makaleleri okuyabilirsiniz. Yedeklerin herhangi bir sebepten dolayı alınamaması halinde, sistemin sizi uyarmasını isteyebilirsiniz. Belirlemeniz gereken şey, yedeğin alınmama süresi hangi değere ulaştığında sistemin sizi uyaracağı bilgisi. Aynı şekilde, sistemin bu bilgileri ne kadar sıklıkla kontrol edip size geri bildirim yapmasını istediğinizi de belirlemeniz gerekiyor.

Bildirimleri mail ile yapmak için Database Mail konfigürasyonunun yapılmış olması gerekir. Bu işlemi gerçekleştirmek için Database Mail Konfigurasyonu isimli makaleyi okuyabilirsiniz. Son alınan yedekleri kontrol edip, belirli bir süreden beridir alınmayan veritabanlarının mail olarak gönderilmesi için aşağıdaki gibi bir job oluşturabiliriz. Bu örnekte 1 haftadır Full yedek alınmayan ya da 6 saattir Log yedek alınmayan veritabanlarının bildirimi yapılacaktır. Önce SQL Server Agent altında Jobs seçeneğini sağ tıklayarak New Job seçeneğini tıklayalım.

Yeni oluşturacağımız job’ın adını yazalım. Sonrasında Steps kısmını seçelim. 

Steps kısmında New seçeneğini tıklayarak yeni job adımı belirleyelim.

Job adımının adını yazalım. Yapacağımız işlem T-SQL tipinde. Command alanına aşağıdaki ifadeyi yazıp OK butonuna basalım. Buradaki Script, önce veritabanlarının son yedekleme tarihlerini geçici bir tabloya atmaktadır. Geçici tablodan yapacağı sorguda belirtilen sürede yedekleri alınmayan veritabanlarını belirleyip sp_send_dbmail Procedure’ü ile ilgili kişilere mail atmaktadır.

IF EXISTS 
(
SELECT d.name AS VeritabaniAdi,
	   d.recovery_model_desc AS RecoveryModel,
	   MAX(b.backup_start_date) AS SonFullBackup,
	   MAX(b2.backup_start_date) AS SonLogBackup
FROM master.sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name=d.name AND b.type='D' AND (b.is_copy_only=0 OR b.user_name='NT AUTHORITY\SYSTEM') 
LEFT JOIN msdb.dbo.backupset b2 ON b2.database_name=d.name AND b2.type='L'
GROUP BY d.name,d.recovery_model_desc
)
BEGIN

SELECT d.name AS VeritabaniAdi,
	   d.recovery_model_desc AS RecoveryModel ,
	   MAX(b.backup_start_date) AS SonFullBackup,
	   MAX(b2.backup_start_date) AS SonLogBackup
INTO #tempbackup
FROM master.sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name=d.name AND b.type='D' AND (b.is_copy_only=0 OR b.user_name='NT AUTHORITY\SYSTEM') 
LEFT JOIN msdb.dbo.backupset b2 ON b2.database_name=d.name AND b2.type='L'
GROUP BY d.name,d.recovery_model_desc

DECLARE @tableHTML  NVARCHAR(MAX) ;  
  
SET @tableHTML =  
    N'<H1>Yedeği Alınmayan Veritabanları</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>Veritabanı Adı</th><th>Recovery Model</th><th>Son Full Yedek</th><th>Son Log Yedek</th>' +  
    N'<th>Açıklama</th></tr>' +  
    CAST ( (  SELECT 	   
       td=[VeritabaniAdi],''
      ,td=[RecoveryModel],''
      ,td=COALESCE(CONVERT(DATETIME,[SonFullBackup],13),''),''
	  ,td=COALESCE(CONVERT(DATETIME,[SonLogBackup],13),''),''
	  ,td=Aciklama,''
FROM
(SELECT [VeritabaniAdi]
      , [RecoveryModel]
      , [SonFullBackup]
	  , [SonLogBackup]
	  ,'Full Yedek 1 Haftadan Uzun Bir Süredir Alınmamaktadır' AS Aciklama
 FROM #tempbackup
 WHERE RecoveryModel='SIMPLE' AND ISNULL(SonFullBackup,'1900-01-01') < GETDATE()-7

 UNION ALL

 SELECT [VeritabaniAdi]
      ,[RecoveryModel]
      ,[SonFullBackup]
	  ,[SonLogBackup]
	  ,CASE
	   WHEN (ISNULL(SonFullBackup,'1900-01-01') < GETDATE()-7 AND DATEDIFF(HOUR,ISNULL(SonLogBackup,'1900-01-01'),GETDATE())<6)
	        THEN 'Full Yedek 1 Haftadan Uzun Bir Süredir Alınmamaktadır'
	   WHEN (ISNULL(SonFullBackup,'1900-01-01') > GETDATE()-7 AND DATEDIFF(HOUR,ISNULL(SonLogBackup,'1900-01-01'),GETDATE())>6)
	        THEN 'Log Yedek 6 Saatten Uzun Bir Süredir Alınmamaktadır'
	   WHEN (ISNULL(SonFullBackup,'1900-01-01') < GETDATE()-7 AND DATEDIFF(HOUR,ISNULL(SonLogBackup,'1900-01-01'),GETDATE())>6)
	        THEN 'Hem Full Yedek 1 Haftadan Uzun Bir Süredir Alınmamaktadır, Hem Log Yedek 6 Saatten Uzun Bir Süredir Alınmamaktadır' END
 FROM [#tempbackup]
 WHERE RecoveryModel='FULL' AND 
 (ISNULL(SonFullBackup,'1900-01-01') < GETDATE()-7 OR DATEDIFF(HOUR,ISNULL(SonLogBackup,'1900-01-01'),GETDATE())>6)
 ) A
 ORDER BY VeritabaniAdi
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) +  
    N'</table>' ;  
  
EXEC msdb.dbo.sp_send_dbmail 
    @recipients='abc@gmail.com', 
    @profile_name= DenemeProfile,
    @subject = 'Yedeği Alınmayan Veritabanları',  
    @body = @tableHTML,  
    @body_format = 'HTML' ; 

DROP TABLE #tempbackup

END

Job’ın çalışma periyodunu belirlemek için Schedules sekmesini tıklayalım. New seçeneği ile yeni bir zaman planlaması oluşturalım. Schedule Type kısmına Recurring diyerek tekrar eden zamanlamayı oluşturalım. Günlük olarak düzenli çalışması için Daily seçeneğini, her 2 saatte bir çalışması için Occurs Every 2 Hours seçeneğini seçelim. OK butonuna basarak işlemi bitirelim. New Jobs seçeneğine geri döndüğünde tekrar OK butonuna basarak job tanımlama işlemini bitiriyoruz.

SET DATEFIRST

Haftanın ilk günü ülkeye göre ya da kullanılan sisteme göre değişiklik gösterir. Bu değer Amerika için Pazar günüyken, ülkemizde Pazartesi günüdür. Haftanın günleri ile ilgili bir işlem yapılacağı zaman (mesela ayın ilk Pazartesi günü gibi) sistemde kayıtlı olan gün sıralaması istediğimiz sonuçları vermeyebilir. Bu nedenle haftanın ilk gününü kendi ihtiyaçlarımıza göre belirlememiz gerekebilir. Bunun için SET DATEFIRST ifadesini kullanabiliriz. Bu ifade session seviyesinde olup session kapandığında ya da farklı bir session açıldığında geçerli olmayacaktır. İfadenin kullanımı şu şekildedir.

SET DATEFIRST 1

Burada yazacağımız sayısal değerler 1-7 arasında olup, 1 Pazartesi, 7 ise Pazar anlamına gelir. 

SET DATEFIRST 1 -- Haftanın ilk günü Pazartesi
 
SELECT DATEPART(dw,GETDATE())
 
SET DATEFIRST 7 -- Haftanın ilk günü Pazar
 
SELECT DATEPART(dw,GETDATE())

Bugünün haftanın kaçıncı günü olduğunu iki farklı değere göre belirledik. Bu ifade session bazlı olduğu için, her kullanmak istediğimizde SET DATEFIRST yazmamız gerekir. Bunu veritabanı seviyesinde kullanmak için aşağıdaki gibi bir stored procedure yazabiliriz. SET DATEFIRST ifadesi kullanıcı tanımlı fonksiyonlar için çalıştırılabilir seviyede olmadığından dolayı fonksiyon olarak yazamamaktayız.

CREATE PROCEDURE usp_DayNumberofWeek(@date DATETIME, @daynumber INT OUTPUT)
AS
BEGIN
SET DATEFIRST 1
SELECT @daynumber= DATEPART(dw,@date)
END
 
EXEC usp_DayNumberofWeek @date='2017-01-01' ,@daynumber=@day OUTPUT
SELECT @day

Eğer parametre olarak şimdiki anı GETDATE() olarak vermek istersek, GETDATE fonksiyonu procedure’lerde parametrenin değeri olarak açık bir şekilde verilemediğinden dolayı başka bir değişkene GETDATE() değerini vererek, @date parametresini, değişkenin değerine eşitleyebiliriz.