K8S搭建SQL server

一、制作sqlserver镜像制作

1.Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-latest
USER root
RUN /bin/cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime && echo 'Asia/Shanghai' >/etc/timezone

2.制作及上传

# 开始制作镜像
mkdir -p /root/manifest/sql_server
cd /root/manifest/sql_server
# 将上面的Dockerfile上传到该目录下
执行命令
docker build -t mssql:2019 .
# 打标签docker image ls |grep mssql
mssql     2019    bf68cc180a54   2 hours ago     1.47GB

docker tag bf68cc180a54 registry-vpc.cnhangzhou.aliyuncs.com/mq_basic/mssql:2019
# 登录阿里云镜像仓库
docker login --username=mq-ops2@191065071xx registry-vpc.cn-hangzhou.aliyuncs.com
# 上传镜像
docker push registry-vpc.cn-hangzhou.aliyuncs.com/mq_basic/mssql:2019
 

二、创建yaml及部署

1.创建secret

kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd" -n ops-share

2.创建持久存储

#需要默认存储类
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: ops-mssql-data
  namespace: ops-share
spec:
  accessModes: ["ReadWriteOnce"] 
  resources:
    requests:
      storage: 100Gi

3.创建sql server deploy

• 需要设置的环境变量ACCEPT_EULA=Y；SA_PASSWORD=密码，这两个变量必须要有否则会部署失败
• 需要把数据文件夹挂载出去，路径为：/var/opt/mssql/data
• 生产环境应当新增一个持久化存储作为备份还原目录

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-deployment
  namespace: ops-share
spec:
  replicas: 1
  selector:
     matchLabels:
       app: mssql
  template:
    metadata:
      labels:
        app: mssql
    spec:
      imagePullSecrets:
      - name: image-repo-account-wangyang
      terminationGracePeriodSeconds: 30
      hostname: mssqlinst
      containers:
      - name: mssql
        image: registry-vpc.cn-hangzhou.aliyuncs.com/mq_basic/mssql:2019
        ports:
        - containerPort: 1433
        env:
        - name: MSSQL_PID
          value: "Developer"
        - name: ACCEPT_EULA
          value: "Y"     
        - name: SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: SA_PASSWORD 
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
        resources:
          limits:
            cpu: "4"
            memory: 4Gi  #内存不少于2Gi
          requests:
            cpu: 128m
            memory: 128Mi
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: ops-mssql-data 
 

4.创建svc

apiVersion: v1
kind: Service
metadata:

  name: mssql-deployment
  namespace: ops-share

spec:

  ports:
  - nodePort: 30344
    port: 1433
    protocol: TCP
    targetPort: 1433
  selector:
    app: mssql

  type: NodePort
 

三、命令行验证

#1.安装sqlcmd命令curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
yum install msodbcsql  mssql-tools -y/opt/mssql-tools/bin/sqlcmd -S 10.100.8.98,30344 -U sa -P "MyC0m9l&xP@ssw0rd"  # 公网地址：8.136.34.48,30344
1> select name from sys.databases;
2> go
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                 
 

导入指定SQL文件

/opt/mssql-tools/bin/sqlcmd -S 172.30.1.47,1433 -U sa -P "MyC0m9l&xP@ssw0rd" -d mk-hybris -i dbo.sql  #-d指定数据库 -i 指定sql文件

四、安装客户端工具(Windows)

下载地址：https://aka.ms/ssmsfullsetup

1.安装，略

2.创建连接

#注意：IP和端口连接使用,

五、出现的问题

1.导入sql报错

解决方法：

#登录数据库sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO
 

2.导入bacpac 文件报错

解决方案

同时删除默认DB中 CXCC_CLOUD_SERVICE_DBA_Manual 和CXCC_CLOUD_SERVICE_DBA_EXTENDED_Manual用户的数据

参考地址：https://learn.microsoft.com/en-us/sql/linux/quickstart-sql-server-containers-kubernetes?view=sql-server-linux-ver15