Microsoft SQL Server'da Veri Senkronizasyonu Kılavuzu

İlgili bilgilerin yalıtılmış sistemler arasında paylaşılması, kuruluşların veri kalitesini ve kullanılabilirliğini iyileştirmelerine olanak tanıdığı için giderek daha önemli hale geldi. Birden fazla dizin sunucusunda kullanılabilir ve tutarlı bir veri kümesine sahip olmanın yararlı olduğu birçok durum vardır. Bu nedenle SQL Server veri senkronizasyonunu gerçekleştirmek için yaygın yöntemleri bilmek önemlidir.

Veri replikasyonu ve veri senkronizasyonu süreçleri ile veri kullanılabilirliği ve tutarlılığı sağlanabilir. Veri çoğaltma, hata toleransı veya erişilebilirlik geliştirme amacıyla bir veritabanının bir veya daha fazla yedek kopyasını oluşturma işlemidir. Veri senkronizasyonu, iki veya daha fazla veri tabanı arasında veri tutarlılığı ve söz konusu tutarlılığı korumak için müteakip sürekli güncellemeler oluşturma sürecidir.

Birçok kuruluşta, çeşitli sistemler arasında veri senkronizasyonu gerçekleştirmek hem arzu edilir hem de zordur. Veri senkronizasyonu yapmamız gereken birçok kullanım durumu bulabiliriz:

• Veritabanı taşıma
• Bilgi sistemleri arasında düzenli senkronizasyon
• Bir bilgi sisteminden diğerine veri aktarma
• Veri kümelerini farklı aşamalar veya ortamlar arasında taşıma
• Veritabanı olmayan bir kaynaktan veri içe aktarma

Veri senkronizasyonu için benzersiz bir yol veya oybirliğiyle kabul edilen bir yöntem yoktur. Bu görev durumdan duruma farklılık gösterir ve ilk bakışta basit olması gereken veri senkronizasyonları bile veri yapılarının karmaşıklığı nedeniyle karmaşık olabilir. Gerçek senaryolarda, veri senkronizasyonu, gerçekleştirilmesi uzun zaman alabilen birçok karmaşık görevden oluşur. Yeni bir gereksinim ortaya çıktığında, veritabanı uzmanları genellikle tüm senkronizasyon sürecini yeniden uygulamak zorundadır. Bunu yapmanın standart bir yolu olmadığından, çoğaltmanın yanı sıra, veri senkronizasyonu uygulamaları nadiren optimaldir. Bu, zor bakım ve daha yüksek masraflarla sonuçlanır. Veri senkronizasyonunun uygulanması ve sürdürülmesi o kadar zaman alıcı bir süreçtir ki, tek başına tam zamanlı bir iş olabilir.

Veri senkronizasyon görevleri için mimariyi manuel olarak, muhtemelen Microsoft Sync Framework kullanarak uygulayabiliriz veya Microsoft SQL Server'ı yönetmek için araçlar içinde önceden oluşturulmuş çözümlerden yararlanabiliriz. Microsoft SQL Server veritabanlarında veri senkronizasyonunu çözmek için kullanılabilecek en yaygın yöntem ve araçları açıklamaya ve bazı önerilerde bulunmaya çalışacağız.

Kaynak ve hedefin yapısına (örneğin, veritabanları, tablolar) dayanarak, yapıların benzer veya farklı olduğu kullanım durumlarını ayırt edebiliriz.

Kaynak ve Hedef Çok Benzer Yapılara Sahiptir

Bu, yazılım geliştirme yaşam döngüsünün çeşitli aşamalarında verileri kullandığımızda çok sık görülen bir durumdur. Örneğin, test ve üretim ortamlarındaki veri yapısı çok benzer. Ortak gereksinim, test ve üretim veri tabanı arasındaki verileri karşılaştırmak ve üretimden test veri tabanına veri aktarmaktır.

Kaynak ve Hedef Farklı Yapılara Sahiptir

Yapılar farklıysa, senkronizasyon daha karmaşıktır. Bu aynı zamanda daha sık tekrarlanan bir görevdir. Yaygın bir durum, bir veritabanından diğerine içe aktarmaktır. En yaygın durum, bir yazılım parçasının başka bir şirket tarafından sağlanan başka bir yazılım parçasından veri alması gerektiğidir. Genellikle, içe aktarmaların programlanmış bir temelde otomatik olarak çalışması gerekir.

Kullanılan yöntem, kişisel tercihlere ve çözmeniz gereken sorunun karmaşıklığına bağlıdır.

Yapılar ne kadar benzer olursa olsun, veri senkronizasyonunu çözmek için dört farklı yol seçebiliriz:

• El ile oluşturulan SQL komut dosyalarını kullanarak senkronizasyon
• Veri karşılaştırma yöntemini kullanarak senkronizasyon (yalnızca kaynak ve hedef benzer yapıya sahip olduğunda kullanılabilir)
• Otomatik olarak oluşturulan SQL komut dosyalarını kullanarak senkronizasyon - ticari ürün gerekir

Kaynak ve Hedef Aynı veya Çok Benzer Yapılara Sahip

El İle Oluşturulmuş SQL Komut Dosyalarını Kullanma

En basit ve sıkıcı çözüm, senkronizasyon için SQL komut dosyalarını manuel olarak yazmaktır.

Avantajlar

• Ücretsiz ve açık kaynak (FOSS) araçları ile gerçekleştirilebilir.
• Tablonun indeksleri varsa, çok hızlıdır.
• SQL komut dosyası, saklı bir prosedüre kaydedilebilir veya SQL Server için bir iş olarak periyodik olarak çalıştırılabilir.
• Sürekli değişen verilerde bile otomatik içe aktarma olarak kullanılabilir.

Dezavantajları

• Böyle bir SQL komut dosyası oluşturmak oldukça sıkıcıdır, çünkü her tablo için genellikle üç komut dosyası gerekir: INSERT , UPDATE ve DELETE .
• Yalnızca SQL sorguları aracılığıyla kullanılabilen verileri senkronize edebilirsiniz, böylece CSV ve XML dosyaları gibi kaynaklardan içe aktaramazsınız.
• Bakımı zordur—veritabanı yapısı değiştirildiğinde, iki veya üç betiği ( INSERT , UPDATE ve bazen de DELETE ) değiştirmek gerekir.

Örnek vermek

ID ve Value sütunlarına sahip Source tablosu ile aynı sütunlara sahip Target tablosu arasında senkronizasyon yapacağız.

Tablolar aynı birincil anahtara sahipse ve hedef tablonun otomatik artan (kimlik) birincil anahtarı yoksa, aşağıdaki senkronizasyon komut dosyasını çalıştırabilirsiniz.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Veri Karşılaştırma Yöntemini Kullanma

Bu yöntemde, kaynak ve hedef verileri karşılaştırmak için bir araç kullanabiliriz. Karşılaştırma işlemi, kaynak veritabanındaki farklılıkları hedef veritabanına uygulayan SQL komut dosyaları oluşturur.

Veri karşılaştırma ve senkronizasyon için bir dizi program vardır. Bu programlar çoğunlukla aynı yaklaşımı kullanır. Kullanıcı, kaynağı ve hedef veritabanını seçer, ancak diğer alternatifler bir DB yedeği, SQL komut dosyaları içeren bir klasör veya hatta bir kaynak kontrol sistemine bağlantı olabilir.

Aşağıda, veri karşılaştırma yaklaşımını kullanan en popüler araçlar yer almaktadır:

• SQL Server için dbForge Veri Karşılaştırma
• RedGate SQL Veri Karşılaştırması
• Apex SQL Veri Farkı

İlk adımda, veriler okunur veya yalnızca kaynaktan ve hedeften gelen daha büyük verilerin sağlama toplamları okunur. Ardından karşılaştırma işlemi gerçekleştirilir.

Bu araçlar ayrıca senkronizasyon için ek ayarlar sunar.

Veri senkronizasyonu için gerekli olan aşağıdaki yapılandırma seçeneklerini ayarlamamız gerekiyor:

Senkronizasyon Anahtarı

Varsayılan olarak, birincil anahtar veya UNIQUE kısıtlaması kullanılır. Birincil anahtar yoksa, bir sütun kombinasyonu seçebilirsiniz. Senkronizasyon tuşu, kaynağın satırlarını hedefin satırlarıyla eşleştirmek için kullanılır.

Tablo Eşleştirme

Varsayılan olarak, tablolar ada göre eşleştirilir. Bunu değiştirebilir ve kendi ihtiyaçlarınıza göre eşleştirebilirsiniz. dbForge Veri Karşılaştırma yazılımında, kaynak veya hedef olarak SQL sorgusunu seçebilirsiniz.

Senkronizasyon Süreci

Onayladıktan sonra araç, kaynak ve hedef verileri karşılaştırır. Tüm süreç, tüm kaynak ve hedef verilerin indirilmesinden ve belirtilen kriterlere göre karşılaştırılmasından oluşur. Varsayılan olarak, eşit olarak adlandırılmış tablolardan ve sütunlardan alınan değerler karşılaştırılır. Tüm araçlar, sütun ve tablo adlarını eşlemeyi destekler. Ayrıca, IDENTITY (otomatik artış) sütunlarını hariç tutma veya değerleri karşılaştırmadan önce bazı dönüşümler yapma olasılığı vardır (yuvarlak kayan nokta türleri, karakter durumunu yok sayma, NULL'u boş bir NULL olarak ele alma, vb.) Veri indirme optimize edilmiştir. Veri hacmi büyükse, yalnızca sağlama toplamları indirilir. Bu optimizasyon çoğu durumda yardımcı olur, ancak veri hacmiyle birlikte işlemleri gerçekleştirmek için gereken süre artar.

Bir sonraki adımda, oluşturulan geçişlere sahip bir SQL betiği var. Bu komut dosyası kaydedilebilir veya doğrudan çalıştırılabilir. Güvende olmak için, bu betiği çalıştırmadan önce bir veritabanı yedeği bile yapabiliriz. ApexSQL Data Diff aracı, komut dosyasını seçilen bir veritabanında çalıştıran yürütülebilir bir program oluşturabilir. Bu komut dosyası, değiştirilmesi gereken mantığı değil, değiştirilmesi gereken verileri içerir. Bu, komut dosyasının yinelenen bir içe aktarma sağlamak için otomatik olarak çalıştırılamayacağı anlamına gelir. Bu yaklaşımın en büyük dezavantajı budur.

Avantajlar

• Gelişmiş SQL bilgisi gerekli değildir ve GUI aracılığıyla yapılabilir.
• Senkronizasyondan önce veritabanları arasındaki farkları görsel olarak kontrol etme olanağına sahipsiniz.

Dezavantajları

• Ticari ürünlerin gelişmiş bir özelliğidir.
• Muazzam miktarda veri aktarılırken performans düşer.
• Oluşturulan SQL komut dosyası yalnızca farklılıkları içerir ve bu nedenle gelecekteki verileri otomatik olarak eşitlemek için yeniden kullanılamaz.

Aşağıda bu araçların tipik kullanıcı arayüzünü görebilirsiniz.

Otomatik Olarak Oluşturulan SQL ile senkronize edin

Bu yöntem, veri karşılaştırma yöntemine çok benzer. Bir önceki yönteme göre tek farkı, veri karşılaştırması olmaması ve oluşturulan SQL betiğinin veri farklılıkları değil, senkronizasyon mantığı içermesidir. Oluşturulan komut dosyası, bir saklı yordama kolayca kaydedilebilir ve periyodik olarak (örneğin, her gece) çalıştırılabilir. Bu yöntem, veritabanları arasında otomatik içe aktarma için kullanışlıdır. Bu yöntemin performansı, veri karşılaştırma yönteminden çok daha iyidir.

Otomatik olarak oluşturulan SQL ile senkronizasyon yalnızca SQL Database Studio tarafından sağlanır.

SQL Database Studio, veri karşılaştırma yöntemine benzer bir arabirim sağlar. Kaynağı ve hedefi (veritabanları veya tablolar) seçmemiz gerekiyor. Ardından seçenekleri ayarlamamız gerekiyor (eşleştirme tuşları, eşleştirme ve eşleme). Tüm parametreleri ayarlamak için bir grafik sorgu oluşturucu özelliği vardır.

Avantajlar

• İleri düzeyde SQL bilgisi gerekli değildir.
• Her şeyi bir GUI'de oldukça hızlı bir şekilde ayarlayabilirsiniz.
• Ortaya çıkan SQL komut dosyası, saklı bir prosedüre kaydedilebilir.
• Otomatik içe aktarma olarak kullanılabilir - SQL Server için bir iş olarak.

Dezavantajları

• Ticari ürünlerin gelişmiş bir özelliğidir.
• Tüm süreç tek adımda yürütüldüğünden, senkronizasyondan önce farklılıklar manuel olarak kontrol edilemez.

Performans Kıyaslamaları

Test durumu

Her biri 2.000.000 satır içeren bir tablo içeren iki veritabanı (A ve B). Tablolar aynı SQL Server üzerinde iki farklı veritabanında bulunmaktadır. Bu test iki uç durumu kapsar: 1) Kaynak tablo 2.000.000 satırın tamamını içerir ve hedef tablo boştur. Senkronizasyonun birçok INSERTS sağlaması gerekir. 2) Kaynak ve hedef tablolar 2.000.000 satır içerir. Fark sadece bir satırda. Senkronizasyonun yalnızca bir UPDATE sağlaması gerekir.

RedGate Veri Karşılaştırma 3 adıma ihtiyaç duyar:

• Karşılaştırmak
• Komut dosyası oluştur
• Komut dosyasını hedef veritabanında çalıştır

ApexSQL Data Diff 2 adıma ihtiyaç duyar:

• Karşılaştırmak
• Tek adımda komut dosyası oluşturun ve komut dosyasını çalıştırın

SQL Database Studio, tüm senkronizasyonu tek adımda gerçekleştirir. Aşağıda saniye cinsinden senkronizasyon süreleri verilmiştir. "Tek tek adımlar" etiketli sütunda, yukarıda listelenen senkronizasyon adımlarının süreleri vardır.

Alçak daha iyi.

Aynı test, ancak veritabanları, bağlantılı bir sunucu üzerinden bağlı olmayan farklı SQL sunucularında.

Alçak daha iyi.

Özet

Sonuçlardan, RedGate ve Apex'in veritabanlarının aynı SQL sunucusunda olup olmadığını umursamadığı açıktır, çünkü senkronizasyon algoritması SQL Server'a bağımlı değildir. SQL Database Studio, SQL Server'ın yerel işlevlerini kullanır; bu nedenle, veritabanları aynı sunucuda olduğunda sonuç daha iyidir.

Kaynak ve hedef farklı bir yapıya sahiptir

Bir geniş tablonun birçok küçük ilgili tabloyla senkronize edilmesi gerektiği durumlar da vardır.

Bu örnek, Continent , Country ve City gibi küçük tablolarla senkronize edilmesi gereken geniş bir SourceData tablosundan oluşur. Şema aşağıda verilmiştir.

SourceData'daki veriler aşağıdaki resimdeki gibi olabilir.

El ile oluşturulan SQL komut dosyalarını kullanma

Kıta Tablosunu Senkronize Eden Komut Dosyası

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Senaryo Senkronize Edilen Şehir Tablosu

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

Bu komut dosyası daha karmaşıktır. Bunun nedeni, Country ve Continent tablolarındaki kayıtların bulunması gerektiğidir. Bu komut dosyası, eksik kayıtları City ekler ve ContryId doğru şekilde doldurur.

UPDATE ve DELETE betikleri de gerekirse aynı şekilde yazılabilir.

Avantajlar

• Herhangi bir ticari ürüne ihtiyacınız yok.
• SQL komut dosyası, saklı yordama kaydedilebilir veya SQL Server için bir iş olarak periyodik olarak çalıştırılabilir.

Dezavantajları

• Böyle bir SQL komut dosyası oluşturmak zor ve karmaşıktır (her tablo için üç komut dosyası – INSERT , UPDATE ve DELETE – genellikle gereklidir).
• Bakımı çok zordur.

Harici araçları kullanma

Bu tür bir senkronizasyon (birçok ilgili tabloya geniş tablo), farklı kullanım durumlarına odaklandığından veri karşılaştırma yöntemiyle yapılamaz. Veri karşılaştırma yöntemi, eklenecek verilerle bir SQL komut dosyası ürettiğinden, ilgili tablolardaki referansları aramak için doğrudan bir yeteneği yoktur. Bu nedenle, bu yöntemi kullanan uygulamalar kullanılamaz (SQL Server için dbForge Data Compare, RedGate SQL Data Compare, Apex SQL Data Diff).

Ancak SQL Database Studio, senkronizasyon komut dosyalarını otomatik olarak oluşturmanıza yardımcı olabilir. Aşağıdaki resimde SQL Database Studio'da Veri Senkronizasyonu Düzenleyicisi adında bir eleman var.

Düzenleyici, iyi bilinen Sorgu oluşturucuya benzer ve çok benzer bir şekilde çalışır. Her tablonun tanımlanmış bir senkronizasyon anahtarına sahip olması gerekir, ancak tablolar arasında tanımlanmış ilişkiler de vardır. Yukarıdaki resimde de senkronizasyon için eşleme var. Sütun listesinde (resmin alt kısmı) City tablosunun sütunları vardır (diğer tablolar için benzerdir).

sütunlar

• Id — Bu sütun, birincil anahtar olduğundan (otomatik olarak oluşturulur) eşlenmez.
• CountryId — Bu sütun, tablo için bir referans olarak tanımlanır.
• Ad — Bu sütun, kaynak tablodaki (geniş tablo) Şehir sütunundan doldurulur.

Sütunlar CountryId ve Name , senkronizasyon anahtarları olarak seçilir. Senkronizasyon anahtarı, kaynak ve hedef tablodaki bir satırı benzersiz şekilde tanımlayan bir sütun kümesidir. Birincil anahtar Id , kaynak tabloda olmadığı için senkronizasyon anahtarı olarak kullanamazsınız.

Senkronizasyondan sonra tablolar şöyle görünür:

Yukarıdaki örnekte, kaynak olarak geniş bir tablo vardı. Kaynak verilerin birkaç ilgili tabloda depolandığı yaygın bir senaryo da vardır. SQL Database Studio'daki ilişkiler yabancı anahtarlar kullanılarak değil, sütun adları kullanılarak tanımlanır. Bu şekilde CSV veya Excel dosyalarından içe aktarma da mümkündür (dosya geçici bir tabloya yüklenir ve senkronizasyon o tablodan çalıştırılır). Benzersiz sütun adlarına sahip olmak iyi bir uygulamadır. Bu mümkün değilse, bu sütunlara takma adlar tanımlayabilirsiniz.

Avantajlar

• Oluşturması kolay ve hızlı
• Bakımı kolay
• Saklı bir yordama kaydedilebilir (saklanan yordam, senkronizasyonu daha sonra bir düzenleyicide açmak için gerekli verilerle birlikte kaydedilir)

Dezavantajları

• Ticari çözüm

Çözümleri Karşılaştırma

Veri senkronizasyonu, bir dizi INSERT , UPDATE veya DELETE komutlarından oluşur. Bu komutların dizilerini oluşturmanın birden çok yolu vardır. Bu makalede, senkronizasyon SQL komut dosyaları oluşturmak için üç seçeneğe baktık. İlk seçenek, her şeyi manuel olarak oluşturmaktır. Uygulanabilirdir (ancak çok zaman alır), SQL'in karmaşık bir şekilde anlaşılmasını gerektirir ve oluşturulması ve sürdürülmesi zordur. İkinci seçenek ticari araçları kullanmaktır. Aşağıdaki araçlara baktık:

• SQL Server için dbForge Veri Karşılaştırma
• RedGate SQL Veri Karşılaştırması
• Apex SQL Veri Farkı
• SQL Veritabanı Stüdyosu

İlk üç araç çok benzer şekilde çalışır. Verileri karşılaştırır, kullanıcının farklılıkları analiz etmesine izin verir ve seçilen farklılıkları senkronize edebilir (hatta otomatik olarak veya komut satırından). Şu kullanım senaryoları için faydalıdırlar:

• Çeşitli hatalar nedeniyle veritabanları senkronize değil.
• Ortamlar arasında veri aktarırken replikasyondan kaçınmanız gerekir.
• Excel veya HTML'deki veri karşılaştırma raporlarına ihtiyaç vardır.

Her araç şu veya bu nedenle sevilir: dbForge harika bir kullanıcı arayüzüne ve birçok seçeneğe sahiptir, ApexSQL diğerlerinden daha iyi performans gösterir ve RedGate en popüler olanıdır.

Dördüncü araç olan SQL Database Studio biraz farklı çalışır. Değişiklikleri değil, senkronizasyon mantığını içeren SQL komut dosyaları oluşturur. Performans da harikadır, çünkü tüm işler doğrudan veritabanı sunucusunda yapılır, bu nedenle veritabanı sunucusu ile senkronizasyon aracı arasında veri aktarımı gerekmez. Bu araç, aşağıdaki kullanım durumları için kullanışlıdır:

• Veritabanlarının farklı yapıda olduğu otomatik veri tabanı geçişleri
• Birden çok ilgili tabloya içe aktarın
• Harici kaynaklardan XML, CSV, MS Excel içe aktarma