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.
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
Now that we have some credentials—and, of course, a matching login and user on
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.
Well, what if we just flip around the backslash and make it a forward slash?
Interestingly, the error message indicates that this is on external table creation, though we haven’t gotten that far yet.
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.
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.
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
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.