PolyBase and Named Instances

One of the built-in connectors for PolyBase in SQL Server 2022, as well as SQL Server 2019, is to connect one SQL Server instance to another. In this post, we’ll look at how to connect to a named instance of SQL Server.

And then There Were Two

We have two SQL Server instances running on the same machine. Before we get started, I do want to point out one thing: PolyBase can only work on one instance for a given server (physical machine or virtual machine) because the PolyBase engine and data movement services are system-level services. This means you cannot have PolyBase installed on your main instance as well as your named instance.

In our case, that’s okay: I have PolyBase on the main instance, WINDEV2208EVAL and another SQL Server instance named WINDEV2208EVAL\SOMEWHERE as a named instance. The named instance has a database called ForensicAccounting, whose goodies I’d like to get ahold of.

There are goodies somewhere.

Will Work for Creds

If we’re going to connect to a remote SQL Server instance, we’re going to need credentials. Specifically, we’ll need SQL authentication credentials, as Windows authentication will not work for us here: the database scoped credential we create must be SQL authenticated.

USE [Scratch]
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<SomeSecureKey>>';
GO
IF NOT EXISTS
(
	SELECT 1
	FROM sys.database_scoped_credentials dsc
	WHERE
		dsc.name = N'DesktopCredentials'
)
BEGIN
	CREATE DATABASE SCOPED CREDENTIAL DesktopCredentials
	WITH IDENTITY = 'PolyBaseUser', Secret = '<<Some Password>>';
END
GO

First Try

Now that we have some credentials—and, of course, a matching login and user on \SOMEWHERE named PolyBaseUser with the secret as a password—we can create an external data source. The question is, how can we connect to it? If we try to specify the named instance the way SQL Server does, we get an error as URIs aren’t allowed to have backslashes in them.

External access operation failed because the location URI is invalid. Revise the URI and try again.

Second Try

Well, what if we just flip around the backslash and make it a forward slash?

105080;CREATE EXTERNAL TABLE failed because the URI contains an unsupported path, fragment, user info, or query. Revise the URI and try again.

Interestingly, the error message indicates that this is on external table creation, though we haven’t gotten that far yet.

Connection Options

It turns out that you can connect to a named instance but you need to specify it in the CONNECTION_OPTIONS parameter like so:

CREATE EXTERNAL DATA SOURCE Desktop WITH
(
	LOCATION = 'sqlserver://WINDEV2208EVAL',
	CONNECTION_OPTIONS = 'Server=WINDEV2208EVAL\SOMEWHERE',
	PUSHDOWN = ON,
	CREDENTIAL = DesktopCredentials
);

In this case, we are actually connecting to the \SOMEWHERE named instance despite the location name. As it turns out, once you specify CONNECTION_OPTIONS, the only important thing in LOCATION is that it use the protocol sqlserver://. From there, the rest will just be a label when querying external work. This means that if you do go this route, you’ll probably want to make the location unique, maybe something like LOCATION = 'sqlserver://WINDEV2208EVAL_SOMEWHERE', as that is still a valid URL and would be unique per instance.

Port Number

Another alternative becomes available if you hard-code the ports on your named instances. For example, let’s say that we use the SQL Server configuration manager to set the TCP port of WINDEV2208EVAL\SOMEWHERE to run on port 29999.

Setting a fixed TCP port.

In that case, we can reference the SQL Server instance by hostname and port.

CREATE EXTERNAL DATA SOURCE Desktop WITH
(
	LOCATION = 'sqlserver://WINDEV2208EVAL:29999',
	PUSHDOWN = ON,
	CREDENTIAL = DesktopCredentials
);

This is guaranteed to be unique because we have the port number in the LOCATION parameter.

Conclusion

There are two ways to reference named instances in SQL Server using PolyBase. One method involves specifying the port number. The other method uses the CONNECTION_OPTIONS parameter, in which you specify the actual SQL Server instance.

Advertisement