Set Sql Server Configuration Property

Quick example function on how to set a sql server configuration property.

function Set-SqlConfigValue{
    [cmdletbinding(SupportsShouldProcess)]
    param(
        [string]$ServerName,
        [string]$ConfigName,
        [int]$ConfigValue
    )
    begin{
        Import-Module SqlPS -DisableNameChecking -Verbose:$false
    }
    process{

        Write-Verbose "Connecting to server $ServerName..."
        $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $ServerName

        try{
            $srvConn.connect();
            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

            Write-Verbose "Checking to see if $ConfigName is a valid property of the Sql Server Configuration..."
            $prop = $srv.Configuration | Get-Member -MemberType Property | where{$_.Name -eq $ConfigName}
            if($prop -eq $null){
                throw "Property $ConfigName is not a valid sql server property"
                return;
            }

            Write-Verbose "Altering configuration $ConfigName to $ConfigValue"
            $srv.Configuration.Properties[$ConfigName].ConfigValue = $ConfigValue;
            $srv.Alter();
        }
        catch{
            throw $_ 
        }
        finally{
            $srvConn.Disconnect();
        }

    }
    end{

    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: