Named Instances

Microsoft SQL Server (MS-SQL) has a feature called named instances. This feature allows you to run multiple databases on the same host (or clustered hosts) with separate settings. Each db (database) instance runs on its own port. However, when using MS-SQL–aware clients running on Windows, you can connect by name instead of by port number. For instance, if your hostname is dbserver and your instance is named proddb, you would connect using the hostname dbserver\proddb.

What Happens on the Back End

• Your client contacts the host, named dbserver in this example, on the default MS-SQL port (1433).
• MS-SQL responds with the named instance's port. The client then connects to that port.

Looker (and many other applications that are not Microsoft-centric) cannot find the port number of a named instance in this way. Once Looker connects to a port, it expects to be able to run queries and will not connect with any other port.

How to Connect an MS-SQL Named Instance with Looker

There is no standard port that named instances run on, as MS-SQL chooses a random port at startup. For Looker to connect to an MS-SQL named instance, you will need to find the port the named instance is running on:

• This SQL And Me article, SQL Server — Finding TCP Port Number SQL Instance Is Listening On, details the method for finding the port. Although the article is from 2012, the procedure is similar across the various versions of MS-SQL.
• Once you've found the port, you can enter the port name in the Host:Port field in Looker's database connection configuration. For example, if the port is 61499 and the host is named dbserver, the connection in Looker will look like this:

    host: dbserver
    port: 61499

NOTE: The database hostname will not take the form dbserver\proddb, as it would if we were connecting by the instance name.

• Lastly, it is important to note that MS-SQL may choose a different port for the named instance whenever the server is rebooted. That could require MS-SQL to change firewall rules and reconfigure Looker to use the new port. To avoid this, you can choose to assign a static port to your named instance, as detailed in the following blog post, How to Assign a Static Port to a SQL Server Named Instance — and Avoid a Common Pitfall.