sodcalculator

10 Tech How-To

Default vs Named Instance in SQL Server

Guide 10-50753 · Updated · Step by step

This article explains the difference between a default instance and a named instance in Microsoft SQL Server. You will learn how each is installed, how they are addressed, and how to choose between them for your environment.

What Is an Instance?

In SQL Server, an instance is a single installation of the database engine that runs as its own service with its own set of system databases, configuration settings, and security boundaries. A single physical or virtual server can host multiple instances, each operating independently. This allows you to run different versions of SQL Server side by side, isolate workloads, or give separate teams their own environment without needing separate hardware.

Default Instance

The default instance is the first and only instance that can be installed without a name. It is identified by the server’s network name alone. When you connect to a default instance, you simply specify the server name (e.g., MY-SERVER) or its IP address. The default instance listens on the standard TCP port 1433, which is the well-known port for SQL Server. This makes connection strings simpler and often avoids the need to specify a port number.

Only one default instance can exist on a given server. If you attempt to install another default instance, the installer will block it because the name is already taken. The default instance’s service name is MSSQLSERVER on Windows, and its directory path is typically C:\Program Files\Microsoft SQL Server\MSSQL{version}.MSSQLSERVER\MSSQL.

Named Instance

A named instance is any instance that is given a unique name during installation. It is addressed using the server name followed by a backslash and the instance name, for example MY-SERVER\SALES. Each named instance runs as its own service with a name like MSSQL$SALES and uses a dynamic TCP port by default (usually in the range 49152–65535), unless a fixed port is manually configured. Because the port is dynamic, clients must either use the SQL Server Browser service to discover the port or specify the port number explicitly in the connection string.

You can install multiple named instances on the same server, as long as each name is unique. Named instances allow you to run different versions (e.g., SQL Server 2019 and 2022) side by side, or to separate development, testing, and production workloads on the same hardware.

Key Differences: Addressing, Ports, and Services

The most practical differences between the two types are how you connect and how the services are managed.

  • Connection string: Default instance uses just the server name (Server=my-server); named instance uses Server=my-server\instance.
  • TCP port: Default instance uses fixed port 1433; named instances use dynamic ports unless you set a static one.
  • Service name: Default instance service is MSSQLSERVER; named instance service is MSSQL$InstanceName.
  • Number allowed per server: Only one default instance; up to 50 named instances (on a standalone server, depending on edition).
  • SQL Server Browser service: Not needed for the default instance (port is known), but essential for named instances to resolve dynamic ports.

These differences matter when you configure firewalls, write connection strings in applications, or manage services in Windows.

How to Choose: Default vs Named

Choosing between a default and a named instance depends on your specific needs.

  • Use a default instance when: you have a single SQL Server on the machine, you want the simplest connection string, and you don’t need to isolate multiple environments.
  • Use a named instance when: you need multiple instances on one server (for isolation, version coexistence, or consolidation), or when you want to avoid the default port for security reasons (though this is not a strong security measure).
  • Consider the application: Some legacy applications expect a default instance and cannot easily be reconfigured. Always check your application’s documentation before choosing.

In practice, many production environments use a single default instance for simplicity, while development and test environments often use named instances to allow multiple versions and configurations on one machine.

Common Issues and Misconceptions

One common misconception is that a named instance is slower than a default instance. There is no performance difference; the engine is identical. The only difference is the extra step of resolving the port via the SQL Server Browser service, which adds negligible overhead.

Another issue is firewall configuration. For a default instance, you only need to open port 1433. For a named instance with dynamic ports, you must either enable the SQL Server Browser service (UDP 1434) or set a static port and open that specific port. Many connection problems are due to the Browser service being disabled or blocked.

Also, note that you cannot change a default instance to a named instance (or vice versa) without uninstalling and reinstalling. The instance type is fixed at installation time.

Frequently Asked Questions

Can I have both a default and a named instance on the same server?

Yes, you can. A server can host one default instance and multiple named instances simultaneously. They run independently and use different ports.

How do I find out if my SQL Server is a default or named instance?

You can check the SQL Server service name in Windows Services (look for MSSQLSERVER for default, MSSQL$InstanceName for named). Or run SELECT @@SERVERNAME in SQL Server Management Studio – if it returns just the server name, it’s a default instance; if it includes a backslash, it’s a named instance.

What is the SQL Server Browser service and why is it needed for named instances?

The SQL Server Browser service listens on UDP port 1434 and responds to client requests by providing the TCP port number for each named instance. Without it, clients cannot discover the dynamic port and must know it in advance.

Can I connect to a named instance without the SQL Server Browser service?

Yes, if you specify the port number in the connection string, for example Server=my-server\sales, 50000. This bypasses the need for the Browser service.

Is a named instance more secure than a default instance?

Not inherently. The default port 1433 is well-known, but security should rely on strong authentication, encryption, and firewall rules, not on obscurity. A named instance with a dynamic port may be slightly less discoverable, but it is not a security measure.

Can I rename an instance after installation?

No, you cannot rename an instance. You would need to uninstall and reinstall with the desired name. You can, however, change the server name that the instance responds to using sp_dropserver and sp_addserver, but the instance name itself remains fixed.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →