Windows PowerShell ile SQL Server 2012 (3. Bölüm)

    Merhaba arkadaşlar, geçen hafta sizlere Windows PowerShell ile SQL Server 2012'de bazı önemli veri tabanı işlemlerinden bahsetmiştim. Bugün sizlere Windows PowerShell ile ileri düzey (advanced) veri tabanı işlemlerinden bahsetmek istiyorum. Bu işlemlere örnek olarak "stored procedure", "trigger" ve "index"'i verebilirim. Bu işlemler veri tabanı tasarımında ve kullanımında geliştiriciye (developer) ve son kullanıcıya birçok kolaylık sağlamaktadırlar.


    Veri tabanına erişim esnasında veya veri tabanı üzerinde işlemler yaparken, sorguları tek bir satırda yapmamız gerekiyor. "Stored procedure", "function", "trigger" gibi teknikleri kullanarak bu zorluktan kurtulabiliriz. Kodları birer kod blokları içerisine yazmak bize birçok avantaj sağlar. Kod parçacıklarını tekrar tekrar kullanabilmemize ve hızlı bir şekilde sorgu yapmamıza olanak sağlar.

"Stored Procedure" Kullanımı

    İlk olrak "stored procedure"'ın ne olduğundan bahsetmek istiyorum. Veri tabanının "server" tarafında tutulan ve ilk derlemeden sonra bir daha derlemeye ihtiyaç duyulmayan SQL ifadeleridir. "Stored procedure"'ler parametreli ve parametresiz olarak çalışabildiği gibi geriye bir değer de döndürebilirler. [1]

    "Stored procedure"'ın SQL Server'da kullanımı aşağıdaki şekildedir. Aldığı parametre yardımıyla soyadı sizin belirlediğiniz bir değer olan ilk 10 öğrenciyi getirir.

CREATE PROCEDURE [dbo].[SISProc] @LastName [varchar]
(50)
WITH ENCRYPTION
AS
SELECT
TOP 10
StudendID,
LastName
FROM
Student.Student
WHERE
LastName = @LastName

    Bu örneği asıl konumuz olan Windows PowerShell ISE'de nasıl yapabiliriz? Aşağıdaki örneği inceleyebilirsiniz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"
$tableName = "Student"
$db = $server.Databases[$dbName]

$sprocName = "SISProc"
$sproc = $db.StoredProcedures[$sprocName]

#stored procedure varsa sil
if ($sproc){
    $sproc.Drop()
}

$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure -ArgumentList $db, $sprocName
$sproc.TextMode = $false
$sproc.IsEncrypted = $true
$paramtype = [Microsoft.SqlServer.Management.SMO.Datatype]::VarChar(50);
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter -ArgumentList $sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)

$sproc.TextBody = @"
SELECT
TOP 10
StudentID,
LastName
FROM
Studenr.Student
WHERE
LastName = @LastName
"@

$sproc.Create()

"Trigger" Kullanımı

    "Trigger" tetikleyici anlamına gelir ve programlarımızda belirli bir işlem gerçekleştiğinde programımızı tetiklemeye yarar veya tablolar arası veri bütünlüğünü korumak için kullanılırlar. Aslında "trigger" bir "stored procedure"'dır. Fakat kendiliğinden çalışırlar. "Trigger"'larda ya hep ya hiç mantığı vardır. Program zorlukla karşı karşıya kaldığında bütün işlem geri döner. "Trigger" tanımlarken "transaction" bloğu olması gerekir. [2]

    SQL Server'da "trigger" kullanımı aşağıdaki şekildedir. "Student" tablosuna eklenen ve tablodan silinen değerleri gösteren bir "trigger" örneğidir.

CREATE TRIGGER [Student].[triggerStudent]
ON [Student].[Student]
AFTER UPDATE
AS
SELECT
GETDATE() AS UpdatedOn,
SYSTEM_USER AS UpdatedBy,
i.LastName AS NewLastName,
i.FirstName AS NewFirstName,
d.LastName AS OldLastName,
d.FirstName AS OldFirstName
FROM
inserted i
INNER JOIN deleted d
ON i.StudentID = d.StudentID

    Bu işlem Windows PowerShell ISE'de nasıl yapılır onu göstermek istiyorum. Aşağıdaki örneği inceleyebilirsiniz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"
$tableName = "Student"
$db = $server.Databases[$dbName]

$tableName = "Student"
$schemaName = "dbo"

$table = $db.Tables | Where Schema -Like "$schemaName" | Where Name -Like "$tableName"
$triggerName = "tr_Student"
$trigger = $table.Triggers[$triggerName]

if ($trigger){
    $trigger.Drop()
}
$trigger = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger -ArgumentList $table, $triggerName
$trigger.TextMode = $false

$trigger.Insert = $false
$trigger.Update = $true
$trigger.Delete = $false

$trigger.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::None
$trigger.ImplementationType = [Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql

$trigger.TextBody = @"
SELECT
GETDATE() AS UpdatedOn,
SYSTEM_USER AS UpdatedBy,
i.LName AS NewLastName,
i.FName AS NewFirstName,
d.LName AS OldLastName,
d.FiName AS OldFirstName
FROM
inserted i
INNER JOIN deleted d
ON i.StudentID = d.StudentID
"@

$trigger.Create()

    Resimde gördüğünüz gibi Windows PowerShell'de oluşturduğumuz "trigger"'ın içerisindeki kodları SQL Server ekranında görebiliyoruz. Hatta bunun üzerinde güncellemeler yapabiliriz.

"Index" Kullanımı

    Veri tabanı üzerinde kayıt sayısı arttıkça çekilen sorguların döndüreceği değerlerin getirilmesi de o kadar zaman alacaktır. Verilere hızlı erişim için kullanılması gereken yöntem "index"'leme yöntemidir. SQL Server sorgusu çalıştırıldığında öncelikle "index"'leme var mı diye kontrol eder. Sorguyu bu duruma göre çalıştırır. Özellikle büyük veri tabanlarında kullanılması son kullanıcıya çok kolaylık sağlar.

    İki çeşit "index" vardır. Bunlar "Clustrered Index" ve "Non-Clustered Index"'dir. "Clustered Index", verilerin "index key" değerine göre fiziksel olarak alfabetik sıralandığı "index" türüdür. Bir tabloda en fazla bir tane bulunur. "Non-Clustered Index" mimarisinde fiziksel bir sıralama söz konusu değildir. "Index" değeri verinin kendisi değil "pointer" değeridir. Bu "pointer" değeri verinin nerede olduğunu gösterir. [3]

    SQL Server'da "index" kullanımı aşağıdaki şekildedir.

CREATE NONCLUSTERED INDEX [idxNLameFName]
ON [Person].[Person]
(
[LName] ASC,
[FName] ASC
)
INCLUDE ( [MiddleName])
GO

    Aynı örneğin Windows PowerShell'de kullanımı aşağıdaki şekildedir.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"
$tableName = "Student"
$db = $server.Databases[$dbName]

$tableName = "Student"
$schemaName = "dbo"

$table = $db.Tables | Where Schema -Like "$schemaName" | Where Name -Like "$tableName"

$indexName = "idxLNameFName"
$index = $table.Indexes[$indexName]

if ($index){
$index.Drop()
}
$index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -ArgumentList $table, $indexName

$idxCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "LName", $false
$index.IndexedColumns.Add($idxCol1)

$idxCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "FName", $false
$index.IndexedColumns.Add($idxCol2)

$inclCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "MiddleName"
$inclCol1.IsIncluded = $true
$index.IndexedColumns.Add($inclCol1)
$index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None
$index.IsClustered = $false
$index.FillFactor = 70

$index.Create()

    Windows PowerShell hakkında anlatacaklarım bu kadar. İlerleyen zamanlar farklı bir konuyla sizlerle yeniden buluşmak dileğiyle, hoşçakalın...


Referanslar:

Kitap: "SQL Server 2012 with PowerShell V3 Cookbook"

[1]http://www.datawarehouse.gen.tr/Makale.aspx?ID=528
[2]http://www.serdardemir.net/sql-trigger-nedir-resimli-anlatim.html
[3]http://www.yazilimciblog.com/sql-index-yapilari/

Windows PowerShell ile SQL Server 2012 (2. Bölüm)

    Merhaba arkadaşlar, geçen hafta Windows PowerShell ile SQL Server 2012 konusuna giriş yapmıştım. Bugün yine aynı konudan devam edeceğim. Bazı önemli veri tabanı işlemlerinden ve bu işlemlerin Windows PowerShell ile nasıl kontrol edildiğinden bahsedeceğim.


    İlk olarak temel veri tabanı işlemlerini Windows PowerShell'den nasıl yapacağımızı anlatmak istiyorum. Bu işlemler veri tabanı oluşturma, tablo ekleme, tablo silme ve veri tabanını silme gibi temel işlemlerdir.

Veri Tabanı Oluşturma

    Öncelikle SQLPS modülünü yüklüyoruz (import). Daha sonra veri tabanı ile bağlantıyı sağlamak için SQL Server SMO nesnesi yaratıyoruz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
 -ArgumentList $instanceName

    Çıktısı aşağıdaki gibi olursa sorun yok demektir.

PS SQLSERVER:\> Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
VERBOSE: Importing cmdlet 'Add-SqlAvailabilityDatabase'.
VERBOSE: Importing cmdlet 'Add-SqlAvailabilityGroupListenerStaticIp'.
VERBOSE: Importing cmdlet 'Backup-SqlDatabase'.
VERBOSE: Importing cmdlet 'Convert-UrnToPath'.
VERBOSE: The 'Decode-SqlName' command in the SQLPS' module was imported, but because its name does not include
 an approved verb, it might be difficult to find. For a list of approved verbs, type Get-Verb.
VERBOSE: Importing cmdlet 'Decode-SqlName'.
VERBOSE: Importing cmdlet 'Disable-SqlAlwaysOn'.
VERBOSE: Importing cmdlet 'Enable-SqlAlwaysOn'.
VERBOSE: The 'Encode-SqlName' command in the SQLPS' module was imported, but because its name does not include
 an approved verb, it might be difficult to find. For a list of approved verbs, type Get-Verb.
VERBOSE: Importing cmdlet 'Encode-SqlName'.
VERBOSE: Importing cmdlet 'Invoke-PolicyEvaluation'.
..............................

VERBOSE: Importing cmdlet 'Switch-SqlAvailabilityGroup'.
VERBOSE: Importing cmdlet 'Test-SqlAvailabilityGroup'.
VERBOSE: Importing cmdlet 'Test-SqlAvailabilityReplica'.
VERBOSE: Importing cmdlet 'Test-SqlDatabaseReplicaState'.
VERBOSE: Importing function 'SQLSERVER:'.
VERBOSE: Importing variable 'm'.

    Daha sonra aşağıdaki kodla veri tabanını oluşturuyoruz.

$dbName = "TestDB"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server,$dbName)
$db.Create()

$server.Databases | Select Name, Status, Owner, CreateDate

Oluşturulan veri tabanı görüntüsü (PowerShell)

Oluşturulan veri tabanı görüntüsü (SQL Server)

Veri Tabanını Silme

    Veri tabanı oluşturmada yaptığımız gibi SQLPS modülünü yüklüyoruz. Ardından yeniden bağlantı sağlamak için SMO nesnesini yaratıyoruz ve veri tabanını silmek için gerekli kodları yazıp çalıştırıyoruz. Hata ile karşılaşmamak için sileceğimiz veri tabanının var olup olmadığını kontrol etmemiz gerekiyor. Aşağıdaki örneği inceleyebilirsiniz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server 
-ArgumentList $instanceName

$dbName = "TestDB"

#veri tabanı var ise sil, yok ise hiçbişey yapma
$db = $server.Databases[$dbName]
if ($db)
{
    $server.KillDatabase($dbName)
}

Tablo Oluşturma

    Veri tabanı oluşturma ve silme işlemlerini göstermek için oluşturduğum "TestDB" veri tabanını sildiğim için tekrardan "SIS" adında bir veri tabanı oluşturdum. Tablo oluşturma ve diğer veri tabanı işlemleri için bu veri tabanını kullanacağım. Windows PowerShell'de yapacağımız her veri tabanı işlemlerinde SQLPS modülünü yüklememiz ve yeni bir SMO nesnesi yaratmamız gerekiyor.

    "Student" isminde bir tablo oluşturuyorum. Hata ile karşılaşmamak için tablonun var olup olmadığını kontrol etmemiz gerekiyor. Ardından oluşturacağımız tablonun her bir kolonunun özelliklerini yazıyoruz ve kod parçacığını çalıştırarak tabloyu oluşturuyoruz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"

$tableName = "Student"
$db = $server.Databases[$dbName]
$table = $db.Tables[$tableName]

#tablo var ise sil
if($table)
{
$table.Drop()
}

#istenilen tabloyu oluştur
$table = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table -ArgumentList $db, $tableName

#column 1
$col1Name = "StudentID"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int;
$col1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -ArgumentList $table, $col1Name, $type
$col1.Nullable = $false
$col1.Identity = $true
$col1.IdentitySeed = 1
$col1.IdentityIncrement = 1
$table.Columns.Add($col1)

#column 2 - İsim
$col2Name = "FName"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarChar(50)
$col2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -ArgumentList $table, $col2Name, $type
$col2.Nullable = $true
$table.Columns.Add($col2)

#column 3 - Soyisim (default değeri var)
$col3Name = "LName"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarChar(50)
$col3 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -ArgumentList $table, $col3Name, $type
$col3.Nullable = $false
$col3.AddDefaultConstraint("DF_Student_LName").Text = "'Doe'"
$table.Columns.Add($col3)

#column 4 Yaş
$col4Name = "Age"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int;
$col4 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -ArgumentList $table, $col4Name, $type
$col4.Nullable = $true
$table.Columns.Add($col4)
$table.Create()

    Oluşturduğumuz bu tablonun "primary key"'i yoktur. "StudentId" kolonunu "primary key" yapmak için aşağıdaki kodu kullanabilirsiniz.

$PK=New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -ArgumentList $table, "PK_Student_StudentID"
$PK.IsClustered = $true
$PK.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey

#hangi kolonu primary key yapacağımızı belirtiyoruz
$PKcol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $PK, $col1Name
$PK.IndexedColumns.Add($PKcol)
$PK.Create()

Veri tabanı ve tabloların görüntüsü

"View" Oluşturma

    Soyadlarına göre sıralanmış ilk 10 öğrencinin listesini getiren bir "view" oluşturdum. Aşağıdaki örneği inceleyebilirsiniz.

Import-Module SQLPS -DisableNameChecking

$instanceName = "KAZUYIX\SQLSERVER2012"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$dbName = "SIS"
$db = $server.Databases[$dbName]
$viewName = "viewStudent"
$view = $db.Views[$viewName]

#varsa sil
if ($view)
{
    $view.Drop()
}
$view = New-Object -TypeName Microsoft.SqlServer.Management.SMO.View 
-ArgumentList $db, $viewName, "dbo"
$view.TextMode = $false
$view.TextBody = "SELECT TOP 10 StudentId, LName, FName 
FROM SIS.Student WHERE LName = 'IN' ORDER BY LName"
$view.Create()

Bu hafta anlatacaklarım bu kadar. Haftaya görüşürüz.


Referanslar:

Kitap: "SQL Server 2012 with PowerShell V3 Cookbook"