New to KubeDB? Please start here.
Arbiter Node Support for Microsoft SQL Server Cluster with Even Nodes
Arbiter Node Concepts
We use Raft Consensus Algorithm to determine the primary of SQL Server Availability Group cluster. Raft relies on a quorum-based voting system, which ideally requires an odd number of nodes to avoid split votes. However, many users prefer a two-replica setup for cost efficiency, a primary for write/read operations and a secondary for read queries.
To resolve the potential split vote issue in a two-node deployment, we introduce an extra node known as an Arbiter. This lightweight node participates solely in leader election (voting) and does not store any database data.
Key Points:
Voting-Only Role: The Arbiter participates solely in the leader election process. It casts a vote to help achieve quorum but does not store or process any database data.
Lightweight & Cost-Effective: The Arbiter is deployed in its own PetSet with a dedicated PVC using minimal storage (default 2GB). This allows you to run a two-node cluster (one primary and one secondary) without extra expense.
Automatic Inclusion: When you deploy a SQL Server Availability Group cluster with an even number of replicas (e.g., two), KubeDB automatically adds the Arbiter node. This extra vote ensures that a clear primary is elected.
Implementation: Along with each mssql container, we run a separate sidecar container named mssql-coordinator which determines the leader of the cluster both on bootstrap and in fail over scenarios.
Note: The arbiter node doesn’t store any data related to sqlserver database. However, it needs a bare minimum of 1Gi~2Gi Storage for storing WAL and Snapshots that are generated by RAFT. But the generation of WAL are not very frequent. So storage space of 2Gi is more than enough for most of the clusters.
Here we will show how to use KubeDB to provision a SQL Server even-sized Availability Group Cluster with Arbiter Node.
Setup
You need to have a Kubernetes cluster, and the kubectl command-line tool must be configured to communicate with your cluster. If you do not already have a cluster, you can create one by using kind.
Now, install KubeDB cli on your workstation and KubeDB operator in your cluster following the steps here. Make sure install with helm command including
--set global.featureGates.MSSQLServer=trueto ensure MSSQLServer CRD installation.To configure TLS/SSL in
MSSQLServer,KubeDBusescert-managerto issue certificates. So first you have to make sure that the cluster hascert-managerinstalled. To installcert-managerin your cluster following steps here.
To keep things isolated, this tutorial uses a separate namespace called demo throughout this tutorial.
$ kubectl create ns demo
namespace/demo created
Deploy Microsoft SQL Server Availability Group Cluster with even nodes.
First, an issuer needs to be created, even if TLS is not enabled for SQL Server. The issuer will be used to configure the TLS-enabled Wal-G proxy server, which is required for the SQL Server backup and restore operations.
Create Issuer/ClusterIssuer
Now, we are going to create an example Issuer that will be used throughout the duration of this tutorial. Alternatively, you can follow this cert-manager tutorial to create your own Issuer. By following the below steps, we are going to create our desired issuer,
- Start off by generating our ca-certificates using openssl,
openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout ./ca.key -out ./ca.crt -subj "/CN=MSSQLServer/O=kubedb"
- Create a secret using the certificate files we have just generated,
$ kubectl create secret tls mssqlserver-ca --cert=ca.crt --key=ca.key --namespace=demo
secret/mssqlserver-ca created
Now, we are going to create an Issuer using the mssqlserver-ca secret that contains the ca-certificate we have just created. Below is the YAML of the Issuer CR that we are going to create,
apiVersion: cert-manager.io/v1
kind: Issuer
metadata:
name: mssqlserver-ca-issuer
namespace: demo
spec:
ca:
secretName: mssqlserver-ca
Let’s create the Issuer CR we have shown above,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2025.3.24/docs/examples/mssqlserver/ag-cluster/mssqlserver-ca-issuer.yaml
issuer.cert-manager.io/mssqlserver-ca-issuer created
Now, Let’s apply the following YAML for a Two-Node Cluster (with Arbiter):
apiVersion: kubedb.com/v1alpha2
kind: MSSQLServer
metadata:
name: ms-even-cluster
namespace: demo
spec:
version: "2022-cu16"
replicas: 2
topology:
mode: AvailabilityGroup
availabilityGroup:
databases:
- agdb1
- agdb2
tls:
issuerRef:
name: mssqlserver-ca-issuer
kind: Issuer
apiGroup: "cert-manager.io"
clientTLS: false
podTemplate:
spec:
containers:
- name: mssql
env:
- name: ACCEPT_EULA
value: "Y"
- name: MSSQL_PID
value: Evaluation
storageType: Durable
storage:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
deletionPolicy: WipeOut
After applying the YAML:
- Two Replica Pods (e.g., ms-even-cluster-0 and ms-even-cluster-1) are created as the primary and secondary SQL Server nodes.
- A separate PetSet with the name
{{ dbName }}-arbiteris automatically created for the arbiter (e.g., ms-even-cluster-arbiter). Which will eventually create a pod having name{{ dbName }}-arbiter-0. The arbiter pod runs a single container that participates only in leader election.
You might see the resources are created like this:
kubectl get ms,petset,pods,secrets,issuer,pvc -n demo
NAME VERSION STATUS AGE
mssqlserver.kubedb.com/ms-even-cluster 2022-cu16 Ready 33m
NAME AGE
petset.apps.k8s.appscode.com/ms-even-cluster 30m
petset.apps.k8s.appscode.com/ms-even-cluster-arbiter 29m
NAME READY STATUS RESTARTS AGE
pod/ms-even-cluster-0 2/2 Running 0 30m
pod/ms-even-cluster-1 2/2 Running 0 30m
pod/ms-even-cluster-arbiter-0 1/1 Running 0 29m
NAME TYPE DATA AGE
secret/ms-even-cluster-auth kubernetes.io/basic-auth 2 33m
secret/ms-even-cluster-client-cert kubernetes.io/tls 4 31m
secret/ms-even-cluster-config Opaque 1 31m
secret/ms-even-cluster-dbm-login kubernetes.io/basic-auth 1 31m
secret/ms-even-cluster-endpoint-cert kubernetes.io/tls 3 31m
secret/ms-even-cluster-master-key kubernetes.io/basic-auth 1 31m
secret/ms-even-cluster-server-cert kubernetes.io/tls 3 31m
secret/mssqlserver-ca kubernetes.io/tls 2 21d
NAME READY AGE
issuer.cert-manager.io/mssqlserver-ca-issuer True 21d
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS VOLUMEATTRIBUTESCLASS AGE
persistentvolumeclaim/data-ms-even-cluster-0 Bound pvc-cf684a28-6840-4996-aecb-ac3f9d7b0961 1Gi RWO standard <unset> 31m
persistentvolumeclaim/data-ms-even-cluster-1 Bound pvc-6d9948e8-5e12-4409-90cc-57f6429037d9 1Gi RWO standard <unset> 31m
persistentvolumeclaim/data-ms-even-cluster-arbiter-0 Bound pvc-24f3a40f-ab24-4483-87d7-3d74010aaf75 2Gi RWO standard <unset> 30m
Note: Your PVC size for data nodes might be 1000Gi, but for most of the cases you will never need more than 2Gi storage for arbiter node.
You can check arbiter pod has only one container by getting the pod yaml.
kubectl get pods -n demo ms-even-cluster-arbiter-0 -oyaml
Connect to the database cluster
KubeDB operator has created a new Secret called ms-even-cluster-auth (format: {mssqlserver-object-name}-auth) for storing the sa password for sql server. This secret contains a username key which contains the username for MSSQLServer SA and a password key which contains the password for MSSQLServer SA user.
Now, we need username and password to connect to this database from kubectl exec command. In this example ms-even-cluster-auth secret holds username and password
$ kubectl get secret -n demo ms-even-cluster-auth -o jsonpath='{.data.\username}' | base64 -d
sa
$ kubectl get secret -n demo ms-even-cluster-auth -o jsonpath='{.data.\password}' | base64 -d
AgciggjkiIaSkDs1
We can exec into the pod ms-even-cluster-0 using the following command:
$ kubectl exec -it -n demo ms-even-cluster-0 -c mssql -- bash
mssql@ms-even-cluster-0:/$ /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "AgciggjkiIaSkDs1" -No
1> select name from sys.databases
2> go
name
----------------------------------------------------------------------------------
master
tempdb
model
msdb
agdb1
agdb2
kubedb_system
(5 rows affected)
1> SELECT name FROM sys.availability_groups
2> go
name
----------------------------------------------------------------------------
msevencluster
(1 rows affected)
1> select replica_server_name from sys.availability_replicas;
2> go
replica_server_name
-------------------------------------------------------------------------------------------
ms-even-cluster-0
ms-even-cluster-1
(2 rows affected)
1> select database_name from sys.availability_databases_cluster;
2> go
database_name
------------------------------------------------------------------------------------------
agdb1
agdb2
(2 rows affected)
See the pod roles:
$ kubectl get pods -n demo --selector=app.kubernetes.io/instance=ms-even-cluster -o jsonpath='{range .items[*]}{.metadata.name}{"\t"}{.metadata.labels.kubedb\.com/role}{"\n"}{end}'
ms-even-cluster-0 primary
ms-even-cluster-1 secondary
ms-even-cluster-arbiter-0 arbiter
From the output above, we can see that ms-even-cluster-0 is the primary node. To insert data, log into the primary MSSQLServer pod. Use the following command,
$ kubectl exec -it ms-even-cluster-0 -c mssql -n demo -- bash
mssql@ms-even-cluster-0:/$ /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "AgciggjkiIaSkDs1" -No
1> SELECT database_name FROM sys.availability_databases_cluster
2> go
database_name
----------------------------------------------------------------------------------------------
agdb1
agdb2
(2 rows affected)
1> use agdb1
2> go
Changed database context to 'agdb1'.
1> CREATE TABLE Data (ID INT, NAME NVARCHAR(255), AGE INT);
2> go
1> INSERT INTO Data(ID, Name, Age) VALUES (1, 'John Doe', 25), (2, 'Jane Smith', 30);
2> go
(2 rows affected)
1> Select * from data
2> go
ID NAME AGE
--------------------------------------------------------
1 John Doe 25
2 Jane Smith 30
(2 rows affected)
1>
Now, Let’s verify that the data inserted into the primary node has been replicated to the secondary node.
Access the inserted data from secondaries
Access the secondary node (Node 2) to verify that the data is present.
$ kubectl exec -it ms-even-cluster-1 -c mssql -n demo -- bash
mssql@ms-even-cluster-1:/$ /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "AgciggjkiIaSkDs1" -No
1> SELECT database_name FROM sys.availability_databases_cluster
2> go
database_name
-------------------------------------------------
agdb1
agdb2
(2 rows affected)
1> use agdb1
2> go
Changed database context to 'agdb1'.
1> select * from data
2> go
ID NAME AGE
--------------------------------------------------------
1 John Doe 25
2 Jane Smith 30
(2 rows affected)
1>
Why do we need arbiter node?
Few of our users don’t want to run 3 node cluster as this was quite expensive for them, instead they wanted to use 2 node cluster, 1 primary and 1 replica. But due to raft implementation, there was chance of split voting. So we introduced this arbiter node so that they can still have 1 primary and 1 replica cluster and not have face potential split vote issue.
Cleaning up
kubectl delete ms -n demo ms-even-cluster
kubectl delete ns demo
Next Steps
- Learn more about Raft implementation
- Learn about backup and restore Microsoft SQL Server database using Stash.
- Want to set up Microsoft SQL Server cluster? Check how to Configure Highly Available Microsoft SQL Server Cluster
- Monitor your Microsoft SQL Server database with KubeDB using Prometheus operator.
- Detail concepts of MSSQLServer object.
- Want to hack on KubeDB? Check our contribution guidelines.