A. Default Instance and Named Instances
When installing MsSql server, the installer gives us two options:
- Default instance
- Named instance
A1. Default Instance
If we choose default instance the installation does not allow us to give a name to the MsSql server instance. The MsSql server instance gets the name of the machine. If, for example, machine name is AX-NAV, then the default instance will be called AX-NAV too.
But if we go to Services we will find the MsSql default instance as SQL Server (MSSQLSERVER).
By default, the default instance listens to port 1433. We may set that port to any other available port. We’ll see how.
A client application is connected to the default instance by using either the default instance name or the IP Address of the hosting machine. For example, here is a .Net connection string
Data Source=AX-NAV; User ID=sa; Password=psw; Initial Catalog=NAVDB;
Instead of the default port 1433 we may use any other available port, say port 50000. Then the connection string becomes
Data Source= AX-NAV,50000; User ID=sa; Password=psw; Initial Catalog=NAVDB;
The same Domain,Portal scheme is used to connect from the MsSql Server Management Studio.
In all cases, we can use the IP Address of the machine hosting MsSql server instead of the name of the default instance.
Data Source=10.3.3.4; User ID=sa; Password=psw; Initial Catalog=NAVDB;
Data Source=10.3.3.4,50000; User ID=sa; Password=psw; Initial Catalog=NAVDB;
A2. Named Instance
If we choose named instance during installation, then the installation forces us to give a name to the MsSql server instance. For example, NAV.
However, the full name of a named instance is ComputerName\InstanceName, for example AX-NAV\NAV and only with this string we can connect to a named instance.
But if we go to Services we will find the named MsSql instance as SQL Server (InstanceName), for example SQL Server (NAV).
CRITICAL: A named instance by default listens to a dynamic port that it chooses when the named instance starts. But if we use a remote connection through a firewall, then we have to use a static port obviously and not a dynamic port. Check this document.
If we set a static port for a named instance, as is the right thing to do, then our connection string becomes
Data Source=AX-NAV\NAV,55555; User ID=sa; Password=psw; Initial Catalog=NAVDB;
Data Source=10.3.3.4\NAV,55555; User ID=sa; Password=psw; Initial Catalog=NAVDB;
When we are on the same computer as the one running a named instance then instead of the computer name we can use the .\ For example .\NAV
B. Multiple Instances
It often happens that we have installed a default instance and a need arises to install SqlExpress too. There are also several programs that install SqlExpress for their own purposes.
Such an installation of SqlExpress is a named instance.
Usually its full name is ComputerName\SqlExpress. For example AX-NAV\SQLEXPRESS or if we are in the same machine becomes .\SQLEXPRESS
If we go to Services we will find that named instance as SQL Server (SQLEXPRESS).
In addition to the default MsSql server instance we may also need to install and a named instance too, not SqlExpress, but a “regular” MsSql server.
In this case, as we have seen above, we need to adjust properly the ports for the two or more servers we have installed.
C. MsSql Remote Connections
If we are to have remote connections to a server we need to set some things up.
With Management Studio connected to a server, go to Properties of the server (right-click on the server name and then Properties) go to Connections and check “Allow remote connections to this server”.
Also for any static port we set up for a server, we need to define a rule in that computer’s Firewall, making an Inbound Rule that gives access to that port.
D. MsSql Server Protocols and Ports Configuration
If we have multiple instances then the default instance is left at 1433 (static port) and that’s a right thing to do.
We said that by default a named instance uses a dynamic port, but this does NOT serve us in the case of remote connections. We need to set up a static port.
Protocols and ports are configured using the Sql Server Configuration Manager.
But there is a chance, especially when the very first installed MsSql instance is of some ancient version, running the Sql Server Configuration Manager found in Start menu, to not show us all installed instances. In this case, we have two solutions.
Either run Computer Management (Server Manager > Tools > Computer Management) and then go to Services and Applications > SQL Server Configuration, or go to C:\Windows\SysWOW64 path and click on SQLServerManagerXX.msc where XX the larger number we will find.
Microsoft in the document https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager gives the following table
SQL Server 2017 C:\Windows\SysWOW64\SQLServerManager14.msc
SQL Server 2016 C:\Windows\SysWOW64\SQL ServerManager13.msc
SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc
In Configuration Manager, click on SQL Server Services. There we will see all MsSql related services, whether running or not, even the Sql Server Agent, and of course all the instances we have installed.
In Configuration Manager, click on SQL Server Network Configuration. We’ll see all the instances we’ve installed and we can adjust their network protocols. For each installed instance there will be the Protocols for XXXXX option, where XXXX is the instance name.
As long as we want remote connections then we have to be sure that TCP/IP is Enabled in all instances. Take special care of the instances of SqlExpress. TCP/IP may is not enabled because SqlExpress is often intended for local use.
There are two other available protocols, namely Shared Memory and Named Pipes, used in local connections.
Double clicking on TCP/IP (or right-click and Properties) opens the dialog box of the TCP/IP properties. We make sure TCP/IP is Enabled.
The second page of the Properties dialog box is named IP Addresses and is where the setting of dynamic and static ports can be made.
Go to the bottom of the page where it says IPAll and there you’ll find two options:
- TCP Dynamic Ports
- TCP Port
If we are setting the default instance, we make sure that
- TCP Dynamic Ports = blank
- TCP Port = 1433
If we are setting a named instance, we make sure that
- TCP Dynamic Ports = 0
- TCP Port = the port we want, e.g. 55555
That’s all we need to do to get everything right. We do not forget to open the ports in the Firewall and check “Allow Remote Connections” on each server in Management Studio.
E. MsSql Server Dynamic Ports
When in a named instance we do not set a static port, as we did above, then by default it will use Dynamic Ports. That is, when the named instance starts running it makes a random port selection.
When we work this way (WARNING: It’s not the right one) then we alse need to run the SQL Browser service. Otherwise remote client applications will not be able to connect to the server.
The SQL Browser service informs remote client programs requesting to connect to a named instance based on its Instance Name, what is the port that has chosen by that named instance.
For this scenario to work, we need to set up a few more things. Open the 1434 UDP port with Inbound Rule in the Firewall because that is the port on which the SQL Browser service listens. And then create another Inbound Rule, a custom one this time, which will allow access to that named instance (in its Service by the Services).
The steps are Inbound Rules > New Rule > Custom > Program > All Programs > Customize ... > Apply to this service we choose Service, e.g. SQL Server (NAV) then press Next, give name to the rule and ok.
Now a client program can connect remotely to the named instance without even setting the port. That is
Data Source=AX-NAV\NAV; User ID=sa; Password=psw; Initial Catalog=NAVDB;
More on that here: https://www.sqlshack.com/how-to-connect-to-a-remote-sql-server/
CRITICAL: If a named instance is not configured with either static or dynamic ports through the SQL Browser service, then client programs will be unable to connect using remote connection.
F. MsSql Server Aliases (Aliases)
A MsSql Server Alias is a name that can be used from client programs to connect to an instance, default or named instance.
Aliases are set in a machine where a client application runs. It’s a client-side feature. To define an Alias we have to define four things
- Alias Name eg SqlSrv
- Port eg 1433
- Protocol eg TCP / IP
- Server eg NewSqlSrv
Why use aliases? Here is a reason: Let’s say there are already some programs that they know how to connect to SqlSrv (computer name and default instance). But we want to replace it with a new server called NewSqlSrv. With an Alias we can do it.
Another scenario
- Alias Name eg NAV
- Port eg 51433
- Protocol eg TCP/IP
- Server eg AX-NAV\NAV
With the above, we hide a named instance behind the NAV alias to all client programs.