T-SQL 101: 4 What are Server Instances in SQL Server?
Before you can start to execute queries against SQL Server, you need to connect to the server. The first concept that you need to understand is what a server instance is.
You need to know is what name you should use to refer to the server. As an example, if I have a computer called SDUPROD and it has a copy of SQL server installed on it, we could often just connect to the name SDUPROD. If we do that, then what we’re doing is connecting that what’s called the default instance of SQL Server.
In early versions of SQL server, we could only have one copy of SQL server installed on the computer. In SQL Server 2000, we got the ability to have more than one copy of SQL server and these instances are quite isolated from each other, but installed on the one machine.
So that introduced the concept of a named instance. At the time, we could install 16 of these. You refer to them with the computer name followed by a backslash, then the name of the named instance.
For example, SDUPROD\Production could be one instance, and SDUPROD\Test could be another instance.
In later versions, the number of these that you could install increased to 50 but that’s way more than would usually ever make sense.
You also don’t have to have a default instance. You might only have named instances.
Local Server
If you want to connect to the local machine, there are other ways you can refer to the SQL Server instance.
. (i.e. a single period) can refer to the local machine. It makes a connection using what’s called a shared memory connection.
(local) is a synonym for the single period, and uses the same type of connection.
You can also use these with named instances. I can refer to the Production named instance on my local machine this way:
.\Production
or
(local)\Production
Finally, you can also use localhost (or even 127.0.0.1) for the server, but this requires a TCP/IP connection, even though it’s to the same machine. If you don’t have TCP/IP enabled as a server protocol, that won’t work.
Ports
Connections to SQL Server are normally made on port 1433. If you need to use another port, you put it after a comma in the server name like this:
PDSERVER, 5185
Learning T-SQL
It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
2019-02-11