一、制作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用户的数据
