SQL Server – Veritabanı Yedekleme Stratejileri

Yedekleme stratejisi oluşturmadan önce sorulması gereken sorulardan en önemlisi; “Veritabanında bulunan veriler ne kadar sıkılıkla güncelleniyor?” sorusudur.

Uygun bir plan oluşturmak amacıyla üretilecek sorular elbette çeşitlendirilebilir ancak soruların içeriği veritabanı ve ilgili verilerin taşıdığı önemi, son yedeklemeden sonra ne miktarda veri kaybına tahammül edilebileceği ve ya veritabanının büyüklüğünü hedef alarak sorgulayacak ise artık günümüzde geliştirilen uygulamaların içerik olarak zenginliği, işlem hacmi, firma için taşıdıkları değer ve herhangi bir veri kaybı durumunda yaşanılacak zaman ve emek kaybı düne göre fazla olduğu için eskiden olduğu kadar yedekleme öncesinde uzun uzadıya tartışmaya değer konular olduğunu sanmıyorum. Çünkü aslında yukarıdaki soruya verilecek cevap bir bakıma yedekleme planını ortaya koymak için minör kriterlerleri sorgulayacak olan diğer tüm soruların da yanıtını içerisinde barındırıyor olacaktır. Özellikle de OLTP (Online Transaction Processing) tipinde veritabanları için.Peki OLTP nedir ?

OLTP, birçok kullanıcı tarafından sürekli ve eşzamanlı olarak veri girişi, verilere anlık erişim ve veri değiş-tokuşu gerektiren işlem (transaction) odaklı uygulamalar tarafından kullanılan veritabanı türüdür. B2B ve B2C olmak üzere tüm e-ticaret sistemleri, online internet bankacılığı, havayolu ve ya turizm portalları gibi ödeme işlemleri, stok bilgisi değiş tokuşu, online rezervasyon gibi anlık verilerin işlendiği tüm uygulama altyapılarının kullanmak durumunda olduğu teknolojidir.

Yedekleme stratejisi belirleme ve bu doğrultuda bir an önce harekete geçmek aslında kolay bir konu olmasına rağmen belkide en çok ihmal edilen ve ya oluşturulurken en çok yanlış yapılan süreçlerden bir tanesidir. Bu uygulamada, bir üretim sunucusunda bulunan farklı uygulamalara ilişkin OLTP veritabanlarını yedeklemek için nasıl bir yol izlenebileceğini adım adım ve mümkün olduğunca detaylı ele alıyor olacağız.

Sürekli olarak erişilen ve güncellenen OLTP veritabanları için sadece düzenli olarak full backup alınması kafi değildir. Eğer full backup ile yetinilirse son yedekleme sonrasında gerçekleşmiş tüm işlemlerin tekrar girilmesi gerekecektir ki işlem yoğunluğu bulunan ve birden fazla uygulamanın birbiriyle etkileşerek aynı veritabanlarını güncellediği profesyonel altyapılarda bu neredeyse imkansızdır. Full, Differential ve Transaction Log yedeklemeleri kombinasyonu iyi bir çözüm olacaktır. Bu yedekleme tiplerine kısaca değinelim;

Full backup: Veritabanının bütünüyle kopyasını tek bir yedekleme dosyasına alır. Yedekleme stratejisine neredeyse her zaman full backup ile başlangıç yapılır ve yedekleme zincirinde kendisinden sonra alınacak differential(değişen) yedekleri etkilemektedir.

Differential backup: Son full backup gerçekleştiği andan itibaren değişen verilerin yedeğini alır. Bu nedenle gerçekleştirilebilmesi için önce full backup alınmış olması gerekir. Genel olarak full yedeklerden daha küçük olacakları ve daha az sürede ve süratli alınabildikleri için full yedekleme sonrasında periodik olarak çalıştırmaya yöneliktirler ve differential(fark) yedeklerinin yedekleme zinciri üzerinde herhangi bir etkisi bulunmamaktadır.

Transaction Log backup: Son transaction log yedeğinin alındığı andan itibaren veritabanında gerçekleşen tüm işlemlerin (transactions) yedeğini alır. Transaction Log yedeğinin alınabilmesi için önce bir full yedek alınmış olması gereklidir. Transactin Log yedekleri veritabanı işlemlerine ilişkin zaman içerisinde dönülebilecek bir nokta sunuyor olacaktır. Bu özellikle hatalı ve ya problem teşkil eden veri giriş çıkışlarına yönelik anlık geri dönülebilirlik imkanı sunar. Her log yedeğinden sonra varsayılan olarak sanal log dosyaları üzerindeki işlenmiş log kayıtları silinir ve doğal olarak bir sonraki log yedeği kendinden önceki log yedeğinden sonraki işlenmiş log kayıtlarını barındırır.

Uygulamamızda da kullanacağımız Transaction Log yedeklemesi, herhangi bir veritabanı için ancak Full Recovery Model etkinleştirildiğinde mümkün olabilir. Aksi taktirde Simple Recovery Model ile Transaction Log yedeklemesi yapılamaz.

Bir yedekleme stratejisinin en önemli özelliği bütünlüğüdür. Yani her yedek gerektiği zaman dönülebilir olmalı ve yedekleme zincirini bozmamalıdır. Örneğin elimizde yüz adet log yedeğimiz olsun. Onuncu yedek dosyasının bozulması durumunda kendini takip eden doksan yedek dosyası işe yaramaz hale gelecektir. Böyle bir durumla karşılaşmamak için yedek dosyaları arşivlenmeden önce muhakkak doğrulanmalıdır. (restore verifyonly)

Şimdi yukarıda bahsettiklerim doğrultusunda bir örnek yedekleme planı çıkaralım. Yedekleme sürelerinin kısaltılması ve yedek dosyalarının boyutlarının düşürülmesi açısından full ve differential(fark) veritabanı yedeklerini kurgulamaya başlayalım.

Full yedekler haftalık periyotlarda differential (fark) yedekler ise full yedeklerin alınmadığı günler günlük periyotlarda alınabilir. Bu kurguda ikinci gün alınan differential (fark) yedek ile yedinci gün alınan diffrential yedek arasında boyut farkı olacağını unutmamak gerekir. Bunun nedeni her differential (fark) yedeğin en son alınan full yedekten sonra yapılan değişiklikleri içermesidir.

Geri dönüş esnasında ise bir differential(fark) yedek bir full yedeğe bağlıdır. Yani differential(fark) yedeğin dönülebilmesi için ilk olarak full yedek dönülmelidir.Bu durumda verilen örnek için ikinci güne geri dönüş esnasında full yedek + ikinci gün differential(fark) yedeği, üçüncü güne geri dönüş esnasında full yedek + üçüncü gün differential (fark) yedeği kullanılmalıdır.

Differential(fark) yedek tanımını düşünürsek plansız aldığımız full yedekler, differential(fark) yedek zincirinin bozulmasına neden olurlar.

Varsayılan yedekleme seçenekleri ile plansız alınan bir full backup alınması durumunda beş,altı ve yedinci günlerde alınacak differential(fark) yedekler değişecektir ve geridönme ihtiyacında bu yedeğede ihtiyaç duyulacaktır. Düzensiz zamanlarda full yedek alınması istendiği zaman differential (fark) yedek zincirini bozmamak için COPY_ONLY özelliği ile yedek alınmalıdır. COPY_ONLY özelliği full yedek alırken veritabanı dosyalarının değişim tabanlı log sıra numaralarını değiştirmeyecektir ve yedekleme değişmesine neden olmayacaktır, aynı özellik log yedeği alırken yedeklenen işlenmiş log kayıtlarının silinmemesini sağlar, varsayılan özelliklerle log yedeği alındığı zaman sanal log dosyalarının içindeki işlenmiş log kayıtları silineceğine daha önce değinmiştik.

Şimdi gelelim log yedeklerimiz için nasıl bir yedekleme strajejisi belirlememiz gerektiğine.Düzenli log yedeği almanın yedek almak dışında amaçlarıda vardır. Başlıca nedenlerden birisi log dosyasının gereksiz yere büyümesini engellemektir. Log yedeği alınırken varsayılan özellik logu yedeklenen veritabanının sanal log dosyası içerisindeki işlenmiş log kayıtlarının silinmesidir. Bizlerin veritabını tanımlama esnasında belirlediğimiz log için mevcut olan ldf dosyası aslında birden fazla sanal log dosyasından oluşmaktadır ve bu sanal log dosyaları dolduğunda SQL Server yeni bir sanal log dosyası oluşturur. Bu durum disk seviyesinde log dosyasının büyümesi olarak gerçekleşir. Bu nedenlerden dolayı log yedeklerinin hangi sıklıkla yedekleneceğini belirlemeden önce log kullanımı gözlemlemek gerekir. Bu amaç için DBCC SQLPERF(logspace) komutu kullanılarak log kullanım yüzdeleri incelenebilir.

İyi tasarlanmış bir log yedek stratejisi sonucu log dosyaları büyümemelidir. Eğer ki log dosyası çok sık büyüme eğilimi gösteriyorsa log yedeklerinin frekansları azaltılarak daha sık aralıklarla log yedeği alınması gerekebilir. Bir log yedeğinin içeriğinde kendinden önce alınan log yedeğinden sonra gerçekleşen işlenmiş log işlemleri bulunur. Dolayısıyla istenilen bir ana dönmek için o ana gelene kadarki tüm log yedekleri sırası ile dönülmelidir. Bundan ötürü log yedeklerinin doğrulanarak güvenli ortamlarda saklanması çok önemlidir

Şimdiye kadar yedekleme tiplerinden ve yedekleme stratejilerini geliştirirken nelere dikkat edilmesi gerektiğinden bahsettik. Şimdi ise veritabanı yedekleme işlemini örneklerle detaylandırmaya çalışalım.

Veritabanı ve veritabanı loglarının yedeklenmesi “BACKUP” komutu temelinde gerçekleşir. Backup komutunun en temel kullanım notasyonu aşağıdaki gibidir.

--BOŞLUKLARI DOLDURMAK İÇİN CTRL+SHIFT+M TUŞ KOMBİNASYONUNA BASINIZ

BACKUP DATABASE <VERİTABANI ADI,VARCHAR,> TO DISK='<YEDEKLENECEK YERİ BELİRTİNİZ,VARCHAR,>'

Yukarıdaki komutun çalışması durumunda belirtilen veritabanının yedeği diskte belirtilen lokasyona yedeklenir. Bu komut ile alınan yedek full yedek tipinde bir yedektir.

Şimdi örneklerimiz için bir veritabanı oluşturalım ve log yedek alabilmek için bu veritabanının recovery durumunu full olarak set edelim.

CREATE DATABASE BACKUPTESTDB
GO
ALTER DATABASE BACKUPTESTDB SET RECOVERY FULL

Differential(fark) yedekleri veritabanındaki değişiklikleri LSN (Log Sequence Number – Log Sıra Numarası) ile takip eder ve bu bilgi de her full yedekten sonra ilgili sistem tablolarına yazılır. Bu bilgiye aşağıdaki information schemaya sorgu çekerek ulaşabiliriz.

SELECT differential_base_lsn,*

FROM sys.master_files

where database_id=DB_ID('BACKUPTESTDB')

Henüz yedek almadığımız için yukarıdaki sorgunun sonucu null olarak gelecektir. İlk full yedeğimizi alalım ve alınan full yedek hakkında bazı incelemeler yapalım.

BACKUP DATABASE BACKUPTESTDB TO DISK='D:\BACKUPTESTFULL_FULL1.BAK'

Yukarıdaki komutun çalışması ile beraber aşağıdaki şekilde sonuçlar alınır. Peki bize bu sonuçlar neler ifade etmektedir?

Yukarıdaki istatistiksel çıktı yedek işlemi esnasında ne kadarlık bir iş yapıldığını (330 veritabanı sayfası işlenmiş) , işlemin ne kadar sürdüğünü (0.018 saniyede yedekleme süreci tamamlanmış) ve yedek dosyasının diske yazılma hızı (saniyede 143.174 MB) hakkında bilgi verir.

İlk yedeğimizin tamamlanması ile birlikte aşağıdaki sorguyu çekmemiz durumunda veritabanımız için bir değişim tabanlı log sıra numarası üretildiğini görürüz.

SELECT differential_base_lsn,*

FROM sys.master_files

where database_id=DB_ID('BACKUPTESTDB')

SQL Server bu LSN (Log Sequence Number – Log Sıra Numarası) değerini daha sonra alınan differential(fark) yedekleri için kullanacak. Şimdi bir differential yedek alalım ve bu durumu gözlemleyelim. Differential (fark) yedeği veritabanı yedekleme işleminin bir özelliği olduğundan dolayı özel bir komut seti bulunmaz. Differential yedek almak için gerekli örnek komutumuz aşağıdaki gibidir.

BACKUP DATABASE BACKUPTESTDB TO DISK='D:\BACKUPTESTFULL_FULL1.BAK' WITH DIFFERENTIAL

Şimdi fark oluşturmak için örnek amaçlı oluşturduğumuz DB’ye tablo oluşturuyor ve rast gele kayıt atıyoruz.

USE BACKUPTESTDB

GO

CREATE TABLE TEST ( KOLON1 UNIQUEIDENTIFIER,KOLON2 UNIQUEIDENTIFIER,KOLON3 UNIQUEIDENTIFIER)

--10000 adet kayıtla dolduruyoruz

INSERT INTO TEST(KOLON1,KOLON2,KOLON3)

SELECT TOP 10000 NEWID(),NEWID(),NEWID()

FROM sys.all_columns A CROSS JOIN sys.all_columns B

GO

Şimdi Differential backup alıyoruz ;

BACKUP DATABASE BACKUPTESTDB TO DISK='D:\BACKUPTESTFULL_DIFF1.BAK' WITH DIFFERENTIAL

Şimdi de restore headeronly komutu ile differential(fark) yedeğin başlık bilgilerini görelim, burdaki amacım differential(fark) yedeğin log sıra numarasının full yedeği takip edip etmediğini görebilmemizdir.

Resimden göründüğü üzere LSN numaraları birbirleri ile aynı.Yani Alınan differential backup Full backupın devamı niteliğinde. Şimdi yeni bir differential(fark) yedeği alıp ikinci differential(fark) yedeğin birinci differential(fark) yedeği kapsadığını görelim. Yedeği almadan önce veritabanı üzerinde biraz daha işlem yaratalım.

Yeni Kayıt Eklemek için ;

INSERT INTO TEST(KOLON1,KOLON2,KOLON3)

SELECT TOP 1000000 NEWID(),NEWID(),NEWID()

FROM sys.all_columns A CROSS JOIN sys.all_columns B

GO

Yeni differential backup için ;

BACKUP DATABASE BACKUPTESTDB TO DISK='D:BACKUPTESTFULL_DIFF2.BAK' WITH DIFFERENTIAL

Alınan Farkların çıktıları karşılaştırılacak olursa full yedekten sonra veritabanı sayfalarının sayısı arttıkça differential(fark) yedek almak için işlenen veritabanı sayfa sayısı artıyor ve süre uzuyor.

Şimdi ise daha önce bahsettiğimiz gibi plansız bir şekilde alınan bir backup’ın bizim LSN değerini nasıl değiştirdiğini görelim. Bu işlem için öncelikli olarak bir backup alıyoruz.

BACKUP DATABASE BACKUPTESTDB TO DISK='D:\BACKUPTESTFULL_FULL2.BAK'

Tekrar kayıt ekliyoruz ;

INSERT INTO TEST(KOLON1,KOLON2,KOLON3)

SELECT TOP 1000 NEWID(),NEWID(),NEWID()

FROM sys.all_columns A CROSS JOIN sys.all_columns B

GO

Şimdi differential backup alıyoruz tekrar ;

BACKUP DATABASE BACKUPTESTDB TO DISK='D:\BACKUPTESTFULL_DIFF3.BAK' WITH DIFFERENTIAL

Sonuçtanda görüldüğü gibi full yedekten sonra differential(fark) yedeği daha az sayfanın işlenmesine neden oldu, şimdi differential(yedekteki) DatabaseBackupLSN değerlerinin değiştiğini görelim

Restore headeronly ile başlık bilgisi görüntülemek için ;

RESTORE HEADERONLY FROM DISK ='D:BACKUPTESTFULL_DIFF3.BAK'

Plansız bir Backup işleminin nasıl bizim lsn değerini değiştirmediğini gösterecek bir uygulama daha yapalım sizler ile.

Bu işlem için tekrar DB üzerinde işlem yapıyoruz ve kayıt ekliyoruz..

INSERT INTO TEST(KOLON1,KOLON2,KOLON3)

SELECT TOP 1000 NEWID(),NEWID(),NEWID()

FROM sys.all_columns A CROSS JOIN sys.all_columns B

GO

Şimdi LSN numarasını kontrol ediyoruz ;

SELECT differential_base_lsn,*

FROM sys.master_files

where database_id=DB_ID('BACKUPTESTDB')

Daha sonra tekrar COPY_ONLY kodu ile birlikte backup alıyoruz ;

BACKUP DATABASE BACKUPTESTDB TO DISK='D:\BACKUPTESTFULL_FULL3.BAK' WITH COPY_ONLY

Şimdi tekrar kontrol ettiğimizde LSN değerinin değişmediğini dolayısıyla yedekleme zincirinde herhangi bir sorun olmadığı gözlemlemiş oluyoruz.

Log yedeklerinde ise durum differential(fark) yedeklerinden farklıdır. Log yedekleri veritabanı log dosyaları içinde barınan işlenmiş log kayıtlarının yedeklenmesini sağlayacağını ve log yedeğinden sonra varsayılan olarak işlenmiş log kayıtlarını sileceğinden bahsetmiştik. Bu bahsedilenleri örneklerimizle görelim.

BACKUP LOG BACKUPTESTDB TO DISK='D:BACKUPTESTDB_LOG1.TRN'

Log dosyasını incelemeye başlıyoruz ;

RESTORE HEADERONLY FROM DISK='D:BACKUPTESTDB_LOG1.TRN'

Log yedek dosyasını incelediğimizde yedeklenen işlenmiş log kayıtlarının belirli aralıkta olduğunu görüyoruz. Log yedekleri tamamen sıralı yedeklerdir. Bir sonraki seferde alınacak log yedeği bu yedekteki Last LSN den başlayacaktır. Bunu gözlemlemek için aşağıdaki komutu kullanılarak sonucunu görelim.

Bu işlem için tekrar kayıt ekliyor;

INSERT INTO TEST(KOLON1,KOLON2,KOLON3)

SELECT TOP 1000 NEWID(),NEWID(),NEWID()

FROM sys.all_columns A CROSS JOIN sys.all_columns B

GO

Tekrar Log Backup alıyoruz ;

BACKUP LOG BACKUPTESTDB TO DISK='D:BACKUPTESTDB_LOG2.TRN'

Şimdi alınan LOG2 inceliyoruz ;

RESTORE HEADERONLY FROM DISK='D:BACKUPTESTDB_LOG1.TRN'

RESTORE HEADERONLY FROM DISK='D:BACKUPTESTDB_LOG2.TRN'

Gerekli inceleme sonrasıda gördüğümüz LSN değerleri birbirlerini takip ediyor olacaktır.

Şimdide log yedeği sonrasında işlenmiş log kayıtlarının silindiğini ve veritabanı log kullanımlarının azaldığı görelim. İlk olarak log dosyalarının büyümesine neden olan komutlar çalıştıralım ve sonrasında veritabanı log kullanım oranlarını görelim.

--VERİTABANINA 600000 KAYIT INSERT EDELIM VE LOG DOSYASI KULLANIM ORANLARINA BAKALIM

INSERT INTO TEST(KOLON1,KOLON2,KOLON3) SELECT TOP 200000 NEWID(),NEWID(),NEWID() FROM sys.all_columns A CROSS JOIN sys.all_columns B

INSERT INTO TEST(KOLON1,KOLON2,KOLON3) SELECT TOP 200000 NEWID(),NEWID(),NEWID() FROM sys.all_columns A CROSS JOIN sys.all_columns B

INSERT INTO TEST(KOLON1,KOLON2,KOLON3) SELECT TOP 200000 NEWID(),NEWID(),NEWID() FROM sys.all_columns A CROSS JOIN sys.all_columns B

--LOG KULLANIM YÜZDELERİNE BAKILIYOR

DBCC SQLPERF(LOGSPACE)

Log kullanım Sonucu ;

Database Name	Log Size (MB)	Log Space Used (%)	Status
BACKUPTESTDB	386	24,25351	0

Şimdi Tekrar bir Log Backup alıyoruz.

BACKUP LOG BACKUPTESTDB TO DISK='D:BACKUPTESTDB_LOG3.TRN'

Log Kullanım Durumu ;

Database Name	Log Size (MB)	Log Space Used (%)	Status
BACKUPTESTDB	386	2,29764	0

Yedekleme Stratejilerini bu şekilde sizlere aktarmış bulunuyorum. Bir sonraki makalede görüşmek üzere..