xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource #649

Zuldan · 2017-06-20T02:20:35Z

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

When using the Microsoft SQLServer Module v21.0.17099 (https://www.powershellgallery.com/packages/SqlServer/21.0.17099), xSQLServerAlwaysOnAvailabilityGroup errors with "Cannot bind parameter 'InputObject'. Cannot convert the "[LABSERVER01]" value of type "Microsoft.SqlServer.Management.Smo.Server**" to type "Microsoft.SqlServer.Management.Smo.Server".**"

If I just use SQLPS, xSQLServerAlwaysOnAvailabilityGroup runs perfectly and creates the AOAG groups as instructed.

BaseSQLServerAlwaysOnAGConfig: [xSQLServerAlwaysOnAvailabilityGroup]SQLAOGroupCLST-TEST01
Error: Message
PowerShell DSC resource MSFT_xSQLServerAlwaysOnAvailabilityGroup  failed to execute Set-TargetResource functionality with error message: Creating the availability group 'CLST-TEST01'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert t
he "[LABSERVER01]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
Error: Exception
SerializedRemoteException      : System.Exception: Creating the availability group 'CLST-TEST01'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the "[LABSERVER01]" value of type "Microsoft.SqlServer.Management.Smo.Server" to 
                                 type "Microsoft.SqlServer.Management.Smo.Server". ---> System.Management.Automation.ParameterBindingException: Cannot bind parameter 'InputObject'. Cannot convert the "[LABSERVER01]" value of type 
                                 "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server". ---> System.Management.Automation.PSInvalidCastException: Cannot convert the "[LABSERVER01]" value of type 
                                 "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
                                    at System.Management.Automation.LanguagePrimitives.ThrowInvalidCastException(Object valueToConvert, Type resultType)
                                    at System.Management.Automation.LanguagePrimitives.ConvertNoConversion(Object valueToConvert, Type resultType, Boolean recurse, PSObject originalValueToConvert, IFormatProvider formatProvider, TypeTable backupTable)
                                    at System.Management.Automation.LanguagePrimitives.ConversionData`1.Invoke(Object valueToConvert, Type resultType, Boolean recurse, PSObject originalValueToConvert, IFormatProvider formatProvider, TypeTable backupTable)
                                    at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, Boolean recursion, IFormatProvider formatProvider, TypeTable backupTypeTable)
                                    at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, IFormatProvider formatProvider)
                                    at System.Management.Automation.ParameterBinderBase.CoerceTypeAsNeeded(CommandParameterInternal argument, String parameterName, Type toType, ParameterCollectionTypeInformation collectionTypeInfo, Object currentValue)
                                    --- End of inner exception stack trace ---
                                    at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                                    at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                                    at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                                    at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                                    --- End of inner exception stack trace ---
SerializedRemoteInvocationInfo : System.Management.Automation.InvocationInfo
ErrorRecord                    : Creating the availability group 'CLST-TEST01'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the "[LABSERVER01]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type 
                                 "Microsoft.SqlServer.Management.Smo.Server".
WasThrownFromThrowStatement    : False
Message                        : Creating the availability group 'CLST-TEST01'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the "[LABSERVER01]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type 
                                 "Microsoft.SqlServer.Management.Smo.Server".
Data                           : System.Collections.ListDictionaryInternal
InnerException                 : 
TargetSite                     : Void EndInvoke()
StackTrace                     :    at System.Management.Automation.Runspaces.AsyncResult.EndInvoke()
                                    at System.Management.Automation.PowerShell.CoreInvokeRemoteHelper[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
                                    at System.Management.Automation.PowerShell.CoreInvoke[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
                                    at System.Management.Automation.PowerShell.Invoke(IEnumerable input, PSInvocationSettings settings)
                                    at Microsoft.PowerShell.DesiredStateConfiguration.Internal.ResourceProviderAdapter.ExecuteCommand(PowerShell powerShell, ResourceModuleInfo resInfo, String operationCmd, List`1 acceptedProperties, CimInstance 
                                 nonResourcePropeties, CimInstance resourceConfiguration, LCMDebugMode debugMode, PSInvocationSettings pSInvocationSettings, UInt32& resultStatusHandle, Collection`1& result, ErrorRecord& errorRecord, PSModuleInfo 
                                 localRunSpaceModuleInfo)
HelpLink                       : 
Source                         : System.Management.Automation
HResult                        : -2146233087

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

N/A

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:

Windows Server 2012 R2
SQL Server 2016 SP1 CU3
PS 5.1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:

SqlServer v21.0.17099

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

7.1.0.0

The text was updated successfully, but these errors were encountered:

johlju · 2017-06-20T16:55:27Z

Hmm... Did we finally get into a problem with using LoadWithPartial in the helper function Connect-SQL (I have been afraid of that). Issue #520. 🤔
Guessing, without actually knowing, since it seems it fails to convert same object type to the same object type.

@randomnote1 Have you seen this error?

randomnote1 · 2017-06-20T20:04:28Z

I haven't seen that yet. The only time I've seen it is when I messed up the smo stubs when running unit testing.

johlju · 2017-06-21T17:05:49Z

I have reproduced the same problem. But it's gonna be quite a task to debug this one I think. :)

johlju · 2017-06-21T18:16:50Z

So when I reproduced this I used SQL Server 2014. I get the same error as above.

VERBOSE: [SQLTEST]: LCM:  [ Start  Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2014-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2014-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_19-30-42: Preferred module SqlServer found.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2014-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_19-30-42: Importing SqlServer module.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2014-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_19-30-42: Connected to SQL sqltest\SQL2014
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2014-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_19-30-42: Getting the effective permissions for the login 'NT SERVICE\ClusSvc' on 'SQL20
14'.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2014-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_19-30-42: Connected to SQL sqltest\SQL2014
VERBOSE: [SQLTEST]: LCM:  [ End    Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2014-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]  in 1.5000 seconds.
PowerShell DSC resource MSFT_xSQLServerAlwaysOnAvailabilityGroup  failed to execute Set-TargetResource functionality with error message: Creating the availability group 'TESTCLU02-AG1'. InnerException: Cannot bind parameter 'InputObject
'. Cannot convert the "[sqltest\SQL2014]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : sqltest.company.local

And it is this row that fails (and throws)

# Create the Availabilty Group
try 
{
    New-SqlAvailabilityGroup @newAvailabilityGroupParams -ErrorAction Stop
}
catch
{
    throw New-TerminatingError -ErrorType CreateAvailabilityGroupFailed -FormatArgs $Name -ErrorCategory OperationStopped -InnerException $_.Exception
}

But when I run this code manually and using SqlServer v21 module I get this error. When using SQLPS I don't get this error. So it seems SqlServer module is not backward compatible. That means we have to rethink the whole idea of loading SqlServer module as the preferred module unless SQL Server is, what 2017?

New-SqlAvailabilityGroup : Cannot write property BasicAvailabilityGroup.This property is not available onSQL Server 2014.
At line:1 char:21
+ ...             New-SqlAvailabilityGroup @newAvailabilityGroupParams -Err ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [New-SqlAvailabilityGroup], UnknownPropertyException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.NewSqlAvailabilityGroupCommand

@Zuldan Since you are using SQL Server 2016 I have to revert the test VM and deploy SQL 2016 instead to try to reproduce the error. It seems it doesn't say the actual error (after the result above), so gonna be interesting to see what it says on a SQL Server 2016.

johlju · 2017-06-21T19:06:56Z

Get the same error with SQL Server 2016 and SqlServer module. So I can reproduce it there too.
But when I run the code manually, everything works.

VERBOSE: [SQLTEST]: LCM:  [ Start  Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_20-47-45: Preferred module SqlServer found.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_20-47-45: Importing SqlServer module.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_20-47-45: Connected to SQL sqltest\SQL2016
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_20-47-45: Getting the effective permissions for the login 'NT SERVICE\ClusSvc' on 'SQL2016'.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-06-21_20-47-45: Connected to SQL sqltest\SQL2016
VERBOSE: [SQLTEST]: LCM:  [ End    Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]  in 1.7960 seconds.
PowerShell DSC resource MSFT_xSQLServerAlwaysOnAvailabilityGroup  failed to execute Set-TargetResource functionality with error message: Creating the availability group 'TESTCLU02-AG1'. InnerException: Cannot bind parameter 'InputObject
'. Cannot convert the "[sqltest\SQL2016]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : sqltest.company.local

Manually everything works. Note that I use all helper functions, exactly as it does when it is run thru LCM.

PS > New-SqlAvailabilityGroup @newAvailabilityGroupParams -ErrorAction Stop

Name                 PrimaryReplicaServerName        
----                 ------------------------        
TESTCLU02A           SQLTEST\SQL2016

But it is the New-SqlAvailabilityGroup cmdlet that has a problem, because it is the only one passing an parameter named InputObject, see below. And the error message that is thrown here is equal to the error message above. So I'm leaning towards that the object returned from Connect-SQL is the correct type, but the wrong "version".

# Set up the parameters for the new availability group
$serverObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName

...

$newAvailabilityGroupParams = @{
    InputObject = $serverObject
    Name = $Name
    AvailabilityReplica = $primaryReplica
}

...

# Create the Availability Group
try 
{
    New-SqlAvailabilityGroup @newAvailabilityGroupParams -ErrorAction Stop
}
catch
{
    throw New-TerminatingError -ErrorType CreateAvailabilityGroupFailed -FormatArgs $Name -ErrorCategory OperationStopped -InnerException $_.Exception
}

Guess we have to try to force load the new and old assembly in Connect-SQL to see if it can be reproduced with only one or both assemblies.
I can see both 'Microsoft.SqlServer.Smo.dll' are loaded into the application domain.

PS > [System.AppDomain]::CurrentDomain.GetAssemblies() | ? Location -like *SqlServer*

GAC    Version        Location                                                                                                                                                                                                      
---    -------        --------                                                                                                                                                                                                      
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Smo.dll                                                                                                                   
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.Sdk.Sfc.dll                                                                                                    
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.ConnectionInfo.dll                                                                                                        
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.SqlEnum.dll                                                                                                               
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Dmf.Common.dll                                                                                                            
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Dmf.dll                                                                                                                   
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.SqlWmiManagement.dll                                                                                                      
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.SmoExtended.dll                                                                                                           
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.RegisteredServers.dll                                                                                          
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.RegSvrEnum.dll                                                                                                            
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.WmiEnum.dll                                                                                                               
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.ServiceBrokerEnum.dll                                                                                                     
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.Collector.dll                                                                                                  
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.CollectorEnum.dll                                                                                              
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.Utility.dll                                                                                                    
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.UtilityEnum.dll                                                                                                
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.HadrDMF.dll                                                                                                    
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.PSSnapins.dll                                                                                                  
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.AlwaysEncrypted.Types.dll                                                                                      
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Management.PSProvider.dll                                                                                                 
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.AnalysisServices.PowerShell.Provider.dll                                                                                            
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.AnalysisServices.AppLocal.Core.dll                                                                                                  
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.AnalysisServices.AppLocal.dll                                                                                                       
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.SqlClrProvider.dll                                                                                                        
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.AnalysisServices.PowerShell.Cmdlets.dll                                                                                             
False  v4.0.30319     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.AnalysisServices.AppLocal.Tabular.dll                                                                                               
True   v2.0.50727     C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll                                                                                                   
False  v2.0.50727     C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17099\Microsoft.SqlServer.Diagnostics.STrace.dll

Zuldan · 2017-06-22T09:15:57Z

@johlju thank you so much for looking into this. I seem to cause a lot of extra for you and @PlagueHO. I feel bad! It looks like this is a tricky problem.

PlagueHO · 2017-06-22T10:05:02Z

Every problem is an opportunity for improvement (or so I'm told 😁 )!

johlju · 2017-06-22T10:20:27Z

And opportunity for learning too 😄

johlju · 2017-06-23T10:24:09Z

Okay, I think I found it. It's wrong of us to use LoadWithPartialName as stated in issue #520. It was a problem with wrong version of the assembly was loaded with LoadWithPartialName, because the wrong assembly is present in the GAC while the SqlServer module assemblies are not. And even if the assembly would have been in the GAC, using LoadWithPartialName would have loaded the first assembly it found. That could on some target nodes be the correct assembly, and on other target nodes it could have been the wrong assembly.

Instead we should just import the correct module and the necessary assemblies (and only the correct version) will load, and then the type used ([Microsoft.SqlServer.Management.Smo.Server]) will be the correct version.

We still have the problem that SqlServer module is not supported on older version. I will investigate that further and see how we solve that. Maybe we need to support loading an assembly with a strong name with older SQL Server versions (2014 and below). Or we just say that for SQL Server 2016 and newer the SqlServer module is the preferred one, and on SQL Server 2014 and older, the old SQLPS is the preferred one. But If using both SQL Server 2016 and SQL Server 2014 on the same target node would mean that SQL Server 2014 would break if SqlServer module is deployed unless the helper function Import-SQLPSModule knows what version we are installing and load the correct module.
But potentially maybe SQLPS for SQL Server 2014 is not compatible with objects in SQL Server 2008 R2 if those would be installed side-by-side. This is a tricky one. 😄

For this to work, we need to have a solution that works when all version of SQL Server is installed side-by-side (2008 R2, 2012, 2014, 2016, 2017 and vNext).

I will start with making this issue work, so @Zuldan can test (if possible). This will be a breaking change.

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649).

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server. Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

johlju · 2017-06-23T12:51:15Z

@Zuldan You can find a fix for your particular issue in my working branch here; https://github.com/johlju/xSQLServer/tree/fix-issue-649. I will not PR in it yet, because I have to test a whole lot of scenarios mentioned above. It will fix your issue (tested and working in my lab).

In my working branch you only need the file xSQLServerHelper.psm1, and in particular the functions Connect-SQL and Import-SQLPSModule.

In the helper function Connect-SQL, this row was change from this

$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')

To this

Import-SQLPSModule

When not using the module SqlServer I'm got the below error from the Set-method after successfull instalation of SQL Server.
This is because the path in $env:PSModulePath to SQLPS module has not been recognized by the PowerShell session. So the Test-method could not verify the installation.

VERBOSE: [SQLTEST]:                            [[xSQLServerSetup]InstallSQL2016Instance] 2017-06-23_12-48-29: Module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [SQLTEST]: LCM:  [ End    Set      ]  [[xSQLServerSetup]InstallSQL2016Instance]  in 443.7490 seconds.
PowerShell DSC resource MSFT_xSQLServerSetup  failed to execute Set-TargetResource functionality with error message: Neither SqlServer module or SQLPS module was found.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : sqltest.company.local

This was solved using this, to refresh the PSModulePath environment variable before loading the SQLPS module. This actually solved another issue that I seen with xSQLServerScript that I hadn't gotten around to investigate yet. 😄

<#
    After installing SQL Server the current PowerShell session doesn't know about the new path
    that was added for the SQLPS module.
    This reloads PowerShell session environment variable PSModulePath to make sure it contains
    all paths.
#>
$env:PSModulePath = [System.Environment]::GetEnvironmentVariable('PSModulePath', 'Machine')

Zuldan · 2017-06-24T02:45:21Z

@johlju wow thank you for making a fix so quickly. I will test this first thing Monday morning and report back.

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue dsccommunity#659). Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

Zuldan · 2017-06-30T07:46:16Z

@johlju apologies for the delay it's been a crazy week. It's looks like you have found the solution. It's working for me. Thanks again for fixing this so quickly. Sending a virtual pack of beer to you!

johlju · 2017-06-30T08:48:41Z

Let's keep this open until the fix is merged. In a week or so I'm hoping I can verify the other scenarios mentioned above, and after that I can PR in the fix.

johlju · 2017-06-30T08:49:02Z

@Zuldan Happy to hear it's working for you!

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue dsccommunity#659). Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

mdaniou · 2017-07-12T13:54:53Z

Hi,

I had the same problem and using Import-SQLPSModule fixes the issue
I am configuring the AGroup on SQL SERVER 2016 installed on WS2016 machines.

Thanks for the fix.

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue dsccommunity#659). Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

johlju · 2017-07-12T15:27:45Z

@mdaniou Glad to hear that it solved you problem.

Please report any issue you have with this fix. I haven't had the time to verify this fix so this will not be included in the 8.0.0.0 release.
I keep the fix working branch up-to-date so you still can re-download the fix. Please note that it will be rebased against the dev-branch, not the release (master) branch.

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue dsccommunity#659). Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

Pniland79 · 2017-07-20T10:31:23Z

Hi @johlju I am new to DSC and having the same problem as above using version 8.0.0.0

I have tried to overwrite the files from your branch into my 8.0.0.0 folder.

Am I missing something else to register your changes?

Errors as follows:

PowerShell DSC resource MSFT_xSQLServerAlwaysOnAvailabilityGroup failed to execute Set-TargetResource functionality with error message: Creating the availability group 'TestAG'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the
"[SERVERA2016\SQLTest]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".

I also notice : Information: PowerShell module SqlServer not found, trying to use older SQLPS module.

Many thanks!

Paul

johlju · 2017-07-20T10:47:36Z

This is interesting. It loads the SQLPS module, and you getting this error.

Can you please post the verbose log from the run when you get the error (remove any sensitive information)? it would help me to do further testing with this bug fix.
Can you please run this cmdlet on the target node and post the result?

Get-Module *sql* -ListAvailable

Have you downloaded the SqlServer module (together with the xSQLServer module) to the target node? Doesn't seem to be able to load the SqlServer module.

Pniland79 · 2017-07-20T11:11:34Z

@johlju Thankyou for the prompt response.

You are a legend. My issue is resolved!

I only downloaded the xSQLServer module and not the SQLServer Module.

Best Regards!

Paul

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue dsccommunity#659). Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

johlju · 2017-08-18T07:53:32Z

I have tested this fix with both SQLPS and SQLServer and it seems it works.

This fix works in these tested scenarios:

SQL Server 2016 with SQLPS module.
SQL Server 2016 with SQLServer module.
SQL Server 2014 with SQLPS module.
SQL Server 2014 with SQLServer module
Both SQL Server 2014 and SQL Server 2016 with SQLPS module
Both SQL Server 2014 and SQL Server 2016 with SQLServer module

The problem I got above with SQL Server 2014 seems to be limited to creating Availability Groups in SQL Server 2014 using the PowerShell module SqlServer. I will open another issue to track that.

So I'm feeling confident that we can merge this code.

johlju · 2017-08-18T08:14:04Z

Submitted new issue #772 to track the problem creating Availability Groups using SqlServer PowerShell module in SQL Server 2014 SP2.

johlju · 2017-08-18T11:29:36Z

Problem with integration test with these fixes. see issue #774.

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue dsccommunity#659). Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

- Changes to xSQLServer - Updated appveyor.yml so that integration tests can run in AppVeyor (issue dsccommunity#774). - Changes to xSQLServerSetup - Fixed so that the integration test copies back the SQLPS module (issue dsccommunity#774).

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649). Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue dsccommunity#659). Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path.

- Changes to xSQLServer - Updated appveyor.yml so that integration tests can run in AppVeyor (issue dsccommunity#774). - Changes to xSQLServerSetup - Fixed so that the integration test copies back the SQLPS module (issue dsccommunity#774).

johlju · 2017-09-11T20:07:32Z

Removed the breaking change for this issue. If anyone feels it still should be a breaking change. Let me know.

- Changes to xSQLServer - Updated appveyor.yml so that integration tests run in order and so that the SQLPS module folders are renamed to not disturb the units test, but can be renamed back by the integration tests xSQLServerSetup so that the integration tests can run successfully. (issue #774). - Changes to xSQLServerHelper - Changes to Connect-SQL and Import-SQLPSModule - Now it correctly loads the correct assemblies when SqlServer module is present (issue #649). - Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server. Previously resources depending on SQLPS module could fail because SQLPS was not found after installation because the PSModulePath environment variable in the (LCM) PowerShell session did not contain the new module path. - Changes to xSQLServerSetup - Fixed so that the integration test renames back the SQLPS module folders if they was renamed by AppVeyor (in the appveyor.yml file). (issue #774). - Fixed so integration test does not write warnings when SQLPS module is loaded (issue #798).

MattLDN · 2017-11-30T16:58:44Z

I've just run into this issue as well, guessing it wasn't included in 9.0.0.0 (and you branch with the fix link doesn't work anymore)

I had PSSQL module installed. I had to install SqlServer module from the PSGallery to make it work.

johlju added bug The issue is a bug. help wanted The issue is up for grabs for anyone in the community. labels Jun 20, 2017

johlju added in progress The issue is being actively worked on by someone. and removed help wanted The issue is up for grabs for anyone in the community. labels Jun 23, 2017

johlju changed the title ~~xSQLServerAlwaysOnAvailabilityGroup - SqlServer Module v21.0.17099 breaks the resource~~ BREAKING CHANGE: xSQLServerAlwaysOnAvailabilityGroup - SqlServer Module v21.0.17099 breaks the resource Jun 23, 2017

johlju added a commit to johlju/SqlServerDsc that referenced this issue Jun 23, 2017

Changes to Connect-SQL and Import-SQLPSModule

307fddb

Now it correctly loads the correct assemblies when SqlServer module is present (issue dsccommunity#649).

Zuldan closed this as completed Jun 30, 2017

johlju reopened this Jun 30, 2017

johlju changed the title ~~BREAKING CHANGE: xSQLServerAlwaysOnAvailabilityGroup - SqlServer Module v21.0.17099 breaks the resource~~ BREAKING CHANGE: xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource Aug 18, 2017

johlju mentioned this issue Aug 18, 2017

xSQLServer: Problem running integration tests with fixes in issue #649 #774

Closed

johlju added the breaking change When used on an issue, the issue has been determined to be a breaking change. label Aug 24, 2017

This was referenced Sep 9, 2017

xSQLServerServiceAccount: New Resource Added #795

Merged

xSQLServer: Using correct assemblies for module SqlServer #800

Merged

vors assigned johlju Sep 11, 2017

johlju removed the breaking change When used on an issue, the issue has been determined to be a breaking change. label Sep 11, 2017

johlju changed the title ~~BREAKING CHANGE: xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource~~ xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource Sep 11, 2017

johlju closed this as completed in #800 Sep 12, 2017

johlju mentioned this issue Sep 15, 2017

SqlAG : Creating an AG fails #812

Closed

johlju mentioned this issue Oct 25, 2017

SqlServerDsc: mixing xSQLServer resources with custom SQL Server resources #893

Closed

johlju removed the in progress The issue is being actively worked on by someone. label Oct 27, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource #649

xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource #649

Zuldan commented Jun 20, 2017 •

edited

Loading

johlju commented Jun 20, 2017

randomnote1 commented Jun 20, 2017

johlju commented Jun 21, 2017

johlju commented Jun 21, 2017

johlju commented Jun 21, 2017 •

edited

Loading

Zuldan commented Jun 22, 2017

PlagueHO commented Jun 22, 2017

johlju commented Jun 22, 2017 •

edited

Loading

johlju commented Jun 23, 2017

johlju commented Jun 23, 2017

Zuldan commented Jun 24, 2017

Zuldan commented Jun 30, 2017

johlju commented Jun 30, 2017

johlju commented Jun 30, 2017

mdaniou commented Jul 12, 2017

johlju commented Jul 12, 2017

Pniland79 commented Jul 20, 2017

johlju commented Jul 20, 2017 •

edited

Loading

Pniland79 commented Jul 20, 2017

johlju commented Aug 18, 2017 •

edited

Loading

johlju commented Aug 18, 2017

johlju commented Aug 18, 2017

johlju commented Sep 11, 2017

MattLDN commented Nov 30, 2017

xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource #649

xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource #649

Comments

Zuldan commented Jun 20, 2017 • edited Loading

johlju commented Jun 20, 2017

randomnote1 commented Jun 20, 2017

johlju commented Jun 21, 2017

johlju commented Jun 21, 2017

johlju commented Jun 21, 2017 • edited Loading

Zuldan commented Jun 22, 2017

PlagueHO commented Jun 22, 2017

johlju commented Jun 22, 2017 • edited Loading

johlju commented Jun 23, 2017

johlju commented Jun 23, 2017

Zuldan commented Jun 24, 2017

Zuldan commented Jun 30, 2017

johlju commented Jun 30, 2017

johlju commented Jun 30, 2017

mdaniou commented Jul 12, 2017

johlju commented Jul 12, 2017

Pniland79 commented Jul 20, 2017

johlju commented Jul 20, 2017 • edited Loading

Pniland79 commented Jul 20, 2017

johlju commented Aug 18, 2017 • edited Loading

johlju commented Aug 18, 2017

johlju commented Aug 18, 2017

johlju commented Sep 11, 2017

MattLDN commented Nov 30, 2017

Zuldan commented Jun 20, 2017 •

edited

Loading

johlju commented Jun 21, 2017 •

edited

Loading

johlju commented Jun 22, 2017 •

edited

Loading

johlju commented Jul 20, 2017 •

edited

Loading

johlju commented Aug 18, 2017 •

edited

Loading