The SQL Server Browser service and multiple instances

The Browser service is a service that can be confusing if you don’t know how it works and what it does. You may have noticed that it sometimes is activated and sometimes not, even if you run the same OS and the same SQL Server version on two identical servers. The answer to this slightly confusing behavior is that the Browser service only gets installed when you create a named instance. If you created a default instance, the service will be disabled.

The functionality that the Browser provides is that it listens on UDP port 1434, and replies with the correct port for the instance requested. For example, if I connect to MyServer\MyInstance, a udp call to port 1434 is done and the Browser service replies with the correct TCP port number for my client to use when it does the “real” connection to the instance. The TCP port in use can be either fixed or dynamic depending on firewall rules on the network and other customer specific rules.

From Microsoft TechNet: Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.

Ok, so the browser will find all my instances. So what if you mix the default instance and named instances on the same server? Since you have named instances, the Browser service will be activated, and according to the text above, the Browser identifies all instances and their ports on the computer, right?

False!

The fact is that the Browser ignores your default instance totally, it only works with named instances. As long as you don’t change the default TCP port 1433, you will however not notice this, since your connection just bypasses the Browser anyway. It’s only when you change port for your default instance that this effect becomes visible. The only way to connect then, is to manually supply the correct port number in your connection string.

This has the practical effect that you really should think twice before using a default instance on a server where you have fixed ports and named instances. Let´s say you want to consolidate three SQL Server instances into one server and they need to have fixed ports because of firewall rules. You can either create one default instance and two named instances, or create all three as named instances. If you create all three as named instances, the Browser will deliver their TCP ports without problems, your connection string will simply be MyServer\MyInstance(1,2,3). If you choose the setup with one default instance which is not on port 1433, you will have to use a connection string like MyServer,1444 for your default instance, and then MyServer\MyInstance for the other two. This is not only ugly and non-consistent, but also poses a problem in the future if you want to change the fixed port numbers.

Connect Azure to your local network

There is much focus from Microsoft on Azure right now, but Azure is reachable via Internet only. A typical company on the other hand, has all its resources on an internal LAN which is shielded from Internet for security reasons. So how can you connect the two and integrate Azure with your local network? (more…)