Friday , December 2 2022

SQL Managed Instance Configuration

In this article, I will be answering the question of how we can configure the Azure Managed Instance that we created in the second part.

As the first step, we learn the IP address we need to connect to the Azure Arc Featured Managed Instance, which we have installed before, with the following code block.

Picture1: IP address

We learned the IP address, we connect to the Azure SQL Managed Instance, that is, Azure Managed Instance, via SQL Server Management Studio with the username and password we gave during the installation phase in our previous article.

Picture2: We are connecting with SSMS.

As you can see in Picture 2, the SQL Server Agent service is turned off. If you want to activate it, you need this article.

In fact, since Azure SQL Managed Instance with Azure Arc runs on SQL Server Linux for us, if you have done the configuration operations in linux environments before, you can apply a similar one here.

In this context, we can also answer the question of why SQL Server works on Linux. We wouldn’t be able to use these Azure Arc-Enabled data services today if they weren’t running on Linux.

We will be using the mssql-conf utility that we use for configuring the SQL Server Linux side here as well. I know this mssql-conf file is located at /var/opt/mssql location because SQL Server is obvious by Linux.

We need to learn the name of the running Pod and container before going through the configuration processes. We can use the following code block to learn this information.

Picture3: We find the Pod and Container name.

What you see in Picture 3 is the Managed Instance (MI) name information that I marked with red. The 0 next to it is the number of pod information.

Namely, if I had created more than one copy/replica of MI, I could also see 1 or 2 values here. In fact, let me address this issue in the next article and answer the question of how we can make Azure Managed Instance High Availability.

We learned the pod name, now let’s learn the names of the containers in this pod. I can also use the following code block for this process.

Picture4: Containers in pods

As you can see, there are 3 containers available.

Fluentbit : A logging tool
collectd : Metric collection tool
arc-sqlmi : and the Managed Instance we created

Since we have gained so much information, if we want to do a pod-level operation, we do our operations using dmcsqlmi-0 and if we want to do container-level operations, we use arc-sqlmi.

So, let’s connect to the container with the code below and start our bash operations.

Picture5

Now let’s switch to the folder where we will do our operations.

Picture6

Let’s examine the configuration file.

Picture7

As you can see, there is a record in our default configuration file that only shows that telemetry is turned off. I am uploading the following new configuration file via the Azure portal.

I am copying the file named mssql-custom.conf where the code block above is registered in the Azure portal into the container using the following line of code.

Picture8

Now that the file has been moved, we can restart it for the action to be active. We will be doing this through bash.

After the restart, let’s check the status of the SQL Server Agent from SQL Server Management Studio.

Picture9

As you can see, we ran the SQL Server Agent service by making the desired configuration on the Azure SQL Managed Instance with Azure Arc.

You can perform many configuration operations via the mssql-custom.conf file. You can perform different configuration operations by editing this file.

For example, you can change the locations of the database files, the information about where to take the backups you have taken, and you can change the Maximum Memory setting of SQL Server.

About Çağlar Özenç

Leave a Reply

Your email address will not be published. Required fields are marked *