7 Configuring SQL Server network traffic

The

SQL Server2008 is a client-server application that effectively provides
exchange of data and instructions on multiple network connections. Understanding network connections and how to configure them is a large part of the work of the database administrator.
Microsoft eases the workload of data
Library administrators by reducing the number of protocols supported by SQL Server2008 to several of the most commonly used protocols. At the same time, however, the work of the database administrator becomes more complex because multiple connection types can be configured with each protocol through the
endpoint server object. This chapter discusses the different endpoints that can be configured, and the protocol configuration that the end
Point relies on, as well as a brief description of the client configuration that can be configured with SQL Server2008
7.1 SQL Server2008 network protocol
SQL SERVER2008 supports 4 kinds of protocols
Shared Memory
TCP/IP
Named Pipes
Virtual Interface Adapter (VIA)
By default, most versions of SQL The server-enabled network protocol is the TCPP protocol and the Shared Memory
protocol. The developer and enterprise evaluation versions are configured to disable all
protocols except the Shared memory protocol during installation, but these protocols can be enabled if required. If a protocol is not enabled, SQL Server does not
Listen for endpoints that are configured to use the protocol. This can reduce the attack surface of SQL Server.
SQL Server Configuration Manager is used to configure the server protocol. With this tool, you can enable, disable, and provision
each supported protocol as needed. The configuration options for network protocols are not straightforward, so it is necessary to interpret them.
When you open SQL Server Configuration Manager, a node is displayed that configures the SQL Server service, the SQL Server
Network protocol, and the SQL Native client protocol. To configure the server protocol, you can expand the SQL Server2008
Network Configuration node, and then select the instance that you want to configure. The right pane shows all 4 supported protocols and their
state. To display configurable properties for any protocol, you can double-click the protocol, or right-click the protocol and select the Properties command to open the
corresponding Properties window. The
7.1.1 Shared Memory protocol
Shared memory protocol can only be used by local connections because it is an inbound

for intra-server communication

Space for shared memory. It has only one configurable property: Enabled. The Enabled property can be set to Yes
Or no, the corresponding result is that the status is enabled or disabled. Applications that are running locally on SQL Server or
Tasks can take advantage of the Shared memory protocol
7.1.2 Named Pipes protocol
The Named pipes protocol uses interprocess communication (nter-process COMMUNICATION,IPC) channels for efficient
Server internal communication and local area network (LAN communication. The Named pipes protocol in SQL Server2008 has been enhanced,
For example, encrypted traffic is now supported, but Named pipes requires too much overhead because it is connected over a network or firewall.
It also requires the opening of additional ports (45), so it is generally best to disable the Named pipes protocol. However, there is a promise
Multiple applications (especially older applications) need to use the Named pipes protocol because they are around
NetBIOS or other LAN-based protocol design. Named pipes provides easy access to remote in a single security domain
Procedure Calls (Remote Procedure calls,rpc), which is useful for these applications. If you need support
These applications, and SQL Server is not exposed to external traffic, then enable the Named pipes protocol and
The risk of the corresponding endpoint is minimal
Named Pipes has two configurable properties: Enabled and default pipe. The Enabled property and the Shared Memory
The protocol works the same way. The default pipe specifies the inter-process pipeline that SQL Server will listen on. The default pipe is
\pipe\mssql<instance Name>sqliquery
7.1.3 TCP/IP
The TCP/P protocol is the primary and preferred protocol for most SQL Server installations. Can be obtained through the Tcpp
Configure it on the two tabs on the Properties window: Protocol tab and IP Address tab, 7-1
is shown.

The Protocols tab has the following 3 configurable properties:
Enabled-the property is the same as the "enabled" property of other protocols.
Keep Alive-Specifies how many milliseconds SQL Server sends a "keep-alive" number
Check to see if the idle connection is still valid. The default value is 30000 milliseconds
Listen All-Specifies whether SQL Server listens for all IP addresses configured on the server.
7-1, the "P Address" tab contains configuration settings for each configured IP address on the server, plus
There is a section where you can configure all the P addresses. In addition to IPV4, SQL Server2008 also supports IPV6 addresses.
A detailed explanation of the pros and cons of IPv6 is beyond the scope of this book, but you know that you can often see a single physical
The adapter has multiple IPV6 addresses associated with it. Unlike IPV4 (which allows the use of a variable-length netmask), the network of IPV6 addresses
Use fixed-length fields (64 or half of the available addresses for each section). According to the IPV6 protocol
Definition, a single host may belong to one or more networks. For example, a host computer will use a IPV6 address in the
Identify yourself in a non-routed network segment, with another IPV6 address identified on a LAN that may include multiple routes
Uniquely identify yourself on the Internet with a third IP address. The interesting thing about IPV6 is that in all cases, the ground
The host portion of the address (2nd) is the same (usually a variant of the hardware or the MAC address of the adapter), and the network
The first 64 bits) are different and are used to identify the network to which the host is connected.
If you include a loopback address (127.00.1), SQL Server will have multiple IPV4 addresses. All the P addresses are
Managed by the IP Address tab of the Tcpp Properties window.
The description of the IP address setting is shown in table 7-1.

7.1.4 Virtual Interface Adapter (VIA)
As in previous versions, SQL Server2008 also supports virtual Interface Adapter (Dummy interface Adapter)
Protocol, which is used in conjunction with supported hardware and network configurations. Virtual Interface Architecture (Irtual Interface
Developed jointly by COMPAQ (now HP), Intel, and Microsoft, the architecture is designed to reduce transmission
A high-performance protocol with a large overhead of the network protocol, which is under the context of the user mode and not the kernel-mode context
Run. The Ⅵia network client is connected to the system area network. Do not place the system area network
is confused with the storage Area network (Storage), although the abbreviations of both

SQL Server2008dba Getting Started Classic
SQL Native Client Configuration
The SQL Native client supports the same 4 server-side protocols, and is also supported by SQL Server Configuration Manager
Enable, disable, or configure these protocols. In addition to configuring client protocols, you can set the binding order of the protocols. Party
method is to expand the SQL Native Client Configuration node and select client protocols. In the right pane, right-click an item
Protocol and select the "Order" option to set the order of all enabled protocols, as shown in 7-2.
As shown in 72, if the Shared memory protocol is enabled, it is always the first protocol in the binding order.
The protocol order cannot be set manually.
You can use the SQL Native Client Configuration node to create aliases. Aliases are useful even if the name of the server
The name in the connection string to the client does not match, and it can also cause the client to connect to the server. For example, it can
Can use a standby server to replace a failed server that serves applications that contain hard-coded connection strings.
If there is no alias, then you need to change the application's connection string, or change the server name. By specifying
Individual names, you can direct the client's request to the server without changing the server name. Aliases can also be used to
Replace the complex named instance name.

Figure 7-2 Setting the order in which protocols are enabled
Figure 7-3 shows the alias Yodahome for the named instance Aughteightdagobah configuration. To open
Alias-New dialog box, you can right-click the aliases node and select the new Alias command. After you create the alias,
You can create a new connection by referencing the alias instead of the instance name.

Figure 7-3 Configuring the alias Yodahome

SQL Server Endpoint
When SQL Server2005 was released, people were pretty confused and panicked about the SQL Server endpoint. Moreover, I
It feels like a lot of people around me don't know exactly what the endpoint is, and why I use the endpoint. The so-called endpoint is just net
Or more accurately, it is the name of the entity on one end of the transport layer connection. In previous versions
SQL Server, the default network endpoint for SQL Server Resolution service is UDP port 1434, and the SQL
The default network endpoint for the server default instance is TCP port 1433. can give default and/or any other named instance
Configure an additional TCP port. Most database administrators do not think of a server listener as an endpoint, but it does
is the endpoint and will still be the endpoint. SQL Server2008 the Connection object as the endpoint, allowing listening for different ports,
Use different transport protocols for different services.
SQL Server provides 4 different types of endpoints:
TSQL (Default and TCP
Database Mirroring
Soap
Service Broker
Each endpoint provides different capabilities that can be configured independently to control the database engine and associated services
Access.
7.3.1 Default TSQL Endpoint
The TSQL endpoint is essentially the same as the standard endpoint in previous versions of SQL Server. During the installation process, you will
To create a 5 TSQL endpoint:
TSQL Default TCP
TSQL Default Ⅵia
TSQL Named Pipes
TSQL Local Computer
· Dedicated Administrator Connection (DAC, dedicated Administrator connection)
The TSQL endpoint provides a connection service for 4 supported protocols (TCP, Ⅵa, Named pipes, and Shared Memory)
Works These protocols correspond to the default TCP, default Ⅵa, Named pipes, and local computer endpoints. The 5th one to support
The endpoint that holds the DAC listens on a dedicated TCP port that is configured to support an administrative connection at startup. With
The port is logged in the current SQL Server log file (the 10th chapter describes the SQL Server log file).
Regardless of the conditions of the network protocol, the TSQL endpoint has two states: start and stop. If the network is enabled
Protocol and starts the endpoint, SQL Server listens for and accepts connections on that endpoint. The stopped endpoint will still listen,
However, the new connection is rejected voluntarily. If the corresponding protocol is disabled, the TSQL endpoint will not listen and will not respond to the customer
-Side requests.
The TSOL endpoint is also referred to as the tabular data stream (TDS, tabular data stream) endpoint. TDS by Sybase
Created in 198 to support Sybase's newly created relational database engine in collaboration with Sybase, Microsoft
Inherited the agreement and made a number of changes to make it more efficient and more reliable. It is still in the TCP,

7 Configuring SQL Server network traffic