SQL-SMO.psm1
|
<# -- Moved to SQL.SMO.dll assembly
Function Make-SqlConnection() { .SYNOPSIS Creates a SQL connection object. .DESCRIPTION Creates a SQL connection object for generating a SQL Management Server Object. .PARAMETER ServerName Mandatory. Specifies the name of the SQL server (preferably the FQDN). .PARAMETER InstanceName Optional. Specifies the InstanceID on the SQL server. Default value is <Blank> (which is synonymous with MSSQLSERVER). .PARAMETER SqlCredential Optional. Specifies the SQL Authentication credentials for the chosen server/instance. When this parameter is not included, the default behavior is to use Integrated Active Directory authentication. .INPUTS <None> This cmdlet does not accept pipeline input. .OUTPUTS System.Data.SqlClient.SqlConnection [CmdletBinding(PositionalBinding = $false)] [OutputType([System.Data.SqlClient.SqlConnection])] param ( [parameter(Mandatory = $true, Position = 0)] [string] $ServerName # Preferably with its FQDN , [parameter(Mandatory = $false, Position = 1)] [AllowEmptyString()] [AllowNull()] [string] $InstanceName = [String]::Empty , [parameter(Mandatory = $false)] [pscredential] $SqlCredential # Only use for SQL Authentication ) if ((![String]::IsNullOrEmpty($InstanceName)) -and ($InstanceName -ne "MSSQLSERVER")) { $srvStr = "$ServerName\$InstanceName"; } else { $srvStr = $ServerName; } $conStr = "Server=$srvStr;"; $sqlCon = New-Object System.Data.SqlClient.SqlConnection; if ($PSBoundParameters["SqlCredential"]) { $user = $SqlCredential.UserName; $pass = $SqlCredential.Password; $pass.MakeReadOnly(); $sqlCred = New-Object System.Data.SqlClient.SqlCredential($user, $pass); $sqlCon.Credential = $sqlCred; } else { $conStr = $conStr + "Integrated Security=true;"; } $sqlCon.ConnectionString = $conStr; # Connection will still be closed. Write-Output $sqlCon -NoEnumerate; } Function New-SMO() { .SYNOPSIS Creates a SQL Management Object .DESCRIPTION This function creates a SQL Management Object which is the backbone of gathering and editing of properties/settings within a SQL instance. .PARAMETER ServerName Mandatory. Specifies the name of the SQL server (preferably the FQDN). .PARAMETER InstanceName Optional. Specifies the InstanceID on the SQL server. Default value is <Blank> (which is synonymous with MSSQLSERVER). .PARAMETER SqlCredential Optional. Specifies the SQL Authentication credentials for the chosen server/instance. When this parameter is not included, the default behavior is to use Integrated Active Directory authentication. .INPUTS <None> This cmdlet does not accept pipeline input. .OUTPUTS Microsoft.SqlServer.Management.Smo.Server .EXAMPLE New-SMO -ServerName "SQL.domain.com" -InstanceName "WSUS" .EXAMPLE New-SMO SQL.domain.com -SqlCredential (Get-Credential sa) .LINK https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/configuring-sql-server-in-smo .LINK https://www.red-gate.com/simple-talk/sql/database-administration/documenting-sql-server-with-powershell/ .LINK https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx .LINK https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx [CmdletBinding()] [OutputType([Microsoft.SqlServer.Management.Smo.Server])] [alias("nsmo")] param ( [parameter(Mandatory = $true, Position = 0)] [string] $ServerName , [parameter(Mandatory = $false, Position = 1)] [AllowEmptyString()] [AllowNull()] [string] $InstanceName = [String]::Empty , [parameter(Mandatory = $false)] [pscredential] $SqlCredential ) $splat = @{ ServerName = $ServerName }; if (![String]::IsNullOrEmpty($InstanceName)) { $splat.Add("InstanceName", $InstanceName); } if ($PSBoundParameters["SqlCredential"]) { $splat.Add("SqlCredential", $SqlCredential); } # Create SQL Connection $sqlConn = Make-SqlConnection @splat -ea Stop; $sqlConn.Open(); try { # Create Server Connection $srvConn = [Microsoft.SqlServer.Management.Common.ServerConnection]::new($sqlConn); } catch { Write-Error -ErrorRecord $_ -ea Stop; } try { # Create SMO $smo = [Microsoft.SqlServer.Management.Smo.Server]::new($srvConn); } catch { Write-Error -ErrorRecord $_ -ea Stop; } Write-Output $smo -NoEnumerate; } #> New-Alias -Name "nsmo" -Value "New-SMO"; New-Alias -Name "setsmo" -Value "Set-SMOContext"; Function IsSMOContextSet() { if ([SQL.SMO.Context]::IsSet -and [SQL.SMO.Context]::IsConnected) { return $true; } else { return $false; } } Function Get-SMOConfiguration() { [CmdletBinding(PositionalBinding=$false)] [OutputType([SQL.SMO.SMOProperty])] [alias("Get-SMOProperty")] param ( [parameter(Mandatory=$false)] [string] $SubProperty ) DynamicParam { if (IsSMOContextSet) { if ($null -eq $global:smoConfigProperties) { [string[]]$global:smoConfigProperties = [SQL.SMO.Context]::Connection.Configuration | ` Get-Member -MemberType Property | ? {$_.Name -ne "Properties" -and $_.Name -ne "Parent"} | Select -ExpandProperty Name } $atts = @{ Mandatory=$false; Position=0 } $rtParam = New-Object Dynamic.DynamicParameter( "Property", $global:smoConfigProperties, $atts, @("prop", "props"), $([string[]]), $true ) } else { $rtParam = New-Object Dynamic.DynamicParameter("Property", $(New-Object System.String 2), @{ Position = 0 }, $null, $([string[]]), $true); } return $rtParam.GenerateLibrary(); } Begin { if (!(IsSMOContextSet)) { Write-Error -ErrorRecord $([SQL.SMO.Error]::Throw( [SQL.SMO.SMOContextNotSetException]::new() )) -ea Stop; } [string[]]$Property = $PSBoundParameters["Property"]; } Process { if (!$PSBoundParameters["SubProperty"]) { if ([String]::IsNullOrEmpty($Property)) { for ($i=0; $i -lt $global:smoConfigProperties.Length; $i++) { $prop = $global:smoConfigProperties[$i]; $cfgProp = [SQL.SMO.Context]::Connection.Configuration.$prop; if ($null -ne $cfgProp) { Write-Output $(New-Object SQL.SMO.SMOProperty($prop, $cfgProp)); } } } else { for ($i=0; $i -lt $Property.Length; $i++) { $p = $Property[$i]; $smop = [SQL.SMO.Context]::Connection.Configuration.$p; Write-Output $(New-Object SQL.SMO.SMOProperty($p, $smop)); } } } } } Function Set-SMOConfiguration() { <# .SYNOPSIS Sets a given property's current value to the specified one. .DESCRIPTION Using a provided SQL Management Object (generated from the "New-SMO" cmdlet), this function can change SQL settings/properties within a SQL server/instance by providing the name of the property (optionally along with a sub-property) and a new value. .PARAMETER SMO Mandatory. Specifies the SQL Management Object needed for changing a SQL server/instance's settings. .PARAMETER Property Mandatory. Specifies the property name of the setting that is to be changed. .PARAMETER SubProperty Optional. Specifies a subproperty name of the given property. e.g. - MaxServerMemory=Property; ConfigValue=SubProperty .PARAMETER NewValue Mandatory. Specifies the new value for the indicated property/subproperty. .INPUTS None or Microsoft.SqlServer.Management.Smo.Server A SQL Management Server Object is received by the SMO parameter. .OUTPUTS System.Management.Automation.PSCustomObject .EXAMPLE Set-SMOProperty -SMO (New-SMO -ServerName SQL.domain.corp) -Property MaxServerMemory -SubProperty ConfigValue -NewValue 12192 .EXAMPLE New-SMO -ServerName SQL.domain.corp | Set-SMOProperty -Property MinServerMemory -SubProperty ConfigValue -NewValue 8192 #> [CmdletBinding(PositionalBinding=$false)] [OutputType([SQL.SMO.PropertyChanged])] param ( [parameter(Mandatory=$true,ValueFromPipeline=$true)] [SQL.SMO.SMOProperty] $SMOProperty , [parameter(Mandatory=$true)] [object] $NewValue ) Begin { if (!(IsSMOContextSet)) { Write-Error -ErrorRecord $([SQL.SMO.Error]::Throw( [SQL.SMO.SMOContextNotSetException]::new() )) -ea Stop; } } Process { $oldValue = $SMOProperty.ConfigValue; [SQL.SMO.Context]::Connection.Configuration.$Property.ConfigValue = $NewValue; [SQL.SMO.Context]::Connection.Configuration.Alter(); Write-Output $(New-Object SQL.SMO.PropertyChanged($SMOProperty, $oldValue, $NewValue)); } } Function Set-SQLMemoryLimit() { <# .SYNOPSIS Changes the Minimum/Maximum memory setting. .DESCRIPTION Using a given SQL Management Object, this function provides an easier, quicker way to adjust the memory limit for the given SQL server/instance. *NOTE* - If neither a MaxMB nor MinMB value are specified, the cmdlet will result in error. .PARAMETER SMO Mandatory. Specifies the SQL Management Object needed for changing a SQL server/instance's settings. .PARAMETER MaxMB Optional. Specifies the maximum amount of RAM that a SQL server instance can use (in Megabytes). .PARAMETER MinMB Optional. Specifies the minimum amount of RAM that a SQL server instance must use (in Megabytes). .INPUTS None or Microsoft.SqlServer.Management.Smo.Server A SQL Management Server Object is received by the SMO parameter. .OUTPUTS System.Management.Automation.PSCustomObject .EXAMPLE Set-SQLMemoryLimit -SMO (New-SMO -ServerName SQL.domain.net) -MaxMB 16384 .EXAMPLE New-SMO SQL.domain.net | Set-SQLMemoryLimit -MaxMB 24576 -MinMB 8192 #> [CmdletBinding(PositionalBinding = $false)] [OutputType([System.Management.Automation.PSCustomObject])] param ( [parameter(Mandatory = $false, Position = 0, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Server] $SMO , [Int64] $MaxMB , [Int64] $MinMB ) if ($null -eq $SMO -and (IsSMOContextSet)) { $SMO = [SQL.SMO.Context]::Connection; } if ((!$PSBoundParameters["MaxMB"]) -and (!$PSBoundParameters["MinMB"])) { throw "You must specify either a Maximum value or a Minimum value!" } else { if ($PSBoundParameters["MaxMB"]) { $retObj = $SMO | Set-SMOConfiguration -Property MaxServerMemory -SubProperty ConfigValue -NewValue $MaxMB } if ($PSBoundParameters["MinMB"]) { $retObj = $SMO | Set-SMOConfiguration -Property MinServerMemory -SubProperty ConfigValue -NewValue $MinMB } return $retObj } } |