SQL Server'da Merge Replication Kurulumu

SQL Server’da Merge Replication, verilerin hem yayıncı (Publisher) hem de abone (Subscriber) tarafında bağımsız olarak değiştirilebildiği, ardından bu değişikliklerin birleştirilerek tüm düğümlerde senkronize edildiği en karmaşık ancak en esnek replikasyon türüdür.

Genellikle sürekli ağ bağlantısının olmadığı veya her iki tarafın da veri girişi yapması gereken dağıtık senkronizasyon senaryoları için tasarlanmıştır.

Transactional Replication’ın aksine, Merge Replication’da veriler anlık olarak akmaz. Bunun yerine belirli aralıklarla çalışan bir Merge Agent, her iki taraftaki değişiklikleri kontrol eder. Eğer aynı satır hem yayıncıda hem de abonede değiştirilmişse, önceden tanımlanmış bir “çatışma çözücü” (Conflict Resolver) devreye girerek hangi verinin kalıcı olacağına karar verir.

Peer To Peer Transactional Replication, Transacational Replication’ın altyapısını kullanıyor. Bu yüzden conflictleri yönetmek daha zor. Merge Replication bu amaçla geliştirildiği için; Microsoft’un ifadesiyle karmaşık bir conflict dedection ve resolution gerektiren bir uygulamanız varsa Peer To Peer Transactional Replication yerine Merge Replication kullanın.

Örneğin bir telekom şirketinin tüm Türkiye de bayileri var ve bu bayilerin yaptığı tüm işlemler tek bir veritabanında toplanmak istiyor. Bu telekom şirketi için Merge Replication kurup her bayiyi bir üye yapabiliriz. Her bayinin kendi lokalindeki veritabanında yaptığı değişikliklerin diğerlerine de yansıması sağlanmış olacaktır.

Avantajları

Aboneler internete bağlı olmasa bile veri ekleyip güncelleyebilir. Bağlantı kurulduğunda değişiklikler senkronize edilir.
Hem ana merkezden şubelere hem de şubelerden ana merkeze veri akışı sağlar.
Kimin verisinin baskın geleceği (örneğin: “Son yazan kazanır” veya “Merkez her zaman haklıdır”) kurala bağlanabilir.

Dezavantajları

Veritabanına her tablo için tetikleyiciler (triggers) ve sistem tabloları ekler, bu da DML (Insert/Update/Delete) işlemlerini ağırlaştırabilir.
Yapılandırması ve hata ayıklaması (troubleshooting) Transactional Replication’a göre çok daha zordur.
Her satıra benzersiz bir kimlik eklemek için rowguid sütunu eklenir, bu da depolama gereksinimini artırır.

Hangi Durumlarda Kullanılır

Satış temsilcilerinin gün boyu çevrimdışı çalışıp akşam ofise gelince verileri ana merkeze aktarması gereken durumlar.
Şubelerin kendi yerel verileriyle hızlı çalışması gerektiği ve gün sonunda genel merkezin bu verileri toplamak istediği senaryolar.
Bağlantı hızının çok düşük veya kararsız olduğu coğrafi olarak uzak lokasyonlar.

Merge Replication kurmadan önce sisteminizde şu yapı taşlarının hazır olması gerekir:

Unique Identifier (rowguidcol): Replike edilecek her tabloda bir uniqueidentifier sütunu bulunmalıdır. Eğer tabloda yoksa, replikasyon kurulum sırasında bunu otomatik olarak ekler (ancak büyük tablolarda bu işlem zaman alabilir).
Snapshot Folder: Yayıncı ile abone arasındaki ilk veri transferi için kullanılan ve her iki sunucunun (özellikle SQL Agent hesaplarının) erişebildiği bir paylaşımlı klasör.
SQL Server Agent: Replikasyon işlemlerini yürüten ajanların (Merge Agent, Snapshot Agent) çalışması için bu servis her iki tarafta da “Automatic” modda olmalıdır.
Ağ Bağlantısı ve Yetki:
- Yayıncı (Publisher), Dağıtıcı (Distributor) ve Abone (Subscriber) arasında gerekli portların (varsayılan 1433) açık olması.
- SQL Agent servis hesaplarının replikasyon klasöründe okuma/yazma yetkisine sahip olması.
Distributor Yapılandırması: Replikasyon trafiğini yönetecek bir Dağıtıcı (genellikle Publisher ile aynı sunucu olur) tanımlanmış olmalıdır.

Merge Replication’da veritabanı şemasında yapılan değişikliklerin (ALTER TABLE vb.) tüm abonelere yansıtılması yönetilmesi gereken bir süreçtir. Ayrıca, çatışmaların (conflicts) sıklığını azaltmak için tabloların mümkün olduğunca bölümlenmiş (filtered) şekilde replike edilmesi önerilir.

Bir örnek üzerinden giderek konuyu daha net anlamaya çalışalım. 2 sunucu üzerinde Merge Replication kurulumu yapacağız. Merge Replication, Snapshot Agent ve Merge Agent ile çalışır. Diğer Replication tiplerinde kullandığımız Distribution Agent’ı kullanmayacağız.

n kurulumun makalesinde yapmış olduğumuz distributor yapısını mevcut olan ikinci sunucumuza da ekliyoruz. İlgili makaleden ilk sunucu için Distribution yapısı olduğu için ikinci sunucu için bu işlemi yapmıyoruz.

Bilgi olması açısından ssql server kurulumunda database engine role’ünün yanına replication role’ünün eklenmesi gerekmektedir. Bu özellik seçilmezse replication servisleri yüklenmez ve sihirbazlar çalışmaz.

En az iki farklı SQL Server Instance gereklidir.

Publisher (Yayıncı): Verinin asıl sahibi.
Subscriber (Abone): Verinin kopyalanacağı yer.
Not: İsterseniz “Distributor” (Dağıtıcı) rolünü üçüncü bir sunucuya verebilirsiniz, ancak genellikle Publisher ve Distributor aynı sunucuda yapılandırılır.

Not: Instance isimlerinin farklı olması bir engel değildir. Hatta karışıklığı önlemek için farklı olması önerilir. Ancak sunucuların birbirini Host Name (veya SQL Instance Name) üzerinden çözümleyebilmesi şarttır.

İlk olarak publisher ve bütün subscriber’larda DB1 veritabanında aşağıdaki script yardımıyla replike edeceğimiz tabloyu oluşturalım.

USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MergeReplicationTable](
	[id] [int] IDENTITY(1,1)  NOT NULL,
	[ad] [nchar](10) NULL,
	[soyad] [nchar](10) NULL,
 CONSTRAINT [PK_MergeReplicationTable] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Bir sonraki adımda Publication’ı oluşturacağız. 1.Sunucu üzerindeki instance’a giderek aşağıdaki gibi Local Publications’a sağ tıklayarak New Publication diyoruz.

Gelen ekranda Do not show this page again’i tıklayarak next diyoruz.

Gelen ekranda hangi veritabanımızı replica edeceksek seçim işlemi yapılmaktadır. İlgili veritabanı seçimi yaptıktan sonra Next denilir.

Not: Mevcut sunucu üzerinde Distributor kurulu olduğu için ilgili Distributor ve Snapshot folder bölümü gelmedi.

Gelen ekranda aşağıdaki gibi Merge Publication’ı seçerek Next diyoruz.

Bir sonraki ekranda aşağıdaki gibi üye tiplerini belirliyoruz. Biz kuracağımız publication’a bağlı olacak tüm üyelerin SQL Server 2008 ve sonrası olacağını belirttik. Aslında iki instance’ımız olacak ve ikiside SQL Server 2019 sürümüne sahiptir.

Bir sonraki ekranda Replike edeceğim article’ların arasından başlangıçta oluşturduğum tabloyu seçerek next diyorum.

Bir sonraki ekranda aşağıdaki gibi publish edeceğimiz tabloya uniqueidentifier veri tipine sahip bir kolon ekleyeceğini, bu kolon üzerinde unique index oluşturacağını ve kolonun ROWGUIDCOL(kolona otomatik olarak yeni bir guid değeri verilmesi ) özelliğinde olacağını söylüyor.

Bir sonraki ekranda bize tablonun tamamını değil de belli bir filtreye göre filtrelenmiş halini aktarmak istersek bu kolaylığı sağlayan ekran geliyor. Aşağıdaki ekrandan Add diyerek istediğimiz filtreyi ekleyip tablonun belli bir kısmını replike edebiliriz. Biz şu anda herhangi bir filtreleme yapmadan next diyerek ilerliyoruz.

Bir sonraki ekranda verilerin snapshot’ının şimdi mi yoksa daha sonra mı alınacağını soruyor. Diğer replication tiplerinden farklı olarak Schedule the Snapshot Agent to run at the following times kısmı da seçili geldi. Buradaki tik’i kaldırıyoruz. Aşağıdaki gibi Create a snapshot immediately and keep the snapshot available to initialize subscription’ı seçerek, snapshot’ın şimdi alınmasını istediğimizi söylüyor ve next diyerek ilerlemeye devam ediyoruz.

Bir sonraki ekranda aşağıdaki gibi Snapshot Agent’ın çalışacağı kullanıcı bilgisini soruyor. Security Settings’e tıklıyoruz. Sql server agent account hesabını seçiyoruz.

Bir sonraki ekranda Snapshot Agent ve Log Reader Agent’ın kullanacağı kullanıcı hesaplarını soruyor. Security Settings’e tıklayarak ilgili kullanıcıları set edebiliriz.

Snapshot Agent için set edeceğiniz kullanıcının minimum hakları:

Distribution veritabanında db_owner olmalı.
Publication yapılacak veritabanında db_owner olmalı.
Snapshot paylaşımında write hakkı olmalı.

Log Reader Agent için set edeceğiniz kullanıcının minimum hakları:

Distribution veritabanında db_owner olmalı.
Publication yapılacak veritabanında db_owner olmalı.

Microsoft bu iki hesap içinde Windows Account set etmemizi öneriyor. Veritabanları üzerinde tanımlanan servis hesapları ilgili veritabanlarında full kontrol yetkisine sahiptir.

Bir sonraki ekranda aşağıdaki gibi Create the publication seçili iken next diyerek ilerliyoruz.

Gelen ekranda Publication’a bir isim veriyoruz ve finish diyerek publication kurulumunu tamamlıyoruz.

Başarılı bir şekilde kurulum gerçekleşti.

Kurulum tamamlandıktan sonra aşağıdaki şekilde publication’ın sağlıklı bir şekilde çalıştığını test edebilirsiniz.

Publication’ı tanımladıktan sonra 1.instance üzerinde aşağıdaki gibi New Subscriptions diyoruz.

Gelen ekranda aşağıdaki gibi Publisher kısmından Publisher’ı tanımladığımız instance’ı seçiyoruz ve oluşturduğumuz MergePublication’ı seçerek next diyoruz.

Bir sonraki ekranda subcriptions’a verilerin nasıl geleceğini soruyor. Yani veriler Distributor’den subcscriber’lara mı aktarılacak yoksa subscriberlar distributor’dan mı veriyi alacaklar. Bu yöntemler Push ya da Pull Subscription olarak geçiyor.

Aşağıdaki ekran görüntüsü, Snapshot Replication kurulumunun en kritik karar noktalarından biri olan Distribution Agent Location (Dağıtım Aracının Konumu) aşamasını gösteriyor.

Genel kullanım senaryolarının %90’ında ilk seçenek olan “Run all agents at the Distributor” (Push Subscription) tercih edilir. Detaylandıralım:

1. Seçenek: Run all agents at the Distributor (Push Subscriptions)

Verinin dağıtılma emri ve yönetimi merkezden (Distributor) kontrol edilir. Veri, Subscriber’a “itilir”.

Yönetimi çok daha kolaydır. Replication Monitor üzerinden tüm akışı tek bir merkezden izleyebilirsin. Eğer sunucuların aynı local network üzerindeyse en mantıklı yoldur.
Bu seçeneği seçmezsen, her bir abone (subscriber) kendi işini kendi takip etmek zorunda kalır. 10 tane aboneneniz olduğunu düşünürseniz, 10 ayrı yerden yönetim yapmanız gerekir.

2. Seçenek: Run each agent at its Subscriber (Pull Subscriptions)

Abone sunucu, belirli aralıklarla “yeni veri var mı?” diye merkez sunucuya sorar ve veriyi kendisi “çeker”.

Eğer ana sunucun (Publisher) donanımsal olarak çok zayıfsa ve 1. seçeneği seçersen, replication trafiği ana sunucuyu iyice yorabilir.
Eğer Publisher/Distributor sunucun üzerinde çok fazla yük (CPU/RAM) varsa ve bu yükü hafifletmek istiyorsan veya Subscriber sayısı çok fazlaysa (yüzlerce abone gibi) tercih edilir.

Bir sonraki ekranda aşağıdaki gibi replike edeceğimiz tabloları hangi instance’taki hangi veritabanına replike edeceğimizi soruyor. Add Subscriber diyerek ikinci olan sunucuyu yapılandırıyoruz.

2. sunucunun subscriber olmasını istediğimiz için Add Subscriber diyerek 2.instance’ı ekleyerek aşağıdaki gibi seçimimizi yapıyoruz.

Gelen ekranda Distribution Agent’ın hangi kullanıcı ile çalışacağını soruyor. Aşağıdaki resimde gördüğünüz ….’ya tıklıyoruz.

Distribution Agent için set edeceğiniz kullanıcının minimum hakları:

Distribution veritabanında db_owner olmalı.
Pull Subscription kullanılacaksa subscription(hangi veritabanına replike edeceksek o veritabanı) veritabanında db_owner olmalı.
Snapshot paylaşımında read hakkı olmalı.
Publication Access List(PAL)’ın bir üyesi olmalı.

Set edeceğiniz kullanıcıyı PAL’a üye yapmak için Publisher’ın olduğu instance’ta publication’a gelip sağ tıklayarak properties diyoruz.

Açılan ekranda aşağıdaki gibi Publication Access List’e giderek listelenen kullanıcılar arasında kendi kullanıcımızın olup olmadığına bakıyoruz.

Eğer yoksa Add diyerek ekliyoruz. Eğer kullanıcımız Add dediğimizde çıkmazsa aşağıdaki gibi bir uyarı verecektir.

Uyarıda, kullanıcının burada listelenmesi için Publisher ve Distributor instance’ında tanımlı olmasına ve replike edeceğimiz DB1 veritabanına erişiminin olması gerektiğini söylüyor. Eğer kullanıcınız burada yoksa publisher ve distributor’un olduğu instance üzerinde kullanıcınızı tanımlayıp DB1 üzerinde de yetkilendirmeniz gerekir.

Kullanıcımız ilgili bölüm altına başarılı bir şekilde eklemiş olduk.

Distribution Agent Security’ye geri dönersek, ben Run under the SQL Server Agent service account’u ve By impersonating the process account’u seçerek ilerliyorum. Tabi SQL Server Agent Servis hesabıma gerekli yetkileri verdim.

Next denilip bir sonraki adıma geçilir.

Bir sonraki ekranda senkronizasyonun nasıl olacağını soruyor.

Run continuousluy seçersek sürekli olarak senkronize olur ve gerçek zamanlıya yakın bir kopyamız olur.
Run on demand only seçersek sadece tetiklediğimizde çalışır.
Define schedule seçersek belirli zaman aralıklarıyla düzenli olarak çalışmasını sağlarız.

Bir sonraki ekranda, subscriber’a(asıl veritabanını replike edeceğimiz veritabanı,yani 2.instance’daki DB1) hemen verileri aktarmak için Immediately’yi seçerek next diyoruz.

Bir sonraki ekranda Subscription Type’ı ve Priority for Conclict Resolution’ı ayarlayacağız. Merge Replication kullanıyorsanız conflictlerin nasıl çözüleceğini belirlemek için başlangıçta yapılaması gereken bir ayar. Subscription’ı oluşturduktan sonra Subscription Type’ı değiştiremiyorsunuz. Bu yüzden ne işe yaradığını anlayıp ihtiyaca göre konfigure etmeniz gerekir.

İki adet Subscription Type var.

Server: Farklı Subscriber’ların farklı önceliği olmasını istiyorsanız Server seçebilirsiniz. Örneğin ben 2.sunucu için Subscription Type’ı aşağıdaki gibi Server seçmişim ve Priority for Conflict Resolution(conflict oluştuğundaki öncelik yüzdesi) olarak ta 75.00 set ettim. Üçüncü bir sunucuya başka bir subscriber oluşturduğumuzu varsayalım ve subscription type olarak yine server seçelim. Priority for Conflict Resolution’ı da 60.00 set edelim. İkinci sunucudaki subscriber ve üçüncü sunucudaki subscriber bir conflict yaşadığında ikinci sunucunun önceliği daha yüksek olduğu için ikinci sunucudaki subscriber’ın yaptığı işlem geçerli olacak. Veriyi diğer subscriber’lara yeniden yayınlayabilir.

Client: Conflict dedection(subscriber’lar arasında aynı veriyi update etmeye çalışma sonucu çakışma) bu subscribtion type’da da var fakat Server subscription type’ında ki gibi subscriber’lara öncelik veremiyorsunuz. Default olarak priority 0.00 olarak geliyor. Bütün subscriber’ların aynı önceliğe sahip olmasını istiyorsanız ve publish’in yapıldığı 1.instance’ın conflict durumunda her zaman kazandığı bir senaryo istiyorsanız set edebilirsiniz. Bir çok senaryoda Client kullanılabilir.

Yukarıdaki seçenek haricinde client da seçilebilir.