search

dsccommunity / SqlServerDsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.
MIT License
359 stars 224 forks source link

SqlSetup: AddNode is not working (missing 4 parameters in commandline) #1171

Open Francois-Rousseau opened 6 years ago

Francois-Rousseau commented 6 years ago

Details of the scenario you tried and the problem that is occurring

As discribed in the SqlSetup documentation, the AddNode is not currently working. I found that they are 4 parameters missing from the command line that launch the setup.exe from SQL.

So I used the resource as discribed in the documentation, had SQL cluster first node installed with no problem, and, as said, the AddNode feature for the second node is not working.

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

Here is some part of the configuration that I used for that :

            SQLSetup 'InstallSQLClusterFirstNode' 
                {
                    Action                = "InstallFailoverCluster"
                    InstanceName          = $Node.InstanceName
                    Features              = $Node.Features
                    SQLCollation          = $Node.SQLCollation    
                    SQLSvcAccount         = $Node.SqlServiceCredential
                    AgtSvcAccount         = $Node.SqlAgentServiceCredential
                    FTSvcAccount          = $Node.SqlFullTextCredential
                   # RSSvcAccount          = $Node.SqlReplicationCredential
                   # ISSvcAccount          = $Node.SqlIntegrationCredential
                    SQLSysAdminAccounts   = $Node.SQLSysAdminAccounts

                    InstallSharedDir      = $Node.InstallSharedDir
                    InstallSharedWOWDir   = $Node.InstallSharedWOWDir
                    InstanceDir           = $Node.InstanceDir
                    InstallSQLDataDir     = $Node.InstallSQLDataDir
                    SQLUserDBDir          = $Node.SQLUserDBDir
                    SQLUserDBLogDir       = $Node.SQLUserDBLogDir
                    SQLTempDBDir          = $Node.SQLTempDBDir
                    SQLTempDBLogDir       = $Node.SQLTempDBLogDir
                    SQLBackupDir          = $Node.SQLBackupDir
                    SourcePath            = $ConfigurationData.NonNodeData.SQLsources.SourcePath
                    UpdateEnabled         = $Node.UpdateEnabled
                    ForceReboot           = $Node.ForceReboot
                    SuppressReboot        = $Node.SuppressReboot
                    SecurityMode          = $Node.SecurityMode
                    SAPwd                 = $Node.SAPWD
                    SQMReporting          = $Node.SQMReporting
                    ErrorReporting        = $Node.ErrorReporting

                    FailoverClusterGroupName = $Node.FailoverClusterGroupName
                    FailoverClusterNetworkName = $Node.FailoverClusterNetworkName
                    FailoverClusterIPAddress = $Node.FailoverClusterIPAddress
                    PsDscRunAsCredential  = $Node.SqlInstallCredential
                    DependsOn             = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
                }
        }elseif(($Node.FailoverClusterNetworkName -ne $null) -and ($Node.ClusterSQLfirstNode -eq $false)){
        #Other nodes
           $InstallTask = '[SQLSetup]InstallSQLCluster'

            $NomPremierNoeud = ($AllNodes | where {$_.ClusterSQLfirstNode -eq $true} | Select-Object -First 1).NodeName
            WaitForAll "InstallationNoeud1"
            {
                ResourceName      = '[SQLSetup]InstallSQLClusterFirstNode'
                NodeName          = "$NomPremierNoeud"
                RetryIntervalSec  = 60
                RetryCount        = 60
            }

            SQLSetup 'InstallSQLCluster' 
                {
                    Action                = "AddNode"
                    InstanceName          = $Node.InstanceName    
                    SQLSvcAccount         = $Node.SqlServiceCredential
                    AgtSvcAccount         = $Node.SqlAgentServiceCredential
                    FTSvcAccount          = $Node.SqlFullTextCredential
                    RSSvcAccount          = $Node.SqlReplicationCredential
                    ISSvcAccount          = $Node.SqlIntegrationCredential

                    SourcePath            = $ConfigurationData.NonNodeData.SQLsources.SourcePath
                    UpdateEnabled         = $Node.UpdateEnabled
                    ForceReboot           = $Node.ForceReboot
                    SuppressReboot        = $Node.SuppressReboot
                    SQMReporting          = $Node.SQMReporting
                    SecurityMode          = $Node.SecurityMode
                    SAPwd                 = $Node.SAPWD

                    FailoverClusterGroupName = $Node.FailoverClusterGroupName
                    FailoverClusterNetworkName = $Node.FailoverClusterNetworkName
                    FailoverClusterIPAddress = $Node.FailoverClusterIPAddress
                    PsDscRunAsCredential  = $Node.SqlInstallCredential

                    DependsOn             = '[WaitForAll]InstallationNoeud1'
            }

I tried to reproduce the issue without DSC, by launching the same command line and SQLSetup throw me the same error (something like the wrong credential as been passed for SQLAgent$) but.. there's no credentials passed in the command line, this is the problem.

I added this piece of hardcoded at line 996 of MSFT_SqlSetup.psm1 just to prove my point:

if ($Action -in @('Addnode')) #ajout FRANCOIS ROUSSEAU
{
    $setupArguments += @{
        SQLSVCACCOUNT="domain\svcaccount"
    }
    $setupArguments += @{
       SQLSVCPASSWORD="pass"
    }
    $setupArguments += @{
        AGTSVCACCOUNT="domain\agentAccount"
    }
    $setupArguments += @{
       AGTSVCPASSWORD="pass"
    }
}

With the 4 missing parameters SQLSVCACCOUNT, SQLSVCPASSWORD, AGTSVCACCOUNT and AGTSVCPASSWORD added to the setupArguments, the AddNode is working properly. (I guess they're only necessary when the SQLENGINE is installed...)

My log: [[SqlSetup]InstallSQLCluster] Starting setup using arguments: /AGTSVCPASSWORD="****" /ACTION="AddNode" /SQLSVCPASSWORD="****" /UPDATEENABLED="False" /AGTSVCACCOUNT="domain\svcaccount" /INSTANCENAME="test" /SKIPRULES="Cluster_VerifyForErrors" /IACCEPTSQLSERVERLICENSETERMS="True" /QUIET="True" /SQLSVCACCOUNT="domain\agentAccount" /SQMREPORTING="False" ... ... [[SqlSetup]InstallSQLCluster] Setup exited with code '0'. Setup finished successfully.

So all the pieces of the puzzle to fix the issue are there, I hope this can help to fix that issue.

Version of the operating system and PowerShell the target node is running

SQL Server edition and version the target node is running

Edition : Standard Edition Version : 11.4.7001.0 Language : 1033

What SQL Server PowerShell modules, and which version, are present on the target node.

Name Version Path

xSqlServer 9.1.0.0
SQLASCMDLETS 1.0
SQLPS 1.0

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

SqlServerDsc 11.3.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\11.3.0.0\SqlServerDsc.psd1

johlju commented 6 years ago

Thanks for this information. It looks like /FEATURES argument is not allowed when /ACTION is 'AddNode', that is why the it's not added to the argument list.

https://github.com/PowerShell/SqlServerDsc/blob/9b6f76804acf1cf241afb8161f85e8a113ecf280/DSCResources/MSFT_SqlSetup/MSFT_SqlSetup.psm1#L1202-L1212

There are more code below these rows that might need to be added as well (and above?) Wonder what other parameters should be added to the argument list, since we always check if the feature is passed in the $Features parameter. 🤔

johlju commented 6 years ago

Looking at this list, there might just be those for that you mention; https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-2017#AddNode

Francois-Rousseau commented 6 years ago

What I understand is that the code is based on what is in $Features but $Features is not allowed in AddNode (by command line it's true, but how DSC will do that if we can't pass the same list of features that were on the 'InstallFailoverCluster' section?)
And as you said, I think that they can be more of 4 parameters missing, in fact, it depends on what features are installed, but if you install AS, I guess you need to add ASSVCPASSWORD and ASSVCACCOUNT, and so on.

johlju commented 6 years ago

If the action is 'AddNode' we could just remove (or not add) the /FEATURES argument from the argumentlist.

There is a check here if it is Features argument, so if 'AddNode' we should just skip that argument and not add it. https://github.com/PowerShell/SqlServerDsc/blob/4997ae4726171110bdac57122114487cab36ab55/DSCResources/MSFT_SqlSetup/MSFT_SqlSetup.psm1#L1354-L1367

This goes against that every property that is part of a configuration should be the desired state, but adding the Features property to an configuration that has Action = 'AddNode' would simplify code they way it's implemented today.

But maybe the problem is that we use the Features property to choose what properties is added to the argument list, for example if Features contain 'SQLENGINE' we add some properties - maybe we should change so that we always add parameters to the argument list if they are bound ($PSBoundParameters) and depending of what Action it is? 🤔