New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
Zuldan opened this Issue Jun 20, 2017 · 24 comments

Comments

Projects
None yet
7 participants
@Zuldan

Zuldan commented Jun 20, 2017

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

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 20, 2017

Contributor

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?

Contributor

johlju commented Jun 20, 2017

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

This comment has been minimized.

Show comment
Hide comment
@randomnote1

randomnote1 Jun 20, 2017

Contributor

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.

Contributor

randomnote1 commented Jun 20, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 21, 2017

Contributor

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

Contributor

johlju commented Jun 21, 2017

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

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 21, 2017

Contributor

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.

Contributor

johlju commented Jun 21, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 21, 2017

Contributor

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  
Contributor

johlju commented Jun 21, 2017

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

This comment has been minimized.

Show comment
Hide comment
@Zuldan

Zuldan Jun 22, 2017

@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.

Zuldan commented Jun 22, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@PlagueHO

PlagueHO Jun 22, 2017

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

PlagueHO commented Jun 22, 2017

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

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 22, 2017

Contributor

And opportunity for learning too 😄

Contributor

johlju commented Jun 22, 2017

And opportunity for learning too 😄

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 23, 2017

Contributor

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.

Contributor

johlju commented Jun 23, 2017

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.

@johlju johlju added in progress and removed help wanted labels Jun 23, 2017

@johlju johlju changed the title from xSQLServerAlwaysOnAvailabilityGroup - SqlServer Module v21.0.17099 breaks the resource to 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
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).

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

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#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 added a commit to johlju/SqlServerDsc that referenced this issue Jun 23, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#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 added a commit to johlju/SqlServerDsc that referenced this issue Jun 23, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 23, 2017

Contributor

@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')
Contributor

johlju commented Jun 23, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@Zuldan

Zuldan Jun 24, 2017

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

Zuldan commented Jun 24, 2017

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

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

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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

This comment has been minimized.

Show comment
Hide comment
@Zuldan

Zuldan Jun 30, 2017

@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!

Zuldan commented Jun 30, 2017

@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!

@Zuldan Zuldan closed this Jun 30, 2017

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 30, 2017

Contributor

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.

Contributor

johlju commented Jun 30, 2017

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 johlju reopened this Jun 30, 2017

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jun 30, 2017

Contributor

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

Contributor

johlju commented Jun 30, 2017

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

johlju added a commit to johlju/SqlServerDsc that referenced this issue Jul 8, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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

This comment has been minimized.

Show comment
Hide comment
@mdaniou

mdaniou Jul 12, 2017

Contributor

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.

Contributor

mdaniou commented Jul 12, 2017

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.

johlju added a commit to johlju/SqlServerDsc that referenced this issue Jul 12, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jul 12, 2017

Contributor

@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.

Contributor

johlju commented Jul 12, 2017

@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.

johlju added a commit to johlju/SqlServerDsc that referenced this issue Jul 13, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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

This comment has been minimized.

Show comment
Hide comment
@Pniland79

Pniland79 Jul 20, 2017

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

Pniland79 commented Jul 20, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jul 20, 2017

Contributor

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.

Contributor

johlju commented Jul 20, 2017

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

This comment has been minimized.

Show comment
Hide comment
@Pniland79

Pniland79 Jul 20, 2017

@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

Pniland79 commented Jul 20, 2017

@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

johlju added a commit to johlju/SqlServerDsc that referenced this issue Aug 13, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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 added a commit to johlju/SqlServerDsc that referenced this issue Aug 17, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 18, 2017

Contributor

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.

Contributor

johlju commented Aug 18, 2017

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 johlju changed the title from BREAKING CHANGE: xSQLServerAlwaysOnAvailabilityGroup - SqlServer Module v21.0.17099 breaks the resource to BREAKING CHANGE: xSQLServerAlwaysOnAvailabilityGroup: SqlServer Module v21.0.17099 breaks the resource Aug 18, 2017

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 18, 2017

Contributor

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

Contributor

johlju commented Aug 18, 2017

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

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 18, 2017

Contributor

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

Contributor

johlju commented Aug 18, 2017

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

johlju added a commit to johlju/SqlServerDsc that referenced this issue Aug 19, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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 added a commit to johlju/SqlServerDsc that referenced this issue Aug 19, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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 added a commit to johlju/SqlServerDsc that referenced this issue Aug 19, 2017

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

johlju added a commit to johlju/SqlServerDsc that referenced this issue Sep 8, 2017

Changes to Connect-SQL and Import-SQLPSModule
Now it correctly loads the correct assemblies when SqlServer module is present (issue PowerShell#649).
Now SQLPS module will be correctly loaded (discovered) after installation of SQL Server (issue PowerShell#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 added a commit to johlju/SqlServerDsc that referenced this issue Sep 8, 2017

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

@johlju johlju removed the breaking change label Sep 11, 2017

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

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Sep 11, 2017

Contributor

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

Contributor

johlju commented Sep 11, 2017

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

@johlju johlju closed this in #800 Sep 12, 2017

johlju added a commit that referenced this issue Sep 12, 2017

xSQLServer: Using correct assemblies for module SqlServer (#800)
- 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).

@johlju johlju removed the in progress label Oct 27, 2017

@MattLDN

This comment has been minimized.

Show comment
Hide comment
@MattLDN

MattLDN Nov 30, 2017

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.

MattLDN commented Nov 30, 2017

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment