search

dsccommunity / SqlServerDsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.
MIT License
359 stars 224 forks source link

SqlAGReplica - Unable to connect to Primary Replica Server #1195

Closed tonedefdev closed 6 years ago

tonedefdev commented 6 years ago

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

Issues when trying to connect the SqlAGReplica to the PrimaryReplicaServerName - I keep returning:

PowerShell DSC resource MSFT_SqlAGReplica  failed to execute Set-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance 'LLSql1.alexandrialaunchlabs.local'. 
+ CategoryInfo          : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : ProviderOperationExecutionFailure
+ PSComputerName        : localhost

All firewalls and anti-virus is disabled. When trying to use the PS command that the SqlAGReplica resource is using "Connect-SQL", - I'm not able to find it to try and test outside of the resource to determine what's wrong. Is there something I am missing?

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

            SqlSetup InstallDefaultInstance
            {
                InstanceName                        = "MSSQLSERVER"
                Features                            = "SQLENGINE,CONN"
                SQLCollation                        = "SQL_Latin1_General_CP1_CI_AS"
                SQLSvcAccount                       = $SqlServiceCredential
                AgtSvcAccount                       = $SqlAgentServiceCredential
                SQLSysAdminAccounts                 = @("Administrators")
                InstanceDir                         = "F:\LaunchLabs"
                SourcePath                          = "G:\"
                UpdateEnabled                       = "False"
                ForceReboot                         = $true
                SecurityMode                        = "SQL"
                SAPwd                               = $SACredentials
                DependsOn                           = "[cChocoPackageInstaller]installDotNet35","[WindowsFeature]NetFramework45","[Script]MountSqlImage","[Group]LocalAdmin","[WindowsFeature]ClusteringCmdInterfaceFeature"
            }

            SqlAlwaysOnService SqlAlwaysOn
            {
                ServerName                          = "LLSql2"
                InstanceName                        = "MSSQLSERVER"
                Ensure                              = "Present"
                DependsOn                           = "[SqlSetup]InstallDefaultInstance"
            }

            SqlServerLogin AddNTServiceClusSvc
            {
                Ensure                              = "Present"
                Name                                = "NT Service\ClusSvc"
                LoginType                           = "WindowsUser"
                ServerName                          = "LLSql2"
                InstanceName                        = "MSSQLSERVER"
                DependsOn                           = "[SqlAlwaysOnService]SqlAlwaysOn"
            }

            SqlServerPermission AddNTServiceClusSvcPermissions
            {
                Ensure                              = "Present"
                ServerName                          = "LLSql2"
                InstanceName                        = "MSSQLSERVER"
                Principal                           = "NT SERVICE\ClusSvc"
                Permission                          = "AlterAnyAvailabilityGroup","ViewServerState"
                DependsOn                           = "[SqlServerLogin]AddNTServiceClusSvc"
            }

            SqlWaitForAG WaitForAG
            {
                Name                                = "LL-AG"
                RetryIntervalSec                    = 60
                RetryCount                          = 30
                DependsOn                           = "[SqlServerPermission]AddNTServiceClusSvcPermissions"
            }

            SqlAGReplica SecondaryNode
            {
                Ensure                              = "Present"
                AvailabilityGroupName               = "LL-AG"
                Name                                = "LLSql2\MSSQLSERVER"
                ServerName                          = "LLSql2.alexandrialaunchlabs.local"
                InstanceName                        = "MSSQLSERVER"
                PrimaryReplicaServerName            = "LLSql1.alexandrialaunchlabs.local"
                PrimaryReplicaInstanceName          = "MSSQLSERVER"
                AvailabilityMode                    = "SynchronousCommit"
                BackupPriority                      = 50
                ConnectionModeInPrimaryRole         = "AllowAllConnections"
                ConnectionModeInSecondaryRole       = "AllowNoConnections"
                EndpointHostName                    = "LLSql2.alexandrialaunchlabs.local"
                FailoverMode                        = "Automatic"
                PsDscRunAsCredential                = $SqlServiceCredential
                DependsOn                           = "[SqlWaitForAG]WaitForAG"
            }

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

OS: Windows Server 2016 Dataceneter

Name                           Value                                                                                                                                                                                                
----                           -----                                                                                                                                                                                                
PSVersion                      5.1.14393.2363                                                                                                                                                                                       
PSEdition                      Desktop                                                                                                                                                                                              
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                                                              
BuildVersion                   10.0.14393.2363                                                                                                                                                                                      
CLRVersion                     4.0.30319.42000                                                                                                                                                                                      
WSManStackVersion              3.0                                                                                                                                                                                                  
PSRemotingProtocolVersion      2.3                                                                                                                                                                                                  
SerializationVersion           1.1.0.1

SQL Server edition and version the target node is running

SQL Server Enterprise 2017

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

ModuleType Version    Name                                ExportedCommands                                                                                                                                                          
---------- -------    ----                                ----------------                                                                                                                                                          
Manifest   14.0       SQLPS                               {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlFirewallRule, Backup-SqlDatabase...}

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

Name         Version  Path                                                                              
----         -------  ----                                                                              
SqlServerDsc 11.4.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\11.4.0.0\SqlServerDsc.psd1
tonedefdev commented 6 years ago

Found my issue - there was no default instance on primary node, and the account being used to access the first node needed explicit permissions. Closing.