Skip to content

configure-sql-server-master-instance.md

Latest commit

 

History

History
71 lines (50 loc) · 2.97 KB

configure-sql-server-master-instance.md

File metadata and controls

71 lines (50 loc) · 2.97 KB
title Configure SQL Server master instance
description Configure the master instance of a SQL Server Big Data Cluster
author HugoMSFT
ms.author hudequei
ms.reviewer wiassaf
ms.date 12/04/2023
ms.service sql
ms.subservice big-data-cluster
ms.topic how-to
ms.custom
kr2b-contr-experiment

Configure the master instance of [!INCLUDE big-data-clusters-2019]

[!INCLUDE SQL Server 2019]

[!INCLUDE big-data-clusters-banner-retirement]

Learn how to configure the master instance of [!INCLUDE big-data-clusters-2019].

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

[!div class="nextstepaction"] [Get started with [!INCLUDEbig-data-clusters-2019] deployment](deploy-get-started.md)