Encryption Konusunda İyileştirmeler

SQL Server 2008 den önceki versiyonlarında, tüm veri tabanını etkin olarak kriptolamak için 3. parti bir araç almak gerekiyordu.

SQL Server 2008 ile birlikte, Transparent data encryption (TDE) özelliği geldi.
TDE ile tüm veritabanının içeriğini kolayca şifrelemek mümkün ve bunu yaparken uygulamalarınızda bir değişiklik yapmanız da gerekmiyor.
TDE kullanmak için, öncelikle "master key" ve "certificate" yaratmak gerekiyor.  Master Key ve sertifikanızı oluşturduktan sonra,

ALTER DATABASE myDatabase SET ENCRYPTION ON


kodunu kullanarak veritabanınızda encrpytion ı açabilirsiniz.

Encryption , veritabanını "key" kullanarak  güvenlik altına alır. Bu key ler korunmalı, backup ı alınmalıdır. Sistemde herhangi bir sorun olduğunda, veriye ulaşabilmek için bu key ler gerekli olacaktır. Bu nedenle key leri daha güvenli ve daha farklı bir donanımda tutmak, güvenliği arttıracaktır.

Sparse Columns ile NULL kolonlarınız daha az er kaplasın

Sparse Column Support
Sparse column desteği, SQL Server 2008 ile gelen yeni özelliklerden.
Null içeren kolonlarımızı daha optimize bir şekilde saklıyor.

İyi bir özellik olmak ile birlikte, gerçekten null içermeyen kolonlarda kullanılmazsa, veritabanımızın disk üzerinde kapladığı alan küçülmek yerine daha fazla da büyüyebilir.

Sparse kolon özelliğini kullanmak için bir kolonu yaratırken "SPARSE" yazmak yeterli.

CREATE TABLE dbo.myTable
(
ID int NULL,
MyValue varchar(50) SPARSE NULL
)
GO

SQL Serrver 2008 ile FileStream Veri Türü

Normalde DB Engine, tüm veriyi, kolon ile ilişkilendirerek, diskteki bir dosyada tutar.

FileStream veri türü ile her türlü veriyi (özellikle binary) SQL Server da saklamak daha kolay hale geldi.

FileStream i kullanabilmek için öncelikle enable etmemiz gerekiyor.
Management Studio'dan Advanced Settings den bunu yapabilir veya
sp_FILESTREAM_configure sistem prosedürünü çalıştırabiliriz.

Bundan sonra, veri tabanına yeni bir file group eklemek gerekiyor. FileStream veri tipi ile tutacağımız veriler, bu yeni açacağımız file group içinde saklanacaklar.

Ancak FileStream  kullanmanın birkaç sınırlaması da var. has the following limitations:

• FileStream verisi içeren bir veritabanı ile "Database mirroring" yapılamıyor.
• Database snapshots, FileStream verilerini desteklemiyor.
• Encryption , FileStream verileri için desteklenmiyor. En azından bunu SQL Server'ın encrpytion yöntemlerini kullanarak yapamıyorsunuz. Başka bir encryption metodu bulmanız gerekiyor.,

SQL Server 2008 - CDC (Change Data Capture) ile Verilerinizdeki Değişikliklerden Haberdar Olun

CDC, SQL Server 2008 ile gelen bir başka önemli özellik.

Verilerinizdeki değişiklikleri CDC kullanarak denetleyebilirsiniz.



CDC, bir tablodaki, insert, update ve  delete işlemlerini yakalayıp, değişiklikleri başka bir tabloya yazar.

Aşağıdaki kodu kullanarak bir tablodaki değişikliklerin kayıtlarını tutmaya başlayabilirsiniz.

--Bir veritabanında CDC seçeneği açık değilse aşağıdaki sistem prosedürü çalıştırılır ve CDC o veri tabanı için aktif hale getirilir.
EXEC sys.sp_cdc_enable_db_change_data_capture


--Değişiklikleri kaydetmek istediğiniz her tablo için de, aşağıdaki prosedürü çalıştırarak, ilgili tablo için CDC yi aktif hale getirebilirsiniz.

EXEC sys.sp_cdc_enable_table_change_data_capture
          @source_schema = 'dbo',
          @source_name = 'myTable',
          @role_name = 'cdc'


CDC tablosundaki verileri okuyabilmek ve diğer CDC işlemleri  için hazırlanmış bir çok sistem prosedürü ve fonksiyonu hazır olarak sunulur.

Sistem prosedürleri :

• sys.sp_cdc_add_ job
• sys.sp_cdc_ generate_wrapper_ function
• sys.sp_cdc_change_ job
• sys.sp_cdc_ get_captured_columns
• sys.sp_cdc_cleanup_change_table
• sys.sp_cdc_ get_ddl_history
• sys.sp_cdc_disable_db
• sys.sp_cdc_help_change_data_capture
• sys.sp_cdc_disable_table
• sys.sp_cdc_help_ jobs
• sys.sp_cdc_scan
• sys.sp_cdc_enable_db
• sys.sp_cdc_start_ job
• sys.sp_cdc_enable_table
• sys.sp_cdc_stop_ job
• sys.sp_cdc_drop_ job

Sistem fonsiyonları :

• cdc.fn_cdc_ get_all_changes_<capture_instance>
• sys.fn_cdc_has_column_changed
• cdc.fn_cdc_ get_net_changes_<capture_instance>
• sys.fn_cdc_increment_lsn
• sys.fn_cdc_decrement_lsn
• sys.fn_cdc_is_bit_set
• sys.fn_cdc_ get_column_ordinal
• sys.fn_cdc_map_lsn_to_time
• sys.fn_cdc_ get_max_lsn
• sys.fn_cdc_map_time_to_lsn
• sys.fn_cdc_ get_min_lsn

Copy Only Backup

"Copy Only Backup", development veya test için tek seferlik backup a ihtiyacınız olduğunuzda kullanabileceğiniz bir seçenektir.  Avantajı, transaction log veya Differential backup lardan etkilenmemesidir. Transaction Log veya Differential backup lar için temel bir backup olarak da kullanılamaz.

Management Studio'daki Backup Interface'inden "Copy Only Backup" seçeneğini işaretleyerek, backup alabilirsiniz.

Kodla yapmak için de:

BACKUP DATABASE [myDatabaseName] TO DISK = N'C:\Backup\myDatabaseName.bak' WITH
COPY_ONLY, NOFORMAT, NOINIT, NAME = N'myDatabaseName-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10

SQL Server 2008 ile Compressed Backups

Compressed Backups (Sıkıştırılmış Yedekler), SQL Server 2008 ile gelen en iyi yeniliklerden biri.
Sıkıştırılmış backup alarak, zaman ve disk yeri tasarrufu yapabilirsiniz. Alınmış backuplarınızı daha sonra başka bir sıkıştırma uygulaması kullanarak sıkıştırmanıza veya sıkıştırılmış bir backup almak için 3. parti bir uygulama kullanmanıza gerek kalmaz.
Sıkıştırılmış backup almak, daha uzun sürecek izlenimi uyandırsa da, backup süresince diskte darboğazlar oluştuğunu biliyoruz, sıkıştırılmış backup ile, diske daha az veri yazılayacağı için backup süresi, normal backup işlemine göre genelde daha az sürer.
Sıkıştırılmış bir backup almak için de, tek yapmanız gereken management studio da backup aldığınız ekranda "Compressed Backup" seçeneğini işaretlemek.

Eğer t-SQL kodu ile backup alıyorsanız aşağıdaki kodu kullanmanız yeterli.

BACKUP DATABASE [myDatabaseName] TO DISK = N'C:\Backup\myDatabaseName.bak' WITH
NOFORMAT, NOINIT, NAME = N'myDatabaseName-Full Database Backup', SKIP,
NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

DBCC Input Buffer ile Tablolarımızı Loglayalım

"DBCC INPUTBUFFER", SQL Server'a client'dan gelen son sql cümleciğini gösterir.

Syntax :
DBCC INPUTBUFFER (session_id [ , request_id ])
[WITH NO_INFOMSGS ]

Parametreleri :
session_id
Son aktif connection'daki oturumun id'sidir.
@@SPID global değişkeni ile, sql server'daki son request'in id si ni alabilirsiniz.

SELECT @@SPID

kodunu çalıştırarak örneğini görebilirsiniz.

request_id

Aktif oturumdaki, son istedğin id sidir.

Son gelen isteğin id sini aşağıdaki kodu kullanarak alabilirsiniz.

SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid

Aşağıdaki kod ise, son request ile ilgili daha fazla detay verir. Durumu ne, hangi veri tabanına gelmiş, kim çalıştırmış, ne kadar süre beklemiş gibi.

SELECT *
FROM sys.dm_exec_requests
WHERE session_id = @@spid

WITH

With, diğer DDL komutlarında olduğu gibi bir özellik vereceğimizi ifade eder.

NO_INFOMSGS

Hata seviyesi 0-10 arasında olan yani server tarafında bilgi mesajı olarak kabul edilen seviyede bir mesaj gelirse, bunu gizler.


Bu kadar teorik bilgiden sonra, "DBCC INPUTBUFFER" ın ne işimize yarayacağına ve nasıl kullanılacağımıza bakalım.

CDC kullanmadan, bir tablodaki değişiklikleri log'luyor isek, bu değişikliğin hangi sql cümlesi kullanılarak (Bu bir update/insert/delete statement olabileceği gibi, bir stored procedure de olabilir) yapıldığını saklayabiliriz.

Veya bir tablo, nedenini bulamadığımız bir şekilde güncelleniyor ise, bu güncelleme işleminin hangi kod tarafından yapıldığını yakalayabiliriz.

Aşağıdaki örnek SQL cümlesi, testCustomer isimli bir tablodaki her değişikliği loglayıp, değişiklikleri de testCustomer_Log isimli bir dosya da saklayacağız.


Önce kodlarımızı hazırlayalım.

CREATE TABLE testCustomer(CustomerID int identity(1,1),

                         FirstName varchar(100),

                         LastName varchar(100))

GO

--DROP TABLE testCustomer_Log

CREATE TABLE testCustomer_Log(ID int identity(1,1),

                            CustomerID int,

                            OldFirstName nvarchar(100),

                            OldLastName nvarchar(100),

                            NewFirstName nvarchar(100),

                            NewLastName nvarchar(100),

                            SqlCommand nvarchar(4000),

                            ChangedOn datetime default(GetDate())

                            )

GO

CREATE TRIGGER trTestCustomer ON testCustomer

AFTER UPDATE,INSERT,DELETE

AS BEGIN

  DECLARE @InputBuffer TABLE(EventType nvarchar(30),

                             Parameters int, 

                             EventInfo nvarchar(4000))

  INSERT INTO @InputBuffer

   exec ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')

  DECLARE @SQLCommand nvarchar(4000)

  SELECT @SQLCommand = EventInfo

  FROM @InputBuffer

  INSERT INTO testCustomer_Log(CustomerID,OldFirstName,OldLastName,

                               NewFirstName,NewLastName,SqlCommand)

  SELECT isNull(i.CustomerID,d.CustomerID),d.FirstName,d.LastName,

        i.FirstName,i.LastName,@SQLCommand

  FROM inserted i

  FULL JOIN deleted d on d.CustomerID = i.CustomerID

END

GO

CREATE PROCEDURE spTEST_UpdateCustomerName @FirstName nvarchar(100),@CustomerID int

AS BEGIN

  UPDATE testCustomer SET FirstName = @FirstName WHERE CustomerID = @CustomerID

END

Data ve Log tablolarını yarattıktan sonra, Data tablomuz olan testCustomer üzerine bir trigger yazıyoruz. Bu trigger de @@SPID ile son oturumun id sini alıp, DBCC INPUTBUFFER a veriyoruz. Bize uyarı mesajları vermesin diye de mesajları "WITH NO_INFOMSG" yazarak kapatıyoruz. 

DBCC INPUTBUFFER 3 kolondan, 1 satırdan oluşan bir tablo döndürür. Bu tablodan dönen değerleri @InputBuffer isimli bir değişken tabloya yazıyoruz ki, sonra DML kodunu çalıştıran kodu alabilelim. 

EventInfo kolonunda, nvarchar(4000) tipinde bir bilgi gelir.  İşte datayı değiştiren kod da bu kolonda bulunur. Yani çalışan script eğer 4000 karakterden fazla ise, ilk 4000 karakterini görebiliriz. Bu kodu @SQLCommand değişkenine atıyoruz.

Sonra değişen bilgilerin eski ve yeni bilgilerini yazabilmek için de, trigger içerisinde kullanılan inserted ve deleted tablolarını full join ile bağlıyoruz.

Unutmamak gerekir ki, bir tabloya insert kodu ile bir kayıt ekliyorsak, sadece inserted tablosunda kayıtlar olur, deleted tablosundan birşey gelmez. Aynı mantıkla, delete ile kayıt siliyorsak, sadece deleted tablosunda silinen kaydın bilgisi olur, inserted tablosunda birşey olmaz. Update ile tabloyu güncelliyor isek, yeni bilgiler inserted tablosunda, eski bilgiler de deleted tablosunda bulunur. 

Birkaç örnek ile trigger ın log tablosuna neler yazdığını bulalım.

INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','DEF')

INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','fsfdsfdsf')

GO

INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN','TestLN')

GO

INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN2','TestLN2')

GO

UPDATE testCustomer SET FirstName = 'Abc Updated' WHERE FirstName = 'Abc'

GO

DELETE FROM testCustomer WHERE FirstName='TestFN'

GO

DECLARE @CustomerID int

SELECT @CustomerID = CustomerID

FROM testCustomer

WHERE FirstName='TestFN2'

exec spTEST_UpdateCustomerName @CustomerID = @CustomerID, @FirstName = 'TestFN3'

GO

SELECT * FROM testCustomer

SELECT * FROM testCustomer_Log

İşte Customer tablosunun son hali. 4 kayıt ekledik.  Kayıtlardan birini update cümlesi ile güncelledik. Sonra başka bir kayıt sildik. Daha sonra da adı TestFN2 olan kaydın adını yazdığımız stored procedure ile güncelledik. 

CustomerID	FirstName	LastName
1	Abc Updated	DEF
2	Abc Updated	fsfdsfdsf
4	TestFN3	TestLN2

 Acaba log tablomuzda neler olmuş?

ID	CustomerID	OldFirstName	OldLastName	NewFirstName	NewLastName	SqlCommand	ChangedOn
1	1			Abc	DEF	INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','DEF') INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','fsfdsfdsf')	13.10.2010
2	2			Abc	fsfdsfdsf	INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','DEF') INSERT INTO testCustomer(FirstName,LastName) VALUES('Abc','fsfdsfdsf')	13.10.2010
3	3			TestFN	TestLN	INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN','TestLN')	13.10.2010
4	4			TestFN2	TestLN2	INSERT INTO testCustomer(FirstName,LastName) VALUES('TestFN2','TestLN2')	13.10.2010
5	2	Abc	fsfdsfdsf	Abc Updated	fsfdsfdsf	UPDATE testCustomer SET FirstName = 'Abc Updated' WHERE FirstName = 'Abc'	13.10.2010
6	1	Abc	DEF	Abc Updated	DEF	UPDATE testCustomer SET FirstName = 'Abc Updated' WHERE FirstName = 'Abc'	13.10.2010
7	3	TestFN	TestLN			DELETE FROM testCustomer WHERE FirstName='TestFN'	13.10.2010
8	4	TestFN2	TestLN2	TestFN3	TestLN2	DECLARE @CustomerID int SELECT @CustomerID = CustomerID FROM testCustomer WHERE FirstName='TestFN2' exec spTEST_UpdateCustomerName @CustomerID = @CustomerID, @FirstName = 'TestFN3'	13.10.2010

İşte tüm yaptığımız değişikliker burada görünüyor.
İlk başta çalıştırdığımız insert cümlesi 2 kayıt birden insert edecek bir batch şeklinde yazılmıştı.  Customer 1 ve Customer 2 kayıtlarında bu her iki cümlenin de yazdığını görebiliyoruz.
En ilginci de, 4 nolu CustomerID nin update cümlesi. Burada ilgili müşteri numarasını bulduktan sonra bunu bir procedure e veriyor ve onun güncelleme yapmasını bekliyorduk.  Yazdığımız tüm kod da burada aynen görünebiliyor.


 Peki testCustomer tablosunu TRUNCATE TABLE ile temizlersek ne olur? Log tablosuna hiç bir şey gelmez. Çünkü truncate kodu herhangi bir trigger'ı tetiklemez. Biz de işlerimizi trigger ile yaptığımız için log tablosunun herhangi birşeyden haberi olmaz.  




Bir sonraki post'da DBCC INPUTBUFFER a benzer iş yapan fn_get_sql sistem fonksiyonundan ve ikisi arasındaki farklardan bahsedeceğiz.