# 创建Secret存储SA密码
kubectl create secret generic mssql-secret \
–namespace sqlserver \
–from-literal=SA_PASSWORD=”FgEdu123!@#”

# 创建存储类
cat > storage-class.yaml << EOF apiVersion: storage.k8s.io/v1 kind: StorageClass metadata: name: sqlserver-storage provisioner: kubernetes.io/aws-ebs parameters: type: gp3 iopsPerGB: "10" encrypted: "true" reclaimPolicy: Retain allowVolumeExpansion: true volumeBindingMode: WaitForFirstConsumer EOF kubectl apply -f storage-class.yaml # 创建StatefulSet cat > mssql-statefulset.yaml << EOF apiVersion: apps/v1 kind: StatefulSet metadata: name: mssql namespace: sqlserver spec: serviceName: mssql replicas: 3 selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: containers: - name: mssql image: mcr.microsoft.com/mssql/server:2019-latest ports: - containerPort: 1433 env: - name: MSSQL_PID value: "Developer" - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql-secret key: SA_PASSWORD resources: requests: cpu: "2" memory: "8Gi" limits: cpu: "4" memory: "16Gi" volumeMounts: - name: mssql-data mountPath: /var/opt/mssql readinessProbe: exec: command: - /bin/bash - -c - /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -Q "SELECT 1" initialDelaySeconds: 30 periodSeconds: 10 livenessProbe: exec: command: - /bin/bash - -c - /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -Q "SELECT 1" initialDelaySeconds: 60 periodSeconds: 30 volumeClaimTemplates: - metadata: name: mssql-data spec: accessModes: ["ReadWriteOnce"] storageClassName: "sqlserver-storage" resources: requests: storage: 100Gi EOF kubectl apply -f mssql-statefulset.yaml # 创建Service cat > mssql-service.yaml << EOF apiVersion: v1 kind: Service metadata: name: mssql namespace: sqlserver spec: selector: app: mssql ports: - port: 1433 targetPort: 1433 clusterIP: None EOF kubectl apply -f mssql-service.yaml # 创建LoadBalancer Service用于外部访问 cat > mssql-loadbalancer.yaml << EOF apiVersion: v1 kind: Service metadata: name: mssql-external namespace: sqlserver spec: selector: app: mssql ports: - port: 1433 targetPort: 1433 type: LoadBalancer EOF kubectl apply -f mssql-loadbalancer.yaml # 查看部署状态 kubectl get pods -n sqlserver kubectl get services -n sqlserver kubectl get pvc -n sqlserver

# 1. 使用本地存储
cat > local-storage-class.yaml << EOF apiVersion: storage.k8s.io/v1 kind: StorageClass metadata: name: local-storage provisioner: kubernetes.io/no-provisioner volumeBindingMode: WaitForFirstConsumer EOF kubectl apply -f local-storage-class.yaml # 创建本地持久卷 cat > local-pv.yaml << EOF apiVersion: v1 kind: PersistentVolume metadata: name: mssql-pv-1 spec: capacity: storage: 100Gi volumeMode: Filesystem accessModes: - ReadWriteOnce persistentVolumeReclaimPolicy: Retain storageClassName: local-storage local: path: /mnt/disks/ssd1 nodeAffinity: required: nodeSelectorTerms: - matchExpressions: - key: kubernetes.io/hostname operator: In values: - node1 EOF kubectl apply -f local-pv.yaml # 2. 配置网络 # 创建网络策略 cat > network-policy.yaml << EOF apiVersion: networking.k8s.io/v1 kind: NetworkPolicy metadata: name: mssql-network-policy namespace: sqlserver spec: podSelector: matchLabels: app: mssql policyTypes: - Ingress - Egress ingress: - from: - podSelector: matchLabels: app: mssql-client ports: - protocol: TCP port: 1433 egress: - to: - podSelector: matchLabels: k8s-app: kube-dns ports: - protocol: UDP port: 53 EOF kubectl apply -f network-policy.yaml # 3. 配置ConfigMap cat > mssql-configmap.yaml << EOF apiVersion: v1 kind: ConfigMap metadata: name: mssql-config namespace: sqlserver data: MSSQL_MAX_SERVER_MEMORY: "12288" MSSQL_MIN_SERVER_MEMORY: "4096" MSSQL_MAXDOP: "2" MSSQL_COLLATION: "Chinese_PRC_CI_AS" EOF kubectl apply -f mssql-configmap.yaml # 更新StatefulSet使用ConfigMap kubectl patch statefulset mssql -n sqlserver --type=json -p='[ { "op": "add", "path": "/spec/template/spec/containers/0/envFrom", "value": [ { "configMapRef": { "name": "mssql-config" } } ] } ]'

# 1. 创建配置文件
cat > mssql-ag.yaml << EOF apiVersion: apps/v1 kind: StatefulSet metadata: name: mssql-ag namespace: sqlserver spec: serviceName: mssql-ag replicas: 3 selector: matchLabels: app: mssql-ag template: metadata: labels: app: mssql-ag spec: containers: - name: mssql image: mcr.microsoft.com/mssql/server:2019-latest ports: - containerPort: 1433 - containerPort: 5022 env: - name: MSSQL_PID value: "Enterprise" - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql-secret key: SA_PASSWORD - name: MSSQL_AGENT_ENABLED value: "True" resources: requests: cpu: "4" memory: "16Gi" limits: cpu: "8" memory: "32Gi" volumeMounts: - name: mssql-data mountPath: /var/opt/mssql volumeClaimTemplates: - metadata: name: mssql-data spec: accessModes: ["ReadWriteOnce"] storageClassName: "sqlserver-storage" resources: requests: storage: 200Gi EOF kubectl apply -f mssql-ag.yaml # 2. 配置Always On Availability Group # 在主节点上执行 kubectl exec -it mssql-ag-0 -n sqlserver -- /bin/bash # 创建数据库 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "FgEdu123!@#" -Q "CREATE DATABASE fgedudb;" # 启用Always On可用性组 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "FgEdu123!@#" -Q "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'availability group', 1; RECONFIGURE;" # 创建可用性组 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "FgEdu123!@#" -Q "CREATE AVAILABILITY GROUP [fgedu_ag] WITH (DB_FAILOVER = ON, AUTOMATED_BACKUP_PREFERENCE = PRIMARY) FOR DATABASE [fgedudb] REPLICA ON N'mssql-ag-0' WITH (ENDPOINT_URL = N'TCP://mssql-ag-0.mssql-ag.sqlserver.svc.cluster.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)), N'mssql-ag-1' WITH (ENDPOINT_URL = N'TCP://mssql-ag-1.mssql-ag.sqlserver.svc.cluster.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)), N'mssql-ag-2' WITH (ENDPOINT_URL = N'TCP://mssql-ag-2.mssql-ag.sqlserver.svc.cluster.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 10, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));" # 配置可用性组监听器 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "FgEdu123!@#" -Q "ALTER AVAILABILITY GROUP [fgedu_ag] ADD LISTENER N'fgedu_ag_listener' (WITH IP ((N'10.0.0.100', N'255.255.255.0')), PORT = 1433);" # 在辅助节点上加入可用性组 kubectl exec -it mssql-ag-1 -n sqlserver -- /bin/bash /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "FgEdu123!@#" -Q "ALTER AVAILABILITY GROUP [fgedu_ag] JOIN;" kubectl exec -it mssql-ag-2 -n sqlserver -- /bin/bash /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "FgEdu123!@#" -Q "ALTER AVAILABILITY GROUP [fgedu_ag] JOIN;" # 验证可用性组状态 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "FgEdu123!@#" -Q "SELECT * FROM sys.dm_hadr_availability_group_states;"