K8s部署SQL Server 2019 Always On时副本加入可用性组报错如何解决？

我有两个节点，名称分别为mssql-primary和mssql-secondary1，基于Kubernetes配置，运行开启了Always On功能的mssql-2019。两个节点运行状态均正常，但我尝试将节点加入可用性组时遇到如下报错：

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

该报错为SQL Server 47106错误，核心原因是辅助副本无法与主副本完成可用性组配置同步，按以下步骤排查即可解决：

排查步骤 #

• 首先检查两个Pod的网络连通性：登录secondary1 Pod，测试是否能ping通mssql-primary的hostname，同时用nc命令测试主副本5022端口是否连通，反向从主副本测试辅助副本的5022端口也要确保连通。
• 检查两个实例是否都创建了HADR端点，端点是否处于启动状态，SA账号是否拥有端点的连接权限。
• 检查当前配置的冗余问题：两个Deployment的yaml里，在spec.template.spec层级已经设置了hostname，在spec层级又重复写了一遍hostname，属于无效配置。

修复步骤 #

1. 创建无头服务实现主机名解析
   K8s默认无法直接解析Pod的hostname，需要为两个实例分别创建无头Service，配置如下：
   mssql-primary-svc.yaml

apiVersion: v1
kind: Service
metadata:
  name: mssql-primary-svc
spec:
  selector:
    app: mssql-primary
  clusterIP: None
  ports:
  - name: sql
    port: 1433
    targetPort: 1433
  - name: hadr
    port: 5022
    targetPort: 5022

mssql-secondary1-svc.yaml

apiVersion: v1
kind: Service
metadata:
  name: mssql-secondary1-svc
spec:
  selector:
    app: mssql-secondary1
  clusterIP: None
  ports:
  - name: sql
    port: 1433
    targetPort: 1433
  - name: hadr
    port: 5022
    targetPort: 5022

执行kubectl apply -f命令创建两个服务。
2. 修正Deployment配置
删除两个Deployment yaml中spec层级下重复的hostname: xxx配置，重新apply生效。
3. 创建并授权HADR端点
分别连接两个MSSQL实例，执行以下SQL语句：

CREATE ENDPOINT [Hadr_endpoint] 
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    )
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [sa]
GO

4. 重新尝试加入可用性组
   完成以上配置后，重新在主副本上创建可用性组，再将辅助副本加入即可。如果仍有报错，可查看/var/opt/mssql/log/errorlog日志获取更详细的错误信息。

内容的提问来源于stack exchange，提问作者Payam Khaninejad