DSCResources/MSFT_xSQLServerNetwork/MSFT_xSQLServerNetwork.psm1
|
Function Get-TargetResource { [CmdletBinding()] [OutputType([System.Collections.Hashtable])] param( [parameter(Mandatory = $true)] [System.String] $InstanceName, # for now support is just for tcp protocol # possible future feature to support additional protocols [parameter(Mandatory = $true)] [ValidateSet("tcp")] [System.String] $ProtocolName ) Write-Verbose "xSQLServerNetwork.Get-TargetResourece ..." Write-Verbose "Parameters: InstanceName = $InstanceName; ProtocolName = $ProtocolName" # create isolated appdomain to load version specific libs, this needed if you have multiple versions of SQL server in the same configuration $dom_get = [System.AppDomain]::CreateDomain("xSQLServerNetwork_Get_$InstanceName") Try { $version = GetVersion -InstanceName $InstanceName if([string]::IsNullOrEmpty($version)) { throw "Unable to resolve SQL version for instance" } $smo = $dom_get.Load("Microsoft.SqlServer.Smo, Version=$version.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") $sqlWmiManagement = $dom_get.Load("Microsoft.SqlServer.SqlWmiManagement, Version=$version.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") Write-Verbose "Creating [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer] object" $wmi = new-object $sqlWmiManagement.GetType("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") Write-Verbose "Getting [$ProtocolName] network protocol for [$InstanceName] SQL instance" $tcp = $wmi.ServerInstances[$InstanceName].ServerProtocols[$ProtocolName] Write-Verbose "Reading state values:" $returnValue = @{ InstanceName = $InstanceName ProtocolName = $ProtocolName IsEnabled = $tcp.IsEnabled TCPDynamicPorts = $tcp.IPAddresses["IPAll"].IPAddressProperties["TcpDynamicPorts"].Value TCPPort = $tcp.IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value } $returnValue.Keys | % { Write-Verbose "$_ = $($returnValue[$_])" } } Finally { [System.AppDomain]::Unload($dom_get) } return $returnValue } Function Set-TargetResource { [CmdletBinding()] param( [parameter(Mandatory = $true)] [System.String] $InstanceName, [parameter(Mandatory = $true)] [ValidateSet("tcp")] [System.String] $ProtocolName, [System.Boolean] $IsEnabled, [ValidateSet("0")] [System.String] $TCPDynamicPorts, [System.String] $TCPPort, [System.Boolean] $RestartService = $false ) Write-Verbose "xSQLServerNetwork.Set-TargetResource ..." Write-Verbose "Parameters: InstanceName = $InstanceName; ProtocolName = $ProtocolName; IsEnabled=$IsEnabled; TCPDynamicPorts = $TCPDynamicPorts; TCPPort = $TCPPort; RestartService=$RestartService;" Write-Verbose "Calling xSQLServerNetwork.Get-TargetResource ..." $currentState = Get-TargetResource -InstanceName $InstanceName -ProtocolName $ProtocolName # create isolated appdomain to load version specific libs, this needed if you have multiple versions of SQL server in the same configuration $dom_set = [System.AppDomain]::CreateDomain("xSQLServerNetwork_Set_$InstanceName") Try { $version = GetVersion -InstanceName $InstanceName if([string]::IsNullOrEmpty($version)) { throw "Unable to resolve SQL version for instance" } $smo = $dom_set.Load("Microsoft.SqlServer.Smo, Version=$version.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") $sqlWmiManagement = $dom_set.Load("Microsoft.SqlServer.SqlWmiManagement, Version=$version.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") $desiredState = @{ InstanceName = $InstanceName ProtocolName = $ProtocolName IsEnabled = $IsEnabled TCPDynamicPorts = $TCPDynamicPorts TCPPort = $TCPPort } Write-Verbose "Creating [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer] object" $wmi = new-object $sqlWmiManagement.GetType("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") Write-Verbose "Getting [$ProtocolName] network protocol for [$InstanceName] SQL instance" $tcp = $wmi.ServerInstances[$InstanceName].ServerProtocols[$ProtocolName] Write-Verbose "Checking [IsEnabled] property ..." if($desiredState["IsEnabled"] -ine $currentState["IsEnabled"]) { Write-Verbose "Updating [IsEnabled] from $($currentState["IsEnabled"]) to $($desiredState["IsEnabled"])" $tcp.IsEnabled = $desiredState["IsEnabled"] } Write-Verbose "Checking [TCPDynamicPorts] property ..." if($desiredState["TCPDynamicPorts"] -ine $currentState["TCPDynamicPorts"]) { Write-Verbose "Updating [TCPDynamicPorts] from $($currentState["TCPDynamicPorts"]) to $($desiredState["TCPDynamicPorts"])" $tcp.IPAddresses["IPAll"].IPAddressProperties["TcpDynamicPorts"].Value = $desiredState["TCPDynamicPorts"] } Write-Verbose "Checking [TCPPort property] ..." if($desiredState["TCPPort"] -ine $currentState["TCPPort"]) { Write-Verbose "Updating [TCPPort] from $($currentState["TCPPort"]) to $($desiredState["TCPPort"])" $tcp.IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value = $desiredState["TCPPort"] } Write-Verbose "Saving changes ..." $tcp.Alter() if($RestartService) { Write-Verbose "SQL Service will be restarted ..." if($InstanceName -eq "MSSQLSERVER") { $dbServiceName = "MSSQLSERVER" $agtServiceName = "SQLSERVERAGENT" } else { $dbServiceName = "MSSQL`$$InstanceName" $agtServiceName = "SQLAgent`$$InstanceName" } $sqlService = $wmi.Services[$dbServiceName] $agentService = $wmi.Services[$agtServiceName] $startAgent = ($agentService.ServiceState -eq "Running") if ($sqlService -eq $null) { throw "$dbServiceName service was not found, restart service failed" } Write-Verbose "Stopping [$dbServiceName] service ..." $sqlService.Stop() while($sqlService.ServiceState -ne "Stopped") { Start-Sleep -Milliseconds 500 $sqlService.Refresh() } Write-Verbose "[$dbServiceName] service stopped" Write-Verbose "Starting [$dbServiceName] service ..." $sqlService.Start() while($sqlService.ServiceState -ne "Running") { Start-Sleep -Milliseconds 500 $sqlService.Refresh() } Write-Verbose "[$dbServiceName] service started" if ($startAgent) { Write-Verbose "Staring [$agtServiceName] service ..." $agentService.Start() while($agentService.ServiceState -ne "Running") { Start-Sleep -Milliseconds 500 $agentService.Refresh() } Write-Verbose "[$agtServiceName] service started" } } } Finally { [System.AppDomain]::Unload($dom_set) } } Function Test-TargetResource { [CmdletBinding()] [OutputType([System.Boolean])] param( [parameter(Mandatory = $true)] [System.String] $InstanceName, [parameter(Mandatory = $true)] [ValidateSet("tcp")] [System.String] $ProtocolName, [System.Boolean] $IsEnabled, [ValidateSet("0")] [System.String] $TCPDynamicPorts, [System.String] $TCPPort, [System.Boolean] $RestartService = $false ) Write-Verbose "xSQLServerNetwork.Test-TargetResource ..." Write-Verbose "Parameters: InstanceName = $InstanceName; ProtocolName = $ProtocolName; IsEnabled=$IsEnabled; TCPDynamicPorts = $TCPDynamicPorts; TCPPort = $TCPPort; RestartService=$RestartService;" $desiredState = @{ InstanceName = $InstanceName ProtocolName = $ProtocolName IsEnabled = $IsEnabled TCPDynamicPorts = $TCPDynamicPorts TCPPort = $TCPPort } Write-Verbose "Calling xSQLServerNetwork.Get-TargetResource ..." $currentState = Get-TargetResource -InstanceName $InstanceName -ProtocolName $ProtocolName Write-Verbose "Comparing desiredState with currentSate ..." foreach($key in $desiredState.Keys) { if($currentState.Keys -eq $key) { if($desiredState[$key] -ine $currentState[$key] ) { Write-Verbose "$key is different: desired = $($desiredState[$key]); current = $($currentState[$key])" return $false } } else { Write-Verbose "$key is missing" return $false } } Write-Verbose "States match" return $true } Function GetVersion { param( [parameter(Mandatory = $true)] [System.String] $InstanceName ) $instanceId = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$InstanceName $sqlVersion = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceId\Setup").Version $sqlVersion.Split(".")[0] } Export-ModuleMember -Function *-TargetResource |