SQL Server Browser

Since SQL Server 2000, Microsoft provided a feature of installing multiple instances on a single machine. Right now, the number of instances that can be installed on a single machine is 50. So, to connect to a desired instance, SQL Server Browser service is designed. SQL Server Browser lists all the available instances on the machine. Before knowing about the purpose and performance of SQL Server Browser, we need to know about the protocols SQL Server uses for connectivity.

Network Protocols used by SQL Server

There are three main protocols used by SQL Server for connectivity,

1. Shared Memory.
2. TCP/IP.
3. Named Pipes.

These protocols can be seen when you open SQL Server Configuration Manager --> SQL Native Client Configuration. Let's see how these protocols are useful.

Shared Memory

It is the basic protocol a client can use to get connected to SQL Server. Using this, most of the activities cannot be performed on the database. But it can be used when the remaining protocols are not configured properly. This protocol can be used when a connection is established to a server existing on the same machine. After going to the above path, if you click on Shared Memory, you will find only one option, whether this protocol is enabled or not. This means only one setting can be made to the Shared Memory Protocol.

TCP/IP

This is the default protocol used to connect to the SQL Server. A connection can be established to the server by specifying its IP address. This is facilitated by TCP/IP protocol. Now a days, all the machines come with a pre-configured TCP/IP protocol as it is the most common protocol used over the internet.

Named Pipes

Named Pipes is an interactive protocol. It shares information between server and client only when the client generates a READ request to the server. It is slower when compared to the above two protocols. This causes heavy network traffic if there are many requests to the server from clients.

Significance of SQL Server Browser

After knowing about the network protocols used by SQL Server, it is easy to know about SQL Server Browser. It is a Windows Service and one of the services installed when an SQL Server instance is installed on a machine. It runs at operating system level and only one service exists per machine for all instances.

When an instance is installed, SQL Server assigns a default TCP/IP port 1433 and a default named pipe \sql\query for Named Pipe protocol. These values can be changed by the administrator in SQL Server Configuration Manager. 

While installing an instance, SQL Server Browser comes in a DISABLED state by default. It can be later turned OFF or ON by the administrator. When there is only one instance installed on the machine, it doesn't make any difference if SQL Server Browser is running or not. The connection can be established to the available single instance though it is stopped or disabled. When there are multiple instances installed on a single machine, they cannot be run on the same default port number 1433. If they need to be connected then SQL Server Browser service must be running. Otherwise, connection can be established by giving a different port number for each instance separately and specifying it along with the IP address and Instance Name in the connection string. 

When multiple instances are installed and in the SQL Server Configuration Manager, if the TCP/IP protocol is enabled to used dynamic ports by setting the value of TCP Dynamic Ports to 0 then SQL Server Browser should be running if there are no static port number assigned to the instances. In this case SQL Server assigns a port number for each instance dynamically. This port number lies between 1024 and 5000 as per Microsoft. Port number assigned once is not the same always. It is changed if the instance is restarted. When SQL Server is started, it claims a UDP port 1434. Then SQL Server Browser searches the registry and finds all the instances available in the computer and returns the first port it got. If the Windows Firewall or any other Firewall is enabled then the UDP port 1434 should be added to its exception list.

Good Practices regarding SQL Server Browser

• If there is only one instance installed on a machine then it can be kept turned off. This reduces overhead on CPU.
• When there are multiple instances installed on the machine, it is better to assign a static port number for each instance. This requires specifying port number along with the IP address which enhances the security. In this case SQL Server Browser can be turned off.
• When the server is configured to use dynamic ports, SQL Server Browser should be running to track the usage of dynamic ports.
• It is good if SQL Server Browser is turned off and if the connection string is specified in the following format,

IP Address \ Instance Name,<Port-Number>

Different Observations Regarding SQL Server Browser with Multiple Instances

• When multiple instances are installed on a machine, they cannot be connected to the client unless SQL Server Browser is turned ON or different static port numbers are assigned.
• If TCP/IP protocol is configured with the value of TCP Dynamic Ports to 0 then SQL Server Browser must be running and it assigns any random dynamic port between 1024 and 5000. Otherwise administrator can specify desired port to be used but that port should not be used by anything in the ports list of Internet Assigned Numbers Authority (IANA).
• If a static TCP port is to be used then it should be configured by giving the port number in IP ALL section of TCP/IP protocol. When connecting to the instance, IP address, Instance Name and Port Number should be specified.
• In the above case, logging in with Windows Authentication without a trusted domain is not supported. Only SQL Server Authentication is supported.
• If there are two instances installed and one instance is assigned a static TCP port then no need of specifying a static port for the other instance. It can be connected by specifying instance name. This doesn't require SQL Server Browser to be turned ON. This is the case when the second instance is an SQLExpress. This doesn't work with other edition of SQL Server. Other editions should be configured with a static port.
• If static ports are assigned and SQL Server Browser is running then connection can be established by specifying the Instance name. No need of giving the port number along with the instance name.
• Other than SQLExpress, if there are two instances one being a default instance and the other a named instance, then connection can be established for both the instances with SQL Server Browser service turned off. The default instance's TCP/IP protocol is configured with a port number of 1433. If you add one more named instance now then the named instances cannot be connected. Only the default instance can be connected.

Limitations of SQL Server Browser

• SQL Server Browser is a Windows Service and runs at the Operating System level. This means for one computer there exists only one service. If it is enabled then it accepts all the incoming connections for all the instances. It cannot be enabled or disabled for a particular instance.
• If there is a cluster maintained then one Browser is not enough. It should be installed and enabled on every node of the cluster.

SQL Server - Know which Object exists in which Database

Sometimes, users need some features in SQL Server. Among them, one feature is to know the name of database in which the given object exists. A troubling situation is finding the name of database in which the given object exists when there are a lot of databases present in the server. For this, Microsoft provided a system Stored Procedure but it didn't provide an official documentation explaining it.

sp_MSforeachdb is an undocumented System Stored Procedure which makes our work easy in finding the database. Execute the following script in your Query Editor to find which object exists in which database. The object can be a Table, Function or a Stored Procedure.

EXEC sp_MSforeachdb

'if exists(select 1 from [?].sys.objects where name=''<Name of Object>​'')

select ''?'' as ''Database Name'' from [?].sys.objects where name=''<Name of Object>'''