SQLAlwaysOnService - Fails To Turn On

[KurtLamb]

SQL Always On Services Fails to actually Turn it on

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

I am running a DSC installation script which will install a AG over the top of a number of SQL server instances. When i run the DSC It connects and attempts to switch it yet fails to actually do it. I can manually change it using the GUI so it can in theory be changed.

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

Config item - SqlAlwaysOnService

    SqlAlwaysOnService EnableAlwaysOn 
    {
        Ensure               = 'Present'
        ServerName           = $ConfigData.allnodes.NodeName
        InstanceName         = $ConfigData.allnodes.InstanceName;
        PsDscRunAsCredential = $SQLSysAdminAccount;
    }

VERBOSE: [SLLWINPXDSC3]: LCM:  [ Start  Resource ]  [[SqlAlwaysOnService]EnableAlwaysOn]
VERBOSE: [SLLWINPXDSC3]: LCM:  [ Start  Test     ]  [[SqlAlwaysOnService]EnableAlwaysOn]
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Importing PowerShell module SQLPS.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Connected to SQL instance 'localhost\TestDB'.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] 2018-07-16_08-23-27: SQL Always On is disabled on "localhost\TestDB".
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] 2018-07-16_08-23-27: SQL Always On is in the desired state for "localhost\TestDB": False.
VERBOSE: [SLLWINPXDSC3]: LCM:  [ End    Test     ]  [[SqlAlwaysOnService]EnableAlwaysOn]  in 3.0160 seconds.
VERBOSE: [SLLWINPXDSC3]: LCM:  [ Start  Set      ]  [[SqlAlwaysOnService]EnableAlwaysOn]
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Importing PowerShell module SQLPS.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] 2018-07-16_08-23-27: Enabling Always On for the instance localhost\TestDB
ChangeHADRService failed for Service 'MSSQL$TestDB'.
    + CategoryInfo          : NotSpecified: (:) [], CimException
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Smo.FailedOperationException,Microsoft.SqlServer.Management.PowerShell.Hadr.EnableSqlHADRServiceCommand
    + PSComputerName        : localhost
 
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] 2018-07-16_08-23-29: SQL Always On has been enabled on "localhost\TestDB". Restarting the service.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Importing PowerShell module SQLPS.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Connected to SQL instance 'localhost\TestDB'.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Getting information about service 'SQL Server'.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] 'SQL Server' service is restarting.
WARNING: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Waiting for service 'SQL Server (TESTDB) (MSSQL$TESTDB)' to stop...
WARNING: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Waiting for service 'SQL Server (TESTDB) (MSSQL$TESTDB)' to stop...
WARNING: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Waiting for service 'SQL Server (TESTDB) (MSSQL$TESTDB)' to stop...
WARNING: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Waiting for service 'SQL Server (TESTDB) (MSSQL$TESTDB)' to stop...
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Starting service 'SQL Server Agent (TESTDB)'.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Waiting for instance localhost\TestDB to report status online, with a timeout value of 120 seconds.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Importing PowerShell module SQLPS.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Connected to SQL instance 'localhost\TestDB'.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Importing PowerShell module SQLPS.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] Connected to SQL instance 'localhost\TestDB'.
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] 2018-07-16_08-23-42: SQL Always On is disabled on "localhost\TestDB".
VERBOSE: [SLLWINPXDSC3]:                            [[SqlAlwaysOnService]EnableAlwaysOn] 2018-07-16_08-23-42: SQL Always On is in the desired state for "localhost\TestDB": False.
VERBOSE: [SLLWINPXDSC3]: LCM:  [ End    Set      ]  [[SqlAlwaysOnService]EnableAlwaysOn]  in 15.9530 seconds.
PowerShell DSC resource MSFT_SqlAlwaysOnService  failed to execute Set-TargetResource functionality with error message: Failed to ensure Always On is Present on the instance 'localhost\TestDB'. 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost

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

Caption                                   OSArchitecture Version  MUILanguages $PSVersionTable.PSVersion
-------                                   -------------- -------  ------------ -------------------------
Microsoft Windows Server 2012 R2 Standard 64-bit         6.3.9600 {en-US}      5.1.14409.1012           

SQL Server edition and version the target node is running

Edition : Enterprise Edition
Version : 13.2.5026.0
Language : 1033

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

Name  Version Path                                                                                     
----  ------- ----                                                                                     
SQLPS 1.0     C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

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

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

[johlju]

This do work when I’m using the resource in my lab. Can you look in SQL Server logs and event logs to see if you see why it can’t be changed?
I’m assuming you have cluster configured.

[KurtLamb]

Hi Johan,

There are no logs that suggest why, i am looking into the Cluster at the moment, i don't think its installed correctly, so i am looking into this. I wish there was a way to get more information directly from the failed item on DSC

[johlju]

You could try running Enable-SqlAlwaysOn manually but it’s probably the only error message returned from that cmdlet?

[johlju]

Although I think Enable-SqlAlwaysOn and Disable-SqlAlwaysOn should have ErrorAction Stopsince it seems it continues even though the cmdlet fails. 🤔

[KurtLamb]

Yep, i think it was the Cluster not been correctly done, need to revert back a step and make sure the cluster is been setup correctly before getting to here! Cheers for the support :)

[michaelsync]

I am haivng the same issue. I can enable the AlwaysOn from SQL configuration manually. (So, I think I have the cluster configurated properly.)

I got this error when I do it from DSC.

PowerShell DSC resource MSFT_SqlAlwaysOnService failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance \u0027MYSQLSERVER01\u0027

More details:

"Message": "PowerShell DSC resource MSFT_SqlAlwaysOnService failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance \u0027MYSQLSERVER01\u0027. ",

"SerializedRemoteException": "System.Exception: System.InvalidOperationException: Failed to connect to SQL instance \u0027MYSQLSERVER01\u0027.", "SerializedRemoteInvocationInfo": "System.Management.Automation.InvocationInfo",
"ErrorRecord": "System.InvalidOperationException: Failed to connect to SQL instance \u0027MYSQLSERVER01\u0027.", "WasThrownFromThrowStatement": false,
"Message": "System.InvalidOperationException: Failed to connect to SQL instance \u0027MYSQLSERVER01\u0027.",
"Data": "System.Collections.ListDictionaryInternal",
"InnerException": null,
"TargetSite": "Void EndInvoke()",
"StackTrace": " at System.Management.Automation.Runspaces.AsyncResult.EndInvoke()\r\n at System.Management.Automation.PowerShell.CoreInvokeRemoteHelper[TInput,TOutput](PSDataCollection1 input, PSDataCollection1 output, PSInvocationSettings settings)\r\n at System.Management.Automation.PowerShell.CoreInvoke[TInput,TOutput](PSDataCollection1 input, PSDataCollection1 output, PSInvocationSettings settings)\r\n at System.Management.Automation.PowerShell.Invoke(IEnumerable input, PSInvocationSettings settings)\r\n at Microsoft.PowerShell.DesiredStateConfiguration.Internal.ResourceProviderAdapter.ExecuteCommand(PowerShell powerShell, ResourceModuleInfo resInfo, String operationCmd, List1 acceptedProperties, CimInstance nonResourcePropeties, CimInstance resourceConfiguration, LCMDebugMode debugMode, PSInvocationSettings pSInvocationSettings, UInt32\u0026 resultStatusHandle, Collection1\u0026 result, ErrorRecord\u0026 errorRecord, PSModuleInfo localRunSpaceModuleInfo)",

[johlju]

@michaelsync Can you please provide the verbose log when you run the configuration on the target node?

[michaelsync]

How do I get that verbose log? I got this error from Azure Automation DSC. That's all I have. I am not sure how to enable that verbose setting. I created this issue #1220 (comment) .. because I thought the issue #1187 is already closed.

[johlju]

Closing this again, and we continue the discussion in issue #1220.