SqlProtocolTcpIp: Auto-detect the TCP/IP address group name by IP address

[claudiospizzi]

Problem description

Currently, it's not possible to detect an TCP/IP address group by using the IP. The TCP/IP address group name is mandatory during compilation time. For SQL Server running multiple instances with multiple per-instance IP's, it's important to define con compile time, which IPx address group belongs to which TCP/IP address.

Verbose logs

Not available, as this is a new feature.

DSC configuration

SqlProtocolTcpIP 'ChangeIP'
{
    InstanceName         = 'MSSQLSERVER'
    IpAddressGroup       = '192.168.1.10'
    Enabled              = $true
    IpAddress            = '192.168.1.10'
    TcpPort              = '1433,1500,1501'
}

Suggested solution

The resource should support entering the IP address in the group name field, so that the group is detected by the IP address.

SQL Server edition and version

SQL Server 2022

SQL Server PowerShell modules

Name      Version    Path
----      -------    ----
SqlServer 22.0.59    C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.0.59\SqlServer.psd1

Operating system

OsName               : Microsoft Windows Server 2022 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 2009
WindowsBuildLabEx    : 20348.1.amd64fre.fe_release.210507-1500
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.20348.1366
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.20348.1366
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version  Path
----         -------  ----
SqlServerDsc 16.3.1   C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.3.1\SqlServerDsc.psd1

[johlju]

I'm not sure I follow what the issue is here. You assign IP address to a IP group 1, 2, 3, etc (for example on per NIC) for each instances. It is probably something I'm not seeing here, so could you explain how the current implementation of the resource does not work in your scenario?

[github-actions]

This issue has been automatically marked as stale because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 40 days. If the issue is labelled with any of the work labels (e.g bug, enhancement, documentation, or tests) then the issue will not auto-close.

[claudiospizzi]

@johlju Sorry for the long delay in my response.

The issue we have is, that in our use case we have multiple instances on a SQL Server installed over time. Every instance will get their own instance IP (with skip as source on the IP). For this we have an SQL Server with 2 instances and 3 IPv4 addresses. e.g.

192.168.1.10 -> Listen on Random Port for SQL INSTANCE 01 and SQL INSTANCE 02
192.168.1.11 -> Listen on 1433 for SQL INSTANCE 01
192.168.1.12 -> Listen on 1433 for SQL INSTANCE 02

So for this, for every installation of SQL Server, I can't tell during DSC compilation, which entry in the SQL Configuration the desired IP has. As we always deploy the IPs prior to the SQL Instance, the IP is always already there in the SQL Configuration, but I don't know on which group, so IP1, IP2, etc.

If I can now specify the group by the existing IP, this would be much easier. We have it already in production in a private branch. Would be cool, if we can give that back to the official module.

Regards,
Claudio

[johlju]

It is possible to set IP1 to the IP you want that instance to listen on and enable IP1, then set all other IP2-IPx to any unique IP (eg, 127.0.0.1, 127.0.0.2, etc.) or just the other IP's you know the node has and disable those. The IP1-IPx does not need to be set i n any particular order.

Or am I missing something here?

[claudiospizzi]

Yes, because we never know how many IPx groups we have on the SQL Server (depending on the number of SQL instances and IPs), I'm not able to define that during compilation time. In addition, trying to set an IP on IP1 which is already set on IP2 will lead to an error. So by identifying the IP by IP group name instead of the IP itself leads to an error in our case.

[johlju]

... trying to set an IP on IP1 which is already set on IP2 will lead to an error.

Yes, all IP's need to be set to an unique address. But since you know the IP to set you probably also know the number of IP's on the node, and can set the other IPs to any other IPv4 or IPv6 address (of a private network for example).

This sounds like this is a problem since one team configures the Windows Server and another team configures an instances at different time on that one server. So the workload of the server is not known during provisioning of the bare metal so the server cannot be provisioned all at once. Is that correct?