MSSQL Server Internal Database Snapshot

Bu makalede Database Snapshot konusunu ele almış olacağız. Database Snapshot veritabanının o anki değiştirilmez görüntüsünü almaktır. Anlık fotoğrafını çekmekte diyebiliriz. Snapshot oluşturduğumuzda fiziksel olarak bir database oluşturmuyoruz. Anlık olarak görüntüsü olan database’i okur. Bu database hiçbir zaman değişmez. Gerçek database üzerinde herhangi bir değişiklik Sparce File dediğimiz bir alana yazılmaktadır.

Snapshot olarak oluşturulmuş bir database’den  gerçek veritabanına bir geri dönüş olduğunda restore işlemi çok hızlı olmaktadır. Sebebi ise snapshot’ın gerçek bir veri içermemesidir. Çünkü veri hala gerçek veritabanı üzerinde!

Gerçek veritabanında birden fazla snapshot oluşturabilirsiniz. Ancak sayı arttıkça I/O de artar ve bu da sistem performansını düşürür.

Oluşturmuş olduğumuz snapshot asla bir yedekleme değildir. Ana veritabanında bir Admin veya bazı hatalar meydana gelirse Snapshot dan eski haline dönülür. Ancak şu çok önemlidir ki Snapshot Ana veritabanına bire bir bağlıdır ana veritabanı Silinirse ya da çökerse Snapshot dan ana veritabanına dönülemez. O yüzden Snapshot işlemi tam olarak bir Backup Stratejisi denemez.

Sql server’ın tüm sürümlerinde bu özellik mevcuttur.

Veritabanı anlık görüntülerinin nihai amacının veritabanında verimli değişiklikler sağlamak, alan kullanımından tasarruf etmek, yüksek düzeyde performans sağlamak ve raporlama, denetim ve analiz gibi etkinliklere yardımcı olmak olduğunu unutmamalıyız.

Aşağıdaki resim de yapının özeti verilmiştir. Bu resimde okuma işlemlerimiz gerçek veritabanına gelen sorguda hem kaynak database’de hem de sparce file değimiz alandan okunmaktadır. Aşağıdaki resimde de görüldüğü gibi  snapshot üzerinde select sorgusu çeken bir kullanıcı kaynak database üzerinde  çekilmiş olan resim üzerinde okuma işlemini yapmaktadır. Kullanıcı snapshot veritabanı ismi farklı olduğu için use snapshotveritabanı dedikten sonra bağlanıp okuma işlemi yapılmaktadır.

Snapshot, bir salt okunur kopyadır ve veritabanının oluşturulduğu zamandaki halini gösterir. Asıl veritabanına yapılan değişiklikler snapshot’ta görünmez.

• Snapshot, Copy-On-Write (COW) mekanizması ile çalışır.
• Sparse File (Seyrek Dosya) Teknolojisi kullanır.
• Gerçek veritabanındaki bir sayfa (page) değiştiğinde, orijinal hali snapshot dosyasına (.ss) yazılır.
• Snapshot’taki SELECT’ler, o zamanın “donmuş” haline erişir.

Peki COW Neden Önemli?

Gerçek veritabanında bir page değiştiğinde:

• SQL Server önce o page’in orijinal halini snapshot’a yazar.
• Sonra asıl veritabanını değiştirir.

Ama kullanıcı yalnızca bağlandığı kaynak üzerinden veri okur.

• Snapshot, sadece bir veri kaynağıdır.
• İki kaynağı birleştirip okuma yapmaz.

Snapshot veritabanı, başlangıçta neredeyse sıfır boyutludur (çok küçük bir boyuta sahiptir) ve zamanla gerçek veritabanında yapılan değişiklikler kadar büyür.

Database Snapshot şu şekilde çalışır:

1. Snapshot oluşturulduğunda:
   • Gerçek veritabanındaki hiçbir veri kopyalanmaz. Snapshot oluşturulduğunda, veritabanının fiziksel dosyaları (.mdf, .ldf) referans alınır.
   • Sadece metadata ve boş bir .ss (sparse file) dosyası oluşturulur.
2. Gerçek veritabanındaki bir page (sayfa) değiştiğinde:
   • SQL Server önce orijinal halini snapshot dosyasına yazar (Copy-on-Write).
   • Sonra asıl veritabanı güncellenir.
   • Snapshot sorgulandığında, eğer bir sayfa değişmemişse doğrudan ana veritabanından okunur; değişmişse snapshot’taki eski versiyon kullanılır.

Bu yüzden, snapshot dosyası sadece değişen sayfaların orijinal hallerini tutar.

Başka bir kullanıcı, snapshot veritabanında, önceden gerçek veritabanında hiç değişime uğramamış bir tabloyu okumak isterse verileri bulabilecek mi?

Evet, bulabilecek. Neden?

Snapshot veritabanı, oluşturulduğu anda gerçek veritabanının tamamının mantıksal bir kopyasıdır. Ama fiziksel olarak sadece değişen sayfaları yedekler (Copy-on-Write).

Dolayısıyla:

• Eğer bir tablo hiç değişmemişse:
  • Snapshot, o tabloyu doğrudan gerçek veritabanının MDF dosyasından okur.
  • Çünkü snapshot, değişmemiş sayfalarda gerçek veritabanına başvurur.
• Bu sayede kullanıcı o tabloyu sorunsuzca okuyabilir.

Örnek üzerinden konunun daha iyi anlaşılacağı kanaatindeyim. Bunun için ilk başta AdventureWorks2016 veritabanının snapshot alma işlemini ele alalım. Bunun  için ilk başta snapshot alacağımız veritabanı altında kaç tane Data File olduğuna bakıyoruz. Çünkü Snapshot alacağımız zaman tüm Data File’ların belirtilmesi gerekiyor. Bunun için ilk başta AdventureWorks2016 veritabanının properties’a tıklayıp gelen ekranda Files bölümünde   Data File’lara bakıyoruz.

AdventureWorks2016 veritabanı altında Files bölümünde tek bir Data File olduğunu görüyoruz. Uzantısı ss veya snap olabilir.

SQL Server Management Studio görsel olarak snapshot alma işlemini desteklemediğinden işlemlerin T-SQL kodu ile yapılması gerekmektedir. Oluşturulan snapshot’lar SSMS’de “Database Snapshots” bölümünü altında yer alır.

CREATE DATABASE AdventureWorks2016_SNAPSHOT ON

( NAME = AdventureWorks2016_Data, FILENAME = 'C:\veritabanı\AdventureWorks2016_Data.ss' )

AS SNAPSHOT OF AdventureWorks2016;

GO

Eğer ikinci bir data file bulunsaydı kodumuz aşağıdaki şekilde olmuş olacaktı.

CREATE DATABASE AdventureWorks2016_SNAPSHOT ON 
(
    NAME = AdventureWorks2016_Data,
    FILENAME = 'C:\veritabanı\AdventureWorks2016_Data.ss'
),
(
    NAME = AdventureWorks2016_Data2,
    FILENAME = 'C:\veritabanı\AdventureWorks2016_Data2.ss'
)
AS SNAPSHOT OF AdventureWorks2016;

Başarılı bir şekilde Snapshot işlemimizi oluşturduk. Databases sekmesi altında Database Snapshots kısmında AdventureWorks2016 veritabanımızın snapshot’ının geldiğini görmüş oluyoruz.

Snapshot işleminin veritabanının o anki fotoğrafı olduğunu söylemiştik. Aşağıdaki resimde tüm tablolarımızın geldiğini görmüş oluyoruz. Almış olduğumuz snapshot üzerinde herhangi bir değişikle veya silme işlemine izin verilmez. Çünkü snapshot okuma modun da sadece okuma işlemlerimiz yapılmaktadır. Bu yapı veritabanı üzerindeki yoğunluğu da azaltabiliriz. Database mirroring teknolojisinde secondary sunucusunda da kullanılabilir bir yapı ikinci örneğimiz bu olmuş olacak şimdi kaldığımız yerden devam edelim.

Gerçek veritabanımıza bir select sorgusu geldiğinde  orijinal veritabanında bir değişiklik olmadığı için orijinal veritabanına gider. Orijinal veritabanında bir değişiklik olursa bu değişiklikler Snapshot için diste ayrılmış olan  sparce file alana yazılmaktadır. Orijinal veritabanında bir veri değişikliğe uğradıysa kullanıcı select çektiğinde  verinin değişime uğramış hali sparse file’a yazılır ve buradan okunmaktadır. Aşağıdaki belirtilen adresten.

Şimdi gerçek veritabanımızdan birkaç  tablo silelim veya yeni bir tablo ekleyelim.

Daha sonra AdventureWorks2016 veritabanından birkaç tablo silelim.

USE [AdventureWorks2016]
GO
DROP TABLE [dbo].[DatabaseLog]
DROP TABLE [dbo].[ErrorLog]
GO

Gerçek veritabanımızda iki dbo uzantılı tablomuz yok ve YUNUS tablosu eklenmiş durumda. Şimdi snapshot oluşturduğumuz database kısmına gelelim yapılan değişikliklerin yansıyıp yansımadığını gözlemleyelim.

Snapshot olarak oluşturduğumuz database altına bu değişikliklerin gelmediğini görmüş oluyoruz. Çünkü snapshot işlemini yukarıdaki değişikliklerden önce yapmıştım.

Gerçek veritabanında yapılan her değişiklik sparce file kısmına yazıldığı için bu file’ın olduğu diskte belli bir süreden sonra yer olmadığında  snapshot olarak aldığımız database suspect moduna düşmektedir. Bununda dikkat edilmesi gerekmektedir. Çözüm olarak oluşturmuş olduğumuz snapshot’ın silinmesi gerekmektedir.

Şimdi gerçek veritabanımızda yukarıda yaptığımız gibi istemediğimiz tablolar silinmiş bunun için snapshot veritabanından dönülür. Ama bu dönüş mevcut database üzerine eklediğimiz tabloların veya verilerinde kaybedilmesine  sebebiyet verecektir.

Şimdi oluşturmuş olduğumuz database snapshot’tan dönelim.

Snapshot veritabanının boyutu aşağıdaki komut ile bulunur. Sonuç olarak snapshot boyutu 207 MB olarak görülmektedir.

SELECT  DB_NAME(sd.source_database_id) AS [SourceDatabase],
        sd.name AS [Snapshot],
        mf.name AS [Filename],
        size_on_disk_bytes/1024 AS [size_on_disk (KB)],
        mf2.size/128 AS [MaximumSize (MB)]
FROM sys.master_files mf
JOIN sys.databases sd
    ON mf.database_id = sd.database_id
JOIN sys.master_files mf2
    ON sd.source_database_id = mf2.database_id
    AND mf.file_id = mf2.file_id
CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse = 1
AND mf2.is_sparse = 0
ORDER BY 1;

İnstance altında hangi database de snapshot yapılmışsa aşağıdaki komut ilgili snapshot’ı bulmaktadır. Bu komut sonucunda detaylı bilgiler yazmaktadır. source_database_id kısmında null olmayanları getirmektedir.

select * from sys.databases where source_database_id is not null

Gerçekte bulunan veritabanının boyutuna bakılınca MB değerininde snapshottan daha yüksek olduğunu görüyoruz. Çünkü yeni değerler eklemiş oldum.

Not: Snapshottan backup alıp instance’ımıza restore edemeyiz. Snapshot veritabanının Backup’ı alınmaz. snapshot veritabanının log dosyası olmaz. Gerçek sistemde snapshot yapılmış bir veritabanının üzerine restore işlemi yapılamaz.  Bir veritabanının Snapshot’ı alınmışsa gerçek sistemde bu  database silinemez. Önce snapshot’ın silinmesi gerekmektedir. Snapshot’ı ise rahatlıkla silebiliriz.

Herhangi bir sorun anında  Snapshot verisini gerçek veritabanı üzerine  dönebilmek için gerçek veritabanına kullanıcıların bağlı olmaması gerekmektedir. Kullanıcıların bağlı olması durumunda geri dönme sırasında aşağıdaki hata alınır.

RESTORE cannot process database ‘A’ because it is in use by this session. It is recommended that the master database be used when performing this operation.

USE master
GO
ALTER DATABASE AdventureWorks2016 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks2016
FROM DATABASE_SNAPSHOT = 'AdventureWorks2016_SNAPSHOT'
ALTER DATABASE AdventureWorks2016 SET MULTI_USER WITH NO_WAIT

Snapshot’ın başarılı bir şekilde veritabanı üzerine restore edildiğini görmüş oluyoruz. Resimde görüldüğü gibi silinen tablolarımız gelmiş oldu. Gerçek veritabanımıza kaydetmiş olduğumuz YUNUS adındaki tablonunda kaybolduğunu görmüş oluyoruz. Çünkü Database Snapshot değişikliklerden önceki verileri çekmiştik.

Yazımızı bitirmeden snapshot veri tabanları konusunda söylememiz gereken son bir konu da, snapshotların kendi başlarına bir veritabanı olmadıkları ve sistemde yaşamaları kaynak veri tabanlarının sistemde yaşıyor olmasına bağlıdır. Bu demek oluyor ki Snapshot işlemini bir yedekleme aracı olarak kullanamayız.

Peki snapshot nerede kullanabiliriz veri tabanına bir kolon ekleme, index tanımlama gibi sunucu baştan tahmin edilemeyecek işlemler öncesinde geçici bir yedekleme için kullanılabilir ve işlem bittikten sonra mutlaka sistemden silinmelidirler.

Kendi sistemimde Database Mirroring yapmış olduğum bir database’im var. Bu database ikinci sunucuda restoring modda olduğu için veritabanı okunmaz mirror veritabanında  okuma işlemini yapabilmek için ikinci sunucuda database snapshot işlemi yaparak okuma  işlemlerimizi yapabiliriz. Mirror sunucusunda snapshot yapılarak rapor çekilebilir. İlk başta principal ve mirror veritabanımızı görelim.

Principal veritabanımız

Mirror veritabanımız, öncelikle Mirror olan veritabanımızın senkron olması gelmektedir.

Mirror veritabanımız Restoring mod da olduğu için  her hangi bir işlem yapılamaz.

Mirror database üzerinde okuma işlemi yapacağım için Snapshot Database işlemi yapıyorum. İlgili A veritabanı altında hangi data file ismi varsa o ismi aşağıdaki name kısmına yazmamız gerekiyor. Principal veritabanı altında ilgili data file ismine aşağıdaki resimde görülmektedir. Çünkü principal veritabanı altında bulunan data file mirror veritabanındada bulunmaktadır. Kaç tane Data File varsa Snapshot oluşturacağımız zaman tanımlanması gerekmektedir.

CREATE DATABASE A_SNAPSHOT ON
( NAME = A, FILENAME = 'C:\veritabanı\A.ss' )
AS SNAPSHOT OF A;
GO

Yukarıdaki kod bloğunu mirror veritabanımın olduğu sunucuda çalıştırdığımda Database Snapshot’ın oluştuğunu görmüş oldum. Mirroring teknolojisinde çok kullanılan bir yöntemdir.

Şimdi Snapshot oluşturduğumuz veritabanına select çekelim bakalım verilerimiz gelecek mi?

Evet resimde görüldüğü gibi  Mirror veritabanımız restoring modda ama Database Snapshot aldığımız için verilerimizi okuyabiliyoruz. Bu yapı ile principal veritabanı üzerinde yükü azaltabiliriz. Şunu da belirtmek gerekir ki Principal veritabanında yeni bir tablo veya insert yapıldığında mirror sunucusundaki  database snapshot’a yansımaz. Güncel veri için tekrar database snapshot alınması lazım mirror database üzerinde.

Microsoft’un sayfasında alınmış kodu çalıştırdığımızda Snapshot olan databaseler görülmektedir.

SELECT  DB_NAME(sd.source_database_id) AS [SourceDatabase],
        sd.name AS [Snapshot],
        mf.name AS [Filename],
        size_on_disk_bytes/1024 AS [size_on_disk (KB)],
        mf2.size/128 AS [MaximumSize (MB)]
FROM sys.master_files mf
JOIN sys.databases sd
    ON mf.database_id = sd.database_id
JOIN sys.master_files mf2
    ON sd.source_database_id = mf2.database_id
    AND mf.file_id = mf2.file_id
CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse = 1
AND mf2.is_sparse = 0
ORDER BY 1;

Bir internal database snapshot oluşturulduğunda ne olur.

1. 10GB’lık bir veritabanında:
   • Snapshot oluşturulduğunda: ~40KB (metadata)
   • 1GB veri değişirse: ~1GB boyutuna ulaşır.
   • 5GB veri değişirse: ~5GB boyutuna ulaşır.
2. Read-heavy sistemde:
   • Çok az yazma varsa: birkaç MB seviyesinde kalır.
3. Write-heavy sistemde:
   • Yoğun yazma varsa: orijinal DB boyutuna yaklaşabilir.

Oluşturulan snapshot’un boyutunu görmek için aşağıdaki komut kullanılmaktadır.

-- Snapshot dosya boyutlarını görüntüleme
SELECT 
    name AS 'Snapshot Name',
    physical_name AS 'File Path',
    size/128.0 AS 'Current Size (MB)',
    growth/128.0 AS 'Growth Increment (MB)'
FROM 
    sys.master_files
WHERE 
    DB_NAME(database_id) LIKE '%Snapshot%';

Bu makalede Database Snapshot işleminin  ne işe yaradığını nasıl yapıldığını uygulamalı bir şekilde ele  almış oldum.

Başka bir makalede görüşmek dileğiyle.

“Onlar gaybe inanırlar, namazı dosdoğru kılarlar, kendilerine rızık olarak verdiğimizden de Allah yolunda harcarlar.” Bakara-3