Error on AvailabilityGroup Creation: SqlAG #1151

mohamednazar · 2018-06-25T15:53:31Z

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

When Trying to create Sql Availability Group I am getting error.

Generated at 2018-06-25 15:42:49 +0000

Chef::Exceptions::PowershellCmdletException: dsc_resource[create_availability_group] (SqlServer::ae-dsc-availability-group line 11) had an error: 
Chef::Exceptions::PowershellCmdletException: Powershell Cmdlet failed: 
PowerShell DSC resource MSFT_SqlAG  failed to execute Set-TargetResource functionality with error message: 
Creating the availability group 'AG_DBTL'. 
InnerException: Cannot bind parameter 'InputObject'. Cannot convert the "[DBServer01]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server". 
    + CategoryInfo          : InvalidOperation: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost

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

dsc_resource 'create_availability_group' do
  resource :SqlAG
  property :Name, 'AG_DB01'
  property :Ensure, 'Present'
  property :InstanceName, 'MSSQLSERVER'
  property :ServerName, 'localhost'
  property :AutomatedBackupPreference, 'Primary'
  property :AvailabilityMode, 'SynchronousCommit'
  property :ConnectionModeInPrimaryRole, 'AllowAllConnections'
  property :FailoverMode, 'Automatic'
  property :PsDscRunAsCredential, ps_credential((node['sqlserver']['Username']), (node['sqlserver']['Password']))
end

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

Caption OSArchitecture Version MUILanguages $PSVersionTable.PSVersion

Microsoft Windows Server 2016 Datacenter 64-bit 10.0.14393 {en-US} 5.1.14393.2248

SQL Server edition and version the target node is running

Edition : Enterprise Edition: Core-based Licensing
Version : 13.2.5026.0
Language : 1033

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

Name Version Path

xSQLServer 9.1.0.0 C:\Program Files\WindowsPowerShell\Modules\xSQLServer\9.1.0.0\xSQLServer.psd1
SQLPS 1.0 C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

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

Name Version Path

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

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

mohamednazar · 2018-06-25T16:36:10Z

I have removed xSqlServer DSC Resource and now my server has only SqlServerDsc resource only.

see the output of Get-Module sql -ListAvailable

PS C:\Users> Get-Module *sql* -ListAvailable

    Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Manifest   11.3.0.0   SqlServerDsc

    Directory: C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Manifest   1.0        SQLPS                               {Backup-SqlDatabase, Save-SqlMigrationReport, Add-SqlAvail...

johlju · 2018-06-26T07:05:56Z

Please see issue #812, I think you experience the same problem?

mohamednazar · 2018-06-26T10:12:32Z

Yes same problem but only one change. #812 facing issue with xSqlServer DSCResource but in my case I am facing issue with SqlServerDsc DSCResource. Also I dont find any solution provided for #812, so please let me know how do I resolve this issue?

Thanks

johlju · 2018-06-26T10:46:51Z

@mohamednazar Issue #812 is for SqlServerDsc as well, just that the issue has been open since before the rename.
Do you have SSMS installed on the target node?
Do you have any other instances of SQL server than version 13?
Are you running SQL Server 2016 with Service Pack 2 (just to confirm the version number)?

mohamednazar · 2018-06-26T10:59:48Z

@johlju

we installed with SSMS 17.7 on target node.
There are only one version (13.0.5149.0) of Sql server is running on target node.
Target node installed with SQL Server 2016 with Service pack 2 and Cumulative Update 1

Microsoft SQL Server 2016 (SP2-CU1) (KB4135048) - 13.0.5149.0 (X64) May 19 2018 09:41:57 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Please let me know if you need any other info?

Thanks,

mohamednazar · 2018-06-27T11:31:56Z

@johlju waiting for your response.

johlju · 2018-06-27T13:16:13Z

I running this in my lab to see if I can reproduce it, but it's gonna take a while since I don't have that much bandwidth for this.

mohamednazar · 2018-06-27T13:18:01Z

Thanks for the update, please let me know as soon as you find anything...

johlju · 2018-06-27T15:57:11Z

The problem seems to be when SSMS is installed prior to the configuration is run. SSMS probably adds assemblies that are the wrong version than SQLPS expects.

PowerShell DSC resource MSFT_SqlAG  failed to execute Set-TargetResource functionality with error message: Creating the availability group 'TESTCLU02-AG1'. InnerException: Cannot bind parameter 'InputObject'. Cannot convert the "[sqltest.company.local\SQL2016]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure

When running the configuration without SSMS 17.7 installed the AG is created successfully.

Successful

Windows Server 2012 R2
SQL Server 2016 SP2 CU1
Run SqlAG configuration

Fails

Windows Server 2012 R2
SSMS 17.7
SQL Server 2016 SP2 CU1
Run SqlAG configuration

mohamednazar · 2018-06-27T18:49:13Z

Thanks after I removed SSMS 17.7 I am able to create SQLAG using DSCResource.

I have to install SSMS before SQLAG creation, so shall I install any lower version of SSMS like 14.0.17177.0?

johlju · 2018-06-28T08:15:55Z

Not sure it helps with a lower version, but please try and let me know the result. An workaround is to install SSMS after AG is created, but then maybe consistency checks fail when SSMS is installed - I have not tested that. 🤔

The problem I think is that "Import-Module SQLPS" is loading the wrong assemblies when SSMS is installed.

johlju · 2018-06-28T09:44:52Z

Using SSMS 17.8.1 together with latest SqlServer module seems to work.

Successful

Windows Server 2012 R2
Install SSMS 17.8.1
Install latest SqlServer module
Install SQL Server 2016 SP2 CU1
Run SqlAG configuration

johlju · 2018-06-28T09:53:00Z

@mohamednazar can you confirm that the SSMS 17.8.1 and latest SqlServer module works for you as well?

mohamednazar · 2018-06-28T09:55:12Z

upgrading ssms to 17.8.1, how to I get latest SqlServer module? Shall I uninstall and reinstall SqlServerDsc module? While I reinstalling will it get latest version of module?

johlju · 2018-06-28T09:58:13Z

SqlServer module and SqlServerDsc module is two different modules. SqlServer is the "new" SQLPS module. https://www.powershellgallery.com/packages/SqlServer

mohamednazar · 2018-06-28T10:01:08Z

Oh. I am using SqlServerDsc module. Shall I continue to use it or should I change to SqlServer? Which one is the correct one?

johlju · 2018-06-28T10:03:04Z

Ah sorry, you should have both installed on the target node 🙂 SqlServerDsc have the DSC resources, and SqlServer has all the cmdlets that SQLPS have.

mohamednazar · 2018-06-28T10:06:17Z

ok I will install both and try with 17.8 version of SSMS. I will update you the status once done.

…munity#1151)

- Changes to SqlServerDsc - Updated helper function Restart-SqlService to have to new optional parameters `SkipClusterCheck` and `SkipWaitForOnline`. This was to support more aspects of the resource SqlServerNetwork. - Changes to SqlServerNetwork - Refactor SqlServerNetwork to not load assembly from GAC (issue #1151). - The resource now supports restarting the SQL Server service when both enabling and disabling the protocol. - Added integration tests for this resource (issue #751).

johlju · 2018-07-09T06:54:38Z

@mohamednazar I have merged a few PR that will mitigate these issue (loading wrong assembly) in certain scenarios. But you combination of using SQLPS module with SSMS 17.x will never work. Closing this, and assuming you got this resolved when using the necessary versions of each components above.

mohamednazar · 2018-07-09T09:56:21Z

@johlju installing SQL AG first then SSMS 17.x is working fine for me. You can close this case.

johlju added the needs more information The issue needs more information from the author or the community. label Jun 26, 2018

johlju added a commit to johlju/SqlServerDsc that referenced this issue Jul 4, 2018

Refactor SqlServerNetwork to not load assembly from GAC (issue dsccom…

291b7a2

…munity#1151)

johlju added a commit to johlju/SqlServerDsc that referenced this issue Jul 4, 2018

Refactor SqlServerNetwork to not load assembly from GAC (issue dsccom…

d33c323

…munity#1151)

johlju mentioned this issue Jul 4, 2018

SqlServerNetwork: Refactored to not load assembly from GAC #1172

Merged

9 tasks

johlju added a commit to johlju/SqlServerDsc that referenced this issue Jul 4, 2018

Refactor SqlServerNetwork to not load assembly from GAC (issue dsccom…

3745098

…munity#1151)

johlju mentioned this issue Jul 6, 2018

SqlServerDsc: Improvement to how Import-SQLPSModule import module #1175

Merged

9 tasks

johlju closed this as completed Jul 9, 2018

johlju added external The issue cannot be resolved within the DSC Resource Kit. and removed needs more information The issue needs more information from the author or the community. labels Jul 9, 2018

mohamednazar mentioned this issue Mar 25, 2019

Steps Required: SQL AlwaysOn Availability Group High Availability #910

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error on AvailabilityGroup Creation: SqlAG #1151

Error on AvailabilityGroup Creation: SqlAG #1151

mohamednazar commented Jun 25, 2018 •

edited by johlju

Loading

mohamednazar commented Jun 25, 2018 •

edited by johlju

Loading

johlju commented Jun 26, 2018 •

edited

Loading

mohamednazar commented Jun 26, 2018

johlju commented Jun 26, 2018

mohamednazar commented Jun 26, 2018

mohamednazar commented Jun 27, 2018

johlju commented Jun 27, 2018

mohamednazar commented Jun 27, 2018

johlju commented Jun 27, 2018 •

edited

Loading

mohamednazar commented Jun 27, 2018

johlju commented Jun 28, 2018

johlju commented Jun 28, 2018 •

edited

Loading

johlju commented Jun 28, 2018

mohamednazar commented Jun 28, 2018

johlju commented Jun 28, 2018

mohamednazar commented Jun 28, 2018

johlju commented Jun 28, 2018

mohamednazar commented Jun 28, 2018

johlju commented Jul 9, 2018

mohamednazar commented Jul 9, 2018

Error on AvailabilityGroup Creation: SqlAG #1151

Error on AvailabilityGroup Creation: SqlAG #1151

Comments

mohamednazar commented Jun 25, 2018 • edited by johlju Loading

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

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

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

SQL Server edition and version the target node is running

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

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

mohamednazar commented Jun 25, 2018 • edited by johlju Loading

johlju commented Jun 26, 2018 • edited Loading

mohamednazar commented Jun 26, 2018

johlju commented Jun 26, 2018

mohamednazar commented Jun 26, 2018

mohamednazar commented Jun 27, 2018

johlju commented Jun 27, 2018

mohamednazar commented Jun 27, 2018

johlju commented Jun 27, 2018 • edited Loading

mohamednazar commented Jun 27, 2018

johlju commented Jun 28, 2018

johlju commented Jun 28, 2018 • edited Loading

johlju commented Jun 28, 2018

mohamednazar commented Jun 28, 2018

johlju commented Jun 28, 2018

mohamednazar commented Jun 28, 2018

johlju commented Jun 28, 2018

mohamednazar commented Jun 28, 2018

johlju commented Jul 9, 2018

mohamednazar commented Jul 9, 2018

mohamednazar commented Jun 25, 2018 •

edited by johlju

Loading

mohamednazar commented Jun 25, 2018 •

edited by johlju

Loading

johlju commented Jun 26, 2018 •

edited

Loading

johlju commented Jun 27, 2018 •

edited

Loading

johlju commented Jun 28, 2018 •

edited

Loading