Bu makalede SQL Server 2017 versiyonuyla gelen DMV ‘ler ile kullanımı kolaylaşan bir özellik olan Smart Backup dan bahsedeceğim.
Bu makaleye başlamadan şunu belirtmekte fayda var; bu özelliği üçüncü parti yazılımlar veya eklentiler ile yapabilmek mümkün MSSQL haricinde diğer ilişkisel veri tabanı sistemlerinde de buna benzer özellikler tanımlanabilir, kullanılabilir.
Bu makalemizin anlaşılabilirliğini artırmak adına öncelikle performans ve veri bütünlüğü açısından hayati öneme sahip olan Checkpoint kavramından bahsetmek istiyorum; SQL Server, veri değişikliklerini önce RAM’de (buffer cache) tutar, bu sırada log dosyasına (LDF) işlemi yazar. Checkpoint olduğunda, bu değişmiş (dirty) geçici verileri .LDF dosyasından kalıcı hale getirir .MDF dosyasına (diske) yazar. Checkpoint, varsayılan olarak her 60 saniyede bir çalışır ancak SQL Server 2012 ile indirect checkpoints tanıtıldı ve veri tabanı bazlı olarak ayarlanabilir.
Şimdi iki Checkpoint yönetimine daha yakından bakarak artı ve eksilerinden bahsedelim; Otomatik checkpoint modunda, tüm buffer pool’daki sayfalar taranır ve değişmiş sayfalar bulunur bu ilk baktığımızda bizim için olumlu gibi görünse de aslında sisteme ek bir CPU maliyeti oluşturacaktır, Indirect Checkpoint modunda ise sadece değişmiş sayfalar işlenir bu da CPU kullanımını azaltır ve yedekleme performansını artırır.
Önceki makalelerde olduğu gibi, bu konuda da farklı senaryoları değerlendirebiliriz. Örneğin, varsayılan olarak her 60 saniyede bir gerçekleşen Checkpoint süresi, 120 ya da 180 saniyeye çıkarılabilir. Bu, sistemin daha seyrek aralıklarla buffer pool’daki değişmiş sayfaları taramasına neden olur. Ancak bu durumda, CPU kullanımı artabilir ve sistem beklenmedik şekilde kapanırsa kurtarma sürecinde riskler oluşabilir.
SQL Server 2017 ‘den önce son Full backuptan değişen page ‘leri anlamak için DBCC PAGE komutu sorgulanırdı, her GAM aralığında bir tane olmak üzere The Differential Changed Map (DCM) son Full backuptan sonraki değişen page ‘leri izleyebilmek için bitmap pointer görevini görüyordu. Şimdi bu anlattıklarımız üzerinden biraz test işlemleri yaparak anlamlandırmaya çalışalım;
Tablomuzu oluşturuyoruz ve içerisine bir miktar veri ekliyoruz.
use [AdventureWorks2017]
--Create the sample table
CREATE TABLE [dbo].[SmartBackupTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[authorName] [varchar](8000) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO
--insert 1000 dummy data
INSERT INTO SmartBackupTest values('SmartBackupTest Murat')
GO 1000
--select the sample data
select top 5 * From SmartBackupTest
Görsel – 1
--Backup the database
BACKUP DATABASE [AdventureWorks2017] TO DISK = 'D:\Data3\Backup\AdventureWorks2017Full.bak' WITH INIT
-- List data and index pages allocated to the 'AdventureWorks2017' table
DBCC IND('AdventureWorks2017',SmartBackupTest,-1)
GO
--set the trace flag
DBCC TRACEON(3604) --DBCC(Database Console Command) çıktıların sunucu tarafında değil management yazdırılmasını sağlar.
DBCC PAGE('AdventureWorks2017',1,6,3) WITH TABLERESULTS
GO
/*
Page File ID: 1
Page Number: 6
Output: 3
*/
Görsel – 2
Şimdi tablomuz üzerinde bazı verilerde değişiklik yapalım ve DIFF backup alalım.
UPDATE SmartBackupTest
SET authorName='SmartBackupTest Murat Kaan'
WHERE id in(34,42,61,56)
DBCC PAGE('AdventureWorks2017',1,6,3) WITH TABLERESULTS
Görsel – 3
BACKUP DATABASE [AdventureWorks2017] TO DISK = 'D:\Data3\Backup\AdventureWorks2017_Diff_1.bak' WITH DIFFERENTIAL;
Görsel – 4
Şimdi tekrar veri ekleyerek değişen page ‘leri belirleyelim
Insert into SmartBackupTest values('SmartBackupTest Murat'+ CAST(NEWID() AS VARCHAR(MAX)))
GO 100
DBCC PAGE('AdventureWorks2017',1,6,3) WITH TABLERESULTS
Görsel – 5
Tekrar bir DIFF backup alarak değişen page sayısına bakalım
BACKUP DATABASE [AdventureWorks2017] TO DISK = 'D:\Data3\Backup\AdventureWorks2017_Diff_2.bak' WITH DIFFERENTIAL;
Görsel – 6
Şimdi yapmış olduğumuz senaryoda FULL backup, ardından veri güncelleyerek ve veri ekleyerek DIFF backuplar ile değişen page sayısını görmüş olduk bunu yukarıdaki örnek olarak gösterdiğim DBCC ile yapmamız mümkün ancak bunun daha kolay ve anlaşılır yolu olarak SQL Server 2017 ile gelen DMV ‘ler bize kolaylık sağlayacaktır. https://www.sqlshack.com/top-8-new-enhanced-sql-server-2017-dmvs-dmfs-dbas/ makalesini okumanızı da tavsiye ederim.
Yeni gelen sys.dm_db_file_space_usage DMV ile gelen “modified_extent_page_count” alanı ile son alınmış olan FULL backuptan sonra değişen page sayısını gösterir.
SELECT file_id, total_page_count, modified_extent_page_count,
(modified_extent_page_count *100) / total_page_count [% Change]
FROM sys.dm_db_file_space_usage
Görsel – 7
Şimdi tekrar update ve insert yapalım ve farkı gözlemleyelim.
UPDATE SmartBackupTest SET authorName='SmartBackupTest Murat-1'
--
insert into SmartBackupTest values('SmartBackupTest Murat + '+ CAST(NEWID() AS VARCHAR(MAX)))
GO 100000
SELECT file_id, total_page_count, modified_extent_page_count,
(modified_extent_page_count *100) / total_page_count [% Change]
FROM sys.dm_db_file_space_usage
Görsel – 8
Görsel – 8 ‘de gördüğümüz üzere AdventureWorks2017 veri tabanında son FULL backuptan sonraki pagelerin değişim oranını ve değişen page sayısını görebiliyoruz bunu tercih ve ortamlarınıza yönelik olarak uyarlayabilir bu şekilde cursor oluşturarak backup senaryolarınızı belirleyebilirsiniz.
Görsel – 9
SQL Server 2017 ile sys.dm_db_log_stats fonksiyonunda bir geliştirmeler yapılmıştır. Bu fonksiyonun “log_since_last_log_backup_mb” sütunu ile alınan son FULL backuptan sonra log file (.ldf) da oluşan verinin miktarını gösterir. Bu sütun sayesinde oluşturulacak bir stored procedures ile sistemde bulunan veri tabanlarının log file kontrollerini yaptırabilir bu sayede kontrolümüz dışında gerçekleşecek bir log file şişme durumu ile karşılaşmayız. Peki ama ben job ayarlarım her saat başı veya her 15 dakika da LOG backup aldırırım neden bu yönteme ihtiyaç duyayım? diyebilirsiniz bu yöntem bize şunun için gereklidir; Sürekli bir LOG backup aldırarak sistemi ihtiyaç olmasa da backup maliyeti oluşturabilirsiniz bunun yerine ilgili stored procedures oluşturularak gerekli kontrolleri sağlar ve ihtiyacı olan veri tabanının backup alınmış olur. Aşağıda bu konuda örnek bir script bırakıyorum.
SQL Server 2017 ile gelen DMV ‘ler ve bize sağlamış oldukları Smart Backup yönteminden bahsettik, bir sonraki makalede görüşünceye dek iyi ki varsınız, sevgiler 🙂
Kaynak:
https://www.sqlshack.com/smart-database-backups-in-sql-server-2017/
https://ola.hallengren.com/ –SQL Server Smart Backup to Multiple Files basşlığı yardımcı olacaktır.
https://www.sqlshack.com/top-8-new-enhanced-sql-server-2017-dmvs-dmfs-dbas/
0 Yorum