sql - 命名实例和默认实例有什么区别？

Question

我创建了一个名为 <SystemName>\SQLEXPRESS 的服务器甚至启用了 TCP/IP 协议(protocol)，使其可在本地 Intranet 上搜索。但问题是，人们能够连接到名称为 <SystemName> 的服务器。 ;不是 <SystemName>\SQLEXPRESS .对 <SystemName>\SQLEXPRESS 所做的任何更改很容易在名称为 <SystemName> 的服务器上复制.但我想知道服务器名称(即系统名称)和 systemname/sqlexpress 之间的区别是什么？

Answer 1

当您仅提供服务器名称或系统名称时，将与该系统上的 SQL 服务器的默认实例建立连接。
如果要连接到该系统上安装的特定命名实例，则必须同时指定:

SystemName/InstanceName

在您包含的示例中(“systemname/sqlexpress”)， sqlexpress是该系统上的 SQL 实例的名称。
您可以在 Microsoft 上找到更多详细信息。 :

Client applications connect to an instance of Microsoft SQL Server 2005 to work with a SQL Server database. Each SQL Server instance is made up of a distinct set of services that can have unique settings. The directory structure, registry structure, and service name all reflect the specific instance name you identify during setup.

An instance is either the default, unnamed instance, or it is a named instance. When SQL Server 2005 is in installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name.

A named instance is identified by the network name of the computer plus the instance name that you specify during installation. The client must specify both the server name and the instance name when connecting.

By default, SQL Server installs in the default instance unless you specify an instance name. SQL Server Express, however, always installs in a named instance unless you force a default installation during setup.

您可以在一个系统上安装多个 SQL Server 实例。 Named 实例和 Default 实例之间没有区别，只是 Default 是默认值，并且您不需要为它显式指定名称。
正如@Brian 在评论中所指出的，可以考虑的一个区别是 SQL Server Browser Service :

If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

However, if the SQL Server Browser service is not running, the following connections do not work:

• Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

• Any component that generates or passes server\instance information that could later be used by other components to reconnect.

• Connecting to a named instance without providing the port number or pipe.

• DAC to a named instance or the default instance if not using TCP/IP port 1433.

• The OLAP redirector service.

• Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

实际上没有区别，但是.... SQL Server 的默认端口是 1433。通常，默认实例在此端口上运行；这也可以配置为在不同的端口上运行。即使您更改了端口，默认实例仍保留为默认实例。然后，您将需要明确指定端口。