Powershell에서 SQL Server Port변경 (via SQLPS)

자동화 하다보면, SQL Server Port변경해줘야하는 경우가있음

Ansible에서 WinShell로 실행하는경우에는 SQLPS로 호출해줘야함

 

 

# set_SQLPort.ps1
# 2020-04-03 (Nexon/GameDB Team)
    Param(
            [string]$v_instance_name 
        ,   [string]$v_instance_port 
    )

    $v_host_name = $env:computername
        
    $v_machine_info = new-object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' $v_host_name
    $v_sql_instance = $v_machine_info.ServerInstances[ $v_instance_name ];
    
    $v_sql_instance.ServerProtocols[ 'Tcp' ].IsEnabled = $true;
    $v_sql_instance.ServerProtocols[ 'Tcp' ].Alter();
    
    $v_sqlCfg_ipAll = $v_sql_instance.ServerProtocols['Tcp'].IPAddresses['IPAll'];
    $v_sqlCfg_ipAll.IPAddressProperties['TcpPort'].Value         = "$v_instance_port";
    $v_sqlCfg_ipAll.IPAddressProperties['TcpDynamicPorts'].Value = ""
    
    $v_sql_instance.ServerProtocols['Tcp'].Alter();