Bu makalede mssql server üzerinde herhangi bir kullanıcının veritabanı üzerinde açtığı connection sayısınını sınırlayacağız. Öncele bu işlemi Resource Governor ile yapılabilirmi. SQL Server Resource Governor ile doğrudan bir kullanıcıya maksimum bağlantı (connection) sayısı limiti veremezsiniz. Resource Governor şu kaynakları kontrol işlemi yapmaktadır. Cpu kullanımı, Memory kullanımı, I/O bant genişliği ve sorgulardaki paralellik yapısına bakmaktadır.
Kullanıcıya bağlantı limiti vermenin yolu genelde LOGON TRIGGER ile çözülür.
1. Kullanıcı giriş yaparken tetiklenir
2. O kullanıcıya ait aktif oturum sayısı sayılır.
3. Limit aşılmışsa bağlantı reddedilir.
Burada dikkat edilmesi gereken hayalet oturumların olması trigger işlemi sonucu bağlantı hatası almanıza sebep verecektir. Aşağıdaki komutla tüm sessionları görebiliriz.
SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND original_login_name = 'YUNUS'Connection Pooling: Eğer bir uygulama üzerinden bağlanıyorsanız, uygulama bağlantıyı kapatsa bile SQL Server bağlantıyı bir süre “uyur” (dormant) halde tutabilir. Bu da belirlediğiniz sayı üzerinde görünmesine neden olur.
Aşağıdaki resimde Yunus login’inde bağlantı sayısı 5 üzeriyse Trigger tetiklenmesine sebep olacaktır. 5 üzeri olan login giriş denemelerinde hata mesajıyla karşılaşmış oluruz.
CREATE TRIGGER trg_LimitConnections
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @LoginName SYSNAME
SET @LoginName = ORIGINAL_LOGIN()
IF @LoginName = 'YUNUS'
BEGIN
IF (
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = 'YUNUS'
) > 5-- Değer burada belirtilir.
BEGIN
ROLLBACK;
END
END
ENDYukarıda instance seviyesinde tanımlanan trigger’da tüm kullanıcılar geçmek zorundadır. Trigger YUNUS login’ine özel yapılmış olsa bile, çünkü trigger’a baktığınızda server seviyesinde oluşturulmuştur.
Detaylı bir şekilde açıklamasını yapıp gerçek sistem üzerinde önerilen trigger yapısını oluşturulım.
Yukarıdaki kod parçasında “On All Server” (Tüm Sunucu İçin) trigger’ıdır. Bu şu anlama gelir: Sunucuya kim bağlanmaya çalışırsa çalışsın (Ahmet, Mehmet, Admin veya Yunus), SQL Server önce bu kodu çalıştırır.
Süreç şöyle ilerler:
- Bir kullanıcı (Örn: AHMET) kullanıcı adı ve şifresini girer.
- Trigger tetiklenir ve SQL Server der ki: “Dur, İçeride her bağlantı için çalışması gereken bir kod var, önce onu çalıştırmalıyım.”
- Kodun en başındaki DECLARE @LoginName ve SET @LoginName = ORIGINAL_LOGIN() satırları tüm kullanıcılar için çalışır.
- Kod, IF @LoginName = ‘YUNUS’ satırına gelince Ahmet için “Hayır” der ve çıkar. Ahmet ikinci if bloğuna girmez.
Eğer 3. adımda (Yunus’un ismine bakmaya çalışırken) bir sistemsel veya anlık bir durumdan dolayı hata oluşursa, SQL Server “Ben bu kodu tamamlayamadım, güvenliği riske atamam” der ve bu trigger aracılığı ile içeri aldığı tüm kullanıcıların bağlantısını kesmektedir. Kapıyı kendi üzerinde geçen tüm başarılı kullanıcıları atmaktadır. Sysadmin bağlantısı bile olsa.
Kodun içinde hata olsa bile diğer kullanıcıların (ve hatta Yunus’un bile) tamamen kilitlenmemesi için “Hata Yakalama” (Try-Catch) kullanmalısın. Eğer “Ben sadece Yunus’un ismini kontrol etmek istiyorum ve başka hiçbir şey sistemi yormasın” diyorsan yapı şu olmalı:
CREATE TRIGGER trg_LimitConnections
ON ALL SERVER
FOR LOGON
AS
BEGIN
-- 1. ADIM: Hata yönetimini başlat. İçeride ne hata olursa olsun bağlantıyı KOPARMA.
BEGIN TRY
-- 2. ADIM: Sadece YUNUS ise içeri gir. Diğerleri bu IF'e takılmadan uçup gitsin.
IF ORIGINAL_LOGIN() = 'YUNUS'
BEGIN
-- 3. ADIM: Yunus'un bağlantı sayısını kontrol et.
IF (
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = 'YUNUS'
) > 5
BEGIN
-- Sınırı aştıysa sadece Yunus'u dışarı at.
ROLLBACK;
END
END
END TRY
BEGIN CATCH
-- 4. ADIM: Eğer yukarıdaki SELECT sorgusu veya IF kontrolü sırasında
-- bir hata olursa (yetki hatası, sistem yoğunluğu vb.) BURAYA GELİR.
-- Burayı boş bıraktığımız için bağlantı güvenli bir şekilde devam eder.
END CATCH
END
İlk kodda sys.dm_exec_sessions tablosuna bakarken bir hata oluşursa (örneğin o an tablo kilitliyse), tüm server kilitlenirdi. Bu yeni kodda hata olursa CATCH bloğuna düşer ve hiçbir şey olmamış gibi hayat devam eder.
SQL Server’da Logon Trigger’lar “Siyah-Beyaz” gibidir. Kod başarıyla biterse kapı açılır, kod en ufak bir hata verirse (kim olduğuna bakmaksızın) kapı yüzüne kapanır. Bu BEGIN TRY…CATCH bloğu bir sigorta gibidir.
Oluşturulan Trigger server seviyesinde olduğu için SSMS üzerinde Server Object kısmında bulunan Triggers kısmından server seviyesinde oluşturulan trigger görülebilir.
Aşağıdaki komut ile Server seviyesinde Trigger’ları görebiliriz.
SELECT * FROM sys.server_triggers
WHERE type_desc = 'SQL_TRIGGER';
Eğer trigger hatalı çalışıyor ve bağlantıları engelliyorsa (veya üzerinde değişiklik yapmak istiyorsanız) şu komutları kullanabilirsiniz:
Trigger’ı Geçici Olarak Devre Dışı Bırakmak:
DISABLE TRIGGER trg_LimitConnections ON ALL SERVER;Eğer yanlışlıkla tüm girişleri kapatırsanız, SQL Server’a DAC (Dedicated Administrator Connection) üzerinden bağlanarak trigger’ı devre dışı bırakmanız gerekir:
Trigger’ı Tekrar Aktif Etmek:
ENABLE TRIGGER trg_LimitConnections ON ALL SERVER;Trigger’ı Tamamen Silmek:
DROP TRIGGER trg_LimitConnections ON ALL SERVER;Eğer trigger yüzünden sunucuya hiç bağlanamıyorsanız, SQL Server’ı “Minimal Configuration Mode” (-f parametresiyle) başlatarak trigger’ları devre dışı bırakıp sisteme girebilirsiniz. Aynı işlemi DAC ile de yapabiliriz.
Önemli Uyarı: Logon Trigger’lar tehlikelidir. Eğer trigger içinde bir hata oluşursa, ilgili kullanıcının (hatta bazen tüm kullanıcıların) server’a girmesini tamamen engelleyebilirsiniz. Bu sebepten Trigger’ın dikkatli oluşturulması gerekiyor.
En doğru yaklaşım Application-level connection pool limiti koymaktır. Firewall / Proxy → IP bazlı sınırlama yapılabilir
Bu makalede Login trigger kavramını görmüş olduk. Başka makalede görüşmek dileğiyle…
Boş konuşmalardan kaçının Müminin-3
