Infrastructure at your Service

Christophe Cosme

SQL Server – PolyBase Services when listening on all IP is disabled

Introduction

You will find a lot of blogs explaining how to install the PolyBase feature with a SQL Server database instance. You will also learn how to configure it and how to use it in these blogs. But I could not find any solution for one case I was faced to.

When you have several SQL Server instances on a server you will either allocate a fix port per instance or a dedicated IP per instance, or probably both.

Of course if you define a dedicated IP address to your SQL Server instance you will automatically disable the TCP/IP Listen All property and you will set a fix port to this specific IP address.

Doing so, the best practice is to remove the fix port in the IPAll Section of the IP Address configuration.

These are the best practices that every DBA is applying considering the security policies.

What about Polybase services

When installing the PolyBase feature, you have probably noticed the 2 new services running on your server
– SQL Server PolyBase Data Mouvement
– SQL Server PolyBase Engine

Now that your standard configuration is set, you have to restart your services.
Restarting your SQL Server Service will automatically restart the services that are depending on it
– SQL Server Agent
– SQL Server PolyBase Data Mouvement
– SQL Server PolyBase Engine

When the configuration set above will apply, you will have the surprise that the 2 PolyBase services won’t start anymore.

Annoying, especially if the security policy prevent me to enable listening on all IP Addresses.

Here is the trick

In fact the fix is quite simple, I found it after trying multiple setting possibilities.

It seems that the PolyBase services are only checking the port in the IPAll Section and not the one set on the IP address you enabled.
Therefore if you let the IP address Listen All property to “No” and setting the fix port in the IPAll section to the same port set on the dedicated IP address of your SQL Server instance, the PolyBase services will be able to start again

Conlusion

With this configuration you won’t break you policies and best practices using PolyBase feature.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Christophe Cosme
Christophe Cosme

Senior Consultant