BREAKING CHANGE: SqlServerNetwork: Does not enable the IPs for the protocol

[wsmelton]

Details of the scenario you try and problem that is occurring:

The ability to actually enable the IPs are not available, or not being done in the current resource. TCP/IP protocol is enabled on the server, but the actual IP

The DSC configuration that is using the resource (as detailed as possible):

xSqlServerNetwork "SqlNetwork" {
	DependsOn = "[xSqlServerSetup]SqlInstallation"
	InstanceName = $AllNodes.InstanceName
	ProtocolName = 'tcp'
	TCPPort = $AllNodes.SqlPort
	IsEnabled = $true
	RestartService = $true
}

$AllNodesSqlPort is set to '1433'

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
OS: Window Server 2012 R2
SQL Server: SQL Server 2014 w/ SP1 x64
PowerShell:

PS C:\Windows\system32> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      5.0.10586.117
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.10586.117
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Version of the DSC module you're using, or 'dev' if you're using current dev branch:

PS C:\Windows\system32> Get-Module xsqlserver -ListAvailable
 Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Manifest   4.0.0.0    xSQLServer

[johlju]

Done some work in this resource now to get tests done for it so it easier to make changes to the resource. I saw that today the resource only supports the IP address group 'IPAll'.

I think we need to add a new parameter IpAddressGroup (anyone got a better name?) which need to be a Key and would be set to a single value ('IPAll', 'IP1','IP2' etc). depending on which IP address group that should be changed.
I also think we need to change ProtocolName from required to Key to support more protocols in the future.
But since the other protocols does not have groups, and IpAdressGroup must be a key, then maybe IpAdressGroup must have a value 'None' when configuring other protocols.
A worse option would be to change the parameter ProtocolName to be assigned 'IPAll','IP1' instead of 'Tcp'.
Either way, this will be a breaking change.

Description on each property in this article (for when this is being worked on):
https://docs.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-ip-addresses-tab

Dumping some code here to get some test data out to help when this is being worked on later.

$version  = '13'
$applicationDomainObject = [System.AppDomain]::CreateDomain('xSQLServerNetwork')
$applicationDomainObject.Load("Microsoft.SqlServer.Smo, Version=$version.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | Out-Null
$applicationDomainObject.Load("Microsoft.SqlServer.SqlWmiManagement, Version=$version.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | Out-Null

$mangedComputerObject = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
$mangedComputerObject.ServerInstances['SQL2016'].ServerProtocols['Tcp'].IPAddresses | Select name
$mangedComputerObject.ServerInstances['SQL2016'].ServerProtocols['Tcp'].IPAddresses['IP1'].IPAddressProperties | Select name

[System.AppDomain]::Unload($$applicationDomainObject)

[claudiospizzi]

I'm not sure, but I think I've covered this in my PR: #1045

[johlju]

This is related to issue #14, see the #339 (comment) and in particular the mention of changing ProtocolName to a Key.

[wsmelton]

I'm not sure this will work 100%:

which need to be a Key and would be set to a single value ('IPAll', 'IP1','IP2' etc). depending on which IP address group that should be changed.

The reason being that we won't know what IP is assigned to which key. SQL Server will pick up the IPs for all NICs on the server as it seems them...which you don't know what that order is until the installation is completed. It would likely be best to have it be a string value and then try and match the IP itself as that would be known.

[johlju]

That suggestion sounds promising. We don't want to write over any group, or getting duplicate IP addresses in two groups.
That IPAddress string parameter would accepts an IP address. and the string 'IPAll' to be able to configure that group?

[johlju]

@claudiospizzi is right, this is being worked on in PR #1045, and it looks like it is what @wsmelton suggest too?

[johlju]

Issue #1377 and issue #1378 will make this issue obsolete as they will deprecate the the SqlServerNetwork resource.