Set SQL Server instance port programmatically

code snippet on how to set SQL instance port from powershell script rather then SQL Server Configuration manager


function SetPort($instance, $port)

{

$filterString="InstanceName='$instance' AND IpAddressName='IPAll' AND ProtocolName='Tcp' AND PropertyName='TcpPort'"

$InstanceList=get-wmiobject ServerNetworkProtocolProperty -namespace "root\Microsoft\SqlServer\ComputerManagement10"-filter $filterString

foreach ($sqlInstancein$InstanceList)

{

if ($sqlInstance-ne$null)

{

$sqlInstance.SetStringValue($port)

}

}

}

functionSetArgumentVariables ($argList)

{

foreach ($argin$argList)

{

$argPair=$arg.Split("=")

if ($argPair.Count-eq2)

{

$key=$argPair[0]

$value=$argPair[1]

switch ($key)

{

"port" {

$result= [System.Int32]::TryParse($value, [ref] $global:Port);

if (!$result)

{

$global:Port=""

}

}

"instance" {$global:Instance=$value}

default {}

}

}

}

}

#SetArgumentVariables $args

SetPort "MyInstance""14330"

Execute TSQL from Powershell

I find this function useful when I need to execute simple query from Powershell script. Since I rarely remember exact syntax I am going to leave here small snippet of code for future reference.

function f_ExecuteSQLQuery ([string]$ServerInstance, [string] $dbName, [string] $sqlQuery)

{

 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString="Server=$ServerInstance;Database=$dbName;Integrated Security=True"

$SqlCmd=New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText=$sqlQuery

$SqlCmd.Connection=$SqlConnection

$SqlCmd.CommandType= [System.Data.CommandType]'Text';

$SqlConnection.Open();

 

$results=$SqlCmd.ExecuteReader();;

 

return$results

$SqlConnection.Close();

}

 

and similar for Stored Procedure call:

 

 function f_ExecuteSQLSP ([string]$ServerInstance, [string] $dbName, [string] $sqlQuery){
    

        
            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
            $SqlConnection.ConnectionString = "Server=$ServerInstance;Database=$dbName;Integrated Security=True"
                
            
            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
            $SqlCmd.CommandText = "exec dbcc_opentran"
            $SqlCmd.Connection = $SqlConnection
            $SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure';
            
            $ip = new-object System.Data.SqlClient.SqlParameter;
            $ip.ParameterName = "@trnpath";
            $ip.Direction = [System.Data.ParameterDirection]'Input';
            $ip.DbType = [System.Data.DbType]'String';
            $ip.Value = "$rf";
            $ip.Size = 2500
            
            $SqlCmd.Parameters.Add($ip)>> $null;
            
            $iip = new-object System.Data.SqlClient.SqlParameter;
            $iip.ParameterName = "@dbanme";
            $iip.Direction = [System.Data.ParameterDirection]'Input';
            $iip.DbType = [System.Data.DbType]'String';
            $iip.Value = "$dbnameparam";
            $iip.Size = 2500
            
            $SqlCmd.Parameters.Add($iip)>> $null;
            
            $SqlConnection.Open();
            
            $results = $SqlCmd.ExecuteNonQuery();;
            
            
            }

            $SqlConnection.Close();
            }

 

 

 

 

Simple powershell logging

Small snippet to use for logging events to the file in a powershell script

#Prepare Logging

[string]$LogFolder = "C:\MyLogFolder\"

[string]$logfilename="$((get-date).tostring("yyyyMMddHHmmss"))_verbose.log"

[string]$InstallationLogFile = "$LogFolder$logfilename"
function f_Logging([string]$Message){

If((Test-Path"$LogFolder") -eq0){

New-Item"$LogFolder"-Type "Directory"-Force

}

Write-output" $((get-date).tostring("MM.dd.yyyy HH:mm:ss")) $Message "|Out-File-FilePath $InstallationLogFile-Append

}


and call a function


f_Logging "My message"