文章目录

  • 运行MS SQL Server数据库
  • 在本地的RHEL上安装MS SQL Server客户端
  • 为MS SQL Server的数据库配置PVC
  • 部署MS SQL Server2019数据库镜像
  • 访问MSServer SQL
  • 导入现有数据库
  • 为MS SQL Server配置Always On Availability Groups功能
  • 部署Always On Availability Groups的Operator
  • 验证主备数据库复制
  • 验证主备数据库故障转移



说明：本文基于《

SQL Server 2019 on OpenShift》，解决了少量部署问题。

运行MS SQL Server数据库

文节在OpenShift 4.2.x上部署MS SQL Server 2019数据库，并验证其运行的可靠能力。

sql server客户端下载 下载sql server2019_sql

在本地的RHEL上安装MS SQL Server客户端

  1. 进入超级用户模式。
$ sudo su
  1. 下载 Microsoft Red Hat 存储库配置文件。
$ curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
  1. 退出超级用户模式。
$ exit
  1. 运行以下命令，以使用 unixODBC 开发人员包安装 mssql-tools。
$ sudo yum check-update
$ sudo yum install mssql-tools unixODBC-devel
  1. 配置环境变量。
$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
$ source ~/.bash_profile
  1. 执行命令，设置ODBC参数。
$ export ODBCINI=/etc/odbc.ini
$ export ODBCSYSINI=/etc

为MS SQL Server的数据库配置PVC

  1. 创建OpenShift项目。
$ oc new-project mssql
  1. 为访问MSSQL创建secret。
$ oc create secret generic mssql --from-literal=SA_PASSWORD="Sql2019isfast"
secret/mssql created
  1. 为MSSQL创建pvc。
$ oc apply -f https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/01_deploy/storage.yaml
persistentvolumeclaim/mssql-data created

部署MS SQL Server2019数据库镜像

  1. 下载sqldeployment.yaml文件到本地。
curl -LO https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/01_deploy/sqldeployment.yaml
  1. 执行命令，部署MSServer SQL。
$ oc apply -f sqldeployment.yaml
deployment.apps/mssql-deployment created
service/mssql-service created

注意，名为mssql-service的服务配置是采用的是LoadBalancer类型的网络策略，对外访问端口是31433。

apiVersion: v1
kind: Service
metadata:
  name: mssql-service
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 31433
      targetPort: 1433
  type: LoadBalancer
  1. 确认有1个Pod已经Running，并且有LoadBalance类型的Service。
oc get pod
NAME                               READY   STATUS              RESTARTS   AGE
mssql-deployment-89f96c9f8-vr2dx   1/1     Running             0          49s
 
$ oc get svc
NAME            TYPE           CLUSTER-IP       EXTERNAL-IP                                                               PORT(S)           AGE
mssql-service   LoadBalancer   172.30.145.213   a6994f3d84d1f4a8896ac061b58bbd76-2120236798.us-east-1.elb.amazonaws.com   31433:30083/TCP   21m
  1. 查看MSServer SQL的日志。
$ POD=$(oc get pods | grep mssql | awk {'print $1'})
$ oc logs $POD

访问MSServer SQL

  1. 执行命令，用MSSQL客户端访问数据库。
$ SERVERIP=$(oc get service | grep mssql-service | awk {'print $4'})
$ PORT=31433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -Q"SELECT @@version"
--------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)
        Mar 14 2020 16:10:35
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Red Hat Enterprise Linux 8.0 (Ootpa)) <X64>
(1 rows affected)
  1. 关闭MSSQL数据库运行。
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -Q"SHUTDOWN WITH NOWAIT"
  1. 查看OpenShift的项目事件，确认运行MSSQL的容器被重新运行起来。
$ oc get ev
LAST SEEN   TYPE      REASON    OBJECT                                 MESSAGE
63s         Normal    Created   pod/mssql-deployment-89f96c9f8-vr2dx   Created container mssql
63s         Normal    Started   pod/mssql-deployment-89f96c9f8-vr2dx   Started container mssql
64s         Normal    Pulled    pod/mssql-deployment-89f96c9f8-vr2dx   Container image "mcr.microsoft.com/mssql/rhel/server:2019-latest" already present on machine
78s         Warning   BackOff   pod/mssql-deployment-89f96c9f8-vr2dx   Back-off restarting failed container
  1. 查看Pod，确认Pod还是以前那个实例名，但是RESTARTS已经变为“1”。
$ oc get pod
NAME                               READY   STATUS              RESTARTS   AGE
mssql-deployment-89f96c9f8-vr2dx   1/1     Running             1          9m

导入现有数据库

  1. 修改LimitRange的配置，增加运行数据库的Pod可使用内存。
$ oc edit limitrange mssql-core-resource-limits
  1. 将default中的memory配置改为2Gi，然后保存退出。
。。。
spec:                 
  limits:             
  - default:          
      cpu: 500m       
      memory: 2Gi     
    defaultRequest:   
      cpu: 50m        
      memory: 256Mi   
    max:              
      cpu: "2"        
      memory: 6Gi     
    type: Container   
  - max:              
      cpu: "2"        
      memory: 12Gi    
    type: Pod
  1. 下载MSSQL数据库备份文件和restore脚本到本地。
$ curl -LO https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak
$ curl -LO https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/02_query/restorewwi.sql
  1. 将备份的MSSQL数据库文件传进当前运行MSSQL的容器中。
$ POD=$(oc get pods | grep mssql-deployment | awk {'print $1'})
$ oc cp ./WideWorldImporters-Full.bak $POD:/var/opt/mssql/WideWorldImporters-Full.bak
  1. 将备份数据库导入当前数据库。
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -irestorewwi.sql
Processed 1464 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 33 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
Processed 3862 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Converting database 'WideWorldImporters' from version 852 to the current version 904.
Database 'WideWorldImporters' running the upgrade step from version 852 to version 853.
。。。
Database 'WideWorldImporters' running the upgrade step from version 902 to version 903.
Database 'WideWorldImporters' running the upgrade step from version 903 to version 904.
RESTORE DATABASE successfully processed 58455 pages in 4.361 seconds (104.718 MB/sec).

注意：如果这步出现“Restore operation failed for database ‘WideWorldImporters’ due to insufficient memory in the resource pool ‘default’.”的错误提示，需要将当前sqlserver的Pod删掉，让系统自动生成一个新Pod即可。
6. 执行命令，查询MSSQL数据库的People表。

$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -Q"USE WideWorldImporters;SELECT TOP 10 FullName, PhoneNumber, EmailAddress FROM [Application].[People] ORDER BY FullName;" -Y30
Changed database context to 'WideWorldImporters'.
FullName                       PhoneNumber          EmailAddress
------------------------------ -------------------- ------------------------------
Aahlada Thota                  (215) 555-0100       aahlada@tailspintoys.com
Aakarsha Nookala               (201) 555-0100       aakarsha@tailspintoys.com
Aakriti Bhamidipati            (307) 555-0100       aakriti@wingtiptoys.com
Aakriti Byrraju                (216) 555-0100       aakriti@example.com
Aamdaal Kamasamudram           (316) 555-0100       aamdaal@wingtiptoys.com
Abel Pirvu                     (216) 555-0100       abel@wingtiptoys.com
Abel Spirlea                   (218) 555-0100       abel@example.com
Abel Tatarescu                 (217) 555-0100       abel@example.com
Abhaya Rambhatla               (231) 555-0100       abhaya@wingtiptoys.com
Abhoy Prabhupāda              (423) 555-0100       abhoy@tailspintoys.com
(10 rows affected)
  1. 删除mssql项目。
$ oc delete project mssql

为MS SQL Server配置Always On Availability Groups功能

有关“Always On Availability Groups”概念可参考微软的《AlwaysOn 可用性组概述 (SQL Server)》说明。

本节主要为MS SQL Server配置以下架构的“Always On Availability Groups”。

sql server客户端下载 下载sql server2019_pvc_02

部署Always On Availability Groups的Operator

  1. 创建新的OpenShift项目。
$ oc new-project ag1
  1. 部署Operator相关资源。
$ oc apply -f https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/05_operator/operator.yaml
serviceaccount/mssql-operator created
clusterrole.rbac.authorization.k8s.io/mssql-operator-ag1 configured
clusterrolebinding.rbac.authorization.k8s.io/mssql-operator-ag1 configured
deployment.apps/mssql-operator created
  1. 创建MSSQL数据库使用的secret
$ oc create secret generic sql-secrets --from-literal=sapassword="Sql2019isfast" --from-literal=masterkeypassword="Sql2019isfast" --namespace ag1
secret/sql-secrets created
  1. 运行以下命令，部署3个MS SQL Serveri数据库。
$ oc apply -f https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/05_operator/sqlserver.yaml --namespace ag1
sqlserver.mssql.microsoft.com/mssql1 created
service/mssql1 created
sqlserver.mssql.microsoft.com/mssql2 created
service/mssql2 created
sqlserver.mssql.microsoft.com/mssql3 created
service/mssql3 created
  1. 查看项目资源，其中每个MSSQL数据库实例都时通过LoadBalancer类型的服务被访问的。
$ oc get pod
NAME                                  READY   STATUS      RESTARTS   AGE
pod/mssql-initialize-mssql1-j22tp     0/1     Completed   0          3m5s
pod/mssql-initialize-mssql2-vb57c     0/1     Completed   0          3m2s
pod/mssql-initialize-mssql3-rhqc4     0/1     Completed   0          2m59s
pod/mssql-operator-5f4cdc58bb-b29dz   1/1     Running     0          12m
pod/mssql1-0                          2/2     Running     0          3m6s
pod/mssql2-0                          2/2     Running     0          3m3s
pod/mssql3-0                          2/2     Running     0          3m
$ oc get svc
NAME             TYPE           CLUSTER-IP      EXTERNAL-IP                                                              PORT(S)             AGE
service/ag1      ClusterIP      None            <none>                                                                   1433/TCP,5022/TCP   3m6s
service/mssql1   LoadBalancer   172.30.136.63   aa39f8a2e430011eab1c4024de7cdd9e-729408089.us-east-1.elb.amazonaws.com   1433:32248/TCP      3m6s
service/mssql2   LoadBalancer   172.30.95.59    aa3a7cbde430011eab1c4024de7cdd9e-817772632.us-east-1.elb.amazonaws.com   1433:30890/TCP      3m6s
service/mssql3   LoadBalancer   172.30.20.213   aa3b28b28430011eab1c4024de7cdd9e-874328604.us-east-1.elb.amazonaws.com   1433:31186/TCP      3m6s
  1. 创建Always On Availability Group使用的2个新的LoadBalancer Service。
$ oc apply -f https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/05_operator/ag_services.yaml --namespace ag1
service/ag1-primary created
service/ag1-secondary created
$ oc get svc
NAME            TYPE           CLUSTER-IP      EXTERNAL-IP                                                               PORT(S)             AGE
ag1             ClusterIP      None            <none>                                                                    1433/TCP,5022/TCP   154m
ag1-primary     LoadBalancer   172.30.66.180   a62c614a9430111eab1c4024de7cdd9e-1957428608.us-east-1.elb.amazonaws.com   1433:31205/TCP      149m
ag1-secondary   LoadBalancer   172.30.247.7    a62ca8a99430111eab1c4024de7cdd9e-692374381.us-east-1.elb.amazonaws.com    1433:30059/TCP      149m
mssql1          LoadBalancer   172.30.136.63   aa39f8a2e430011eab1c4024de7cdd9e-729408089.us-east-1.elb.amazonaws.com    1433:32248/TCP      154m
mssql2          LoadBalancer   172.30.95.59    aa3a7cbde430011eab1c4024de7cdd9e-817772632.us-east-1.elb.amazonaws.com    1433:30890/TCP      154m
mssql3          LoadBalancer   172.30.20.213   aa3b28b28430011eab1c4024de7cdd9e-874328604.us-east-1.elb.amazonaws.com    1433:31186/TCP      154m
  1. 查看当前数据库实例在Always On Availability Group中的复制关系角色，其中mssql1-0是PRIMARY角色。
$ curl -LO https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/05_operator/checkreplicas.sql
$ SERVERIP=$(oc get service | grep ag1-primary | awk {'print $4'})
$ PORT=1433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -icheckreplicas.sql -Y30
replica_server_name            role_desc                      operational_state_desc
------------------------------ ------------------------------ ------------------------------
mssql1-0                       PRIMARY                        ONLINE
mssql2-0                       SECONDARY                      NULL
mssql3-0                       SECONDARY                      NULL
  1. 创建backup数据库testag，然后将其加到AG组ag1中。
$ curl -LO https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/05_operator/setupag.sql
$ PORT=1433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -isetupag.sql
Changed database context to 'master'.
Changed database context to 'master'.
Processed 328 pages for database 'testag', file 'testag' on file 1.
Processed 2 pages for database 'testag', file 'testag_log' on file 1.
BACKUP DATABASE successfully processed 330 pages in 0.170 seconds (15.142 MB/sec).

验证主备数据库复制

  1. 通过主数据库服务ag1-primary创建一个表ilovesql，作为被复制的测试表。
$ curl -LO https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/05_operator/testag.sql
$ SERVERIP=$(oc get service | grep ag1-primary | awk {'print $4'})
$ PORT=1433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -itestag.sql
Changed database context to 'testag'.
(1 rows affected)
  1. 通过主数据库服务ag1-primary查询ilovesql表的记录，确认有一条记录。
$ SERVERIP=$(oc get service | grep ag1-primary | awk {'print $4'})
$ PORT=1433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -Q"SELECT 'Connected to Primary = '+@@SERVERNAME;USE testag;SELECT * FROM ilovesql" -Y60
------------------------------------------------------------
Connected to Primary = mssql1-0
(1 rows affected)
Changed database context to 'testag'.
col1        col2
----------- ------------------------------------------------------------
          1 SQL Server 2019 is fast, secure, and highly available
(1 rows affected)
  1. 通过备数据库服务ag1-secondary查询ilovesql表的记录，确认有一条记录。
$ SERVERIP=$(oc get service | grep ag1-secondary | awk {'print $4'})
$ PORT=1433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -Q"SELECT 'Connected to Secondary = '+@@SERVERNAME;USE testag;SELECT * FROM ilovesql" -K ReadOnly -Y60
------------------------------------------------------------
Connected to Secondary = mssql2-0
(1 rows affected)
Changed database context to 'testag'.
col1        col2
----------- ------------------------------------------------------------
          1 SQL Server 2019 is fast, secure, and highly available
(1 rows affected)

验证主备数据库故障转移

  1. 查看3个MSSQL数据库在Availability Groups中的角色关系。当前mssql1-0是PRIMARY，其他是SECONDARY。
$ SERVERIP=$(oc get service | grep ag1-primary | awk {'print $4'})
$ PORT=1433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -icheckreplicas.sql -Y30
replica_server_name            role_desc                      operational_state_desc
------------------------------ ------------------------------ ------------------------------
mssql1-0                       PRIMARY                        ONLINE
mssql2-0                       SECONDARY                      NULL
mssql3-0                       SECONDARY                      NULL
  1. 执行命令，通过Job模拟故障转移，
$ curl -LO https://raw.githubusercontent.com/liuxiaoyu-git/sqlworkshops-sqlonopenshift/master/sqlonopenshift/05_operator/failover.yaml
$ oc apply -f failover.yaml
serviceaccount/manual-failover created
role.rbac.authorization.k8s.io/manual-failover created
rolebinding.rbac.authorization.k8s.io/manual-failover created
job.batch/manual-failover created
  1. 再次查看3个MSSQL数据库在Availability Groups中的角色关系。当前mssql2-0成为PRIMARY，mssql1-0变为SECONDARY。
$ SERVERIP=$(oc get service | grep ag1-primary | awk {'print $4'})
$ PORT=1433
$ sqlcmd -Usa -PSql2019isfast -S$SERVERIP,$PORT -icheckreplicas.sql -Y30
replica_server_name            role_desc                      operational_state_desc
------------------------------ ------------------------------ ------------------------------
mssql1-0                       SECONDARY                      NULL
mssql2-0                       PRIMARY                        ONLINE
mssql3-0                       SECONDARY                      NULL
  1. 根据“验证主备数据库复制”的步骤再验证主备MSQL实例之间的关系。