Makale Özeti

SQL veritabanımızdaki verilerin başka bir yerde de olmasını istediğimizde elimizde değişik senaryolara uygun değişik opsiyonlar vardır. Bunlardan bazıları, Distributed Transactions, Log Shipping (Sadece SQL Server Enterprise Edition’da mevcut), backup-restore, DTS (Data Transformation Services) ve replication. Her birinin bir diğerine göre avantajları – dezavantajları olan bu metodlardan replication’dan ve replication’ın gelişmiş özelliklerinden biri olan “Dynamic Filters” konusundan bahsedeceğim.

Makale

SQL Server Replication – Dynamic Filters

SQL veritabanımızdaki verilerin başka bir yerde de olmasını istediğimizde elimizde değişik senaryolara uygun değişik opsiyonlar vardır.

Bunlardan bazıları, Distributed Transactions, Log Shipping (Sadece SQL Server Enterprise Edition’da mevcut), backup-restore, DTS (Data Transformation Services) ve replication. Her birinin bir diğerine göre avantajları – dezavantajları olan bu metodlardan replication’dan ve replication’ın gelişmiş özelliklerinden biri olan “Dynamic Filters” konusundan bahsedeceğim.

Replication Terminolojisi

Replication’ı bir derginin basım aşamasından halka sunulması aşamasına kadarki olan sürece benzetecek olursak, dergiyi ve içindeki yazıları hazırlayan birim (Publisher)

 dergiyi dağıtan dağıtımcı şirket (distributor) ve derginin aboneleri (subscriber(s)) olarak adlandırabiliriz.

Publisher verilerin kaynağının bulunduğu, Distributor verileri Publisher’dan alan ve Subscriber’a ulaştıran  server’dır. Replication senaryonuza göre, Publisher ve Distributor ayrı ayrı server’larda bulunabileceği gibi, ikisi aynı server’da da olabilir. Bunu dergiyi yayınlayan ve dağıtan firmanın aynı olması gibi yorumlayabiliriz.

Baktığımızda, veriyi hazırlayan server, dağıtan server ve veriyi alan server’ı anlıyoruz fakat publisher’dan yola çıkan ve subscriber’a iletilen bu verilerin teknik isimleri yok mu?

Bir yayın evi (publisher) bir yada daha fazla dergiyi abonelerine (subscriber) sunar. Aslında abonelerin istedikleri dergilerin içindeki yazıları okumaktır, içinde 50 tane yazı bulunan bir dergiye üye olduğunuzda aslında sizi ilgilendiren içindeki 50 yazıdır.

SQL replication’da dergilerin içindeki yazılar yerine tablolar, view’ler, user-defined function ya da stored procedure’ler gibi SQL objeleri bulunur, ki bunların teknik adı “article”’dır. Derginin teknik adı ise “publication” ‘dır. Yani herhangi bir abone bir publication’a üye olduğunda publisher, subscriber’a, subsriber’ın üye olduğu publication’ın içindeki bütün article (tablo, view, user-defined function ya da stored procedure)’ları düzenli olarak göndermeye başlar.

Replication Tipleri

MS SQL Server 2000’de üç tip replication mevcuttur, Snapshot, Transactional ve Merge replication.

Snapshot replication’da kaynak veritabanındaki article’ların script’i alınarak subscriber’lara bulk insert yapılır. Dikkat edilmesi gereken husus, Snapshot’replication incremental bir replication tipi değildir, yani replication zamanı geldiğinde en son replication’dan bu zamana kadar olan değişiklikleri değil, tablodaki bütün verilerin en baştan script’ini alıp subscriber’a bulk insert yapar.

Transactional replication incremental bir replication yönetimidir. Transaction log dinlenerek kaynak veritabanında çalıştırılan sorguların subscriber’larda da çalıştırılması sağlanır.

Merge replication’da kaynak veritabanındaki replike olacak tablolarda insert, update, delete trigger’ları oluşturularak herhangi bir insert, update, delete işleminde aksiyon alınarak veri subscriber’lara iletilir.

Merge ya da Transactional replication tipleri incremental’dir, yani değişiklikleri replike ederler.

Unutmamanız gereken nokta, herhangi bir veritabanından diğerine sadece değişiklikler replike edilecek de olsa (incremental), ilk başta iki taraf eşit hale getirilmelidir. Bunun için ister siz manual olarak karşılıklı vertiabanlarında replike olacak tabloları eşitlersiniz, ya da yaptığınız Replication tipi Merge ya da Transactional bile olsa, bir kerelik eşitlenme (initialization) için, veritabanının publisher’da script’i alınıp, subcscriber’da bulk insert yapılır (snapshot replication’da olduğu gibi).

Replication’a Başlamadan Önce

  1. Collation olarak Turkish kullanıyorsanız minimum Service Pack 1 yüklemelisiniz, aksi taktirde Publisher ayarlarını yaparken aşağıdaki hatayı alırsınız :

“Error 21112 : ‘-PollingInterval’ is not a valid parameter for the Log Reader Agent”

  1. SQL Server’ı kurduktan sonra, SQL Server’ın kurulu olduğu makinenin ismini değiştirdiyseniz muhtemelen sorun yaşayacaksınız. SQL Server kurduktan sonra makinenizin ismini değiştirirseniz, (diyelim SQLSRV makinenizin eski adı, yeni adı ise, SQLSERVER)

EXEC sp_dropserver  SQLSRV, droplogins

GO

EXEC sp_addserver SQLSERVER, local

GO

Komutları yeni serverınızı Remote Servers’a local olarak ekleyecektir.

  1. Snapshot replication kullanacaksanız ya da Merge, Transactional replication’da ilk eşitlemeyi (initialization) manual değil de snapshot ile yapacaksanız, script’lerin bulunacağı snapshot folder’a gerekli izinleri vermeyi unutmayın.
  2. Distributor ve Publisher arasında yetki problemi yüzünden replication’da sorun yaşarsanız, SQL Server’a sağ klikleyin, Replication tab’ına gidip Configure opsiyonunu seçerek Distributor, Publisher  ve Subscriber’ların birbirlerine bağlanmaları için gerekli kullanıcı adı ve şifreleri değiştirebilirsiniz.
  3. Enterprise Manager’da Local makinenizi “Local” olarak değil de ismi ile (“SQLSERVER” gibi) register edin.

Dynamic Filters

Senaryo : Bir şirket düşünün, şirketin bir merkez ve 3 tane şubesi olsun. Bütün ödemeler Merkeze yapılsın ve şubelere transfer olsun.  Merkeze girilen veriler Şube ID’lerine göre şubelere gönderilsin.

Şubelere gönderilmeyecek, merkezde kalacak veriler ise merkezin ID’si ile girilsin.

Merkez ID : 100, Merkezdeki SQL Server’ın adı : BilgeHQSQL

Şube1 ID   : 101, Şube1’deki SQL Server’ın adı  : Bilge1SQL

Şube2 ID   : 102, Şube2’deki SQL Server’ın adı : Bilge2SQL

Şube3 ID   : 103, Şube3’deki SQL Server’ın adı : Bilge3SQL

Şeklinde olduğunu varsayalım.

Merkez SQL Server’ımızdaki “odeme” tablosu aşağıdaki gibi olsun.

SubeID

Miktar

101

7.000.000.000

102

3.000.000.000

101

13.000.000.000

103

1.000.000.000

100

500.000.000

SubeID’si 101 olan Miktar, Şube1’e replike edilsin, SubeID’si 102 olan Miktar Şube2’ye replike edilsin, ŞubeID’si 103 olan Miktar Şube3’e replike edilsin, ŞubeID’si 100 olan Miktar ise Merkez’de kalsın, replike edilmesin istiyoruz.

Publisher / Distributor, Publication, Article ve Dynamic Filter’ların Hazırlanması

İlk önce Merkez SQL server’ımızı Publisher / Distributor olarak ayarlayalım (aynı server’ı hem publisher hem de distributor olarak ayarlayacağız)

SQL Server özelliklerinden Replication tab’ına gelip “Configure” diyelim :

 

Daha sonraki seçeneklerde “Make BILGEHQSQL it’s own Distributor” seçeneği ile Publisher ve Distributor’un aynı server olmasını istediğimizi belirtiyoruz.

Yazımızın odak noktası dynamic filter olduğu için replication’un ayarlanmasındaki detay bilgilere girmiyorum. Herhangi bir noktada sorun yaşarsanız, ya da sormak istediğiniz ekstra sorular olursa mail adresimden bana ulaşabilirsiniz.


BILGEHQSQL sunucusunu Publisher / Distributor olarak ayarladıktan sonra, publication ve article’ları yaratalım.

Bu senaryoda Replication tipi olarak Merge replication’ı kullanıyorum.


Replike edeceğimiz tablo olarak senaryoda kullanacağımız “odeme” tablosunu seçelim.

Son aşamada “Yes Define Data filters” seçeneğini işaretleyelim. Eğer bu seçeneği işaretlemezseniz, tablonun tamamını subscriber’lara göndereceksiniz demektir. Eğer tablonun belirli kolonlarını (dikey filtreleme) ya da belirli satırlarını (yatay filtreleme) göndermek istiyorsanız, burada Yes, seçeneğini işaretlemelisiniz.


Subscriber’lara göndereceğiniz tablodaki verileri vertically (dikey) ya da horizontally (yatay) filtreleyebilirsiniz. Dikey filtrelemeyi, Publisher’daki tablonun kolonlarından bazılarını karşı tarafa replike etmek istemediğiniz zaman kullanabilirsiniz. Yatay filtrelemeyi ise Publisher’daki tablonun sıralarından bazılarını karşı tarafa replike etmek istemediğiniz zaman kullanabilirsiniz.

Yatay filtreleme tiplerinden static filtreleme,

SELECT <published_columns> FROM [dbo].[odeme] WHERE SubeID=102

gibi filtreleme koşulunuzun statik (her zaman 102) olduğu filtreleme cinsidir.

Dynamic filtreleme ise,

SELECT <published_columns> FROM [dbo].[odeme] WHERE SubeID=HOST_NAME()

gibi filtreleme koşulunuzun dinamik (o an replication için veritabanına bağlanacak subscriber’ın hostname’ine göre HOST_NAME() sistem fonksyonunun döndüreceği değer değişecektir) olduğu filtreleme cinsidir.


Bir sonraki ekranda filtremizi belirliyoruz. Burada dikkat ederseniz, bağlanan subscriber’a göre HOST_NAME() değişkeni değişecektir ve her subscriber’a kendi SubeID’sine ait bilgiler gönderilerektir.

Örneğin, Bilge1SQL sunucusu bu publication’a subscribe olduğunda, HOST_NAME() değişkeni 101 olacaktır ve Bilge1SQL sunucusuna sadece SubeID=101 olan satırlar gönderilecektir. Bilge2SQL sunucusu bağlandığında HOST_NAME() değişkeni 102 olacaktır ve Bilge2SQL sunucusuna sadece SubeID=102 olan satırlar gönderilecektir.

Not : Eğer burada SubeID kolonunun veri tipi olarak sayı verdiyseniz, convert fonksyonu ile subeID’yi NVARCHAR a cevirmeyi unutmayın

SELECT <published_columns> FROM [dbo].[odeme] WHERE Convert(nvarchar, SubeID)=HOST_NAME()

Subscriber’ların Publication’lara Üye Yapılması

SQL Server’da bir publication’ın Publisher’dan Subscriber’a ulaştırılmasının Pull ve Push olmak üzere iki yolu vardır.

Push seçeneğinde subscribtion Publisher/Distributor’dan Subscriber’a gönderilir, iş Publisher’ın dır.

Pull seçeneğinde subscribtion Subscriber tarafından Publisher/Distributor’dan alınır, iş Subscriber’ın dır.

Senaryomuzda Dynamic Filter’ın çalışabilmesi için Subscriberların’ın Publisher/Distributor’a bağlanması ve subscriber’ların kendilerini 101, 102, 103 olarak tanıtması gerekiyour. Bunun için Pull subscribtion oluşturmanız gerekir.


 

İlk Subscriber’ımız Bilge1SQL’den, BilgeHQSQL’de oluşturduğumuz  publication’u pull edelim.

 

 

 

 

 

 

 

 

 

Publication olarak daha önce yarattığımız ve dynamic filter ayarladığımız BilgeHQSQL’deki publication’ı seçelim.

Publisher’daki “odeme” tablosu BilgeDB adlı veritabanındaydı. Subscriber’da odeme tablosunun hangi veritabanında yaratılacağını seçiyoruz. Publisher ve Subscriber’da farklı veritabanları seçebiliriz. Örneğimizde veritabanı isimlerini aynı ayarlıyoruz.


Yazının başında Merge replication’un incremental (her defasında tüm tabloyu değil, sadece değişiklikleri subscriber’a göndererek iki tablonun aynı olmasını sağlayan) bir replication tipi olduğundan bahsetmiştim. Fakat değişikliklerin eklenmesi için önce publisher ve subscriber’ın eşitlenmesi (initialization) gerekir, daha sonra olacak değişiklikler incremental olarak gönderilir demiştim. Burada iki tabloyu manual olarak (no subscriber already has the schema and data) eşitlemeyi seçebilir, ya da bu işlemi SQL’e bırakabilirsiniz.

Sihirbazın geri kalan pencerelerini varsayılan opsiyonlarla geçebiliriz.

Buraya kadar herşey yolunda gibi görünse de yolunda olmayan bir nokta var !

Bilge1SQL adlı subsciber’ımızın BilgeHQSQL adlı publisher/distributor’daki odeme adlı tablodaki SubeID’si 101 olan datalara ihtiyacı var. Bunun için Publisher’a gidip “where SubeID=Host_Name()” fonksyonunda hostname’ini 101 olarak tanıtması gerekiyor. Fakat Bilge1SQL subscriber default olarak Host_Name() olarak makinenin ismi olan Bilge1SQL’i gönderir. Bilge1SQL server’ının Publisher/Distributor’a hostname’ini 101 olarak tanıtabilmesi için Bilge1SQL server’da SQL Server Agent altında Merge Replication’ın yarattığı JOB’ın sonuna –Hostname 101 parametresini eklemeniz

gerekiyor.

Böylelikle Bilge1SQL adlı server’ımız kendisini Publisher/Distributor (BilgeHQSQL)’a Host_Name() = 101 olarak tanıtacak ve dynamic filter’da sadece SubeID=101 olan satırları kendine replike edecektir.

Aynı şekilde Bilge2SQL ve Bilge3SQL subscribtion’larını yaratıp –Hostname 102, -Hostname 103 parametrelerini verdiğiniz zaman her şubenin sadece kendi ID’sinin verisini almasını sağlarsınız.

Alper Önsoy