Talk about SQL Server network configuration

Open SQL Server Configuration Manager, which shows the network configurations for SQL Server, what does that mean?

Figure one: MSSQLServer's protocol

These configuration options are meant to ensure that the client and database servers are properly connected for the next series of operations. Here I use an imaginary simplified example to explain the explanation.

Suppose we write a small program called Test1.exe, after running, there is process Test.exe process, database server database engine is also running, there is a corresponding process sqlservr.exe. Next we need test1 access to the database server engine, that is, process test1.exe access process sqlservr.exe, so how does the communication between processes?

1. Two processes on a single computer

　　If two processes are on the same computer, that is to say, Test1.exe and Sqlservr.exe are running on the same computer, two processes have their own independent system resources, and the two are isolated. However, it is possible to open a shared memory area, that is, two processes can be shared, and thus communicate between processes. The shared memory can only be used in the case of the client and server side in a computer, not on a computer, where the share of the Ram AH.

2. Two processes not on a single computer

(1) Two processes belong to a local area network: We know that two processes are not on a single computer, so we cannot use shared memory, so we can communicate by named pipes. Named Pipes is a protocol developed for LAN.

(2) Two processes belong to different LAN: this time the previous method is not, also use the most commonly used TCP/IP protocol, to ensure that the client and the server connection.

Summarize:

(1) For the client and server are on a computer, you can use shared memory, the fastest, of course, can also use Named pipes and TCP/IP, equivalent to the same computer, two processes do not have to go around a bend to transmit "glances", natural efficiency is lower.

(2) for the client and server in the same LAN: can use Named pipes, the fastest, of course, can also be through TCP/IP, less efficient.

(3) For clients and servers that are not on the same LAN: Only TCP/IP is used.

Write here, you already understand the agreement in figure one, you may say, simply open it all, how easy, I suggest not to do so, but open on demand, why? First: All open increases the system overhead, you think Ah, each open a protocol, need to have a while loop in the program to detect it, the natural cost more (may not be obvious). Second: Full open security reduction, imagine, the client will be connected through a door and the server, why also open some other door ah.

Next, we think that the client program to communicate with the database engine, there can be many ways: Shared memory,named pipes,tcp/ip, then when all is available, which protocol does it have to have a sequence? It does have a sequence, and we can set it up by the client protocol, such as:

The default order of four Protocols is listed (via protocol is not used), and the shared memory is the first because it is the fastest and can be changed in order by right-clicking. Such as

All right, let's talk about it. The settings in aliases can be set by creating a new alias that uses a specific protocol when the client connects to the database server, such as the client program Test.exe on the same computer and the database server program sqlservr.exe, by default, using shared Memory connection, but also the fastest, but if the alias is set to "Connect database server program with TCP/IP protocol", then the client and database server can only communicate through TCP/IP, although slower.

You should be more aware of what is in SQL Server Configuration Manager.

　　

Talk about SQL Server network configuration