SqlAG : Creating an AG fails #812
Comments
|
Hi, |
|
That seems odd. The Looking at the cmdlet in the SQLServer module, it looks like the functionality hasn't changed from SQLPS: |
|
It must be something with the wrong assembly being loaded. @MakwaWes can you do this just to get a bit more information.
|
|
I had to progress in my work so I removed the module "SqlServer" from the machine and it's working flawlessly since. Unfortunately, I can't answer completely your questions but I'll try.
It used to also have the module "SqlServer" v21.0.17152 before I removed it.
I hope this helps. |
|
@MakwaWes, did you have SQL Server Management Studio installed on the box? |
|
No, as described in the issue, the server OS is a Windows Core therefore we don't deploy any GUI stuff on it. |
|
Oops, missed that. 😕 Thanks! |
johlju
changed the title
|
@MakwaWes Can you please confirm that this is still a problem with SqlServerDsc v10? |
|
I can confirm that this does occur in v10. I replaced line 335 in MSFT_SqlServerEndpoint.psm1 with the below and it worked fine for me. If this is an acceptable fix, I will send you a PR for it. |
|
@dcrreynolds Did you confirm this when using SqlServer module? If so, I think we need to confirm that the change works for SQLPS as well so we don't break that. |
|
According to Get-Module -ListAvailable, SQLPS is the only relevant module I have. |
|
I've exactly the same issue. I've replaced the same line 335 as @dcrreynolds, but in the MSFT_SqlAG.psm1 file. On my system, SSMS 17.4 is installed. The only relevant SQL module is SQLPS. By the way, exactly the same issue on MSFT_SqlAGListener: And on MSFT_SqlAGReplica: I think we have a general problem with all AG resources and PowerShell modules. |
|
@claudiospizzi I've run into the same issues with MSFT_SqlAGListener and MSFT_SqlAGReplica now too. |
|
Update: Update the text a bit. Hmm.. Before this was a problem when the wrong version of the SMO library was loaded. 🤔 We had the same problem before in the closed issue #649 and I thought we solved it. @claudiospizzi and @dcrreynolds do you both have SSMS 17.x installed? I'm thinking that both the old and the new assemblies are in the session, but it loads (finds) the new SqlServer assemblies first and then when a cmdlet from SQLPS is used which needs the old assemblies, it breaks, because the new assemblies does not work with SQLPS. On the target node that's falling, can you both please add this row After the following row. Please run the configuration again with verbose output, and hopefully this will output verbose messages and list all the loaded assemblies in the session. |
|
I am hitting similar error when trying to create AG. I have tried replacing the line Environment xSQLServer module, Version 9.0 , SQL Server 2016, SSMS 17.4 already installed I appreciate any pointers to workaround through this error. |
|
@johlju Here the GAC output of my lab environment: |
|
UPDATE: Using SQLServer module solved the issue. |
|
This issues seems to persist in the new SQLSERVER Module. However the issue is resolved in the following (Earlier) version of the module. |
|
Any update on this? Running into this issue with SqlAG on v11.2 of the module |
|
@claudiospizzi Seemed you got two version of Microsoft.SqlServer.Smo.dll loaded into the session. This can be the reason. |
|
@brwilkinson @smastrorocco Can you please do a run on the target and output the verbose output after you added the row mentioned in this #812 (comment). Looking for a combination of assemblies and modules that make this problem happen. |
johlju
added
needs more information
|
@johlju We're executing the resource through Chef, here is the error from the DSC resource ***EDIT: I just noticed your comment about adding the verbose output with the additional line. I'm adding that now and will leave another reply with it. Error executing action `run` on resource 'dsc_resource[<MY-AG>]'
================================================================================
Chef::Exceptions::PowershellCmdletException
-------------------------------------------
Powershell Cmdlet failed: PowerShell DSC resource MSFT_<MY-AG> failed to execute Set-TargetResource
functionality with error message: Creating the availability group '<MY-AG>'.
InnerException: Cannot bind parameter 'InputObject'. Cannot convert the
"[<MY-SERVER>]" value of type "Microsoft.SqlServer.Management.Smo.Server" to
type "Microsoft.SqlServer.Management.Smo.Server".Here's the PS modules loaded: Directory: C:\Program Files\WindowsPowerShell\Modules
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 11.2.0.0 SqlServerDsc
Directory: C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 1.0 SQLASCMDLETS {Add-RoleMember, Backup-ASDatabase, Invoke-ASCmd, Invoke-P...
Manifest 1.0 SQLPS {Backup-SqlDatabase, Add-SqlAvailabilityDatabase, Add-SqlA... |
stale
bot
removed
the
needs more information
|
@johlju Sorry for the delay: VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Dmf\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dmf.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.RegisteredServers\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.RegisteredServers.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.RegSvrEnum\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.RegSvrEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ServiceBrokerEnum\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ServiceBrokerEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Collector\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Collector.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.CollectorEnum\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.CollectorEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Utility\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Utility.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.UtilityEnum\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.UtilityEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.HadrDMF\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.HadrDMF.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:False Version:v2.0.50727 Location:C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Dmf\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dmf.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:False Version:v2.0.50727 Location:C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] GAC:True Version:v2.0.50727 Location:C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] Importing PowerShell module SQLPS.
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] Connected to SQL instance '<MY-SERVER>'.
VERBOSE: [<MY-SERVER>]: [[SqlAG]Add<MY-AG>] The cluster login 'NT AUTHORITY\SYSTEM' has the required permissions.
VERBOSE: [<MY-SERVER>]: LCM: [ End Set ] [[SqlAG]Add<MY-AG>] in 0.8620 seconds.
PowerShell DSC resource MSFT_SqlAG failed to execute Set-TargetResource functionality with error message: Creating
the availability group '<MY-AG>'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the
"[<MY-SERVER>]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type
"Microsoft.SqlServer.Management.Smo.Server".
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : ProviderOperationExecutionFailure
+ PSComputerName : localhost
VERBOSE: [<MY-SERVER>]: LCM: [ End Set ]
The SendConfigurationApply function did not succeed.
+ CategoryInfo : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
+ FullyQualifiedErrorId : MI RESULT 1
+ PSComputerName : localhost
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 3.434 seconds |
|
No worries. Looking at the output there are only one version of SQLPS, and the PowerShell module SqlServer is not present, but something is loading two version of the same assembly. So the wrong assembly is probably being used which gives this error. The configuration you are running, is it configuring only one instance, or several instances of different SQL Server versions? |
johlju
added
the
needs more information
stale
bot
removed
the
needs more information
johlju
added
the
needs more information
|
Hey @johlju been following this feed for some time now, I am currently running into this same issue on the listener script for the InputObject, I had this same issue when creating the AG but my workaround was adding .name to serverobject variable for InputObject. I tried to use the same work around for the listener InputObject and it is giving me this error: We are using an older version of DSC 8.2, I know behind, but here is my output for Get-Module sql -ListAvailable I have been following your recommendations and steps but looks like I am only running 1 assembly in the GAC_MSIL and that is version 14 Any help on the matter would be appreciated! Thanks |
|
I guess since you are still on 8.2.0.0 this has worked previously? I see you are using the latest SqlServer module, will it work if you downgrade that? The assemblies are packaged with SqlServer 🤔 So they should not load the SQLPS assemblies (v14). |
|
Are any of you installing Analysis Services? The helper function That row should be replaced with |
|
So this did work with SqlPS but now we are trying to copy the module SQLServer from a golden server to the target nodes that DSC runs on, I will try to downgrade and see what I get and let you know. I ran: and got back: I updated the Connect-SQLAnalysis in my xSQLHelper.psm1 will post what happens next |
|
@johlju No, we are not installing AS. I believe the multiple versions are coming from the SQL install, and then SSMS v17.x. The resources work fine until SSMS is installed. Our setup is nothing special, it's more or less the default settings for the resources in this case. Single instance installed on single node. Resources are executed through chef-client using the |
|
OK tried with versions 21.0.17099, 21.0.17152, 21.0.17199, 21.0.17262 and no luck, and looks like we are not install AS either |
|
also, when I dont use any versions of SQLServer it defaults to SQLPS and works just fine, problem is I need the SQLServer module on the target nodes |
|
@travrat Could you provide your configuration with obfuscated sensitive information? Please make it as small as possible while still being able to reproduce the problem. I will try running it my lab an see if I get the same problem. |
|
I have merged a few PR's that will mitigate the assembly problem in certain scenarios, for example when running more than one major version of SQL Server and using SQLPS. and refactored SqlServerNetwork resource to not import assemblies from GAC. See change log for a little more information. There are a few more issues that need to be resolved (Connect-SQLAnalysis helper function, and SqlServerReplication resource) to only use the GAC that is in the session that is loaded by But there are scenarios, using some components together, that will never work due to hove the assemblies are loaded. If using SSMS 17.x, make sure to have SqlServer module on the target node (both SqlServer and SqlServerDsc should be on the target node). Using SQLPS module with SSMS 17.x will not work. |
|
This issue has been automatically marked as stale because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 10 days. If the issue is labelled with any of the work labels (e.g bug, enhancement, documentation, or tests) then the issue will not auto-close. |
stale
bot
added
the
stale
|
This issue has been automatically closed because it is has not had activity from the community in the last 40 days. |
johlju
removed
the
needs more information
Details of the scenario you tried and the problem that is occurring:
Deploying a new AOAG on a SQL Server 2016 fails with the following error :
PowerShell DSC resource MSFT_xSQLServerAlwaysOnAvailabilityGroup failed to execute Set-TargetResource functionality with error message: Creating the availability group 'CustomAGName'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the "[SQLServer\CustomInstanceName]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".It worked after I edited the resource "MSFT_xSQLServerAlwaysOnAvailabilityGroup.psm1" on the server.
Line 369
Original : "InputObject = $serverObject"
Updated (and working) : "InputObject = $serverObject.Name"
The DSC configuration that is using the resource (as detailed as possible):
Nothing fancy, pretty much as in the example of the resource.
Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2016 Core
SQL Server 2016 Enterprise Core
PSVersion 5.1.14393.1198
What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
SQLServer 21.0.17178
Version of the DSC module you're using, or 'dev' if you're using current dev branch:
xSQLServer 8.1.0.0 (also tried with dev)
Thank you,
Wesley
The text was updated successfully, but these errors were encountered: