SQL-SMO.psm1
|
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. return $sqlCon } 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 } 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 } } |