SQL-SMO.psm1
Function ConvertFrom-SecureToPlain { <# .SYNOPSIS Converts a SecureString into a System.String. .DESCRIPTION Reads a System.Security.SecureString and places the encrypted characters back into a plain-text string. .PARAMETER SecurePassword Mandatory. Specifies the SecureString to convert into plain-text. .INPUTS <None> or System.Security.SecureString .OUTPUTS System.String .EXAMPLE ConvertFrom-SecureToPlain -SecurePassword (Get-Credential DOMAIN\user).Password .EXAMPLE $pass = "hello" | ConvertTo-SecureString -AsPlainText -Force $pass | ConvertFrom-SecureToPlain #> param ( [Parameter(Mandatory=$true, Position=0, ValueFromPipeline=$true)] [System.Security.SecureString] $SecurePassword ) $PasswordPointer = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecurePassword) $private:PlainTextPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto($PasswordPointer) [Runtime.InteropServices.Marshal]::ZeroFreeBSTR($PasswordPointer) $private:PlainTextPassword } Function Make-ConnectionString([string]$ServerString,[string]$CredentialString) { <# .SYNOPSIS Returns a formatted sql connection string. .PARAMETER ServerString Mandatory. Specifies the SQL name (or SQL name\SQL instance). .PARAMETER CredentialString Mandatory. Specifies authentication string portion either using SQL authentication credentials as "User=<user>;Password=<plain-text pass>;" or using Active Directory Integrated authentication as "Integrated Security=true;" .INPUTS <None> This cmdlet does not accept pipeline input. .OUTPUTS System.String .EXAMPLE The specified command: Make-ConnectionString -ServerString SQL.domain.io -CredentialString "Integrated Security=true;" -- would result in the following output string: "Server=SQL.domain.io;Integrated Security=true;" .EXAMPLE The specified command: Make-ConnectionString -ServerString SQL.domain.io -CredentialString "User=sa;Password=SaP@ssw0rd!;Trusted Connection=yes;" -- would result in the following output string: "Server=SQL.domain.io;User=sa;Password=SaP@ssw0rd!;Trusted Connection=yes;" #> return $("Server=$ServerString;$CredentialString") } 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 } if ($PSBoundParameters["SqlCredential"]) { $user = $SqlCredential.UserName $pass = $SqlCredential.Password | ConvertFrom-SecureToPlain $credStr = "User ID=$user;Password=$pass;" } else { $credStr = "Integrated Security=true;" } $conStr = Make-ConnectionString -ServerString $srvStr -CredentialString $credStr return $(([System.Data.SqlClient.SqlConnection]::new($conStr))) } 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 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 } return $smo } Function Set-SMOProperty() { <# .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([System.Management.Automation.PSCustomObject])] param ( [parameter(Mandatory=$true,Position=0, ValueFromPipeline=$true)] [Microsoft.SqlServer.Management.Smo.Server] $SMO , [parameter(Mandatory=$true,Position=1)] [string] $Property , [parameter(Mandatory=$false)] [string] $SubProperty , [parameter(Mandatory=$true,Position=2)] [object] $NewValue ) $ErrorActionPreference="Stop" $retObj = New-Object PSObject -Property @{ Property = $Property } if (!$PSBoundParameters["SubProperty"]) { $old = $SMO.Configuration.$Property $SMO.Configuration.$Property = $NewValue } else { $old = $SMO.Configuration.$Property.$SubProperty $SMO.Configuration.$Property.$SubProperty = $NewValue $retObj | Add-Member -MemberType NoteProperty -Name SubProperty -Value $SubProperty } $SMO.Configuration.Alter() $retObj | Add-Member -MemberType NoteProperty -Name NewValue -Value $NewValue $retObj | Add-Member -MemberType NoteProperty -Name OldValue -Value $old $retObj | Add-Member -MemberType NoteProperty -Name Server -Value $smo.Name return $retObj } 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=$true,Position=0, ValueFromPipeline=$true)] [Microsoft.SqlServer.Management.Smo.Server] $SMO , [Int64] $MaxMB , [Int64] $MinMB ) 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-SMOProperty -Property MaxServerMemory -SubProperty ConfigValue -NewValue $MaxMB } if ($PSBoundParameters["MinMB"]) { $retObj = $SMO | Set-SMOProperty -Property MinServerMemory -SubProperty ConfigValue -NewValue $MinMB } return $retObj } } |