Transactional Replication Kurulumu

Bu makaleyi daha net anlayabilmeniz için “SQL Server Replication Nedir?” isimli makalemi okumanızı tavsiye ederim.

Transactional Replication’da ilk olarak replike edilecek tablo/objenin snaphot’ı(aktarılacak tablonun schema bilgisi ve tablonun içinde o anda mevcut olan datanın fotografı gibi düşünebilirsiniz) alınır ve subscriber’lara aktarılır. Bu işlemin hemen arkasından veri ve şema’da olan değişiklikler sıralı bir şekilde subscriber’lara neredeyse gerçek zamanlı olarak aktarılır. Asıl veritabanında gerçekleşen transaction’lar hangi sırayla gerçekleştiyse subscriber’lara da aynı sırayla aktarılır. Bu şekilde Transaction tutarlılığı garanti altına alınmış olur.

Benim şu ana kadar yönettiğim sistemlerde Transactional Replication’ı daha çok raporlama amaçlı kullandık. Asıl server’da çok yoğun işlemler olduğu için rapor sorguları performans sorununa yol açabilir ve rapor çekeceğimiz tabloları başka bir sunucuya aktarıp asıl sunucuda performans kaybı olmadan raporlarımızı çekebiliriz. Transactional Replication neredeyse eş zamanlı çalışacağı için raporlarımızda neredeyse gerçek zamanlı olacaktır. Always ON kullanıyor olsaydık secondary sunucudan Read yapabiliyor olurduk fakat rapor sorgularının bir çok index ihtiyacı olabilir ve Always ON’da secondary sunucuda index oluşturulamıyor. Bu yüzden Always On’un secondary sunucusunu raporlama amaçlı kullanmak istersek raporlar için gerekli indexleri primary sunucuda oluşturmamız gerekir. Gereğinden fazla index eklemek çalışan uygulamanın insert,update ve delete performansını yavaşlatacağı için primary sunucuda rapor amaçlı index eklemek istemiyor olabilirsiniz. Bu durumda Transactional Replication biçilmiş bir kaftan olarak karşımıza çıkacaktır. Eğer raporlama için ekstra index ihtiyacınız bulunmuyorsa ya da ben rapor sorgularım için gerekli index’leri primary’de eklerim uygulamam için performans problemi oluşturmaz diyorsanız “Always On Secondary Sunucudan Read Yapmak” isimli makalemi okumanızı tavsiye ederim. Always On ile ilgileniyorsanız sitemizin arama çubuğuna Always ON yazdığınız takdirde işinize yarayacak bir çok makale bulabilirsiniz.

Bu kısımda Transactional Repliation’ın çalışma prensiplerini inceleyeğiz. Çok detaylı bir şekilde anlatıp sizi sıkmak istemediğim için genel olarak mantığına değinelim.

Snapshot Agent, Log Reader Agent ve Distribution Agent kullanır. Bu kavramlarla ilgili detayları “SQL Server Replication Nedir?” isimli makalemde bulabilirsiniz.

Özetle Snapshot agent tarafından veritabanının snapshot’ı alınıp subscriber’e uygulanır. Daha sonra Log Reader Agent tarafından publisher’daki replike edilecek olarak işaretlenmiş transaction’lar “distribution” veritabanına aktarılır. Distribution Agent’da, Snapshot tarafından alınan snapshot’ı ve Log Reader Agent tarafından “distribution” veritabanına aktarılan transaction’ları Subscriber’lara aktarır.

Önemli bir not olarak Transactional Replication’da, asıl veritabanında yapılan her değişiklik replike edilen veritabanına yansıtılır. Yani 1.transaction’da a kolonunu 3 olarak set ettiniz ve 2.transaction’da a kolonunu bu sefer 5 olarak set ettiniz. Transactional replication replike veritabanındaki a kolonunu direk 5 olarak set etmez. Önce 3 sonra da 5 olarak set eder. Bu şekilde her transaction bir iz bırakarak geçmişe dönük izlemeye olanak sağlar.

Şimdi adım adım iki sunucu arasında Transactional Replication kurulumunu yapalım:

İki instance’ımız var. Bunlardan bir tanesi Publisher ve Distributor olacak,(Yani local Distributor) diğeride Subscriber olacak. Publisher ve Distributor yapacağımız 1.instance’ımızdaki AdventureWorks2014 veritabanının Person.Person  tablosunu aktaracağız.

İlk olarak distribution veritabanını oluşturacağız. Aşağıdaki gibi Configure Distribution diyoruz.

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

Bir sonraki ekranda aşağıdaki gibi seçim yaparak Distributor’ü kurulum yaptığımız sunucuda oluşturuyoruz. Arka planda distribution veritabanıda oluşacak. Next diyerek ilerliyoruz.

Bir sonraki ekranda snapshot dosyalarının tutulacağı bir paylaşım istiyor. “Paylaşım Tanımlamak ve Tanımladığımız Paylaşımı SQL Server’ın olduğu sunucuya Map Etmek” isimli makalemde nasıl paylaşım oluşturacağımızı anlattım. Paylaşımda iki instance’ın kullandığı sql server servis hesabını,sql server agent servis hesabını ve kurulum yaptığınız kullanıcıyı yetkilendirmeniz gerekir.

Ben aşağıdaki gibi Sunucu1 üzerinde Replication isimli bir klasör oluşturup gerekli yetkileri verdim ve Snapshot folder olarak paylaşım adresini yazdım. Next diyerek ilerliyoruz.

Bir sonraki ekranda aşağıdaki gibi distribution veritabanının data ve log dosyalarının hangi path’lerde tutulacağı bilgilerini girip next diyerek ilerliyoruz.

Bir sonraki ekranda bu distribution veritabanının hangi Publisher’ın kullanacağını belirliyoruz. Biz Publisher ve distributor’ın aynı instance üzerinde kuracağımız için aynı instance’ı seçili bırakıp next diyerek ilerliyoruz.

Bir sonraki ekranda aşağıdaki gibi Configure Distribution seçili iken next ve Finish diyerek distributor yapılandırmasını tamamlıyoruz.

Kurulum tamamlandıktan sonra aşağıdaki gibi sistem veritabanlarının içinde distribution veritabanını görmeniz gerekir.

Distributor yapılandırmamız bittikten sonra aynı sunucuda(Publisher ve Distributor’u aynı sunucuda yaptığımız için) aşağıdakigibi Local Publications’a sağ tıklayarak New Publication diyoruz.

Gelen ekranda aşağıdaki gibi replike edeceğimiz veritabanını seçiyoruz.

Gelen ekranda aşağıdaki gibi Transactional Replication’ı seçerek Next diyoruz.

Bir sonraki ekranda article’ları soruyor. Biz sadece Person.Person tablosunu aktaracağımız için aşağıdaki gibi seçim yapıp next diyoruz.

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. 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 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.

Benim kendi test ortamımda iki sql server instance’ı ve iki sql server agent instance’ı da aynı windows sql server servis hesabını kullanıyor ve gerekli yetkileri var. Snapshot paylaşımı üzerinde de bu kullanıcıyı daha önce yetkilendirdim. Bu yüzden aşağıdaki gibi set ederek ilerliyorum.

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.

Bir sonraki adımda Subscriber’ı kuracağımız instance’a geçiyoruz ve aşağıdaki gibi Local Subscription’a sağ tıklayarak New Subscriptions diyoruz.

Sonraki ekranda publisher yazan yerden Publisher’ı kurduğumuz 1.Sunucudaki instance’ı seçiyoruz ve aşağıdaki ekran karşımıza çıkıyor. Next diyerek ilerliyoruz.

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. “SQL Server Replication Nedir?” isimli makalemi okuduysanız oradaki örnekten devam edersek  Stok takip personeli gazeteleri abonelere kendimi dağıtacak yoksa aboneler gazete deposuna gelip gazateyi kendimi alacak sorusudur diyebiliriz.

Microsoft aşağıdaki ekranda görüldüğü gibi yönetimin kolay olması için stok takip personelinin biraz yorulmasını tercih edebilirsiniz diyor. Bizde yönetimin kolay olması için push subscriptions’ı seçerek Distrubution agent Location’ı Distributor’un bulunduğu instance’ta çalışacak şekilde set ediyoruz ve next diyerek ilerliyoruz.

Bir sonraki ekranda aşağıdaki gibi replike edeceğimiz tabloları hangi instance’taki hangi veritabanına replike edeceğimizi soruyor. Add Subscriber diyerek yeni bir instace seçebiliriz. Subscriber’dan replike edilecek veritabanının instance’ını, SubscriptionDatabase den de replike edilecek veritabanını seçerek devam ediyoruz. Ben ReplikeTest isimli bir veritabanı oluşturup bu veritabanını seçerek Next dedim.

Bir sonraki 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 AdventureWorks2014 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 AdventureWorks2014 üzerinde de yetkilendirmeniz gerekir.

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.

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.

Biz sürekli replike etmesini istediğimiz için Run continuously’yi seçiyoruz.

Bir sonraki ekranda, subscriber’a(asıl veritabanını replike edeceğimi veritabanı. Bizim örneğimizde ReplikeTest isminde oluşturduk.) hemen verileri aktarmak için Immediately’yi seçerek next diyoruz.

Bir sonraki ekranda aşağıdaki gibi Create the subscription(s) seçili haldeyken next diyoruz ve Finish diyerek işlemi tamamlıyoruz.

Publisher ve Distributor’un olduğu instance’da View Job history diyerek replikasyon için oluşturulan job’ları ve doğru çalışıp çalışmadıklarını görebilirsiniz. Makale de anlattığımız yetkileri verdiğinizden emin olun.

Replikasyonun sonunda aşağıda da görebildiğiniz gibi Person.Person tablomuz ReplikeTest veritabanına başarılı bir şekilde aktarıldı.

Şimdi asıl veritabanımız olan AdventureWorks2014 veritabanındaki Person.Person tablosuna manual bir kayıt ekleyerek ReplikeTest veritabanına senkronize olup olmayacağına bakalım.

Aşağıdaki script yardımıyla kaydımızı ekleyelim.

USE [AdventureWorks2014]
GO
INSERT INTO [Person].[BusinessEntity]
           ([rowguid]
           ,[ModifiedDate])
     VALUES
           (NEWID()
           ,'2016-10-21')
GO
INSERT INTO [Person].[Person]
           ([BusinessEntityID]
           ,[PersonType]
           ,[NameStyle]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[EmailPromotion]
           ,[AdditionalContactInfo]
           ,[Demographics]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           (1
           ,'EM'
           ,0
           ,'t'
           ,'t'
           ,'test'
           ,'test'
           ,'test'
           ,2
           ,null
           ,null
           ,newid()
           ,'2016-10-21')
GO

Insert sonrası subscriber’a giderek ReplikeTest veritabanındaki Person.Person tablosuna select çektiğimizde ilgili kaydın replike edildiğini göreceksiniz.

Replikasyon durumunu ilgili Publication’ın üstüne sağ tıklayarak Launch Replication Monitor diyerek aşağıdaki şekilde görebilirsiniz.

Açılan ekranda warning sekmesine gelerek alert üretilmesinide sağlayabilirsiniz. Diğer sekmelerden de replikasyon durumu hakkında bilgi alabilirsiniz.