SQL Server'de veri güvenilirliğini genişleten günlük ve veri depolama algoritmalarının açıklaması

Orijinal ürün sürümü: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Özgün KB numarası: 230785

Özet

Bu makalede, Microsoft SQL Server günlüğü ve veri algoritmalarının veri güvenilirliğini ve bütünlüğünü nasıl genişlettiği açıklanır.

Altyapıların temel kavramları ve Kurtarma ve YalıtımDan Yararlanma Semantiği Algoritması (ARIES) hakkında daha fazla bilgi edinmek için aşağıdaki Veritabanı Sistemlerinde ACM İşlemleri belgesine bakın (Birim 17, Sayı 1, Mart 1992'nin altında):

Dış bağlantı: ARIES: Write-Ahead Günlüğü Kullanarak Fine-Granularity Kilitlemeyi ve Kısmi Geri Almaları Destekleyen İşlem Kurtarma yöntemi

Belge, hatalarla ilgili olarak veri güvenilirliğini ve bütünlüğünü genişletmek için SQL Server tekniklerini ele alır.

Önbelleğe alma ve alternatif hata modu tartışmaları hakkında daha fazla bilgi için Microsoft Bilgi Bankası'ndaki aşağıdaki makaleleri okumanızı öneririz:

• SQL Server'de disk denetleyicilerini önbelleğe alma açıklaması

• Disk sürücüsü önbelleklerini her veritabanı yöneticisinin bilmesi gereken SQL Server kullanma hakkında bilgiler

Bu makalede kullanılan terimler

Ayrıntılı tartışmaya başlamadan önce, bu makalenin tamamında kullanılan terimlerden bazıları aşağıdaki tabloda tanımlanmıştır.

Write-Ahead Günlüğü (WAL) protokolü

Protokol terimi WAL'yi tanımlamak için mükemmel bir yoldur. Verilerin doğru şekilde depolandığından ve değiştirildiğinden ve bir hata olduğunda bilinen bir duruma kurtarılabildiğinden emin olmak için gereken belirli ve tanımlı bir uygulama adımı kümesidir. Bir ağ tutarlı ve korumalı bir şekilde veri alışverişi yapmak için tanımlanmış bir protokol içerdiği gibi WAL da verileri korumak için protokolü açıklar.

ARIES belgesi WAL'yi aşağıdaki gibi tanımlar:

WAL protokolü, bazı verilerde yapılan değişiklikleri temsil eden günlük kayıtlarının, değiştirilen verilerin geçici olmayan depolamadaki verilerin önceki sürümünü değiştirmesine izin verilmeden önce kararlı depolamada olması gerektiğini onaylar. Başka bir ifadeyle, sayfa güncelleştirmelerinin kararlı depolamaya yazıldığını açıklayan günlük kayıtlarının en azından geri alma bölümlerine kadar sistemin sayfanın kalıcı olmayan depolama sürümüne güncelleştirilmiş bir sayfa yazmasına izin verilmez.

Önceden yazma günlüğü hakkında daha fazla bilgi için SQL Server Books Online'daki Önceden Yazma İşlem Günlüğü konusuna bakın.

SQL Server ve WAL

SQL Server WAL protokolünü kullanır. Bir işlemin doğru şekilde işlendiğinden emin olmak için, işlemle ilişkili tüm günlük kayıtlarının kararlı depolama alanında güvenliği sağlanmalıdır.

Bu durumu netleştirmek için aşağıdaki özel örneği göz önünde bulundurun.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Ardından, aşağıdaki tabloda açıklandığı gibi etkinliği basit günlüğe kaydetme adımlarına bölün.

"Kilitleme" ve "günlüğe kaydetme" terimleri kafanız karışmasın. Önemli olsa da, WAL ile ilgilenirken kilitleme ve günlüğe kaydetme ayrı sorunlardır. Önceki örnekte, SQL Server genellikle sayfa 150'deki mandalı, işlemin tüm zamanını değil, sayfadaki fiziksel ekleme değişikliklerini gerçekleştirmek için gereken süre boyunca tutar. Satır, aralık, sayfa veya tabloyu gerektiği gibi korumak için uygun kilit türü oluşturulur. Kilit türleri hakkında daha fazla bilgi için SQL Server Kitaplar Çevrimiçi kilitleme bölümlerine bakın.

Örne daha ayrıntılı bir şekilde baktığınızda LazyWriter veya CheckPoint işlemleri çalıştırıldığında ne olduğunu sorabilirsiniz. SQL Server, kirli ve sabitlenmiş sayfayla ilişkilendirilmiş işlem günlüğü kayıtları için kararlı depolamaya tüm uygun temizleme işlemlerini gerçekleştirir. Bu, ILIŞKILI işlem günlüğü kayıtları boşaltılana kadar WAL protokolü veri sayfasının hiçbir zaman kararlı depolamaya yazılmamasını sağlar.

SQL Server ve kararlı depolama

SQL Server, disk kesim boyutları (genellikle 4.096 bayt veya 512 bayt) bilgilerini ekleyerek günlük ve veri sayfası işlemlerini geliştirir.

bir işlemin ACID özelliklerini korumak için SQL Server hata noktalarını hesaba eklemesi gerekir. Bir hata sırasında, birçok disk sürücüsü belirtimi yalnızca sınırlı sayıda kesim yazma işlemi garanti eder. Çoğu belirtim, bir hata oluştuğunda tek bir kesim yazmanın tamamlanmasını garanti eder.

SQL Server, kesim boyutunun katlarında 8 KB veri sayfaları ve günlük (boşaltıldıysa) kullanır. (Çoğu disk sürücüsü varsayılan kesim boyutu olarak 512 bayt kullanır.) Bir hata varsa, SQL Server günlük eşlik ve yırtılmış yazma teknikleri kullanarak bir kesimden daha büyük yazma işlemlerini hesaba katabilir.

Yırtılmış sayfa algılama

Bu seçenek, SQL Server güç kesintilerinden veya diğer sistem kesintilerinden kaynaklanan tamamlanmamış G/Ç işlemlerini algılamasına olanak tanır. Doğru olduğunda, sayfa diske her yazıldığında 8 kilobaytlık (KB) veritabanı sayfasında her 512 baytlık kesim için bir bit çevrilmeye neden olur. Sayfa daha sonra SQL Server tarafından okunduğunda bir bit yanlış durumdaysa, sayfa yanlış yazılmıştır; yırtılmış bir sayfa algılanır. Kurtarma sırasında hatalı yazılmış tüm sayfaların kurtarma tarafından okunma olasılığı yüksek olduğundan yırtılmış sayfalar algılanır.

SQL Server veritabanı sayfaları 8 KB olsa da, diskler G/Ç işlemlerini 512 baytlık bir kesim kullanarak gerçekleştirir. Bu nedenle veritabanı sayfası başına 16 kesim yazılır. İşletim sisteminin diske ilk 512 baytlık kesimi yazması ile 8 KB G/Ç işleminin tamamlanması arasında sistem başarısız olursa (örneğin, güç kesintisi nedeniyle) yırtılmış bir sayfa oluşabilir. Veritabanı sayfasının ilk kesimi hatadan önce başarıyla yazılırsa, disk üzerindeki veritabanı sayfası güncelleştirilmiş olarak görünür, ancak başarılı olmayabilir.

Pil destekli disk denetleyicisi önbelleklerini kullanarak verilerin diske başarıyla yazıldığından veya hiç yazılmadığından emin olabilirsiniz. Bu durumda, bu gerekli olmadığından yırtılmış sayfa algılamayı "true" olarak ayarlamayın.

Günlük eşlik

Günlük eşlik denetimi, yırtılmış sayfa algılamaya benzer. Her 512 baytlık kesim eşlik bitleri içerir. Bu eşlik bitleri her zaman günlük kaydıyla yazılır ve günlük kaydı alındığında değerlendirilir. Günlük yazma işlemlerini 512 baytlık bir sınıra zorlayarak, SQL Server işleme işlemlerinin fiziksel disk kesimlerine yazıldığından emin olabilir.

Performans etkileri

SQL Server tüm sürümleri Win32 CreateFile işlevini kullanarak günlük ve veri dosyalarını açar. dwFlagsAndAttributes üyesi, SQL Server tarafından açıldığında seçeneğini içerirFILE_FLAG_WRITE_THROUGH.

FILE_FLAG_WRITE_THROUGH sisteme herhangi bir ara önbellek üzerinden yazmasını ve doğrudan diske gitmesini sağlar. Sistem yine de yazma işlemlerini önbelleğe alabilir, ancak bunları tamamen boşaltamaz.

seçeneği FILE_FLAG_WRITE_THROUGH , yazma işlemi başarılı bir tamamlama döndürdüğünde verilerin kararlı depolamada doğru şekilde depolanmasını sağlar. Bu, verilerin sağlanmasını sağlayan WAL protokolüyle hizalanır.

Birçok disk sürücüsü (SCSI ve IDE), 512 KB, 1 MB veya daha büyük yerleşik önbellekler içerir. Ancak, sürücü önbellekleri genellikle pil destekli bir çözüm değil, bir kapasitör kullanır. Bu önbelleğe alma mekanizmaları, bir güç döngüsü veya benzer bir hata noktası arasında yazma garantisi vermez. Yalnızca kesim yazma işlemlerinin tamamlanmasını garanti eder. Yırtılmış yazma ve günlük eşlik algılamasının SQL Server 7.0 ve sonraki sürümlerde yerleşik olmasının nedeni budur. Sürücülerin boyutu artmaya devam ettikçe önbellekler daha büyük hale gelir ve bir hata sırasında daha fazla miktarda veriyi açığa çıkarabilir.

Birçok donanım satıcısı, pil destekli disk denetleyicisi çözümleri sağlar. Bu denetleyici önbellekleri önbellekteki verileri birkaç gün boyunca koruyabilir ve hatta önbelleğe alma donanımının ikinci bir bilgisayara yerleştirilmesine izin verebilir. Güç doğru şekilde geri yüklendiğinde, daha fazla veri erişimine izin verilmeden önce yazılmamış veriler boşaltılır. Bunların çoğu, en iyi performans için okuma ve yazma önbelleği yüzdesinin oluşturulmasına izin verir. Bazıları büyük bellek depolama alanları içerir. Aslında, bazı donanım satıcıları pazarın belirli bir kesimi için 6 GB önbellek ile üst düzey pil destekli disk önbelleğe alma denetleyicisi sistemleri sağlar. Bunlar veritabanı performansını önemli ölçüde geliştirebilir.

Gelişmiş önbelleğe alma uygulamaları, sistem sıfırlama, güç kesintisi veya başka bir hata noktası durumunda gerçek yeniden yazma özellikleri sağlayabildiği için denetleyici önbelleğini devre dışı bırakmayarak isteği işler FILE_FLAG_WRITE_THROUGH .

Sürücü başlıklarını, dönüş hızlarını ve diğer sınırlayıcı faktörleri taşımak için gereken mekanik süre nedeniyle önbellek kullanılmadan G/Ç aktarımları daha uzun olabilir.

Sektör sıralaması

G/Ç performansını artırmak için kullanılan yaygın bir teknik sektör sıralamadır. Mekanik kafa hareketini önlemek için okuma/yazma istekleri sıralanır ve başın verileri alması veya depolaması için daha tutarlı bir hareket sağlar.

Önbellek birden çok günlük ve veri yazma isteğini aynı anda tutabilir. WAL protokolü ve WAL protokolünün SQL Server uygulaması, sayfa yazmanın düzenlenebilmesi için önce günlük yazma işleminin kararlı depolamaya boşaltılması gerekir. Ancak önbelleğin kullanılması, veriler gerçek sürücüye (kararlı depolamaya yazılır) yazılmadan günlük yazma isteğinden başarı döndürebilir. Bu, veri sayfası yazma isteğinin SQL Server yol açabilir.

Yazma önbelleği katılımıyla veriler yine de geçici depolama alanında olarak kabul edilir. Ancak Win32 API WriteFile çağrısından etkinliği tam olarak SQL Server nasıl gördüğü başarılı bir dönüş kodu elde edildi. SQL Server veya WriteFile API çağrısını kullanan herhangi bir işlem yalnızca verilerin kararlı depolamayı doğru şekilde edindiğini belirleyebilir.

Tartışma amacıyla, veri sayfasının tüm kesimlerinin eşleşen günlük kayıtlarının kesimlerinden önce yazacak şekilde sıralandığını varsayalım. Bu, WAL protokolünü hemen ihlal eder. Önbellek, günlük kaydından önce bir veri sayfası yazıyor. Önbellek tamamen pille yedeklenmediği sürece, bir hata felaket sonuçlara neden olabilir.

Veritabanı sunucusu için en uygun performans faktörlerini değerlendirirken dikkate alınması gereken birçok faktör vardır. Bunlardan en önemlisi, "Sistemim geçerli FILE_FLAG_WRITE_THROUGH özelliklere izin verir mi?"

Test, birçok disk sürücüsü yapılandırmasının uygun pil Yedeklemesi olmadan yazma önbelleği içerebileceğini göstermiştir. SCSI, IDE ve EIDE sürücüleri yazma önbelleklerinden tam olarak yararlanır. SSD'lerin SQL Server ile birlikte nasıl çalıştığı hakkında daha fazla bilgi için aşağıdaki CSS SQL Server Mühendisleri Blog makalesine bakın:

SQL Server ve SSD'ler - RDORR'un Öğrenme Notları - Bölüm 1

Birçok yapılandırmada, bir IDE veya EIDE sürücüsünün yazma önbelleğini doğru bir şekilde devre dışı bırakmanın tek yolu, belirli bir üretici yardımcı programını kullanmak veya sürücünün kendisinde bulunan jumper'ları kullanmaktır. Yazma önbelleğinin sürücünün kendisi için devre dışı olduğundan emin olmak için sürücü üreticisine başvurun.

SCSI sürücülerinin yazma önbellekleri de vardır. Ancak bu önbellekler genellikle işletim sistemi tarafından devre dışı bırakılabilir. Herhangi bir soru varsa, uygun yardımcı programlar için sürücü üreticisine başvurun.

Yazma Önbelleği Yığınlama

Yazma Önbelleği Yığınlama, Kesim Sıralama'ya benzer. Aşağıdaki tanım doğrudan önde gelen bir IDE sürücüsü üreticisinin web sitesinden alınmıştır:

Normalde bu mod etkindir. Yazma önbelleği modu, arabellek dolana veya konak aktarımı tamamlanana kadar ana bilgisayar yazma verilerini arabelleğe kabul eder.

Disk yazma görevi, konak verilerini diske depolamaya başlar. Ana bilgisayar yazma komutları kabul edilmeye devam edilir ve yazma komut yığını dolana veya veri arabelleği dolana kadar veriler arabelleğe aktarılır. Sürücü, sürücü aktarım hızını iyileştirmek için yazma komutlarını yeniden sıralayabilir.

Otomatik Yazma Yeniden Konumlandırma (AWR)

Verileri korumak için kullanılan bir diğer yaygın teknik de veri işleme sırasında hatalı kesimleri algılamaktır. Aşağıdaki açıklama önde gelen bir IDE sürücüsü üreticisinin web sitesinden gelir:

Bu özellik, yazma önbelleğinin bir parçasıdır ve ertelenmiş yazma işlemleri sırasında veri kaybı riskini azaltır. Disk yazma işlemi sırasında bir disk hatası oluşursa, disk görevi durur ve şüpheli kesim, sürücünün sonunda bulunan alternatif kesimlerden oluşan bir havuza yeniden ayrılır. Yeniden ayırmanın ardından disk yazma görevi tamamlanana kadar devam eder.

Önbellek için pil yedeklemesi sağlanmışsa bu güçlü bir özellik olabilir. Bu, yeniden başlatma sırasında uygun değişiklikleri sağlar. Disk hatalarını algılamak tercih edilir, ancak WAL protokolünün veri güvenliği bunun ertelenmiş bir şekilde değil gerçek zamanlı olarak yapılmasını gerektirir. WAL parametrelerinde AWR tekniği, kesim hatası nedeniyle günlük yazma işleminin başarısız olduğu ancak sürücünün dolu olduğu bir durumu hesaba ekleyemez. İşlemin doğru şekilde durdurulabilmesi, yöneticinin uyarılabilmesi ve verilerin güvenliğini sağlamak ve medya hatası durumunu düzeltmek için doğru adımların atılması için veritabanı altyapısının hatadan hemen haberdar olması gerekir.

Veri güvenliği

Bir veritabanı yöneticisinin verilerin güvenliğini sağlamak için alması gereken çeşitli önlemler vardır.

• Yedekleme stratejinizin yıkıcı bir hatadan kurtarmak için yeterli olduğundan emin olmak her zaman iyi bir fikirdir. Site dışı depolama ve diğer önlemler uygundur.
• veritabanı geri yükleme işlemini ikincil bir veritabanında veya test veritabanında sık sık test edin.
• Önbelleğe alma cihazlarının tüm hata durumlarını (güç kesintisi, bozuk kesimler, hatalı sürücüler, sistem kesintisi, kilitlemeler, güç artışı vb.) işleyebildiğinden emin olun.
• Önbelleğe alma cihazınızın:
  • Tümleşik pil Yedeklemeye sahiptir
  • Açma/kapamada yazmaları yeniden verebilir
  • Gerekirse tamamen devre dışı bırakılabilir
  • Hatalı kesim yeniden eşlemeyi gerçek zamanlı olarak işler
• Yırtılmış sayfa algılamayı etkinleştirin. (Bunun performans üzerinde çok az etkisi vardır.)
• Mümkünse, hatalı disk sürücüsünün çalışırken değiştirmesine izin veren RAID sürücülerini yapılandırın.
• İşletim sistemini yeniden başlatmadan daha fazla disk alanı eklemenize olanak sağlayan daha yeni önbelleğe alma denetleyicileri kullanın. Bu ideal bir çözüm olabilir.

Sürücüleri test etme

Verilerinizin güvenliğini tam olarak sağlamak için tüm verileri önbelleğe almanın doğru şekilde işlendiğinden emin olmanız gerekir. Birçok durumda, disk sürücüsünün yazma önbelleğini devre dışı bırakmanız gerekir.

Microsoft, yardımcı programını kullanarak çeşitli SCSI ve IDE sürücülerinde SQLIOSim test gerçekleştirmiştir. Bu yardımcı program, sanal veri cihazına ve günlük cihazına ağır zaman uyumsuz okuma/yazma etkinliğinin benzetimini sağlar. Test performansı istatistikleri, devre dışı yazma önbelleğe alma ve 5.200 ile 7.200 arasında RPM aralığına sahip bir sürücü için saniyede 50 ile 70 arasında ortalama yazma işlemlerini gösterir.

Yardımcı program hakkında SQLIOSim daha fazla bilgi için Microsoft Bilgi Bankası'ndaki aşağıdaki makaleye bakın:

SqlIOSim yardımcı programını kullanarak disk alt sistemindeki SQL Server etkinliğinin simülasyonunu oluşturma

Birçok bilgisayar üreticisi, yazma önbelleğini devre dışı bırakarak sürücüleri sipariş etmektedir. Ancak test her zaman böyle olmayabilir. Bu nedenle, her zaman tamamen test edin.

Veri cihazları

Günlüğe kaydedilmemiş durumlar dışında tüm durumlarda, SQL Server yalnızca günlük kayıtlarının boşaltılması gerekir. Günlüğe kaydedilmemiş işlemler yapılırken veri sayfalarının da kararlı depolama alanına boşaltılması gerekir; bir hata durumunda eylemleri yeniden oluşturmak için tek tek günlük kaydı yoktur.

LazyWriter veya CheckPoint işlemi bunları kararlı depolama alanına boşaltana kadar veri sayfaları önbellekte kalabilir. Wal protokolünü kullanarak günlük kayıtlarının doğru şekilde depolandığından emin olmak, kurtarmanın bir veri sayfasını bilinen bir duruma kurtaramasını sağlar.

Bu, veri dosyalarının önbelleğe alınmış bir sürücüye yerleştirildiğini göstermez. SQL Server veri sayfalarını kararlı depolama alanına boşalttığında, günlük kayıtları işlem günlüğünden kesilebilir. Veri sayfaları geçici önbellekte depolanıyorsa, hata durumunda bir sayfayı kurtarmak için kullanılacak günlük kayıtlarının kesilmesi mümkündür. Hem verilerinizin hem de günlük cihazlarınızın kararlı depolamayı doğru şekilde barındırdığından emin olun.

Performansı artırma

Size olabilecek ilk soru şudur: "Önbelleğe alınan bir IDE sürücüm var. Ama devre dışı bırakınca performansım beklenenden daha az oldu. Neden?"

Microsoft tarafından test edilen IDE sürücülerinin çoğu 5.200 RPM ve SCSI sürücüleri 7.200 RPM'de çalışır. IDE sürücüsünün yazma önbelleğini devre dışı bırakırsanız mekanik performans bir faktör haline gelebilir.

Performans farkını gidermek için izleyebileceğiniz yöntem açıktır: "İşlem hızını ele alın."

Birçok çevrimiçi işlem işleme (OLTP) sistemi yüksek işlem oranı gerektirir. Bu sistemler için, yazma önbelleğini uygun şekilde destekleyebilecek ve veri bütünlüğünü sağlamaya devam ederken istenen performans artışını sağlayabilecek bir önbelleğe alma denetleyicisi kullanmayı göz önünde bulundurun.

Önbelleğe alma sürücüsündeki SQL Server gerçekleşen önemli performans değişikliklerini gözlemlemek için küçük işlemler kullanılarak işlem hızı artırıldı.

Test, 512 KB'tan küçük veya 2 MB'tan büyük arabelleklerin yüksek yazma etkinliğinin yavaş performansa neden olabileceğini gösterir.

Aşağıdaki örneği göz önünde bulundurun:

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

aşağıda SQL Server için örnek test sonuçları verilmişti:

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

Tüm işlem serisini INSERT tek bir işleme sarmalama işlemi, tüm yapılandırmalarda yaklaşık dört saniye içinde çalışır. Bunun nedeni, gerekli olan günlük boşaltma sayısıdır. Tek bir işlem oluşturmazsanız, her INSERT biri ayrı bir işlem olarak işlenir. Bu nedenle, işlem için tüm günlük kayıtlarının boşaltılması gerekir. Her temizleme 512 bayt boyutundadır. Bunun için önemli mekanik tahrik müdahalesi gerekir.

Tek bir işlem kullanıldığında, işlemin günlük kayıtları paketlenebilir ve toplanan günlük kayıtlarını boşaltmak için tek, daha büyük bir yazma kullanılabilir. Bu, mekanik müdahaleyi önemli ölçüde azaltır.

Boşaltılan Günlük Baytları/sn değerinin azaltılıp azaltılamayacağını belirlemek için günlük boşaltma ile ilişkili deyimleri inceleyin. Önceki örnekte tek bir işlem kullanılmıştır. Ancak, birçok senaryoda bu istenmeyen kilitleme davranışına neden olabilir. İşlemin tasarımını inceleyin. Sık ve küçük günlük boşaltma etkinliğini azaltmak için toplu işleri çalıştırmak için aşağıdaki koda benzer bir kod kullanabilirsiniz:

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server, SQL Server G/Ç Güvenilirlik Programı Gözden Geçirme Gereksinimleri indirme belgesinde açıklandığı gibi sistemlerin kararlı medyaya garantili teslimi desteklemesini gerektirir. SQL Server veritabanı altyapısının giriş ve çıkış gereksinimleri hakkında daha fazla bilgi için bkz. Microsoft SQL Server Veritabanı Altyapısı Giriş/Çıkış Gereksinimleri.