Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Haziran 2017  >>
PaSaÇaPeCuCuPa
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
Keywords

DBA Mail Alert yazı dizisindeki 4. yazım Disk Kullanım Değerleri üzerine. Bu SP vasıtasıyla server a mount edilmiş disklerin doluluk oranlarını görebilir, belirtiğiniz orandan büyükleri schedule vasıtasıyla belirli aralıklarla mail attırabilirsiniz.

CREATE proc [DiskUsageMailSP]
as

DECLARE @CURR INT
CREATE TABLE #T(NAME VARCHAR(100),MINN INT,MAXX INT,CONF INT,RUNN INT)
INSERT INTO #T EXEC sp_configure
SELECT @CURR=RUNN FROM #T WHERE NAME='Ole Automation Procedures'
IF @CURR=0
BEGIN
EXEC sp_configure 'Ole Automation Procedures',1
RECONFIGURE
END

declare @Rpar int
select @Rpar = 50 --Doluluk oranı %50 den büyük olan diskler raporlansın

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'DiskSpaceStats' AND type='U') 
BEGIN 
declare @RunLocal bit = 1
      CREATE TABLE [dbo].[DiskSpaceStats] 
      ( 
            DriveLetter       char(1),
            TotalSize         decimal(20,0),
            AvailableBytes    decimal(20,0),
            DriveType         char(10),
            PercentFree       decimal(18,3),
            Report_Date       datetime default getdate() 
      )
      ON [PRIMARY]
END 
 
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#DiskSpace%') 
DROP TABLE #DiskSpace 
CREATE TABLE #DiskSpace
(
      RowID int Identity Primary Key, 
      DriveLetter char(1),
      TotalSize decimal(20,0),
      AvailableBytes decimal(20,0),
      DriveType char(10),
      PercentFree decimal(18,3)
)
 
DECLARE @counter int
DECLARE @FSOobject int
DECLARE @RecordCount int
DECLARE @count int
DECLARE @string  char(35)
DECLARE @DriveLetter char(1)
DECLARE @TotalSize char(50)
DECLARE @AvailableSpace char(50)
DECLARE @DriveType char(10)
DECLARE @error int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)

 

SET @count=0
SET @counter=67
SET @error=0
      
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSOobject OUT
 
IF @hr <> 0
      BEGIN
            EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
            PRINT @desc
            RETURN
      END
 
WHILE @counter < 91 OR @error <3
      BEGIN
            SET @String='Drives.item("'+char(@counter)+'").DriveLetter'
            EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveLetter OUT
            IF @HR <> 0
                  BEGIN
                        EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                        SELECT @counter=@counter+1
                        SELECT @error=@error +1
                        GOTO ErrorPoint
                  END
 
            SET @String='Drives.item("'+char(@counter)+'").DriveType'
            EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveType OUT
            IF @HR <> 0
                  BEGIN
                        EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                        PRINT @desc
                        GOTO ErrorPoint   -- tugi --RETURN                   
                        --RETURN
                  END
 
        IF @DriveType = 2 BEGIN
                  SET @String='Drives.item("'+char(@counter)+'").TotalSize'
                  EXEC @HR = sp_OAGetProperty @FSOobject, @string, @TotalSize OUT
                  IF @HR <> 0
                        BEGIN
                             EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                             SELECT @counter=@counter+100
                             SELECT @error=1
                        END 
       
                  SET @String='Drives.item("'+char(@counter)+'").AvailableSpace'
                  EXEC @HR = sp_OAGetProperty @FSOobject, @string, @AvailableSpace OUT
                  IF @HR <> 0
                        BEGIN
                             EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                             PRINT @desc
                             GOTO ErrorPoint --tugi --RETURN                      
                             --RETURN
                        END
 
                  IF @Error < 3
                        BEGIN
                             INSERT INTO #DiskSpace (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
                             VALUES (@DriveLetter, @TotalSize, @AvailableSpace, @DriveType, (convert(decimal(20,0), @AvailableSpace)/convert(decimal(20,0), @TotalSize)))
                        END
            END
   
            SELECT @counter=@counter+1
      END
ErrorPoint:
 
INSERT INTO [dbo].[DiskSpaceStats] (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
SELECT DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree 
FROM #DiskSpace
 
      EXEC @hr = sp_OADestroy @FSOobject
      IF @hr <> 0
            BEGIN
                  EXEC sp_OAGetErrorInfo @FSOobject
            RETURN
      END
 
select * from #DiskSpace

IF @CURR=0
BEGIN
EXEC sp_configure 'Ole Automation Procedures',0
RECONFIGURE
END

 

Diğer DBA Mail Alert lerle alakalı yazılarıma ulaşmak için aşağıdaki linkleri kullanabilirsiniz.

Database File Statistics

Log Stats

Long Running Queries

Disk Usage Report

 

İyi Çalışmalar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


DBA Mail Alert yazı dizisindeki 3. yazım uzun süredir çalışan query ler üzerine. Bu SP vasıtasıyla sistemde uzun süredir çalışan ve kaynakları yiyip tüketen:) queryleri monitor edebilir , belirtiğiniz süreden daha uzun süredir devam edenleri schedule vasıtasıyla belirli aralıklarla mail attırabilirsiniz.

CREATE proc [LongRunningQueriesMailSP]
as
declare @Rparam int
select @Rparam =60 --60 sn den uzun süren queryleri getirmek için


 create table #TempGONDER(
 DBName_SPID varchar(100),
 lastwaittype varchar(100),
 cmd varchar(100),
 Time varchar(100),
 HostAndLogin varchar(100),
 SSS int
 );
 INSERT INTO #TempGONDER  select DB_NAME(p.dbid)+' - '+CONVERT(varchar(5),p.spid) as DBName_SPID,
p.lastwaittype,p.cmd,
CONVERT(VARCHAR(5),DATEDIFF(S,p.last_batch,GETDATE())/3600)+' hour '+CONVERT(VARCHAR(5),(DATEDIFF(S,p.last_batch,GETDATE())%3600)/60)+' minute ' +CONVERT(VARCHAR(5),((DATEDIFF(S,p.last_batch,GETDATE())%3600)%60)%60)+' second' as Time,
RTRIM(LTRIM(hostname)) +' - '+RTRIM(LTRIM(loginame))  as HostAndLogin,
DATEDIFF(S,p.last_batch,GETDATE())
from sys.sysprocesses p 
where p.spid >50
and p.lastwaittype!='MISCELLANEOUS' and p.status!='sleeping'
and (p.program_name='Microsoft SQL Server Management Studio - Query' or 
p.program_name  like '%.Net%')
and p.spid!=@@spid 
and DATEDIFF(S,p.last_batch,GETDATE())>@Rparam
 
select *FROM #TempGONDER 

 

Diğer DBA Mail Alert lerle alakalı yazılarıma ulaşmak için aşağıdaki linkleri kullanabilirsiniz.

Database File Statistics

Log Stats

Long Running Queries

Disk Usage Report

 

İyi Çalışmalar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


DBA Mail Alert yazı dizisindeki 2. yazım Log Statslar üzerine. Bu SP vasıtasıyla hangi DB log unun ne kadar dolulukta olduğunu görebilir, belirtiğiniz orandan büyükleri schedule vasıtasıyla belirli aralıklarla mail attırabilirsiniz.

CREATE proc [LogStatsMailSP]
as
declare @CmpRate float
select @CmpRate = 70 --%70 den fazla kullanılan Log file ları monitor etmek

create table #SQLPerfLogS(DatabaseName varchar(100),LogSize varchar(100),LogUsed varchar(100)
,Status varchar(10));

insert into #SQLPerfLogS exec('dbcc sqlperf(logspace)')
select * from #SQLPerfLogS	

 

Diğer DBA Mail Alert lerle alakalı yazılarıma ulaşmak için aşağıdaki linkleri kullanabilirsiniz.

Database File Statistics

Log Stats

Long Running Queries

Disk Usage Report

 

İyi Çalışmalar





	
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


DBA lerin en önemli işlerinden biride serverları ı monitor etmektir. Database lerin doluluk durumları, Log file ların kullanım oranları, uzun süredir çalışan query ler yada data file ların bulunduğu disklerin durumunu takip etmek serverların availability lerini sağlamak için oldukça önemlidir.

Size bu konu üzerine yazmış olduğum 4 SP den bahsedeceğim. Bu SP ler için schedule ayarlayarak attıracağınız mailler ile server ların durumlarını monitör edebilirsiniz.Mail göndermek ile alakalı daha önceden yazmış olduğum yazımı okumanızı tavsiye ederim. Bu yazıma aşağıdaki linkten ulaşabilirsiniz.

http://www.turgaysahtiyan.com/post/SQL-Server-2008-den-Mail-Gonderme.aspx


İlk yazım Database File Statistic SP si. Yani verdiğiniz parametreden daha büyük doluluk oranlarına sahip olan DB leri monitör etmek.

Database File Statistic

CREATE proc [DatabaseFileStatsMailSP]
as
declare @Rparam int
select @Rparam = 70 --%70 den fazlası dolu olan DB leri mail atacağız.

 create table #TempGONDER(DatabaseName varchar(100),CurrentDataFileSizes int,CurrentDataSizes int,UnAllocatedDataSize int,UnAllocatedDataRate int,AllocatedDataRate int,HasDisableAutoGrowthFile tinyint,DataFileCount tinyint);
 EXEC sp_MSforeachdb '
 use ?;
 insert into #TempGonder
 select DatabaseName =DB_NAME(),
 CurrentDataFileSizes=(select SUM((size*8)/1024) from sys.database_files(nolock) where type=0),
 CurrentDataSizes=(SELECT     (SUM (reserved_page_count)*8)/1024  FROM sys.dm_db_partition_stats(nolock)),
 UnAllocatedDataSize=(select SUM((size*8)/1024) from sys.database_files(nolock) where type=0)-(SELECT     (SUM (reserved_page_count)*8)/1024  FROM sys.dm_db_partition_stats(nolock)),
 UnAllocatedDataRate=CONVERT(INT,(
 CONVERT(DECIMAL(10,2),(select SUM((size*8)/1024) from sys.database_files(nolock) where type=0)-(SELECT     (SUM (reserved_page_count)*8)/1024  FROM sys.dm_db_partition_stats(nolock)))/
 CONVERT(DECIMAL(10,2),(select SUM((size*8)/1024) from sys.database_files(nolock) where type=0))
 )*100),
 AllocatedDataRate=100-CONVERT(INT,(
 CONVERT(DECIMAL(10,2),(select SUM((size*8)/1024) from sys.database_files(nolock) where type=0)-(SELECT     (SUM (reserved_page_count)*8)/1024  FROM sys.dm_db_partition_stats(nolock)))/
 CONVERT(DECIMAL(10,2),(select SUM((size*8)/1024) from sys.database_files(nolock) where type=0))
 )*100),
 HasDisableAutoGrowthFile=(SELECT CASE WHEN EXISTS(select * from sys.database_files(nolock) where type=0 AND growth=0) THEN 1 ELSE 0 END),
 DataFileCount=(select count(1) from sys.database_files(nolock) where type=0)'
 
Select * FROM #TempGONDER WHERE DatabaseName not in ('master','model','msdb') 
and (HasDisableAutoGrowthFile=1 OR AllocatedDataRate>@Rparam)

 

Kolon adları ve açıklamalarından bahsetmek gerekirse;

DatabaseName Database in Adı
CurrentDataFileSizes DB File ının şu anki büyüklüğü
CurrentDataSizes DB nin gerçek büyüklüğü
UnAllocatedDataSize Boş alan boyutu
UnAllocatedDataRate Boş olan alanın DB File a oranı
AllocatedDataRate Dolu olu alanın DB File oranı
HasDisableAutoGrowthFile(*) DB nin Auto growth özelliği olmayan file ı var mı?
DataFileCount DB kaç data file dan oluşmakta.

(*) HasDisableAutoGrowthFile kolonundan ekstra olarak bahsetmek istiyorum. Eğer Data file ların auto growth ları boş ise ve file lar dolarsa otomatik olarak büyüyemeyecek ve database kullanılamaz hale gelecektir.

 

Diğer DBA Mail Alert lerle alakalı yazılarıma ulaşmak için aşağıdaki linkleri kullanabilirsiniz.

Database File Statistics

Log Stats

Long Running Queries

Disk Usage Report

 

İyi çalışmalar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


http://www.turgaysahtiyan.com/post/SQL-Server-e28093-SP%28Stored-Procedure%29-lere-Topluca-Yetki-Verme.aspx adresindeki yazımda Stored Procedure lere topluca nasıl yetki verilebileceğini anlatmıştım. Şimdi ise geçenlerde yazdığım bir başka SP den bahsedeğim. Bu SP ile sadece SP lere değil server da ki tüm objelere (table,function,view vs) topluca yetki verebilirsiniz.

SP şu şekilde.

CREATE PROC GivePermissionForAllObjects
	@UserName varchar(max) = '' --Yetki verilmek istenen user ya da role
	,@Databases varchar(Max) = '' --Yetki verilmek istenen DB ya da DB ler, Boş bırakılırsa bütün DB ler
	,@GrantDeny varchar(Max) = 'DENY' --GRANT, DENY, REVOKE
	,@PermissionType varchar(Max) = 'EXECUTE' --Alter, Execute, View Definition vs.
	,@ObjectType varchar(2)='' --SP,View,Function vs.
AS
if (isnull(@ObjectType,'')='') or (isnull(@ObjectType,'')='?') begin
print '
--Object Types
--FN	SQL_SCALAR_FUNCTION
--C 	CHECK_CONSTRAINT
--UQ	UNIQUE_CONSTRAINT
--SQ	SERVICE_QUEUE
--U 	USER_TABLE
--D 	DEFAULT_CONSTRAINT
--PK	PRIMARY_KEY_CONSTRAINT
--V 	VIEW
--S 	SYSTEM_TABLE
--IT	INTERNAL_TABLE
--P 	SQL_STORED_PROCEDURE
--TF	SQL_TABLE_VALUED_FUNCTION
--TR	SQL_TRIGGER
--PC	CLR_STORED_PROCEDURE
--
'
Return
end
SET NOCOUNT ON
--variables
declare @DatabasesTmp varchar(max)=@Databases
declare @DBName sysname,@ProcName sysname,@SchemaName sysname
declare @sql varchar(max)
Declare @Tbl_DB Table(DB varchar(Max))
Create Table ##Prodecures(DBName sysname, ProcName sysname, SchemaName sysname)
--

--Split Databases
if @DatabasesTmp<>'' begin
	while @DatabasesTmp <> '' begin
		if CHARINDEX(',',@DatabasesTmp)>0 begin
			insert @Tbl_DB select SUBSTRING(@DatabasesTmp,1,CHARINDEX(',',@DatabasesTmp)-1)
			set @DatabasesTmp = SUBSTRING(@DatabasesTmp,CHARINDEX(',',@DatabasesTmp)+1,LEN(@DatabasesTmp))
		end else begin
			insert @Tbl_DB select @DatabasesTmp
			set @DatabasesTmp = ''
		end
	end
end else begin
	insert @Tbl_DB select name from sys.databases where database_id>4
end
--

--code
declare CursorX cursor for 
select DB from @Tbl_DB
open Cursorx
fetch from Cursorx into @DBName
while @@FETCH_STATUS=0
begin
	set @sql = 'Use ' +@DBName + '; '+CHAR(10)+'
		insert ##Prodecures
		select '''+@DBName+''',name,SCHEMA_NAME(schema_id) from sys.objects where type='''+@ObjectType+''' '
	exec(@sql)
	fetch next from Cursorx into @DBName	
end
close Cursorx
deallocate Cursorx

declare CursorY cursor for 
select DBName,ProcName,SchemaName from ##Prodecures
open CursorY
fetch from CursorY into @DBName,@ProcName,@SchemaName
while @@FETCH_STATUS=0
begin
	set @sql = 'use ['+ @DBName + ']; ' +
		@GrantDeny+' '+@PermissionType+' ON ['+@SchemaName+'].['+@ProcName+'] TO ['+@UserName+'] AS [dbo]; '
	exec(@sql)
  fetch next from CursorY into @DBName, @ProcName, @SchemaName
end
close CursorY
deallocate CursorY

Drop Table ##Prodecures

SET NOCOUNT OFF
--code

 

Object Type için kullanılabilecek parametreler aşağıdaki gibidir.

FN SQL_SCALAR_FUNCTION
C CHECK_CONSTRAINT
UQ UNIQUE_CONSTRAINT
SQ SERVICE_QUEUE
U USER_TABLE
D DEFAULT_CONSTRAINT
PK PRIMARY_KEY_CONSTRAINT
V VIEW
S SYSTEM_TABLE
IT INTERNAL_TABLE
P SQL_STORED_PROCEDURE
TF SQL_TABLE_VALUED_FUNCTION
TR SQL_TRIGGER
PC CLR_STORED_PROCEDURE

 

Bir kaç örnek vermek gerekirse;

turgay user ına bütün AdventureWorks ve AdventureWorks2008 DB lerinde ki bütün table lar için SELECT yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','AdventureWorks,AdventureWorks2008','GRANT','SELECT','U'

 

turgay user ıne bütün DB lerde ki Stored Procedure ler için EXECUTE yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','','GRANT','EXECUTE','P'

 

turgay user ına bütün DB lerde verilmiş View SELECT hakkını geri almak. (REVOKE)

exec GivePermissionForAllObjects 'turgay','','REVOKE','SELECT','V'

 

turgay user ı bütün DB lerde Sistem Table larını Inherit alsa dahi SELECT çekemesin

exec GivePermissionForAllObjects 'turgay','','DENY','SELECT','S'

 

Bu şekilde istediğiniz DB de ki objelere topluca yetki verebilir yada geri alabilirsiniz.

 

İyi çalışmalar.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Database in zarar görmesi yada bunun gibi database e erişilemeyen durumlarda backup alınmamış işlemlerin kayıpsız olarak geri alınabilmesi için kullanılan back up tipi Tail-Log Backup tır.

Sistem böyle bir durumda normal backup scriptlerinin kullanılmasına izin vermez ve sadece Tail-Log Backup kullanabilirsiniz.

Tail-Log Backup ile Backup Alma


Tail Log backup ın kullanılabilmesi için database Recovery Model in full yada bulk-logged olması gerekmektedir. Ayrıca bu backup işlemi GUI den yapılamaz. Not düşmekte fayda var. Log file ın zarar gördüğü durumlarda Tail-Log backup alınamaz.

Aşağıdaki durumlarda Tail-Log Backup alarak backup lanmamış işlemlerinizi kurtarabilirsiniz.

  • Eğer database online ise restore işlemlerine başlamadan önce WITH NORECOVERY option ı ile tail-log backup alabilirsiniz.
BACKUP LOG database_name TO  WITH NORECOVERY

 

  • Eğer database offline ise ve start edilemiyor ise Tail-Log Backup alabilirsiniz. WITH NORECOVERY kullanımı opsiyoneldir. Eğer database damaged ise WITH CONTINUE_AFTER_ERROR yada WITH NO_TRUNCATE option ı kullanılabilir. WITH NO_TRUNCATE option ı damaged database ler haricinde kullanılmaması önerilir.
BACKUP LOG database_name TO  [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }

 

Şöyle bir senaryo düşünelim.

Live ortamda kullandığımız bir database var ve belirli aralıklarla bu database i backup lıyoruz. Yeni backup zamanı gelmeden disk te bir problem oldu ve mdf file bozuldu. Şu anda Database e erişemiyoruz ve normal backup lama işlemlerini yapamıyoruz. Eldeki backup lardan dönersekte veri kaybımız olacak. Bizim amacımız log dosyası vasıtasıyla veri kayıpsız mdf file a tekrar ulaşmak.

Şimdi bunun örneğini yapalım.

İşlemlere başlamadan önce C:\Backup klasörünü yaratalım. Çünkü backup lar buraya alınacak.

use master
GO

--yeni bir database create ediyoruz.
create database TailSampleDB
GO

--yeni bir table create ediyoruz.
use TailSampleDB
create table TailSampleTbl (a varchar(10))
GO

--Yeni bir satır insert ediyoruz.
--Amacımız her backup tan evvel bir satır ilave etmek.
--Son satır ilavesinden sonra mdf file ı sileceğiz ve 
--Tail-Log Backup ile bu satırı yani datanın son halini kurtarmaya çalışacağız.
insert TailSampleTbl select '1'
GO

--İlk satır ilavesinden sonra full backup alıyoruz.
BACKUP DATABASE [TailSampleDB] TO  DISK = N'C:\backup\full.bak' WITH NOFORMAT, NOINIT,  NAME = N'TailSample-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--İkinci satırı insert ediyoruz.
insert TailSampleTbl select '2'
GO

--İkinci satırdan sonra diff backup alıyoruz.
BACKUP DATABASE [TailSampleDB] TO  DISK = N'C:\backup\diff.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT,  NAME = N'TailSample-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--Üçüncü satırı insert ediyoruz.
insert TailSampleTbl select '3'
GO

--mdf file ı silebilmek için sql server service ini stop ediyoruz.
--daha sonra mdf file ı bulup siliyoruz.
--service i tekrar start ediyoruz.
--şu anda SSMS ten database bakacak olursanız erişilemez olduğunu göreceksiniz.
--Ayrıca SSMS üzerinden backup alınmadığını görebilirsiniz.

--Restore işlemlerine başlamadan önce data kaybı olmaması için Tail-Log Backup alıyoruz.
BACKUP LOG TailSampleDB TO DISK='C:\backup\tail.bak'

--Restore lere başlıyoruz. Önce Full restore.
--Tail log backup a kadar restore işlemleri WITH NORECOVERY option ı ile
RESTORE DATABASE [TailSampleDB] FROM  DISK = N'C:\backup\full.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

--Diff back up restore
RESTORE DATABASE [TailSampleDB] FROM  DISK = N'C:\backup\diff.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

--Tail-Log Backup ı restore ediyoruz.
--Bu restore işlemi son adım olduğu için WITH NORECOVERY kullanıyoruz.
RESTORE LOG [TailSampleDB] FROM  DISK = N'C:\backup\tail.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

--Tabloyu kontrol ediyoruz
use TailSampleDB
select * from TailSampleTbl

--bingoo
--Son satır olmak üzere bütün satırlar kurtarıldı.

 

Umarım faydalı bir yazı olmuştur.

İyi çalışmalar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bu yazımdan önce eğer okumadıysanız SQL Server da Kullanıcı Yaratma ve Yetkilendirme yazımı okumanızı tavsiye ederim.

Bir user sistemde bulunan objeler(SP,Scalar Valued Function vs.) için execute yetkisi vermek için

GRANT EXECUTE TO [sql_login] 
GO


kodunu kullanmanız mümkün. Aynı şekilde bu işlemi Management Studio üzerinden de yapabilmeniz mümkün.

Bunun için Database >> Properties >> Permission ekranını kullanabilirsiniz.

image 

Fakat execute yetkisi verildiği zaman sadece SP lere değil Scalar Valued Function gibi diğer execute komutu kullanılan objelere de yetki vermiş olursunuz.

Peki sadece SP lere yetki vermek için herhangi bir yöntem yok mu?. Hali hazırda management studio nun bunun için sunduğu hazır bir çözüm yok.

Başka bir ekstra yetki vermeden Server side yada Database side da böyle bir yetkiyi topluca vermek mümkün değil. Örneğin “Tüm DB lerde table lara select yapmak istiyorum” tarzında ki bir istek için Database side da datareader yetkisi verilebilir ama SP leri execute etmek gibi bir istekte sadece bu işe yarayan bir özellik yok ne yazık ki. Bunu yapabilmek için ekstradan bir şeyler yapmak gerekmekte.

SP ler için tek tek yetkilendirme yapmak mümkün. Bunun için aşağıdaki gibi bir script i kullanabiliriz. Bu script AdventureWorks DB sinde ki uspGetBillOfMaterials SP si için sql_login user ına VIEW DEFINITION yetkisi vermektedir.

use [AdventureWorks]
GO
GRANT VIEW DEFINITION ON [dbo].[uspGetBillOfMaterials] TO [sql_login]
GO

Verilebilecek yetkiler ve açıklamaları şu şekildedir.

 

Alter SP de değişiklik yapmak için
Control SP yi control etmek için
Execute SP yi çalıştırabilmek için
Take ownership SP nin owner ını değiştirmek için
View Definition SP nin kodlarını görüntüleyebilmek için

 

Verilmiş yetkiyi almak için REVOKE, yetkisizlik tanımlamak için DENY komutunu kullanabilirsiniz.

 

Evet görüldüğü gibi SP lere tek tek yetkilendirme yapabiliyoruz. Ama biz server da ki bütün SP lere topluca yetki vermek istiyoruz.

İşte böyle bir durumda aşağıda yazmış olduğum script i kullanabilirsiniz.


Parametreler

@UserName Yetki verilmek istenen user ya da role
@Databases Yetki verilmek istenen DB ya da DB ler
@GrantDeny GRANT, DENY, REVOKE
@PermissionType Alter, Execute, View Definition vs.


Kullanımı

exec GivePermissionForAllSPs 'Developer_User','AdventureWorks,AdventureWorks2008','GRANT','VIEW DEFINITION'

Code

CREATE PROC GivePermissionForAllSPs
	@UserName varchar(max) = ''
	,@Databases varchar(Max) = ''
	,@GrantDeny varchar(Max) = 'DENY'
	,@PermissionType varchar(Max) = 'EXECUTE'
AS

SET NOCOUNT ON
--variables
declare @DatabasesTmp varchar(max)=@Databases
declare @DBName sysname,@ProcName sysname,@SchemaName sysname
declare @sql varchar(max)
Declare @Tbl_DB Table(DB varchar(Max))
Create Table ##Prodecures(DBName sysname, ProcName sysname, SchemaName sysname)
--

--Split Databases
if @DatabasesTmp<>'' begin
	while @DatabasesTmp <> '' begin
		if CHARINDEX(',',@DatabasesTmp)>0 begin
			insert @Tbl_DB select SUBSTRING(@DatabasesTmp,1,CHARINDEX(',',@DatabasesTmp)-1)
			set @DatabasesTmp = SUBSTRING(@DatabasesTmp,CHARINDEX(',',@DatabasesTmp)+1,LEN(@DatabasesTmp))
		end else begin
			insert @Tbl_DB select @DatabasesTmp
			set @DatabasesTmp = ''
		end
	end
end else begin
	insert @Tbl_DB select name from sys.databases where database_id>4
end
--

--code
declare CursorX cursor for 
select DB from @Tbl_DB
open Cursorx
fetch from Cursorx into @DBName
while @@FETCH_STATUS=0
begin
	set @sql = 'Use ' +@DBName + '; '+CHAR(10)+'
		insert ##Prodecures
		select '''+@DBName+''',name,SCHEMA_NAME(schema_id) from sys.procedures where type=''P'' '
	exec(@sql)
	fetch next from Cursorx into @DBName	
end
close Cursorx
deallocate Cursorx

declare CursorY cursor for 
select DBName,ProcName,SchemaName from ##Prodecures
open CursorY
fetch from CursorY into @DBName,@ProcName,@SchemaName
while @@FETCH_STATUS=0
begin
	set @sql = 'use ['+ @DBName + ']; ' +
		@GrantDeny+' '+@PermissionType+' ON ['+@SchemaName+'].['+@ProcName+'] TO ['+@UserName+'] AS [dbo]; '
	exec(@sql)
  fetch next from CursorY into @DBName, @ProcName, @SchemaName
end
close CursorY
deallocate CursorY

Drop Table ##Prodecures

SET NOCOUNT OFF
--code

 

İyi çalışmalar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server DBA lerin en çok dikkat etmesi gereken konulardan biri DB file ların boyutları ve Auto Growth larıdır. Herhangi bir plansız büyüme esnasında disk dolarsa yada file da boş yer kalmaz ve auto growth atanmamışsa uygulama isteklere cevap veremeyecektir.

Aşağıdaki script i kullanarak DB file ların boyutlarını,ne kadarlık yerin kullanıldığını ve Auto growth set edilip edilmediğini sorgulayabilirsiniz.

select databasename=DB_NAME(dbid), name, fileid, drive=LEFT(filename,1),filename
      ,filegroup=FILEGROUP_NAME(groupid)
      ,'size'=CONVERT(nvarchar(15),CONVERT(bigint,size)*8) + N' KB'
      ,'maxsize'=(case maxsize when -1 then N'Unlimited' 
                        else CONVERT(nvarchar(15),CONVERT(bigint,maxsize)*8) + N' KB'
                        end)
      ,'growth'=(case status & 0x100000 when 0x100000 then
                     CONVERT(nvarchar(15),growth) + N'%'
                     else
                     CONVERT(nvarchar(15),CONVERT(bigint,growth)*8) + N' KB' end)
      ,'usage'=(case status & 0x40 when 0x40 then 'log only' else 'data only' end)      
      from sys.sysaltfiles
order by dbid

 

İyi çalışmalar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Şöyle bir senaryo düşünelim. Bir server da bulunan DB lerin tamamını başka bir server a kopyalamamız gerekiyor. Bunun için en basitinden yapılacak işlem ilk server da ki DB lerin Backup ını alıp diğer server da Restore etmek.

Böyle bir restore işleminde DB leri tek tek restore etmek istemiyoruz. Yazacağımız bir SP ile bu DB lerin tamamının yeni ortama restore edilmesini istiyoruz.

İş arkadaşım Kadir Evciler ile beraber yazdığımız aşağıdaki SP ile bu isteğimizi gerçekleştirebiliriz.

Kullanılan Parametreler

@DUMPName Backup Device ın Name i
@DataFileLocation Yeni server da DB Data File larının bulunacağı klasör
@LogFileLocation Yeni server da DB Log File larının bulunacağı klasör
@RestoreAsSameName 1 ise DB aynı isimle Restore edilir, değil ise DB isminin yanına tarih eklenir.

 

Kullanımı

exec Usp_restore 'MyBackUpDevice','C:\Data','C:\Log',1

 

Script

CREATE  proc [dbo].[Usp_restore]
@DUMPName varchar(30),
@DataFileLocation varchar(max)=null,
@LogFileLocation varchar(max)=null,
@RestoreAsSameName tinyint=1
as
DECLARE @DatabaseName varchar(255),@Position varchar(10),@restoreSQL varchar(max),
@LogicalName varchar(100),@PhysicalName varchar(100),@Type char(1),@CurFileName varchar(100)
CREATE TABLE #TempContent ([BackupName] nvarchar(255),[BackupDescription] nvarchar(255),[BackupType] float,[ExpirationDate] nvarchar(255),[Compressed] float,[Position] float,[DeviceType] float,[UserName] nvarchar(255),[ServerName] nvarchar(255),[DatabaseName] nvarchar(255),[DatabaseVersion] float,[DatabaseCreationDate] nvarchar(255),[BackupSize] float,[FirstLSN] float,[LastLSN] float,[CheckpointLSN] float,[DatabaseBackupLSN] float,[BackupStartDate] nvarchar(255),[BackupFinishDate] nvarchar(255),[SortOrder] float,[CodePage] float,[UnicodeLocaleId] float,[UnicodeComparisonStyle] float,[CompatibilityLevel] float,[SoftwareVendorId] float,[SoftwareVersionMajor] float,[SoftwareVersionMinor] float,[SoftwareVersionBuild] float,[MachineName] nvarchar(255),[Flags] float,[BindingID] nvarchar(255),[RecoveryForkID] nvarchar(255),[Collation] nvarchar(255),[FamilyGUID] nvarchar(255),[HasBulkLoggedData] float,[IsSnapshot] float,[IsReadOnly] float,[IsSingleUser] float,[HasBackupChecksums] float,[IsDamaged] float,[BeginsLogChain] float,
[HasIncompleteMetaData] float,[IsForceOffline] float,[IsCopyOnly] float,[FirstRecoveryForkID] nvarchar(255),[ForkPointLSN] nvarchar(255),[RecoveryModel] nvarchar(255),[DifferentialBaseLSN] nvarchar(255),[DifferentialBaseGUID] nvarchar(255),[BackupTypeDescription] nvarchar(255),[BackupSetGUID] nvarchar(255),[CompressedBackupSize] float);
CREATE TABLE #TempFileList ([LogicalName] nvarchar(255),[PhysicalName] nvarchar(255),[Type] nvarchar(255),[FileGroupName] nvarchar(255),[Size] float,[MaxSize] float,[FileId] float,[CreateLSN] float,[DropLSN] float,[UniqueId] nvarchar(255),[ReadOnlyLSN] float,[ReadWriteLSN] float,[BackupSizeInBytes] float,[SourceBlockSize] float,[FileGroupId] float,[LogGroupGUID] nvarchar(255),[DifferentialBaseLSN] float,[DifferentialBaseGUID] nvarchar(255),[IsReadOnly] float,[IsPresent] float,[TDEThumbprint] nvarchar(255));
insert into #TempContent exec ('restore headeronly from '+@DUMPName)
declare ACur cursor for
select DatabaseName,convert(varchar(10),Position) from #TempContent
open ACur
fetch from ACur into @DatabaseName ,@Position
while @@FETCH_STATUS=0
begin
 
      truncate table #TempFileList
      insert into #TempFileList exec('restore filelistonly from '+@DUMPName+' with file='+@Position)
      set @restoreSQL='RESTORE DATABASE ['+@DatabaseName+CASE WHEN @RestoreAsSameName=1 THEN '' ELSE CONVERT(varchar(8),GETDATE(),112)+REPLACE(CONVERT(varchar(8),GETDATE(),108),':','') END+'] FROM  '+@DUMPName+' WITH REPLACE,  FILE = '+@Position+',  '+CHAR(10)
     
      declare BCur cursor for
      select LogicalName,PhysicalName,Type from #TempFileList
      open BCur
      fetch from BCur into @LogicalName ,@PhysicalName ,@Type
      while @@FETCH_STATUS=0
      begin
            if @Type='D'
            begin
                  select top 1 @CurFileName=Value from dbo.SplitDelimited(@PhysicalName,'\') order by Id desc
                  set @restoreSQL=@restoreSQL+'MOVE N'''+@LogicalName+CASE WHEN @RestoreAsSameName=1 THEN '' ELSE CONVERT(varchar(8),GETDATE(),112)+REPLACE(CONVERT(varchar(8),GETDATE(),108),':','') END+''' TO N'''+@DataFileLocation+'\'+@CurFileName+CASE WHEN @RestoreAsSameName=1 THEN '' ELSE CONVERT(varchar(8),GETDATE(),112)+REPLACE(CONVERT(varchar(8),GETDATE(),108),':','') END+''',  '+CHAR(10)
            end
            else if @Type='L'
            begin
                  select top 1 @CurFileName=Value from dbo.SplitDelimited(@PhysicalName,'\') order by Id desc
                  set @restoreSQL=@restoreSQL+'MOVE N'''+@LogicalName+CASE WHEN @RestoreAsSameName=1 THEN '' ELSE CONVERT(varchar(8),GETDATE(),112)+REPLACE(CONVERT(varchar(8),GETDATE(),108),':','') END+''' TO N'''+@LogFileLocation+'\'+@CurFileName+CASE WHEN @RestoreAsSameName=1 THEN '' ELSE CONVERT(varchar(8),GETDATE(),112)+REPLACE(CONVERT(varchar(8),GETDATE(),108),':','') END+''',  '+CHAR(10)
            end
           
            fetch next from BCur into @LogicalName ,@PhysicalName ,@Type
      end
      set @restoreSQL=@restoreSQL+'STATS = 10'
      close BCur
      deallocate BCur
      exec(@restoreSQL)
      fetch next from ACur into @DatabaseName ,@Position
end
close ACur
deallocate ACur
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server da User Management DBA ler için sık karşılaşılan durumlardan biridir. User ları yetkilerine göre gruplara ayırmak ve yönetmek için oldukça detaylı permission level lar mevcuttur.

Bu yazımda SQL Server da yeni bir user yaratma, bu user a server role ü verme ve daha sonrada database role ü verme işlemlerini anlatıyor olacağım.

Kullanıcı Yaratma (Login Create)

SQL serverda 2 tip login authentication mode vardır. SQL Server Authentication ve Windows Authentication.

Windows Authentication ile olan bir windows user ını SQL Server a yetkilendirebilir ve şifre kontrolünü windows a bırakabilirsiniz. Microsoft önerdiği best practise bağlantı şekli budur.

SQL Server Authentication da ise windows dan bağımsız SQL Server user ları oluşturabilirsiniz. Burada şifre kontrolünü SQL Server yapmaktadır.

Windows Authentication Login Create

Aşağıdaki script “tugi” isimli windows login ini SQL Server login olarak create etmektedir.

CREATE LOGIN [FUNNYCIK\tugi] FROM WINDOWS --User ı windows userlarından al
	WITH DEFAULT_DATABASE=[master] --Default DB olarak master ı set et

SQL Server Authentication Login Create

Aşağıdaki script ile ise “sql_login” adında yeni bir SQL Server user ı create ediyoruz.

CREATE LOGIN [sql_login] 
	WITH PASSWORD=N's', --password bilgisi
	DEFAULT_DATABASE=[master], --default db olarak master kullanılsın
	CHECK_EXPIRATION=OFF, --şifre expire olmasın
	CHECK_POLICY=OFF --şifre için policy kontrolü yapılmasın

Bu create işlemlerini Management Studio üzerinden de yapmak mümkündür. Bunun için Security >> Login kısmında sağ tıklayıp New Login yazısını tıklıyoruz. Açılan pencereden gerekli seçimleri yaparak Windows yada SQL Server user create edebilirsiniz.

image

Bunun haricinde Login değiştirme için ALTER LOGIN, login silme için DROP LOGIN kodları kullanılmaktadır.

Kullanıcıya Server Role ü Ekleme (Add server role - sp_addsrvrolemember)

Kullanıcı default olarak public server role ünde olacak şekilde yaratılır. Kullanıcıya başka server role ler vermek için aşağıdaki script şablonunu kullanabilirsiniz.

EXEC master..sp_addsrvrolemember 
	@loginame = N'sql_login', 
	@rolename = N'sysadmin'

 

rolename in alabileceği değerler ve açıklamaları aşağıdaki gibidir.

bulkadmin Bulk Insert yapabilir.
dbcreator DB create edebilir, alter ile değişiklik yapabilir, drop edebilir yada restore edebilir.
diskadmin Disk File ları yönetebilir.
processadmin Herhangi bir devam eden process i terminate edebilir.
securityadmin login leri ve bunların özelliklerini manage edebilir.
serveradmin Server-side konfigurasyonları değiştirebilir ya da server ı shut down edebilir.
setupadmin Linked server ekleyebilir yada çıkartabilir, bazı sistem SP lerini çalıştırabilir.
sysadmin En yüksek seviyedeki yetkidir. Instance üzerinde ki bütün yetkilere sahiptir.

 

Management Studio üzerinden server-side yetki vermek için create login ekranında ki Server Roles kısmını kullanabilirsiniz.

image

Verilmiş Server Side role ü geri almak için sp_dropsrvrolemember kodu kullanılmaktadır.

Kullanıcıya Database Role ü Verme (Add Database Role - sp_addrolemember)

Kullanıcı ilk create edildiğinde herhangi bir DB ye yetkili değildir. Kullanıyı bir DB ye create etmek için aşağıdaki script i uygulayabilirsiniz.

USE [AdventureWorks]
GO
CREATE USER [sql_login] FOR LOGIN [sql_login]
GO

 

Bu işlem user ı sadece AdventureWorks DB sine create edecektir. Bunun üstüne user için hangi işlemleri yapabileceği hakkında yetki verilmesi gerekmektedir. Aşağıdaki örnek script ile sql_login user ına AdventureWorks DB si için db_owner yetkisi verilmektedir.

USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_owner', N'sql_login'
GO

 

Verilebilecek yetki tipleri ve açıklamaları aşağıdaki gibidir.

db_owner DB üzerinde verilebilecek en yüksek level lı yetkidir. DB üzerinde bütün konfigurasyonları yapabilir ayrıca DB yi drop da edebilir.

db_securityadmin

Yetkileri yönetebilir, role yetkilerinde değişiklik yapabilir.

db_accessadmin

Windows ve SQL Server Login lerinin DB ye erişimlerinde ekleme yada çıkarma yapabilir.

db_backupoperator

DB yi backuplayabilir.

db_ddladmin

DB üzerinde herhangi bir DDL (Data Definition Language) komut çalıştırabilir.

db_datawriter

User tabloları üzerinde delete, insert, update komutlarını çalıştırabilir.

db_datareader

User tabloları üzerinde select komutu çalıştırabilir.

db_denydatawriter

User tablolarında delete, insert, update komutları çalıştırılamaması için kullanılır.
db_denydatareader User tabloları üzerinde select komutu çalıştıralamaması için kullanılır.

 

Management Studio üzerinden DB role ü vermek için create login ekranında ki User Mapping kısmını kullanabilirsiniz.

image

Verilmiş DB role ü geri almak için sp_droprolemember kodu kullanılmaktadır.

 

Server side ve DB side role tanımlamaları bu şekilde. Daha detaylı yetkilendirmeleri daha sonraki yazılarımda işliyor olacağım.

İyi çalışmalar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Merhaba,

Bu yazımda size Management Studio yu daha randımanlı kullanmak ve çok fazla sql server yönetimimiz olduğu durumda bunlara nasıl hızlıca erişeceğimizden bahsedeceğim.

Artık orta ölçekli firmalarda bile birden fazla SQL Server kullanılmakta. Hele birde büyük ölçekli bir firmada SQL server yönetimi ekibinde çalışıyorsanız eminim ki onlarca SQL server la uğraşmak zorunda kalıyorsunuzdur.

Böyle bir durumda sunuculara erişmek için her defasında server connection ı kurmak zaman kaybından başka bir şey değildir.

Registered Servers internet explorer da ki favorite mantığı gibi server ları management studio ya tanımlamaya ve yeri geldiğinde bu server lara hızlıca erişmenize yarar.

Central Management Servers da Registered Servers ile aynı yapıdadır. Dolayısıyla aşağıdaki adımları Central Management Server içinde kurgulayabilirsiniz. Tek farkı Central Management Server da server lara Windows Authentication bağlanılması zorunludur.


Management Studio nun default kurulumunda Registered Servers açık gelmemektedir. Açmak için View >> Registered Servers yazısını tıklayın.

rs1

Gelen ekranı istediğiniz bir yere yerleştirin ve + lara basarak SQL Server Group yazısına kadar inin.

rs2

Yeni bir server ı Registered Servers a eklemek için SQL Server Group yazısının üstündeyken sağ tuşa basın New Server Registration yazısını tıklayın. Bu arada New Server Group linkiyle daha düzenli bir görünüm için yeni gruplar ekleyebilirsiniz.

rs3

Gelen ekran aşağıdaki gibi olacaktır.

image

Burdaki bilgilerden çok aşina olduğunuz için bahsetmiyorum. Asıl göstermek istediğim ikinci ekran olan Connection Properties kısmında.

image

Connect to database kısmından bağlantı sonucu database i direk seçtirtebilirsiniz. Network kısmından Protocol ve Packet Size bilgilerini set edebilirsiniz.

Ve benim asıl bahsetmek istediğim, küçük ama hayat kurtaran:) kısım. Use Custom Color.

Yanlış bir serverda query execute etmek hepimizin başına en az bir kere gelmiştir. Şahsen benim başıma baya çok geldi. Use Custom Color kısmı bize bunu bir nebzede olsa engellemek şansı tanıyor. Burdaki color u set ederek query penceresinin altına istediğiniz bir rengi atayabilirsiniz.

image

Bu şekilde önemli gördüğünüz server lara kırmızı rengi atarsanız belki execute etmeden önce bir şansınız daha doğar. :)

Bu küçük ek bilginin ardından konumuza geri dönelim. Serverların tamamını yukarıda anlattığım şekilde register ediyoruz.

image

Tanımlamalarımız bitti. Bu ekrandan bağlanmak istediğimiz server a çift tıklamamız yeterli. Yada sadece bir query penceresi açmak istiyorsak server a sağ tıklayıp New Query yazısını tıklıyoruz.

Son olarak bu server bilgilerini nasıl import/export ederiz bundan bahsedeceğim. Çünkü makinamıza format attığımızda yada makina değiştirdiğimizde tanımlama için harcadağımız zamanı boşa harcamak istemeyiz.

Bunun için Local Server Groups yazısının üstündeyken sağ tuşa bastığımızda çıkan ekranda ki Import/Export linklerini kullanacağız.

image

Export işlemi ile dışarıya dosya atabilir, import işlemiyle de bu attığımız dosyayı okuyarak serverları register edebiliriz.

İşinize yaraması umuduyla.

 

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan