Masters Of SQL

Merhabalar :)

Audit Yazı dizimizin 2.bölümü ile karşınızdayız. Bir önceki bölümde "Audit nedir?", "Nasıl kurulur?" sorularının cevabını vermeye çalıştık.

Yazımızın 1.bölümüne gitmek için : Nedir Bu SQL Server AUDIT? (Bölüm 1)

Bu bölümüzde, Auditi’imizin analizini yapmaya çalışacağız. İlk olarak bakalım bir önceki bölümümüzde kurduğumuz Audit’imizi kurabilmiş miyiz? Bunun için; fn_get_audit_file fonksiyonu, Audit’imizin için en sık kullanacağımız fonksiyon olabilir. Fonksiyona, Audit’imizin Path’ini vererek aşağıdaki gibi çalıştırıyoruz.

    
select * from fn_get_audit_file ('D:\Audit\*.sqlaudit',default,default)

Bu fonksiyon sayesinde Audit’imize düşen logları görebiliriz. Sorguyu kendimize göre düzenleyerek, daha okunabilir bir hale getirebiliriz.

    
select event_time, object_name, statement, database_name, class_type, session_server_principal_name, server_principal_name, action_id, succeeded, session_id 
from fn_get_audit_file ('D:\Audit_Deneme\*.sqlaudit',default,default) 
order by 1 desc

Tablodaki kolonları inceleyecek olursak, yapılan işin ne zaman yapıldığını, hangi statement'ın çalıştığını, kim tarafından yapıldığını, yaptığı işin başarılı olup olmadığı gibi soruların cevabını bulabiliriz.

DMV lerle inceleyecek olursak;

    
select * from sys.dm_audit_class_type_map

fn_get_audit_file fonksiyonundan dönen class type seçeneklerini inceleyebilmemize olanak sağlayan bir DMV.

    

select * from sys.server_audits
select * from sys.server_audit_specifications
select * from sys.server_audit_specification_details

DMV leri sayesinde, auditlerimizin detayını görüntüleyebiliyoruz. Şimdi, Auditimizi test etme zamanı:) Bakalım, gerçekten de loglayacak mı?
Çalışma amaçlı bir DB oluşturuyorum.

    
CREATE DATABASE BAKALIM_OLCAKMI

Audit'imize bakalım. İstediğimiz şekilde loglamış mı?

    
select event_time, object_name, statement, database_name, class_type, session_server_principal_name, server_principal_name, action_id, succeeded, session_id 
from fn_get_audit_file ('D:\Audit_Deneme\*.sqlaudit',default,default) 
order by 1 desc

Bu seferki denememizde tablo create, alter, drop işlemlerini deniyoruz.

    
--Tablo create 
USE [BAKALIM_OLCAKMI]
GO

CREATE TABLE [dbo].[Audit_Deneme](
	[column_1] [int] NOT NULL,
	[column_2] [int] NULL
) ON [PRIMARY]

--Tablo alter(kolon ekleme)
ALTER TABLE dbo.Audit_Deneme
	ADD deneme int NULL

--Tablo drop 

DROP TABLE dbo.Audit_Deneme

Az evvelki scriptimizi yeniden çalıştırıyoruz. Bakalım loglamış mı?

    
--Login create
USE [master]
GO
CREATE LOGIN [AuditDeneme] WITH PASSWORD=N'12345', 
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--Login drop
DROP LOGIN [AuditDeneme] 
GO

Geldik bir yazımızın daha sonuna. Umarım sizlere faydalı olabilmişizdir. Bir sonraki yazı dizimizde görüşmek üzere. Soğuklar da geliyor, kendinize iyi bakın, Yoda’nın tabiriyle “DBA hastalanmaz”. Geçen sene beni nasıl şartladıysa “DBA hastalanmaz” diye kendime telkin ede ede hastalanmadım da gerçekten :D 

Merhaba güzel insanlar, görüşmeyeli nasılsınız? Bugün bir Audit yazısıyla karşınızdayız. Aradığınızı bulmanızı, bulamadığınızı sormanızı temenni eder, iyi okumalar dilerim :)

Gelelim Audite :)

SQL Server dünyasına 2008’de giren Audits özelliği, security anlamında DBA’lere güzel avantajlar sağlayan bir özelliktir. Örneğin; server üzerinde kim database oluşturmuş, kim database silmiş, kim bir değişiklik yapmış, yeni oluşturulan bir login bilgisi, incelemek istediğimiz bir tabloya kim kayıt eklemiş, kim kayıt silmiş tarzında sorularımıza cevap bulabileceğimiz, veri tabanımızda belirlediğimiz çerçevede güvenliğimizi sağlayabilmemize olanak sağlayan, 2012 de filter seçeneği ile filtreleme de yapabildiğimiz ekstra yeni özelliklerle hayatımıza dahil olan, incelenmesi gereken bir özelliktir.

Ne dersiniz? Beraber yapalım mı basit bir Audit örneği :)

Security’nin altındaki Audits’e tıklarak “New Audit” diyoruz.

Seçeneklerimiz File, Security Log ve Application Log. Ben, D’nin altında açtığım Audit klasörünün altına yazmasını istediğim için file’ı seçiyorum.

İster OK’a basarak, Audit’imizi oluşturabilir, istersek Sol üst köşedeki Script’e tıklayarak, penceremizde açılan scripti çalıştırarak Audit’imizi oluşturabiliriz.

    
USE [master]
GO

CREATE SERVER AUDIT [Audit_DDL]
TO FILE 
(	FILEPATH = N'D:\Audit'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)

WHERE statement not like '%truncate%'
   

Auditimizi oluşturduk. Fakat defaultta oluşturduğumuz Audit disable gelir. Bu sebeple Auditimizin üzerine sağ tıklayarak enable duruma getiriyoruz.

Auditimizi oluşturmuş olsak da halen hedefimize ulaşmış sayılmayız. Şimdiki adımımızda, Server bazlı obje değişikliklerini, yeni oluşturulan loginlerin loglamasını hedeflediğimiz bir server bazlı Audit oluşturarak, bu Auditimizi kullanmasını sağlamalıyız. Bunun için, Server Audit Specifications’a sağ tıklayarak New Server Audit Specification… a tıklıyoruz.

Karşımıza çıkan pencerede, neleri loglamasını istiyorsak seçiyoruz. Ben yeni oluşturulan veya silinen bir database i loglaması için Database Change Group, server düzeyinde obje değişikliklerini yakalaması için Schema Object Change Group u ve Login create- drop larını yakalayabilmek için Server Principal Change Group u seçiyorum. Siz isterseniz, kendi ihtiyaçlarınıza göre farklı kombinasyonlar yapabilirsiniz.

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Server_DDL]
FOR SERVER AUDIT [Audit_DDL]
ADD (DATABASE_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
   

Yine, enable etmek gerekiyor tabi :) Audit oluşturma işlememiz tamamlandı. Auditimizin kontrolleri bir sonraki bölümümüzde :) Bakalım Auditimiz olmuş mu? 

Gün geçmiyor ki Microsoft yeni bir sürümle karşımıza çıkmasın :). Daha önce Padawan'ın güzel güzel anlattığı SQL Server 2016 CTP3 kurulum makalesinden sonra yoğun baskılar sonucunda bana da yenilikleri makalesini yazmak kaldı; arkadaş eskiden bir usta çırak ilişkisi vardı :) çıraklar ustalarına böyle zorla birşeyler yaptıramazlardı ah ah nerede o eski günler :):) işin şaka ve goy goy kısmını geçtikten sonra asıl mevzuya girelim tabii detaylara fazla girmeden sadece literatürünüz gelişsin diye yazdım. :)

• SQL Server 2016 (CTP 3.0) indirmek için tıklayınız Community Technology Preview 3 (CTP 3.0), go to Evaluation Center.
• Azure hesabınız varsa SQL Server 2016 kurulu hazır Virtual Machine için tıklayınız

SQL Server 2016 Faydaları:
• Geliştirilmiş bellek performansı sayesinde 30x daha hızlı işlemler, disk tabanlı ilişkisel veritabanları ve gerçek zamanlı operasyonel analitik işlemlerde 100x daha hızlı sorgular.
• Kurum içi ve bulut içinde kullanılan sistemlerde uygulamalarda değişiklik yapmadan verilerinizi Encrypted olarak korumanıza yardımcı olur.
• Stretch Veritabanı teknolojisi sayesinde uygulamada değişiklik olmadan güvenli bir şekilde, OLTP verileriniz için Microsoft Azure da tarihsel olarak tutmanızı sağlar.
• Windows, iOS ve Android için yerel uygulamalar ile mobil cihazlarda zengin görsellik.
• PolyBase T-SQL kullanarak ilişkisel ve ilişkisel olmayan sorgularınızın yönetimini kolaylaştırın.
• AlwaysOn tarafında Azure SQL Server secondaries node kullanarak daha hızlı hibrid yedekleme, yüksek kullanılabilirlik ve felaket kurtarma senaryoları (HADR)

Genelde yenilikler SSMS üzerinde olduğundan dolayı O Services i anlattım. Ayrıca özellikle Analytics le ilgilenen arkadaşlar için yeni services olan SQL Server R Services duyuruldu bu kısımlar biraz daha olgunlaştıkça makale halinde yayınlamaya çalışacağız.

Database Engine (SSMS)
   
Stretch Database
Server terminolojisine yeni katılmış bir özellik. Yaptığı iş aslında çok güzel (gerçi şu azure a bir alışamadım :) ) lokalde bulunan veritabanının tarihsel olarak değişimlerini azure da bulunan sql sunucuya gönderiyor. Bu sayede hem lokal db de çalışırken hem de uzak verilere kesintisiz olarak erişmemizi sağlıyor.




JSON Desteği
Evet artık geldi beklenen gün bugünmüş. Yazılım tarafına ve rakip dblere JSON geleli yıllar oldu :):) peki kullanımı nasıl?

         SELECT * FROM OPENJSON ('{"version":"SQL Server 2016 (CTP3)","build":13,"date":"28.10.2015"}')
         

            SELECT [key], value
            FROM OPENJSON('["en-GB", "en-UK","tr-TR"]')
         

Columnstore Index
SQL Server hayatına 2012 ile birlikte giren ColumnStore Index her geçen gün güçlenerek büyümeye devam ediyor. Önce OLAP mimarisine geldi sonrasında OLTP ye eklediler şimdi ise InMemory table lara ekleme yapıldı. Microsoft un sayfasında açıkladığı gibi gelen yapılar (gelecek diyelim :) ) aşağıda.  

Columnstore Index Feature	SQL Server 2012	SQL Server 2014	SQL Server 2016 Community Technology Preview 3 (CTP 3.0)
Batch execution for multi-threaded queries	yes	yes	yes
Batch execution for single-threaded queries			yes
Archival compression option.		yes	yes
Snapshot isolation and read-committed snapshot isolation			yes
Specify columnstore index when creating a table.			yes
AlwaysOn supports columnstore indexes.	yes	yes	yes
AlwaysOn readable secondary supports read-only nonclustered columnstore index	yes	yes	yes
AlwaysOn readable secondary supports updateable columnstore indexes.			yes
Read-only nonclustered columnstore index on heap or btree.	yes	yes	yes1
Updateable nonclustered columnstore index on heap or btree			yes
Additional btree indexes allowed on a heap or btree that has a nonclustered columnstore index.	yes	yes	yes
Updateable clustered columnstore index.		yes	yes
Btree index on a clustered columnstore index.			yes
Columnstore index on a memory-optimized table.			yes
Nonclustered columnstore index definition supports using a filtered condition.			yes

1 To create a readable nonclustered columnstore index, store the index on a read-only filegroup.

     

Query Store
InMemory tablolarda desteklenmeye başlandı (CTP3). SQL Server 2016 ile hayatımıza giren bir kavram ve yaptığı iş DBA ler için ileride çok işe yarayacak gibi duruyor. Peki ne olaki bu Query Store :). Sorgular esnasında oluşan Execution Planlarları saklayarak geriye doğru inceleme yapmamızı ve istediğimiz birini kullanmamızı sağlayacak.

Kısaca aşağıdaki ayarla açıp kapatacağız.

    ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;
    

Temporal Tables (System-Versioned Tables)
Yazılım ekipleri ve DBA ler yıllardır tablolarda yapılan değişiklikleri görebilmek için taklalar atmak zorunda kalmışlardı ya trigger kullanarak insert, update ve delete ler başka bir tabloya alınmış ya da yazılım katmanında history tablolar yapılarak çözüm üretmişlerdi. SQL Server 2016 ile artık tabloların tarihsel olarak geçmişine otomatik olarak ulaşabileceğiz.

    		CREATE SCHEMA History
GO
CREATE TABLE dbo.Personel 
(
    PersonelNo int NOT NULL PRIMARY KEY CLUSTERED, 
    Ad nvarchar(50) NOT NULL, 
    Soyad nvarchar(50) NOT NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, 
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,   
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)   
)
WITH 
    (
		SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.Personel ) 
    );

    

Yeni bir özellik olduğundan dolayı bazı kısıtlamaları var tabii. Bunları uzun uzun yazmayalım linkten öğrenebilirsiniz. https://msdn.microsoft.com/en-US/library/mt604468.aspx


Always Encrypted
Verileriniz artık daha güvenli “Bizede mi lolo” Şifreleme için kullanılan KEY’i olmayan veriye ulaşamayacak (ama KEY’i yedeklemeyi unutmayın).


Dynamic Data Masking
Yine 3th parti uygulamalara taş koyacak Özellikle Day1 (devan diyenlerde var) ve UAT sistemlerde gizlenmesi gereken veriler için kullanacağımız güzel bir özellik. Daha detaylı olarak bir makalede inceleyeceğiz bununla birlikte default value ları aşağıdaki gibidir.




Birazda neler yapabileceğimizi hayal edelim :)

    
    select * from Personel 
    

Sonrasında Soyad kolonuna MASKED uyguluyoruz.

ALTER TABLE Personel
ALTER COLUMN Soyad ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');

ve bir tane TestUser oluşturarak onunla tablomuza Select çekiyoruz.

CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Personel TO TestUser;

EXECUTE AS USER = 'TestUser';
SELECT * FROM Personel;
REVERT;

Veri tabanı içerisinde MASKED uygulanmış kolonları bulmak için aşağıdaki query çalıştırılır.

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl 
    ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;

        

Replication Geliştirmeleri
Memory-Optimized tablolarda artık replication destekleniyor. Azure database i içinde replication desteği başladı.