SQL/VegaShellSql.ps1
|
function GetSQLInstanceInfo { <# .SYNOPSIS Cette fonction sert a effectuer une récupération de divers informations sur une instance .DESCRIPTION Server --> nom du serveur disant, si non precisé alors local par defaut Server Port Version InstanceId InstallPath RegRoot .EXAMPLE GetSQLInstanceInfo Server1 #> param ( [ValidateNotNullOrEmpty()] [string]$Server = $env:ComputerName ) begin { try { if ([System.Net.NetworkInformation.Ping]::New().Send($Server)) { Write-Information "Ping réussi vers $Server" } } catch { TryCatchError } } process { $Result = Invoke-Command -ComputerName $Server -Authentication Negotiate -ScriptBlock { [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null $SSMS = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $Server Write-Information "Recuperation et connexions aux instances réussi" $Instances = $SSMS.ServerInstances foreach ($Instance in $Instances) { $Parent = @{Name = "Server"; Expression = { $_.Parent.Name } } $Port = @{Name = "Port"; Expression = { $_.ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value } } $Version = @{Name = "Version"; Expression = { $_.Parent.services["MSSQL`$$($Instance.Name)"].AdvancedProperties['VERSION'].Value } } $InstanceId = @{Name = "InstanceId"; Expression = { $_.Parent.services["MSSQL`$$($Instance.Name)"].AdvancedProperties['INSTANCEID'].Value } } $InstallPath = @{Name = "InstallPath"; Expression = { $_.Parent.services["MSSQL`$$($Instance.Name)"].AdvancedProperties['INSTALLPATH'].Value } } $RegRoot = @{Name = "RegRoot"; Expression = { $_.Parent.services["MSSQL`$$($Instance.Name)"].AdvancedProperties['REGROOT'].Value } } [array]$return += @($Instance | Select-Object $Parent, @{Name = "Instance"; Expression = { $_.Name } }, $Port, $Version, $InstanceId, $InstallPath, $RegRoot) } return $return } } end { return $Result } } function DeleteSqlDatabase { <# .SYNOPSIS Cette fonction sert a effectuer une suppression de database dans SQL .DESCRIPTION Supprime la base si elle a bien été trouvée dans SQL, sinon ne fait rien. Le nom de la database doit etre complet et unique Peut etre executé a distance .EXAMPLE DeleteSqlDatabase -servername "S[...]]SQLI05\DATABASE0000003I" -databaseName "TestPaul" #> param( [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] $serverName, [ValidateNotNullOrEmpty()] [Parameter(Mandatory = $true)] $databaseName ) begin { try { Import-Module SQLSERVER Write-Verbose "Modules Installed and Imported succesfully" $server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName) $db = $server.databases[$databaseName] } catch [System.IO.FileNotFoundException] { Write-Warning "Module SQLSERVER introuvable" break } catch { TryCatchError } } process { try { if ($db) { Write-Information "La base $($db.Name) va etre supprimé sur $serverName" $server.KillAllprocesses($databaseName) $db.Drop() [boolean]$StatusSuppression = $true } else { Write-Information "Aucune base trouvé" [boolean]$StatusSuppression = $false } } catch { [boolean]$StatusSuppression = $false TryCatchError } } end { if ($StatusSuppression -eq $true) { Write-Output "La base a été supprimée" } } } function CheckInstanceAvailability { <# .SYNOPSIS Cette fonction sert retourner les informations d'un availability group .DESCRIPTION lancé en local sur le srv .EXAMPLE #> param ( [ValidateNotNullOrEmpty()] [string]$Server = [scriptblock]::Create($env:COMPUTERNAME) ) begin { try { if ([System.Net.NetworkInformation.Ping]::New().Send($Server)) { Write-Information "Ping réussi vers $Server" } } catch { TryCatchError -LastError $PSItem } } process { try { $Result = Invoke-Command -ComputerName $Server -Authentication Negotiate -ScriptBlock { $ErrorActionPreference = [System.Management.Automation.ActionPreference]::Stop try { Import-Module SQLSERVER set-location "SQLSERVER:\SQL\$($args[0])" $Instances = (Get-ChildItem).InstanceName } catch { TryCatchError -LastError $PSItem } :label1 Foreach ($instance in $instances) { Write-Information "Work in progress on $instance" try { set-location "SQLSERVER:\SQL\$($args[0])\$instance\AvailabilityGroups" $AVLGroups = Get-ChildItem if ($null -eq $AVLGroups) { Write-Information "No AvailabilityGroups on instance" continue label1 } } catch { Write-Information "No AvailabilityGroups on instance" continue label1 } } try { Set-Location .\$($AVLGroups.Name)\AvailabilityReplicas $AVLReplica = Get-ChildItem | select-object Name, Role, ConnectionState, RollupSynchronizationState return $AVLReplica } catch [System.Management.Automation.ItemNotFoundException] { Write-Output "No AvailabilityGroups detected" break } catch { TryCatchError -LastError $PSItem } } -ArgumentList $Server } catch { TryCatchError -LastError $PSItem } } end { if ($Result) { return $Result } } } |