Kubernetes: Failed to join the instance 'mssql-secondary1' to the availability group 'fghyt'. (Microsoft.SqlServer.Management.HadrModel)

Viewed 122

I have two node with names of mssql-primary and mssql-secondary1 configured with Kubernetes and mssql-2019 with always-on enabled. Everything works fine on both nodes but when I try to connect them to availability group I face below error:

Failed to join the instance 'mssql-secondary1' to the availability group 'fghyt'. (Microsoft.SqlServer.Management.HadrModel)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46041.41+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

------------------------------ ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot join availability group 'fghyt'. Download configuration timeout. Please check primary configuration, network connectivity and firewall setup, then retry the operation. Failed to join local availability replica to availability group 'fghyt'.  The operation encountered SQL Server error 47106 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the

primary.yml:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-primary-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mssql-primary
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mssql-primary
    spec:
      hostname: mssql-primary
      terminationGracePeriodSeconds: 10
      securityContext:
        fsGroup: 1000
      volumes:
        - name: task-pv-storage-primary
          persistentVolumeClaim:
            claimName: mssql-pv-claim-primary
      containers:
      - name:  mssql-primary
        image: mcr.microsoft.com/mssql/server:2019-latest
        env:
        - name:  MSSQL_PID
          value:  "Developer"
        - name:  ACCEPT_EULA
          value: "Y"
        - name:  MSSQL_ENABLE_HADR
          value: "1"
        - name:  MSSQL_AGENT_ENABLED
          value: "true"
        - name:  MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name:  mssql
              key: SA_PASSWORD
        resources:
          limits:
            memory: 3G
        volumeMounts:
          - name:  task-pv-storage-primary
            mountPath:  /var/opt/mssql
    hostname: mssql-primary

Secondary1.yml:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-secondary1-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mssql-secondary1
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mssql-secondary1
    spec:
      hostname: mssql-secondary1  
      terminationGracePeriodSeconds: 10
      securityContext:
        fsGroup: 1000
      volumes:
        - name: task-pv-storage-secondary1
          persistentVolumeClaim:
            claimName: mssql-pv-claim-secondary1
      containers:
      - name:  mssql-secondary1
        image: mcr.microsoft.com/mssql/server:2019-latest
        env:
        - name:  MSSQL_PID
          value:  "Developer"
        - name:  ACCEPT_EULA
          value: "Y"
        - name:  MSSQL_ENABLE_HADR
          value: "1"
        - name:  MSSQL_AGENT_ENABLED
          value: "true"
        - name:  MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name:  mssql
              key: SA_PASSWORD
        resources:
          limits:
            memory: 3G
        volumeMounts:
          - name:  task-pv-storage-secondary1
            mountPath:  /var/opt/mssql
    hostname: mssql-secondary1
1 Answers

Based on your error I have found this similar issue.

It is basically said that:

The hostname should be of max 15 characters for the node specifier while creating the availability group or else it won't recognize the remaining characters.

If you encourage any problems during renaming see also this issue, where reinstalling window clustering helped. If it won't help try re-configuring availability group. Following videos show it step-by step:

Related