Concepts
Overview
Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft, widely used for enterprise applications. By integrating SQL Server with Portworx Data Services (PDS) on Kubernetes, users can take advantage of automated management, enhanced scalability, high availability, and data resilience in a cloud-native environment.
PDS provides seamless deployment, configuration, and management of SQL Server clusters, offering features such as automated backups, data replication, and scaling. With PDS, SQL Server is deployed on Kubernetes and managed by the PDS Deployment Operator, which simplifies administration and lifecycle management.
The SQL Server service in PDS is available through a custom resource called mssql, which facilitates the management of SQL Server instances, including scaling, monitoring, and upgrades.
Users can configure enterprise Microsoft SQL to get the full functionality of Microsoft-provided SQL in PDS
For supported SQL Server versions, refer to the official SQL Server supported versions documentation.
Clustering
PDS utilizes SQL Server Always On Availability Groups (AG) for high availability and disaster recovery. AGs provide an advanced replication mechanism to ensure that database instances are synchronized, and that failover happens automatically when a primary node becomes unavailable.
Key Features of SQL Server Always On
-
Automated Failover: In the event of a failure of the primary node, one of the secondary replicas is automatically promoted to become the new primary.
-
High Availability: Continuous replication ensures that data is always available, even in the event of node or zone failures.
-
Fault Tolerance: Data is replicated to multiple availability zones or nodes, reducing the risk of downtime and data loss.
-
Read-Scale: Secondary replicas can be configured for read-only operations, allowing for horizontal scaling of read workloads.
PDS supports deploying SQL Server clusters with multiple replicas, ensuring high availability and scalability. The primary node handles both reads and writes, while secondary replicas handle read-only queries and provide failover support.
Only one Availability Group (AG) is supported in SQL Server.
Replication
Application-Level Replication
SQL Server supports two types of replication: transactional replication and Always On Availability Groups. In PDS, Always On Availability Groups are recommended as they provide better integration with Kubernetes and Portworx for enhanced fault tolerance and automated recovery.
-
Transactional Replication: Replicates changes in real-time across multiple databases.
-
Always On Availability Groups: Provides automatic failover and data replication across nodes, ensuring high availability.
Storage-Level Replication
PDS integrates with Portworx to provide storage-level replication. Each SQL Server pod uses persistent volumes (PVs) to store database data. These volumes are replicated across multiple Kubernetes worker nodes or availability zones using Portworx’s built-in replication capabilities. This setup ensures rapid recovery in case of node failure by maintaining multiple copies of the data across zones or nodes.
Configuration
SQL Server configurations can be fine-tuned via environment variables or custom configuration files. These settings allow users to adjust the server's performance, security, and other operational characteristics.
For a full list of allowed configurations and their descriptions, refer to the SQL Server configuration parameters documentation.
Scaling
PDS supports vertical scaling for SQL Server. Vertical scaling involves adjusting the CPU and memory resources allocated to the SQL Server pods dynamically. This is useful when a single SQL Server instance requires more resources to handle increased workload demands. PDS updates pods in a rolling fashion to minimize disruption.
- Vertical Scaling: Allows you to scale SQL Server pods up or down by adjusting CPU and memory resources, which is suitable for managing workloads that require more processing power.
Horizontal scaling (adding more read replicas for scaling out read traffic) is not supported for SQL Server in PDS. Scaling is limited to vertical adjustments only.
Connectivity
PDS provisions service endpoints for SQL Server instances. These endpoints provide stable IPs and DNS records to facilitate client connections. SQL Server clients can connect using below endpoints
Endpoints
| Service Name | Details |
|---|---|
sql-<name>-<namespace>-<pod-id>-vip | Endpoint for each pod |
sql-<name>-<namespace>-rw | Endpoint to master node (read/write) |
sql-<name>-<namespace>-ro | Endpoint to all replica nodes (read only) |
Each connection endpoint is available through Kubernetes service resources such as LoadBalancer or ClusterIP, and can be configured to meet specific application requirements.
Backups and Restore
PDS integrates with Portworx to handle SQL Server backup and restore operations efficiently.
Backups
Backups in PDS are managed using the SQL Server backup mechanism (full database backup). Backups can be taken on-demand or scheduled based on user-defined intervals.
Restore
Restoring from a backup involves creating a new SQL Server cluster and restoring the backup data onto it. This ensures that no data is overwritten, and the restored data can be validated independently of the original cluster.
-
Full Database Restore: Only full database restores are supported (no granular data restores at the key-value level).
-
Password Rollback:** Credentials and security configurations will be rolled back to the state at the time of the backup.
Monitoring
PDS integrates SQL Server with Prometheus for monitoring and metrics collection. Each SQL Server pod exposes a Prometheus exporter that provides metrics in the Prometheus format.
Metrics include:
-
SQL Query Performance: Query execution times, cache hit ratios, and lock statistics.
-
Replication Health: Monitoring replication lag and sync status.
-
Storage Utilization: Disk usage, memory, and CPU utilization.
For a complete list of supported SQL Server metrics, refer to the SQL Server metrics documentation.