#region SMO-Assemblys laden

# Lädt SMO-Assemblies - Nur notwendig, wenn sqlps nicht geladen wird

# Alte Variante, funktioniert zuverlässig mit der Funktion LoadWithPartialName
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')

# Aber Powershell 2.0 steht das Cmdlet Add-Type zur Verfügung. Leider lädt es nicht immer
# die aktuellste Version, wenn man den friendly-Name angibt, was zu Problemen führen kann
# Eine gute Beschreibung zu dem Thema gibt es hier: 
# http://www.madwithpowershell.com/2013/10/add-type-vs-reflectionassembly-in.html
Add-Type -AssemblyName 'Microsoft.SqlServer.Smo'

# Gibt man die Version der Assemblys an, klappt das Laden, allerdings ist man Versionsabhängig
# und der Code ist nicht selbsterklärend. 
# Hier die Version für SQL-Server 2008 (R2)
add-type -AssemblyName 'Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
 
# und das Laden der SMO-Bibliotheken für SQL Server 2012
add-type -AssemblyName 'Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.SqlEnum, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop

# und mit SQL-Server 2014
add-type -AssemblyName 'Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.SMOExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.SqlEnum, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop
add-type -AssemblyName 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ErrorAction Stop

# Eine weitere Variante, die zudem das Nachinstallieren von SMO überflüssig macht ist, die 
# SMO-Assemblies und das CLR-Assembly einmalig von Microsoft herunter zu laden und dann als 
# Powershell-Modul verfügbar zu machen. Einen ausführlichen Artikel dazu gibt es auf der Netz-Weise
# Homepage unter
# http://www.netz-weise-it.training/weisheiten/tipps/item/323-zugriff-auf-sql-server-per-smo-mit-powershell-ohne-smo-installation.html


# geladene Assemblies kann man sich mit der Methode GetAssemblies() auflisten
[appdomain]::CurrentDomain.GetAssemblies() | Where-Object {$_.FullName -like '*sqlserver*'}

#endregion
 

#region Instanzen auflisten

# Alle verfügbaren Instanzen auflisten mit der Methode EnumAvailableSqlServers
# Der Übergabewert $false gibt an, dass auch Instanzen im Netzwerk gesucht werden sollen.
# Die Suche im Netz geht über den SQL-Browser, der Port UDP1434 muß also verfügbar sein.  
# Achtung, für die Demo wird nur die erste gefundene Instanz ausgegeben und der Variablen 
# $SqlInstanceName zugewiesen!
$SQLInstanceName = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($false) |
    Select-Object -Property name -first 1
$SQLInstanceName

# Eine weitere Variante, Instanzen im Netz auflisten
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

#endregion

#region Serverinformationen

# Den Server referenzieren und in der Variablen $SQLSvr speichern
$SQLSvr = New-Object Microsoft.SqlServer.Management.Smo.Server $SQLInstanceName.Name
$SQLSvr

# Server Versionsinformationen
$SQLSvr.Information.VersionString
$SQLSvr.Information.ProductLevel
$SQLSvr.Information | out-gridview
$SQLSvr.Databases | Out-GridView
$SQLSvr | Get-Member

#endregion

#region Datenbank anlegen
# Eine neue Datenbank PSHDB mit Standard-Werten anlegen
$dbName = 'PSHDB'
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($SQLSvr, $dbName)
# Die Methode Create() wird zum anlegen benötigt!
$db.Create()

#endregion

#region Datenbanken konfigurieren

$dbname = 'Adventureworks2012'
# Datenbank abfragen und Referenz speichern
$db2 = $SQLSvr.Databases[$dbName]
# Datenbankoptionen ändern
$db2.DatabaseOptions.AnsiNullsEnabled = $false
$db2.DatabaseOptions.AnsiPaddingEnabled = $false

# Den aktuellen Bensitzer abfragen
$db2.Owner

# Den Besitzer der Datenbank ändern
# SetOwner braucht 2 Parameter: den Besitzer und die Option "overrideIfAlreadyUser"
$db2.SetOwner('sa',$False)

# Datenbank-Zugriffsmodus ändern
# Die 3 Modi heißten: multiple, restricted, single
$db2.DatabaseOptions.UserAccess = [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::restricted
$db2.Alter()

$db2.DatabaseOptions.UserAccess
$db2.DatabaseOptions.UserAccess = [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::multiple


# Einige Optionen müssen direkt gesetzt werden
$db2.AutoUpdateStatisticsEnabled = $true
# Achtung, ältere Kompatiblitätslevel werden unter Umständen nciht mehr unterstützt und führen zu
# einem "FailedOperationExceptinon"-Fehler!
$db2.CompatibilityLevel = [Microsoft.SqlServer.Management.Smo.CompatibilityLevel]::Version100
$db2.Alter()
# Datenbank auf NurLesend setzen
$db2.DatabaseOptions.ReadOnly = $true
$db2.Alter()

# Größe der Datenbank abfragen - die Rückgabe erfolgt in KB
"{0:#.###} MB" -f ( $db2.DataSpaceUsage / 1KB )

# Datenbankinformationen erneut abfragen
$db2.Refresh()

# Und die Testdatenbank wieder löschen
$db.Drop()

#endregion

#region Server Verwalten

# Laufende Serverprozesse auflisten
$SQLSvr.EnumProcesses()

# Blockierte Prozesse auflisten
$SQLSvr.EnumProcesses() |
    Where-Object -Property BlockingSpid -NE -Value 0 |
    Select-Object -Property Name, Spid, Command, Status, Login, Database, BlockingSpid

# Alle Einträge im Errorlog auslesen, die nach dem 31.05.2015 aufgetreten sind
$date = get-date -Year 2015 -Month 05 -Day 31
$SQLSvr.ReadErrorLog() |
    Where-Object Text -Like '*' |
    Where-Object LogDate -ge $date |
    Out-GridView

#region Index-Defragmentierung per SMO

# zuerst wird eine Tabelle abgerufen
$databasename = 'AdventureWorks2012'
$database = $SQLSvr.Databases[$databasename]
$tableName = 'Person'
$schemaName = 'Person'
$table = $database.Tables |
	Where-Object {( $_. Schema -eq $schemaName ) -and ( $_.Name -eq $tableName )}
# MSDN:
# EnumFragmentation enumerates a list of fragmentation information for the index
# using the default fast fragmentation option.

# Reorganize / Rebuild aller Indizies abhängig von Fragmentierung und Pagesize

# $table.Indexes beinhaltet ein Index-Objekt für jeden Index
$table.Indexes 

$Reorg = @{ UpperBoundary=5; LowerBoundary=0 } 
$pagelimit = 100
$ExcludedIndexType = 'PrimaryXmlIndex','SecondaryXmlIndex'
Foreach ( $Index in ( $table.Indexes | where-object { 
	    $ExcludedIndexType -Notcontains $_.IndexType }))
{
    $IndexFrag = $index.EnumFragmentation()
    If (( $IndexFrag.AverageFragmentation -ge $Reorg.LowerBoundary ) -and 
		    ( $IndexFrag.AverageFragmentation -le $Reorg.UpperBoundary ) -and
		    ( $IndexFrag.pages -ge $pagelimit ))
	    {
		    'Index {0} wird reorganisiert' -f $IndexFrag.Index_Name
		    $index.Reorganize()
	    }
	ElseIf (( $IndexFrag.AverageFragmentation -gt $Reorg.UpperBoundary ) -and
		    ( $IndexFrag.pages -ge $pagelimit ))
	    {
		    'Index {0} wird neu aufgebaut' -f $IndexFrag.Index_Name
		    $index.Rebuild()
	    }
}

#endregion


# Datenbank reparieren
$databasename = 'AdventureWorks2012'
$database = $SQLSvr.Databases[$databasename]
#RepairType Values: AllowDataLost, Fast, None, Rebuild
$database.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None)

#endregion

#region SQL-Server Agent

# Alle Jobs auflisten
$SQLSvr.JobServer.Jobs | Select-Object -Property * 

# Datenbankmail aktivieren
$SQLSvr.Configuration.DatabaseMailEnabled.ConfigValue = 1
$SQLSvr.Configuration.Alter()
$SQLSvr.Refresh()

# Ein DB-Mail Konto anlegen
$accountName = 'DBMail'
$accountDescription = 'Database Mail'
$displayName = 'mail'
$emailAddress = 'sqlagent@bit-weise.de'
$replyToAddress = 'sqlagent@bit-weise.de'
$mailServerAddress = 'smtp.bit-weise.de'
$account = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount -ArgumentList $SQLSvr.Mail, $accountName, $accountDescription, $displayName, $emailAddress
$account.ReplyToAddress = $replyToAddress
# Achtung, der Account wird erst mit der Create()-Methode angelegt!
$account.Create()

# Achten Sie darauf, dass der Agent läuft!!!

# SQL-Server Jobs anlegen
$JobName = 'Recycle Errorlog'
if($SQLSvr.JobServer.Jobs[$jobName])
	{ $SQLSvr.JobServer.Jobs[$jobName].Drop() }
$job = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -ArgumentList $SQLSvr.JobServer, $jobName
# Einen Operator angeben
$operator = $SQLSvr.JobServer.Operators['SQLAdmin']
$job.OperatorToEmail = $operator.Name
# Die Benachrichtiungsoption kann sein: Never, OnSuccess, OnFailure, Always
$job.EmailLevel = [Microsoft.SqlServer.Management.SMO.Agent.CompletionAction]::OnFailure
# Anlegen des Jobs wieder über die Create()-Methode
$job.Create()
# Den Job auf der lokalen Instanz anlegen
$job.ApplyToTargetServer($instanceName)
# Als nächstes muß ein Jobstep hinzugefügt werden. 
$jobStep = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep($job, 'Cycle Error Log')
$jobStep.Subsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::TransactSql
$jobStep.Command = 'Exec sp_cycle_errorlog'
$jobStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithSuccess
$jobStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure
$jobStep.Create()

# Einen Job ausführen
$jobserver = $SQLSvr.JobServer
$jobname ='Recycle ErrorLog'
$job = $jobserver.Jobs[$jobname]
$job.Start()
# Warten, bis der Job beendet wurde und dann den Jobstatus ausgeben
# Wichtig, der Job muß aktualisiert werden, um die aktuellen Daten zu erhalten!
while ( $job.CurrentRunStatus -eq 'Running') {start-sleep -Seconds 1; $job.Refresh()}
$Job.Refresh()
$job.LastRunDate

#endregion


#region Verschlüsselung

# Für die Verschlüsselung ist ein Master-Key notwendig, der einmalig 
# erstellt werden muß
$masterdb = $SQLSvr.Databases['master']
if($masterdb.MasterKey -eq $null)
{
	$masterkey = New-Object Microsoft.SqlServer.Management.Smo.MasterKey -ArgumentList $masterdb
	$masterkey.Create('P@ssword')
	Write-Output "Master Key angelegt : $($masterkey.CreateDate)"
}

# Ein Zertifikat anlegen in der MasterDB anlegen
$certificateName = 'Test Certificate'
$masterdb = $SQLSvr.Databases['master']
if ($masterdb.Certificates[$certificateName])
	{
	 $masterdb.Certificates[$certificateName].Drop()
	}
$certificate = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Certificate -ArgumentList $masterdb,$certificateName
# Eigenschaften für das Zertifikat setze
$certificate.StartDate = 'August 01, 2015'
$certificate.Subject = 'Selbstsigniertes Zertifikat zur Verschlüsselung'
$certificate.ExpirationDate = 'August 01, 2020'
$certificate.Create()
# Das Zertifikat anzeigen
$certificate | Select-Object *

#endregion


#region Backups auflisten
# Von allen Datenbanken das letzte Backupdatum anzeigen
$SQLSvr.Databases |
	Select-Object Name, RecoveryModel, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate 
#endregion