Please feel free to provide feedback or file bugs here.

SqlServer module Join-SqlAvailabilityGroup connection pooling error

When I call Join- SqlAvailabilityGroup commandlet and active database for pooled connections is not master, I get an error message. There are could be number of different reasons why active database for the PowerShell session would change.

1 vote
Sign in
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jes Borland shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      • Jes Borland commented  ·   ·  Flag as inappropriate

        Code to reproduce:

        (get-module SqlServer -listavailable | sort -Property Path -Descending)[0] | Import-Module -DisableNameChecking

        $mxrAGName = 'yourAGName'
        $mxrPrimaryNode = 'PrimaryNodeName'
        $mxrReplicaNode = 'SecondaryNodeName'
        $mxrInstanceName = 'SQLInstanceName'
        $mxrEPPortNo = 2222
        $mxrPrimarySQLName = "$($mxrPrimaryNode)\$($mxrInstanceName)"
        $mxrReplicaSQLName = "$($mxrReplicaNode)\$($mxrInstanceName)"
        $endpointURL = "TCP://$([System.Net.Dns]::GetHostEntry($mxrReplicaNode).hostname):$($mxrEPPortNo)"

        $sqlTextCheckConnections = @"
        select @@servername as ServerName, session_id, login_time, last_request_end_time, host_name, program_name,db_name(database_id) as ActiveDBName
        from sys.dm_exec_sessions where
        host_name = (select host_name from sys.dm_exec_sessions where session_id = @@spid)
        Invoke-Sqlcmd -ServerInstance $mxrReplicaSQLName -Query $sqlTextCheckConnections | ft -AutoSize

        $agName = $mxrAGName

        # If replica already part of ag, just remove it for testing
        $sqlText = "ALTER AVAILABILITY GROUP [$($mxrAgName)] REMOVE REPLICA ON N'$mxrReplicaSQLName'"
        Invoke-Sqlcmd -ServerInstance $mxrPrimarySQLName -Query $sqlText

        $mxrPrimarySrvObj = get-item "SQLSERVER:SQL\$($mxrPrimarySQLName)" -ErrorAction Stop
        $mxrReplicaSrvObj = get-item "SQLSERVER:SQL\$($mxrReplicaSQLName)" -ErrorAction Stop
        # $mxrReplicaSrvObj.ConnectionContext.SqlConnectionObject.ChangeDatabase('master')

        $agPSPath2ag = "SQLSERVER:SQL\$mxrPrimarySQLName\AvailabilityGroups\$($agName)"

        # Reproduce the problem. Issue statement that could quietly change active database
        $mxrReplicaSrvObj.ConnectionContext.ExecuteScalar('select db_name()')

        $result = New-SqlAvailabilityReplica -Name $mxrReplicaSQLName `
        -EndpointUrl $endpointURL `
        -FailoverMode 'Manual' `
        -AvailabilityMode 'AsynchronousCommit' `
        -ConnectionModeInSecondaryRole 'AllowNoConnections' `
        -Path $agPSPath2ag `
        -ErrorAction Stop
        <# Sucess

        $result = Join-SqlAvailabilityGroup `
        -InputObject $mxrReplicaSrvObj `
        -Name $agName `
        -ErrorAction Stop

        <# Getting error
        Join-SqlAvailabilityGroup : Availability-group DDL operations are permitted only when you are using the master database. Run the USE MASTER command, and retry your availability-group DDL command.
        At C:\tfs\Database Solutions\EnvironmentStandardsAndConfigurationBuild\InstallSQLServer\HCPBuild\AGBuild\Test-SqlSMOJoinAGProblem.ps1:59 char:11
        + $result = Join-SqlAvailabilityGroup `
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidOperation: (:) [Join-SqlAvailabilityGroup], SqlException
        + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.JoinSqlAvailabilityGroupCommand

      Feedback and Knowledge Base