Applies to: SQL Server 2019 (15.x)
Because SQL Server Big Data Clusters is on Kubernetes as containerized applications, and uses features like stateful sets and persistent storage, this infrastructure has built-in health monitoring, failure detection, and failover mechanisms that cluster components leverage to maintain service health. For increased reliability, you can also configure SQL Server master instance and/or HDFS name node and Spark shared services to deploy with additional replicas in a high availability configuration. Monitoring, failure detection, and automatic failover are managed by the big data cluster management service, namely the control service. This service is provided without user intervention â all from availability group setup, configuring database mirroring endpoints, to adding databases to the availability group or failover and upgrade coordination.
The following image represents how an availability group is deployed in a SQL Server Big Data Cluster:
Here are some of the capabilities that availability groups enable:
If the high availability settings are specified in the deployment configuration file, a single availability group named containedag is created. By default, containedag has three replicas, including primary. All CRUD operations for the availability group are managed internally, including creating the availability group or joining replicas to the availability group created. Additional availability groups cannot be created in the SQL Server master instance in a big data cluster.
All databases are automatically added to the availability group, including all user and system databases like master and msdb. This capability provides a single-system view across the availability group replicas. Additional model databases - model_replicatedmaster and model_msdb - are used to seed the replicated portion of the system databases. In addition to these databases, you will see containedag_master and containedag_msdb databases if you connect directly to the instance. The containedag databases represent the master and msdb inside the availability group.
Important
Databases that are created on the instance as a result of a workflow, like attach database, aren't automatically added to the availability group. SQL Server Big Data Clusters administrators will have to do this manually. To learn how to enable a temporary endpoint to the SQL Server instance master database, see Connect to SQL Server instance. Before the SQL Server 2019 CU2 release, databases that were created as a result of a restore statement had the same behavior, and databases had to be added manually to the contained availability group.
PolyBase configuration databases are not included in the availability group because they include instance level metadata specific to each replica.
An external endpoint is automatically provisioned for connecting to databases within the availability group. This endpoint master-svc-external plays the role of the availability group listener.
A second external endpoint is provisioned for read-only connections to the secondary replicas to scale out the read workloads.
To deploy SQL Server master in an availability group:
hadr featureYou can use either the aks-dev-test-ha or the kubeadm-prod built-in configuration profiles to start customizing your big data cluster. These profiles include the settings required for resources you can configure additional high availability. For example, below is a section in the bdc.json configuration file that is relevant for enabling availability groups for SQL Server master instance.
{
...
"spec": {
"type": "Master",
"replicas": 3,
"endpoints": [
{
"name": "Master",
"serviceType": "LoadBalancer",
"port": 31433
},
{
"name": "MasterSecondary",
"serviceType": "LoadBalancer",
"port": 31436
}
],
"settings": {
"sql": {
"hadr.enabled": "true"
}
}
}
...
}
The following steps walk through an example on how to start from aks-dev-test-ha profile and customize your big data cluster deployment configuration. For a deployment on a kubeadm cluster, similar steps would apply, but make sure you are using NodePort for the serviceType in the endpoints section.
Clone your targeted profile
azdata bdc config init --source aks-dev-test-ha --target custom-aks-ha
Optionally make any edits to the custom profile as necessary.
Start cluster deployment using the cluster configuration profile created above
azdata bdc create --config-profile custom-aks-ha --accept-eula yes
Depending on the type of workload you want to run against SQL Server master, you can connect either to the primary for read-write workloads or to the databases in the secondary replicas for read-only type of workloads. Here is an outline for each type of connection:
Connect to databases on the primary replicaFor connections to the primary replica, use sql-server-master endpoint. This endpoint is also the listener for the AG. When using this endpoint, all connections are in the context of databases within the availability group. For example, a default connection using this endpoint will result in connecting to the master database within the availability group, not the SQL Server instance master database. Run this command to find the endpoint:
azdata bdc endpoint list -e sql-server-master -o table
Description Endpoint Name Protocol
------------------------------------ ------------------- ----------------- ----------
SQL Server Master Instance Front-End 11.11.111.111,11111 sql-server-master tds
Note
Failover events can occur during a distributed query execution that is accessing data from remote data sources like HDFS or data pool. As a best practice, applications should be designed to have connection retry logic in case of disconnects caused by failover.
Connect to databases on the secondary replicasFor read-only connections to databases in secondary replicas, use the sql-server-master-readonly endpoint. This endpoint acts like a load balancer across all the secondary replicas. When using this endpoint, all connections are in the context of databases within the availability group. For example, a default connection using this endpoint will result in connecting to the master database within the availability group, not the SQL Server instance master database.
azdata bdc endpoint list -e sql-server-master-readonly -o table
Description Endpoint Name Protocol
--------------------------------------------- ------------------ -------------------------- ----------
SQL Server Master Readable Secondary Replicas 11.11.111.11,11111 sql-server-master-readonly tds
For certain operations like setting server level configurations or manually adding a database to the availability group, you must connect to the SQL Server instance. Prior to SQL Server 2019 CU2, operations like sp_configure, RESTORE DATABASE or any availability groups DDL will require this type of connection. By default, big data cluster does not include an endpoint that enables instance connection and you must expose this endpoint manually.
Important
The endpoint exposed for SQL Server instance connections only supports SQL authentication, even in clusters where Active Directory is enabled. By default, during a big data cluster deployment, sa login is disabled and a new sysadmin login is provisioned based in the values provided at deployment time for AZDATA_USERNAME and AZDATA_PASSWORD environment variables.
Important
The contained availability group DDL is exclusively self managed in BDC. Any (external user) attempt to drop the contained availability or the database mirroring endpoint is not supported and can result in unrecoverable BDC state.
Here is an example that shows how to expose this endpoint and then add the database that was created with a restore workflow to the availability group. Similar instructions for setting up a connection to the SQL Server master instance apply when you want to change server configurations with sp_configure.
Note
Starting with SQL Server 2019 CU2, databases created as result of a restore workflow are added automatically to the contained availability group.
Determine the pod that hosts the primary replica by connecting to the sql-server-master endpoint and run:
SELECT @@SERVERNAME
Expose the external endpoint by creating a new Kubernetes service
For a kubeadm cluster run below command. Replace podName with the name of the server returned at previous step, serviceName with the preferred name for the Kubernetes service created and namespaceName* with the name of your big data cluster.
kubectl -n <namespaceName> expose pod <podName> --port=1533 --name=<serviceName> --type=NodePort
For an aks cluster run, run the same command, except that the type of the service created will be LoadBalancer. For example:
kubectl -n <namespaceName> expose pod <podName> --port=1533 --name=<serviceName> --type=LoadBalancer
Here is an example of this command run against aks, where the pod hosting the primary is master-0:
kubectl -n mssql-cluster expose pod master-0 --port=1533 --name=master-sql-0 --type=LoadBalancer
Get the IP of the Kubernetes service created:
kubectl get services -n <namespaceName>
Important
As a best practice, you should cleanup by deleting the Kubernetes service created above by running this command:
kubectl delete svc master-sql-0 -n mssql-cluster
Add the database to the availability group.
For the database to be added to the AG, it has to run in the full recovery model and a log backup has to be taken. Use the IP from the Kubernetes service created above and connect to the SQL Server instance then run the T-SQL statements as shown below.
ALTER DATABASE <databaseName> SET RECOVERY FULL;
BACKUP DATABASE <databaseName> TO DISK='<filePath>'
ALTER AVAILABILITY GROUP containedag ADD DATABASE <databaseName>
The following example adds a database named sales that was restored on the instance:
ALTER DATABASE sales SET RECOVERY FULL;
BACKUP DATABASE sales TO DISK='/var/opt/mssql/data/sales.bak'
ALTER AVAILABILITY GROUP containedag ADD DATABASE sales
These are known issues and limitations with contained availability groups for SQL Server master in big data cluster:
Warning
Updating the synchronization mode to asynchronous commit for any of the replicas in the quorum commit, will result in an invalid configuration for high availability. Running in this configuration involves a data loss risk since in case of failure events affecting the primary replica, there is not an automatic failover triggered and the user must accept the risk for data loss when issuing manual failover.
sp_configure require a connection to the SQL Server instance master database, not the availability group master. You cannot use the corresponding primary endpoint. Follow the instructions to expose an endpoint and connect to the SQL Server instance and run sp_configure. You can only use SQL authentication when manually exposing the endpoint to connect to the SQL Server instance master database.CREATE DATABASE and RESTORE DATABASE like CREATE DATABASE FROM SNAPSHOT are not automatically added to the availability group. Connect to the instance and add the database to the availability group manually.RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4