SQL Server’da Bir Database’i Page Seviyesinde Backup’tan Dönme

“Sql server detected a logical consistency-based i/o error” diye başlayan bir hatayla karşılaşmışsanız geçmiş olsun. Muhtemelen diskte bir sektör okuma hatası var. SQL Server’ın en küçük yapıtaşı olan 8 KB’lık page’lerden bir ya da birkaçı bozulmuş.
Koskoca milyon satırlık tabloda sadece 3-5 satır bozuk diye sorgunuz çalışmıyor. Tüm database i yedekten dönseniz yeni eklenen kayıtlar gidecek bu kez. Neyse ki kolay bir yolu var. Database’i page bazlı olarak yedekten dönebilme.
Yazdım. Beğenmeniz dileğiyle.

SQL Server Change Data Capture ile Değişiklik Yapılan Kayıtların Loglanması

Merhaba,

Bu makalemizde SQL Server tarafında yapılan maniplasyonların (Insert, Update, Delete) geri planda otomatik olarak kayıt altına alınmasını anlatıyor olacağız.

Şimdi bir senaryo düşünelim. Bir ticari yazılımımız var. Bu yazılımı dışarıdan satınaldık ve özelliklerine müdahele edemiyoruz kaynağı bizde olmadığı için.

Sistem üzerinde önemli bir fatura hareketinin değiştirildiğini ya da çıkarıldığını düşünelim. Son dönemlerdeki ticari yazılımlar bunların kayıt altına alınmasına izin veriyor ancak vermeyenler de var. Bu anlamda bizim database bazında bu kayıtların loglanmasına ihtiyacımız söz konusu.

Bu işlerle biraz uğraşanlar için ilk akla gelen tabiki trigger yazılması. Doğru bu bir çözümdür ancak sıkıntıları vardır.

Bu sıkıntılar genel olarak şöyledir;

  • Sizin yazdığınız trigger ticari programın kendisinin hata vermesine sebep olabilir ve kayıtların yapılmamasına sebep olabilir. Zira trigger lar transactionların bir parçasıdır ve trigger da gerçekleşen hata tüm transaction ı rollback  yapar.
  • Özellikle mevzuat değişimi gereği sıklıkla versiyon geçişi söz konusudur ve bu versiyon geçişlerinde database düzenlemesi yapıldığı için büyük ihtimal trigger larınız silinir ve her seferinde yeniden oluşturacak scriptler oluşturmanız gerekecektir.
  • Genel olarak Türkiye şartlarında dönem mali dönem bağımlı çalışmak tercih edildiği için her yıl başında fiziken yeni tablolar oluşturulmaktadır ve bunlar için de trigger lar yeniden yazılmalıdır.

Anlaşılacağı üzere trigger meselesi etkin bir çözümdür fakat biraz zahmetlidir.

Peki bizim yazımızın da konusu olan bu durum için bir çözüm yok mu? Birim fiyatı 5000 TL olan bir malzemenin satış faturasındaki fiyatını 50 TL olarak  değiştiren bir kişiyi tespit etmenin pratik bir yolu yok mudur?

Bu noktada imdadımıza SQL Server Change Data Capture (CDC) dediğimiz özellik yetişiyor. Bu arkadaş yetenekli bir arkadaş. SQL Server’da bildiğiniz üzere tüm manipülasyon işlemleri önce Log dosyasına sonra Data dosyasına yazılır.  Burada log dosyası diye bahsettiğim SQL server’ın sistem log dosyası değil database’in Log dosyasıdır (LDF).

İşte CDC sistem üzerinde Log dosyasını izler ve olan değişiklikleri hızlı bir şekilde kayıt altına alır.

Örnek olarak siz aşağıdaki gibi bir UPDATE cümlesi çalıştırdınız.

CUSTOMERS tablosunun 20 alandan oluştuğunu varsayalım oysa biz sadece bir alanı update ettik. Dolayısıyla SQL Server transaction log üzerinde sadece bir alanlık işlem hacmi söz konusu.

İşte Change Data Capture sadece bu bilgiyi okuyarak arka planda veriyi logluyor.

Siz  CDC yi configure ederken belli bir süreliğine dataları loglayıp belli bir tarihten öncesini sildirebiliyorsunuz. Burada yazacağınız bir script ile önce bu datalara herhangi bir warehouse ortamına alıp daha sonra sistemden temizleyebilirsiniz.

Öncelikle şunu başta belirtmek isterim ki bu özellik SQL Server 2008 den beri vardır ancak Enterprise edition üzerinde çalışır. Tabi test ortamları için developer edition da enterprise ın tüm özelliklerine sahiptir.

Şimdi bu CDC nasıl çalışıyor bir bakalım.

1. Önce bir tablo oluşturalım.

2.Database imizde CDC yi enable yapıyoruz.

3.Tablomuzda CDC yi enable yapalım.

CDC yi enable ettikten sonra system tables altında aşağıdaki tablolar oluşur.

  • cdc.captured_columns : Adından da anlaşılacağı üzere değişikliklerin takip edileceği kritik alanları tutar. Bu tablo manuel olarak edit edilebilir durumda olup içeriği değiştirilebilir.
  • cdc.change_tables :Hangi tabloların değişiminin takip edileceği bilgisini tutar.
  • cdc.ddl_history :Şema bilgilerindeki değişiklikleri tutar.
  • cdc.lsn_time_mapping: Asıl Tablo üzerinde yapılan her transaction işlemi bu tablo içerisinde tutulur ve içerisindeki lsn bilgisine göre hangi sırada yapıldığı bilgisi tutulur.

4.Şimdi bir kayıt ekleyelim.

5.UPDATE yapalım.

6.DELETE Yapalım

Görüldüğü gibi tablo üzerinde 2 insert,1 update ve 1 delete işlemi yaptık. Burada sistemde 4 satır kaydın logunun tutulması gerekiyor. Bakalım görebilecek miyiz?

Şimdi tablolarımıza bir bakalım.

CDC.captured_colums tablosu

CDC.ddl_history tablosu

CDC.index_column tablosu

CDC.lsn_time_mapping tablosu

Sistemde loglanan kayıtları ya doğrudan ya da tarih parametresi alan table valued function lar ile görebiliyoruz. Bu tablodaki kayıtlar ise log sequence number (lsn) ile tutuluyor. Bu fonksiyonlar da yine tablo bazlı olarak otomatik oluşuyor. Aşağıdaki resimde bu fonksiyonları görebilirsiniz.

Log kayıtlarını ulaşmak  istediğimizde  eğer tablonun tamamına ulaşmak istiyor isek

select

* from cdc.dbo_customers_CT şeklinde kullanıyoruz.

burada tablo formatı cdc.<schema>_<tablename>_CT şeklinde.

Bunun kullanımını sonucu aşağıdaki gibi.

table valued functionlar  ise aşağıdaki gibi kullanılıyor.

DECLARE @from_lsn binary(10), @to_lsn binary(10);

–minimum lsn numarasını buluyoruz.

SET @from_lsn = sys.fn_cdc_get_min_lsn(‘dbo_customers’);

–maximum lsn numarasını buluyoruz.

SET @to_lsn = sys.fn_cdc_get_max_lsn();

— CDC ile ilgili işlemlerde tablo bazlı oluşan cdc function larını kullanıyoruz.

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customers(@from_lsn, @to_lsn, ‘all’);

Görüldüğü üzere sistem 4 adet fazladan alan ve sistemde yapılan değişiklik üzerine loglanan kayıtları getirdi.

Burada

__$start_lsn log: sequence number bilgisini içeriyor. Buradan kayıt tarihine erişebiliyoruz.

__$seqval: Sequnce değeri yani işlemin hangi sırada gerçekleştiği bilgisine erişmek için bu alan kullanılıyorç

__$operation:2 Insert, 4 Update ve 1 Delete için kullanılıyor.

__$operation:1 Insert,Delete 0 Update

anlamına gelmektedir.

Burada kayıt zamanını elde etmek istediğimizde

sys.fn_cdc_map_lsn_to_time function ını kullanıyoruz.

select sys.fn_cdc_map_lsn_to_time(__$start_lsn) as KayitZamani,

* from cdc.dbo_customers_CT

Burada oluşan log kayıtlarını temizlemek için ise

sp_cdc_cleanup_change_table

komutunu kullanıyoruz.

Kullanımı aşağıdaki gibi.


— aşağıdaki kod  3 gün öncesine ait logları temizliyor.
declare @lsn binary(10);
set @lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,getdate()-3);
exec sys.sp_cdc_cleanup_change_table @capture_instance = ‘dbo_Customers’, @low_water_mark=@lsn

–CDC yi disable etmek için ise 
sp_cdc_disable_db,
sp_cdc_disable_table 
komutları kullanlr 

EXECUTE sp_cdc_disable_table@source_schema = N’dbo’,@source_name = N’Customers’,@capture_instance =N’dbo_Customers’

CDC çalıştırabilmek için SQL Server agent a ihtiyacımız söz konusu.  Sistem 2 adet job ı otomatik olarak oluşturmaktadır. Bunlardan birisi değişen datanın capture edilmesini sağlarken diğeri de logları temizlemektedir.

Sonuç:

  • CDC gerçekten çok ihtiyaç duyulan ve çok kullanışlı bir araç.
  • Sistemdeki insert, update ve delete leri loglayabiliyor.
  • Eğer update cümlesinde kayıt değişmiyor ise gereksiz yer teşkil etmiyor.
  • Örneğin: UPDATE CUSTOMERS SET NAME=NAME cümlesini çalıştırdığımızda herhangi bir loglama yapmıyor çünkü değişen bir şey yok.
  • Sistemin çalışıyor olması için SQL Server Agent’ın mutlaka çalışması gerekir. Çünkü loğları okuyan bir job bu işleri yerine getirmektedir.
  • Yazacağımız bir script ile istediğimiz tablolarda çalıştırıp istemediklerimizde çalıştırmayabiliriz. Hatta çok fazla kolon olan bir tabloda istediğimiz kolonlar için aktif hale getirirken istemediklerimizi es geçebiliriz.

Bir sonraki makalede görüşmek üzere.

Veritabanı Uzmanı Olun.

Veritabanı Tasarımı yapabilen biri analitik düşünme kabiliyetine sahiptir.

Çapraz sorgular ile veriyi sorgulayabilen biri çözüm üretme becerisine sahiptir.

Bir veritabanı sistemini yönetmeyi bilen biri çalışan bir sistemin dilinden anlayan, sorun çözen, performans yönetimini iyi bilen biri demektir.

Hadi siz de Ağustos ayının son indiriminden faydalanarak,

Uygulamalarla SQL Öğreniyorum kursu ile SQL dilini öğrenin ve analitik düşünme yeteneğinizi daha da geliştirin.

https://www.udemy.com/course/sql-ogreniyorum/?couponCode=AGUSTOS3

Alıştırmalarla SQL Öğreniyorum kursu ile SQL sorguları ile veriyi analiz etmeyi ve sözel sorulara veri ile cevap vermeyi öğrenin.

https://www.udemy.com/course/alistirmalarla-sql-ogreniyorum/?couponCode=AGUSTOS3

Uçtan Uca SQL Server kursu ile bir veritabanı yönetim sistemini uçtan uca yönetmeyi öğrenin.

https://www.udemy.com/course/uctan-uca-sql-server-egitimi/?couponCode=AGUSTOS3

Tüm kurslar 24.99 TL

Kupon kodu:AGUSTOS3

Devexpress Dashboard ve SQL Server’da Büyük Veri ile Dashboard Performansı

Devexpress’i diğer iş zekası uygulamalarından ayıran en temel özelliği data modelleri oluşturmadan doğrudan SQL cümlesi yazarak çok pratik şekillerde interaktif dashboardlar oluşturmayı sağlaması idi.

Aslında bunu yaparken de çok pratik bir yöntem kullanıyor ve siz aksini belirtmedikçe tüm işi veritabanı sunucuya yaptırıyor. Ona bir işi yaptırmak için ise onun dilinden iyi anlaması gerekiyor tabi.

Ne demek istediğimi şöyle anlatayım.

Çözümpark’ta yayınladığım makaleme bir gözatın.

SQL Injection ile Veritabanı Sunucuya Zararlı Yazılım (Keylogger) Atma

Veritabanı güvenliği konusunda en çok karşılaşılan ve eneski saldırı yöntemlerinden #SQLInjection ile neler yapılabildiğine bir örnek. Basit bir öneri formu ile sunucuya hiçbir yerden dosya download etmeden klavye dinleyen bir keylogger uygukaması atmak.
Çok daha fazlası BTK Akademi ‘de

#sqlserver #mssql #veritabanıgüvenliği #dbsecurity #sqlinjection #keylogger #dbhacking

SQL Server’da Fulltext Arama Üzerine Yeni Bir Yaklaşım – Ev Yapımı Fulltext

İlişkisel veritabanları dikeyde arama yapar. Yani bu sistemler satırlarda arama yapmak için tasarlanmıştır. Performansı da gayet iyidir.
Peki ya yatayda durumlar nasıl?
Yatayda dediğim konu verilerin bir sütunda text olarak tutulması. Arka arkaya tren vagonu gibi dizilmiş kelimeler, cümleler.
Örneğin bir özgeçmiş, kitap text’i, twitter verisi gibi yapılardan bahsediyorum. Buralarda bir kelimeyi aramak normal sql cümlesi ile yapılmaz. Fulltext search dediğimiz yapılar kullanılır.

Peki, hazırcılık yapmak yerine kendi fulltext search algoritmamızı yazmaya ne dersiniz?
4milyon satırlı bir e ticaret datasında milisaniye mertebesinde arama yapmaya?
Ya da 1000 satırlı bir e-kitap datasında kelime ararken yine milisaniye mertebesinde sonuç bulmaya?
Cevabı burada 🙂

SQL Öğrenme Üzerine…

Koskoca bir evrenin içinde yaşıyoruz. Yıldızlar, galaksiler, gezegenler… Ancak onlara herkesiN baktığı gibi bakarsak onların güzelliğini görmemiz imkansız. Çünkü çoğunluğun yaptığı şey zahmetsiz olandır. Aynı zamanda kıymetsiz. Şehrin meydanından, evin balkonundan gökyüzüne bakıp yıldızların güzellğinden bahsetmek olmaz.

Şehirlerdeki ışık kirliliği gökyüzünün mücevherlerini görmemizi engeller. Aşağıdaki fotoğrafta ışık kirliliğinin azaldıkça gökyüzünün nasıl berraklaştığını görüyorsunuz. Yani demek istediğim o ki, bir şeyi tam olarak görebilmek, gözlemleyebilmek, öğrenebilmek için ona doğru zamanda, doğru noktadan bakmalısınız ve etrafta size gerek olmayan herşeyden kurtulmalısınız.

Konuya böyle bir giriş yaptıktan sonra aslı meselemize gelmek isterim. Bir çok arkadaşımız üniversitede veritabanı, veritabanı yönetimi, veritabanı programlama dersleri adı altında SQL öğreniyor. Ancak teknikler hep tek düze.
Dört tane temel sql komutu,
50 satırdan oluşan TBLOGRENCI tablosu,
Tahtada yazılan kağıtta sorulan sınav soruları,
Sonrasında da “Abi sql mi kaldı herkes big data konuşuyor” diyen bilmiş öğrenci profili.

Sonuç itibariyle piyasada kullanılan uygulamaların bir çoğu hala yapısal veritabanlarını kullanıyor. Yani analiz edilecek verilerin büyük bir kısmı halen sql ile sorgulanıyor. Hiçbir şey yapılmasa bile ön işleme kısmı bu şekilde yapılıyor.
SQL sanıldığının aksine çok gelişmiş bir dil. Tabi TSQL ya da PLSQL gibi yapılardan bahsediyorum. Herhangi bir programlama dilinde yapabileceğiniz neredeyse her türlü işi sadece TSQL ya da PLSQL kullanarak yapabilirsiniz.
Ama bu iş dört tane komut ezberleyerek, tahtada öğrenip kağıtta yazarak yapılmaz.

  • Bir vt yönetim sisteminin nasıl çalıştığının bilinmesi gerekir.
  • Index diye geçtiğimiz şeyin 1 milyar satırda 1 dakikada gelen bir sorguyu 10 ms de nasıl geldiğinin görülmesi gerekir.
  • Bu kadar kıymetli olan verilerimizin başına bir iş geldiğinde nasıl kayıpsız yedekten dönülebileceğinin bilinmesi gerekir.
  • TSQL dili ile eldeki veriyi analiz etme, sorgulama, temizleme, düzenleme gibi işlemlerin nasıl yapılacağının bilinmesi gerekir.

Bunlarla giriş yaptıktan sonra zaten olaya karşı bir ilgi ve öğrenme isteği doğar ve şimdi burada madde madde yazmayacağım bir çok konu daha kendiliğinden öğrenilme aşamasına geçer.

İşte bu eksiklikleri görerek çeşitli eğitimler hazırladım Udemy’de.

Dedim ya evin balkonundan bakarak gökyüzünün güzellikleri görülmez. Yerine gitmen gerekir.

Hadi durma! SQL’i öğrenmek için kurslarıma bir gözat.

Veriyle konuşmanın alfabesi olan SQL dilini sıfırdan öğrenmek için (Konu hakkında hiçbir şey bilmesen bile) kendi 9.500 öğrencisi ile kategorisinin en çok satan kursu olan “Uygulamalarla SQL Öğreniyorum” kursunu bu link ile 24.99 TL’ye alabilirsin.

https://www.udemy.com/course/sql-ogreniyorum/?couponCode=AGUSTOS2

SQL dilini temel anlamda öğrendin. Fakat kendini daha da geliştirmek istiyorsun. İş hayatına ve mülakatlara hazırlanmak için gerçek hayat örnekleri, sorular ve alıştırmalar ile unutulmayacak şekilde hafızanıza kazınmasını istiyorsun.

O zaman sürekli yeni senaryo, veri setleri ve sorularla güncellenen, 70’ten fazla çözümlü soru videolarıyla, yayınlanalı henüz bir ay olmasına rağmen 2.150 öğrencisi ve 4.9 değerlendirme puanı ile “Alıştırmalarla SQL Öğreniyorum” kursu tam sana göre. Bu link ile 24.99 TL’ye alabilirsin.

https://www.udemy.com/course/sql-ogreniyorum/?couponCode=AGUSTOS2

“SQL dilini öğrendim ama bu bana yetmez. Ben bir veritabanı sistemini yönetmeyi, büyük veri ile uğraşmayı, performans sorunlarına çözüm üretmeyi istiyorum. Bir Database Admin olmak istiyorum” diyorsan o zaman 11.800 öğrencisi ile ve 4.75 değerlendirme puanı ile “Uçtan Uca SQL Server” kursuna mutlaka gözatmalısın. Bu link ile 24.99 TL’ye alabilirsin.

https://www.udemy.com/course/alistirmalarla-sql-ogreniyorum/?couponCode=AGUSTOS2

Sivas Bilim ve Teknoloji Üniversitesi ile Veritabanı Güvenliği konuştuk

Sivas Bilim ve Teknoloji Üniversitesi ile yaptığımız online seminerde Veritabanı Güvenliği üzerine konuştuk. Eski öğrencim olan sevgili Özlem Ceviz’in organize ettiği ve Üniversite Rektörü sayın Prof. Dr. Mehmet KUL’un da katıldığı etkinlikte.

Brute force saldırıları

SQL Injection saldırıları

Bu saldırılardan korunma yöntemleri üzerine canlı uygulamalı bir ders yaptık.

https://www.sivas.edu.tr/hdetay/veritabani-guvenligi-158

#sivas.edu.tr #sivasbtu #veritabanıgüvenliği #vtgüvenliği #dbsecurity #sqlserver #mssql

Erciyes’ten gece manzaraları

Bilen bilir. Amatör olarak fotograf merakım var. Özellikle de gece ve astrofotografçılığa ayrı bir ilgim var. Bu alanda en çok bilinenlerden olan sevgili Betül Türksoy’un daveti ile (https://twitter.com/betulturksoy_) Erciyes’te bir gece çekim etkinliğine katıldım. Zifiri karanlıkta 2.500 rakımda ışıl ışıl gökyüzü, temiz ve serin hava, milyonlarca yıldız size çok farklı bir deneyim yaşatıyor. Ayrıca güzel bir günbatımını ve #Neowise kuyruklu yıldızını da şehir ışıklarının hemen üstünde gözlemleme şansımız oldu.

Fotograflar burada. Umarım beğenirsiniz.