Mart 2018 – Faruk Erdem

Aylar: Mart 2018

SQL Data Discovery and Classification

Posted on 26 Mart 201820 Eylül 2018 by ferdem

SQL Data Discovery and Classification , veri gizliliği standartlarını ve GPDR(General Data Protection Regulation) gibi yasal uyumluluk gereksinimlerini karşılamak ve veritabanında bulunan sütunların kritik seviyelerini öğrenebileceğimiz ve bunları kendimize göre etiketleyip rapor halinde sunabileceğimiz SSMS 2017(17.5) üzerine entegre edilmiş faydalı tool’dur.
SQL Data Discovery and Classification, SQL Server 2008 ve sonrası için desteklenir.

Bu kadar bilgiden sonra uygulamaya geçelim 🙂

Veritabanı üzerinde sağ tuş yaptıktan sonra Tasks/Classify Data sekmesini seçiyoruz.

Classify Data sekmesine tıkladıktan sonra aşağıda ki ekran gelecektir bu ekranda manuel olarak kolonları ekleyebilir yada aşağıdaki gibi mevcut kolonları işaretli SSMS yardımı ile otomatik ekleyebilirsiniz.

İşaretli olan yer seçildikten sonra kolonları seçiyoruz , buradan verilerin hassasiyetini kendimiz manuel olarak da değiştirebiliriz bu işlemler yapıldıktan sonra “Accept selected recommendations” butonuna tıklayarak veri analizine başlatıyoruz ve bundan sonra save butonuna tıklayarak son durumu kaydetmiş oluyoruz.

Bütün işlemler bittikten sonra “View Report” sekmesine tıklayarak raporlama kısmına geçiyoruz.

Ve rapor aşağıda ki gibi veri hassasiyetlerine göre sınıflandırılmış şekilde karşımıza çıkıyor.

SQL de Loginleri Kolay Yoldan Bulmak

Posted on 26 Mart 2018 by ferdem

Birden fazla instance üzerinde veritabanlarınız koşuyor ve veritabanı içinde birden fazla kullanıcıyı yetkilendirme yapıyorsanız aşağıda ki script işinize yarayacaktır. Aşağıda ki script Windows login ve SQL Login’lerin oluşturulma tarihleri ve sysadmin yetkisi var ise sysadmin yetkisini bulmanızı yardımcı olacaktır.

select loginname,
(CASE sysadmin 
WHEN 0 
THEN 'SysAdmin yetkisi bulunmamaktadır.' 
ELSE'SysAdmin yetkisi bulunmaktadır.' end) as sysadmin_kontrol
,createdate
 from sys.syslogins
 where loginname not like '%##%'
and loginname not like '%NT%'
and loginname like '%Aranacak Login İsmi%'

select loginname,

(CASE sysadmin

WHEN 0

THEN 'SysAdmin yetkisi bulunmamaktadır.'

ELSE'SysAdmin yetkisi bulunmaktadır.' end) as sysadmin_kontrol

,createdate

from sys.syslogins

where loginname not like '%##%'

and loginname not like '%NT%'

and loginname like '%Aranacak Login İsmi%'

SQL de Sysadmin yetkisine sahip kullanıcıları görüntülemek

Posted on 22 Mart 201822 Mart 2018 by ferdem

Bazı durumlarda sysadmin yetkisine sahip kullanıcıları görmek isteriz manuel olarak ssms üzerinden loginlere tek tek bakmaktan ise aşağıda ki T-SQL ‘i kullanarak listeyebilirsiniz.

Aşağıda ki T-SQL de SQLServer Authentication ve Windows Authentication loginlerin sysadmin olanları görüntülenmektedir.

SELECT 
 USER_NAME(grantee_principal_id) AS 'User'
 , state_desc AS 'Permission'
 , permission_name AS 'Action'
 , CASE class
 WHEN 0 THEN 'Database::' + DB_NAME()
 WHEN 1 THEN OBJECT_NAME(major_id)
 WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable',dr.authentication_type_desc
FROM sys.database_permissions dp
join sys.database_principals as dr on dp.grantee_principal_id=dr.principal_id
WHERE class IN (0, 1, 3)
AND minor_id = 0 
and USER_NAME(grantee_principal_id) not like 'public'

SELECT

USER_NAME(grantee_principal_id) AS 'User'

, state_desc AS 'Permission'

, permission_name AS 'Action'

, CASE class

WHEN 0 THEN 'Database::' + DB_NAME()

WHEN 1 THEN OBJECT_NAME(major_id)

WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable',dr.authentication_type_desc

FROM sys.database_permissions dp

join sys.database_principals as dr on dp.grantee_principal_id=dr.principal_id

WHERE class IN (0, 1, 3)

AND minor_id = 0

and USER_NAME(grantee_principal_id) not like 'public'

MSSQL Compatibility Level

Posted on 16 Mart 201816 Mart 2018 by ferdem

SQL Server da her sürüm için veritabanına numara verilmiştir. SQL Server belirtilen veritabanın davranışlarının belirtilen sürüm ile uyumlu olacak şekilde ayarlar bundan dolayı bazı özellikler bazı sürümlerde kullanılmamaktadır.

Örneğin: SQL Serverın 2012 versiyonunda Alter Table komutunu kullanırken servername.databaseadı.schemadı.table formatında kullanılmıyordu fakat SQL Serverın 2014 versiyonundan sonra bu formatta da kullanabiliriz. Daha iyi anlaşılabilmesi için aşağıda örnek ile açıklamak istedim.

SQL Server 2012 : Alter Table dbo.Table_1 ADD name nvarchar(30)

SQL Server 2014 ve Sonrası için: Alter Table MSSQLServer.Mssqlİnstance. Dbo.Table_1 ADD name nvarchar(30)

Burada dikkat edilecek diğer bir konu eski sürümden yeni sürüme geçtiğinizde eğer birkaç versiyon birden atlama yaptıysanız SQL Server Compatibility Level’e kendi destekleyeceği en düşük seviyeye çekecektir. Bundan dolayı bazı problem yaşayabilirsiniz. Compatibility Level’i Veritabanın üzerine sağ tıklayarak Properties kısmındaki options içinde yer alan Compatibility Level bölümünden değiştirebiliriz.

Örnek olarak SQL Server 2008 kullanılırken SQL Server 2012 ye geçtiğinizde hala 2008 versiyonunda ki bazı şeyleri kullanabilirsiniz fakat SQL Server 2016 versiyonuna yükselttiğinizde SQL Server 2008 de bulunan bazı özellikleri kullanamayacaksınız.

Aşağıda Sql Server sürümleri ve onlara karşılık gelen compatibility leveller bulunmaktadır.

Ürün	Veritabanı Sürümü	Uyumluluk Düzeyi Tanımı	Desteklenen Uyumluluk Seviyesi Değerleri
SQL Server 2017	14,00	140	140, 130, 120, 110, 100
Azure SQL Veritabanı	12,00	*130	140, 130, 120, 110, 100
SQL Server 2016	13,00	130	130, 120, 110, 100
SQL Server 2014	12,00	120	120, 110, 100
SQL Server 2012	11,00	110	110, 100, 90
SQL Server 2008 R2	10,00	100	100, 90, 80
SQL Server 2008	10,00	100	100, 90, 80
SQL Server 2005	9,00	90	90, 80
SQL Server 2000	8,00	80	80

*Not: Azure SQL veritabanları 12 aralık 2014 de piyasaya sürülmüştür. İlk piyasaya sürüldüğünde compatibility level’i 120 olarak ayarlanmıştır fakat 2015 de yeni veritabanı oluşturulduğunda default’u 130 olarak gelmektedir.

Availability Group da Bulunan Primary Node’un En Son Backupının Ne Zaman Alındığını Görmek

Posted on 16 Mart 201816 Mart 2018 by ferdem

Always-On yapısını kullanıyorsanız ve sadece o instance üzerinde ki aktif veritabanlarının son backuplarının ne zaman alındığını görmek istiyorsanız aşağıda ki scriptten faydalanabilirsiniz.

SELECT sd.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(bs.backup_finish_date), 101),'Daha Backup Alınmamış') as LastBackUpDate,
COALESCE(Convert(varchar(12), MAX(bs.user_name), 101),'NA') as UserName
FROM sys.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sd.name
INNER JOIN sys.dm_hadr_database_replica_states dhrs ON DB_NAME(dhrs.database_id)=bs.database_name
where dhrs.is_primary_replica=1
GROUP BY sd.Name
ORDER BY sd.Name

SELECT sd.Name as DatabaseName,

COALESCE(Convert(varchar(12), MAX(bs.backup_finish_date), 101),'Daha Backup Alınmamış') as LastBackUpDate,

COALESCE(Convert(varchar(12), MAX(bs.user_name), 101),'NA') as UserName

FROM sys.sysdatabases sd

LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sd.name

INNER JOIN sys.dm_hadr_database_replica_states dhrs ON DB_NAME(dhrs.database_id)=bs.database_name

where dhrs.is_primary_replica=1

GROUP BY sd.Name

ORDER BY sd.Name

Veritabanı Adını Değiştirmek

Posted on 16 Mart 201820 Eylül 2018 by ferdem

Veritabanın adını değiştirmek için farklı yöntemler var.

En basit yöntem olarak SSMS(SQL Server Management Studio) üzerinden değiştirmek fakat veritabanı üzerinde session veya lock varsa veritabanı ismini değiştiremezsiniz. Bu yöntemi kullanmak istiyorsanız sp_who yardımı ile sorgulara bakarak sessionları kill edip veritabanı ismini değiştirebilirsiniz.
Bununla ilgili Rename Failed isimli makalemden yararlanabilirsiniz.

Diğer bir yöntem ise aşağıda ki gibi veritabanını single_user mode’a çekerek veritabanı üzerinde ki sorguları otomatik olarak kill etmiş oluyorsunuz veritabanı ismini değiştirerek multi_user’a tekrar çektiğinizde veritabanı adı değişmiş oluyor.

ALTER DATABASE Şuanki_Veritabanı_Adı SET SINGLE_USER WITH ROLLBACK IMMEDIATE ----->>burda tek kullanıcı moduna çekiyoruz.
GO
alter database Şuanki_Veritabanı_Adı MODIFY NAME= Yeni_Veritabanı_Adı

ALTER DATABASE Yeni_Veritabanı_Adı SET MULTI_USER WITH ROLLBACK IMMEDIATE ----->>burda eski moduna çekiyoruz.

GO

ALTER DATABASE Şuanki_Veritabanı_Adı SET SINGLE_USER WITH ROLLBACK IMMEDIATE ----->>burda tek kullanıcı moduna çekiyoruz.

alter database Şuanki_Veritabanı_Adı MODIFY NAME= Yeni_Veritabanı_Adı

ALTER DATABASE Yeni_Veritabanı_Adı SET MULTI_USER WITH ROLLBACK IMMEDIATE ----->>burda eski moduna çekiyoruz.

*Not: Veritabanı failover cluster ,availability group,Log Shipping gibi mimarileri kullanıyorsa yukarıdaki veritabanı ismini değiştiremezsiniz.

SQL Anlık Sorguları Görmek

Posted on 16 Mart 201816 Mart 2018 by ferdem

DBA olarak anlık sorguları görmek isteriz . Aşağıda ki sorgu da sorgunun ne kadar sürdüğünü, locklanıyorsa hangi session_id ile locklandığını ,sorgunun bekleme tipini ve hangi makineden geldiği gibi bilgileri bu sorgu yardımı ile görebiliriz.

select r.total_elapsed_time / 1000.0 as total_elapsed_s,percent_complete,r.blocking_session_id,
r.last_wait_type,
s.login_name,'MySessionID= ' + cast(r.session_id as varchar) as MySessionID,DB_NAME(r.database_id) as DatabaseName,command
,SUBSTRING(t.text, (r.statement_start_offset/2) + 1,
((CASE statement_end_offset 
WHEN -1 THEN DATALENGTH(t.text)
ELSE r.statement_end_offset END 
- r.statement_start_offset)/2) + 1) AS statement_text
,r.status,wait_time
,wait_type,wait_resource,text,start_time,s.program_name
,r.last_wait_type,s.host_name,r.granted_query_memory * 8 / 1024 as memory_mb
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.session_id <> @@SPID 
--and r.database_id =DB_ID('Hangi DB'ye gelen sorguları listelemek istiyorsanız')
--and t.text like '%text bazlı sorgulama yapmak için text içeriği%' 
--and r.session_id = sessionid
--and s.Login_Name like '%Login bazlı filtreleme%' 
--and s.program_name LIKE '%Program bazlı filtreleme örneğin Management%'
--and r.wait_type !='bekleme tipine göre filtreleme'
--and r.blocking_session_id<>0"

order by start_time asc

select r.total_elapsed_time / 1000.0 as total_elapsed_s,percent_complete,r.blocking_session_id,

r.last_wait_type,

s.login_name,'MySessionID= ' + cast(r.session_id as varchar) as MySessionID,DB_NAME(r.database_id) as DatabaseName,command

,SUBSTRING(t.text, (r.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(t.text)

ELSE r.statement_end_offset END

- r.statement_start_offset)/2) + 1) AS statement_text

,r.status,wait_time

,wait_type,wait_resource,text,start_time,s.program_name

,r.last_wait_type,s.host_name,r.granted_query_memory * 8 / 1024 as memory_mb

from sys.dm_exec_requests r

inner join sys.dm_exec_sessions s on r.session_id = s.session_id

cross apply sys.dm_exec_sql_text(r.sql_handle) t

where r.session_id <> @@SPID

--and r.database_id =DB_ID('Hangi DB'ye gelen sorguları listelemek istiyorsanız')

--and t.text like '%text bazlı sorgulama yapmak için text içeriği%'

--and r.session_id = sessionid

--and s.Login_Name like '%Login bazlı filtreleme%'

--and s.program_name LIKE '%Program bazlı filtreleme örneğin Management%'

--and r.wait_type !='bekleme tipine göre filtreleme'

--and r.blocking_session_id<>0"

order by start_time asc

SQL Server Analysis Service Port Değiştirirken Alınan Hata(The service cannot be started: The following system error occurred: Only one usage of each socket address (protocol/network address/port) is normally permitted. The following system error occurred: Only one usage of each socket address (protocol/network address/port) is normally permitted. )

Posted on 9 Mart 2018 by ferdem

“SQL Servisinin Başlamama Sorunu” isimli makalemdeki farklı servislerin aynı portu kullanmasından dolayı aldığımız hata mesajı burda da karşıma çıkıyoruz fakat oradaki çözümden farklı bir yol izleyeceğiz.

Servisi başlatırken yukarıdaki gibi hata ile karşılaşırsanız event viewer dan application kısmında ki loglar incelemeniz gerekir.

Yukarıda ki sorunun sebebi iki instancen aynı portu kullanmak istemesidir. Bundan dolayı instancen portu değiştirmemiz gerekmektedir. SSMS üzerinden değiştiremeyiz çünkü bağlanmaya çalıştığımızda servis durduğu için bağlanamazsınız bundan dolayı da aşağıda ki path de bulunan msmdsrv.ini dosyasının içinde ki port yazan kısmı değiştirmemiz gerekiyor.

Yukarıda ki değişikliği yaptıktan sonra servisi başlatarak bağlantı kurabilirsiniz.

SQL Servisinin Başlatılamaması(The request failed or the service did not respond in a timely fashion.Consult the event log or other applicable error logs for details.)

Posted on 9 Mart 2018 by ferdem

Servisi başlatırken yukarıda ki hata alıyorsanız bunun sebeplerinden birisi port’un farklı bir uygulama tarafından kullanılıyor olmasıdır. Bu sorunu çözmek için eğer server da birden fazla instance varsa bunun konfigurasyonu sırasında iki instance aynı portu vermiş olabilirsiniz .1434 ‘ü ilk tanımlandığınız servis açılır fakat diğer servis açılırken yukarıda ki gibi hata verir.
İşletim seviyesinde(Event viewer) logları incelediğimizde aşağıda ki gibi hata mesajı döner.

SQL Server Configuration Manager dan hangi instance’ın hangi veritabanını kullandığını bulamadıysak aşağıda ki komutu kullanarak 1434 portunu kullanan ip’leri görebiliriz.

netstat -n -a -o  | find "aranacak port Ör:1433 "

1	netstat -n -a -o \| find "aranacak port Ör:1433 "

Yukarıdaki komut iki farklı değer döndürüyorsa listening yazan kullanılan porttur diğeri ise boş olan porttur. Boş olan portun spid’sini aşağıda ki gibi kill ederek sorunu çözebilirsiniz.

Taskkill /F /PID 3312

1	Taskkill /F /PID 3312