Configure the master instance of SQL Server Big Data Clusters

Applies to: SQL Server 2019 (15.x)

Important

The Microsoft SQL Server 2019 Big Data Clusters add-on will be retired. Support for SQL Server 2019 Big Data Clusters will end on February 28, 2025. All existing users of SQL Server 2019 with Software Assurance will be fully supported on the platform and the software will continue to be maintained through SQL Server cumulative updates until that time. For more information, see the announcement blog post and Big data options on the Microsoft SQL Server platform.

Learn how to configure the master instance of SQL Server 2019 Big Data Clusters.

You can configure the master instance of SQL Server 2019 Big Data Clusters. However, server configuration settings can't be configured at deployment time. Beginning in Cumulative Update 9, Big Data Cluster includes a configuration management feature. This enables administrators to alter or tune various parts of the Big Data Cluster post-deployment. It also gives them deeper insights into configurations running in their big data cluster.

This article describes a temporary workaround for configuring SQL Server master instance settings, including: SQL Server edition, enable or disable SQL Server Agent, enable specific trace flags, enable/disable customer feedback, or domainmapping.

To change master instance settings, follow these steps:

1. Create a custom mssql-custom.conf file that includes targeted settings. The following example enables SQL Agent, telemetry, sets a PID for Enterprise Edition, and enables trace flag 1204:

   [sqlagent]
   enabled=true
   
   [telemetry]
   customerfeedback=true
   userRequestedLocalAuditDirectory = /tmp/audit
   
   [DEFAULT]
   pid = Enterprise
   
   [traceflag]
   traceflag0 = 1204
   

2. Copy mssql-custom.conf file to /var/opt/mssql in the mssql-server container in the master-0 pod. Replace <namespaceName> with the big data cluster name.

   kubectl cp mssql-custom.conf master-0:/var/opt/mssql/mssql-custom.conf -c mssql-server -n <namespaceName>
   

3. Restart the SQL Server instance. Replace <namespaceName> with the big data cluster name.

   kubectl exec -it master-0  -c mssql-server -n <namespaceName> -- /bin/bash
   supervisorctl restart mssql-server
   exit
   

Important

If SQL Server master instance is in an availability groups configuration, copy the mssql-custom.conf file in all the master pods. Each restart will cause a failover, so you must make sure you're scheduling this activity during downtime.

Known limitations

• The steps above require Kubernetes cluster admin permissions
• You can't change the server collation for the SQL Server master instance of the big data cluster post deployment.

Next step

Get started with SQL Server Big Data Clusters deployment