11. Microsoft SQL server network settings
Overview of Network Services
SQL Server application interface
Network Connection Library
Network components and SQL server performance
After you install Microsoft SQL Server, you must set its network settings. Up to now, you or your Microsoft Windows NT or Windows 2000 system administrator may have set the required network communication protocol. If not, you can simply use the console to set the network communication protocol. The communication protocol you select is usually determined based on the company's principles or other systems already set on the network. Although there are some differences in efficiency and functionality between different communication protocols, most of them can meet your needs.
In this chapter, you will learn how to set various components in SQL Server, including the network hardware layer, network protocol layer, and SQL server network Connection Library layer. In addition, we will introduce database connection components, such as DB-lib, Open Database Connectivity (ODBC), and ODBC connection-pooling, determine whether the SQL server has a network connection bottleneck.
Overview of Network Services
Communication between the SQL Server client and the server requires a variety of software and hardware layers. Let's take a brief look at these levels. Their functions will be described in more detail later in this chapter.
The top layer is the SQL Server application development interface (API ). The API layer is composed of one of the following services:
• DB-LIB (legacy SQL Server API)
• ODBC (capable of connecting to SQL Server or other database products)
• Ole db (used by Activex programmers)
• ODS (Open Data Service)
The API is located at the top layer of the network link library. The network link library is usually abbreviated as net-library or net-libs. The Network Connection Library can translate SQL Server commands and data into system calls and communicate with the network protocol layer. The Network Connection Library is an SQL server component, and the network protocol layer is an operating system component. You can select one or more network connection libraries from the following list:
• Named Pipes)
• Nwlink IPX/SPX
• Banyan vines
The Network Connection Library layer can contain more than one Network Connection Library, and the network protocol layer can also contain more than one network communication protocol, and each network Connection Library communicates with one or more network protocols. The network protocol layer is an operating system component that uses the network protocol language. The call requests and data of SQL Server are encapsulated in Network calls so that they can be transmitted at the protocol layer through the network. In addition to multiprotocol, each network communication protocol supports a specific network connection library. Multiple communication protocols use the Remote Procedure Call and RPC functions of Windows 2000 and Windows NT. It also supports TPC/IP communication terminals, nwlink IPX/SPX, and named pipelines.
For Windows NT or Windows 2000 servers, it is quite common to use several different network communication protocols at the same time. These communication protocols will be described in more detail in the "Network Connection Library" section later in this chapter.
The underlying communication layer consists of network hardware and drive devices. This layer is usually independent from the network protocol layer, but there are still some correlations. For example, some devices only support a specific set of network communication protocols. There are many available network technologies, and new technologies are constantly being developed. The network hardware layer consists of many different technologies, including the following:
• Token Ring)
• Asynchronous Transfer Mode (ATM)
• Fiber Optics)
These communication layers are at both the client and the servo end, as shown in Figure 11-1. As you can see, there are many processing steps to transfer data from an ODBC call to an actual call. In this chapter, we will not only introduce how different communication layers work, but also discuss troubleshooting and other related issues.
Figure 11-1 SQL Server communication layer
SQL Server application interface
To communicate with SQL Server, your application must use the SQL Server language. One of the methods is to use tools provided by SQL Server, such as the command column osql or SQL Server Query analyzer (isqlw ). These tools are useful for simple queries, but they are powerless for everyday applications. For example, for inventory processing, accounts payable, and accounts receivable, using GUI programs is more efficient than typing a long SQL statement. In fact, most users of such applications do not know the SQL language. Generally, developers use APIs to write applications that can be connected to SQL Server. APIS provide calls that can execute many database functions.
SQL provides several APIs, including DB-lib, ODBC, and OLE DB. DB-LIB is the most primitive SQL Server API that can be used in both Microsoft SQL Server and Sybase products. ODBC is a new and elastic language that can be used to communicate with products of various relational database management systems (RDBMS. Programmers can also use SQL Server Using OLE DB and other APIs. This section describes different APIs.
Since DB-LIB was first announced in 1988, it has become part of SQL Server, it is also the most primitive SQL server program API. While DB-LIB has become an inherent part of SQL Server, ODBC has gradually become the main API. Both C and C ++ languages and Microsoft Visual Basic Support DB-lib. DB-LIB calls are generated by application code and then transmitted to the network protocol layer through the network link library, and then to the network hardware layer.
ODBC is a standard API developed by Microsoft to facilitate the connection between Windows PC and various RDBMS. Through the odbc api program, you can use the same application to communicate with a variety of different systems. Although ODBC is common, it is not necessarily the most efficient API for all RDBMS. Generally, using built-in APIs for specific RDBMS will support many additional features and be optimized.
ODBC supports other connections by Using Dynamic Server Pages (ASP) on the Internet. Supports ActiveX, microsoft foundation classes (MFC), and Extensible Markup Language (XML ). In recent years, the support level of ODBC has rapidly increased, making it an API that supports multiple RDBMS.
Regardless of the system of the RDBMS you want to connect to, ODBC APIs share the same format, but ODBC drivers are different. Each RDBMS you want to use must have a unique ODBC driver. This driver converts ODBC to the RDBMS network protocol of the machine. Updated RDBMS versions usually require a new ODBC driver for function optimization, but there are often compatibility issues between the two versions. As a result, DB-LIB typically uses a particular network-Linked Library, while ODBC uses a network-Linked Library with multiple protocols. The Network Connection Library facilitates the connection between ODBC applications and servers without selecting specific protocols.
ODBC online set Area
The online sharing capability in applications starts with ODBC 2.x. Generally, each time a different user logs on to the application, the application creates an additional online connection from the application layer to the database. Since the chance of establishing and maintaining these connections to the database occupies a lot of system resources, the efficiency of this process becomes very low.
Other threads in an online set area application use the existing ODBC online instead of a different online thread. This function is particularly useful for Internet applications that require repeated connections. If the application requires an online set area, you must register the application itself at startup.
When an application requires an ODBC connection, ODBC Connection Manager determines whether to use a new connection or an existing connection. The application does not know this decision, and the execution will continue to work in the usual way.
Once the execution thread completes the ODBC connection, the application will generate a call to release the connection. Then, ODBC Connection Manager will control the connection again. If the link is idle for a certain period of time, it will be closed.
For more information about the ODBC online set area, see Microsoft ODBC software development kit (SDK ).
You can also use other APIs to communicate with SQL Server. These APIs include ole db, ODS (open data serves, open data services) and others such as SQL-DMF (SQL Distributed Management Framework, SQL Distributed Management Architecture), SQL-DMO (SQL distributed management objects, SQL distributed management objects) and SQL-NS (SQL namespace, SQL namespace ). Generally, each protocol supports a specific function or a certain proportion of the market, and requires a customized program interface.
For more information about these special APIs, see SQL Server 2000 online books.
Network Connection Library
The Network Connection Library layer of SQL Server converts API calls to specific protocol calls and then transmits them to the network protocol layer. The Network Connection Library layer is set on the client using the client network utility, and on the server side using the server network utility ). Using these tools, you can set one or more SQL SERVER network connection libraries on the server side, or a network connection library on the client system. The Network Connection Library set by the client is the same as that set by the server, so that SQL server can communicate normally. A single network can accommodate multiple communication protocols at the same time. For example, on the same network, some client systems may communicate with SQL Server through named pipes, while other client systems may communicate with SQL Server through TCP/IP.
As you can see from the previous example, the SQL server must have both the named pipe and TCP/IP installed to communicate with the client normally.
SQL Server 2000 SERVER network utilities
It is common to set up several communication protocols on the servo system. By default, the server installs the named pipe and TCP/IP network connection libraries. To set more network connection libraries on the server, follow these steps:
1. Select Server network utilities from start/ASSEMBLY/Microsoft SQL Server, and the SQL server network utilities dialog box is displayed, from 11 to 2.
Figure 11-2 General page of the SQL server network utility dialog box
2. the SQL server network utility dialog box has two volume labels: general and communication protocol network link library. Generally, tabs are used to enable and disable network communication protocols. The enabled communication protocols are arranged in the list on the right hand side, and SQL Server tries to use these communication protocols in the order they are arranged. The General Tab allows the following operations:
O to enable additional communication protocols, select one or more communication protocols in the disabled communication protocol list, and click Enable.
O to disable the communication protocol, first select one or more communication protocols in the list of enabled communication protocols, and then click Disable.
O modify the attributes of the enabled communication protocol, select the name of the communication protocol, and then press the attribute.
O enable forced communication protocol encryption through SSL (Secure Sockets Layer.
O supports Winsock Proxy.
The network link page of the communication protocol is only used to display information. From this tab, you can see the version number and date of the most recent change to the network link library, 11-3.
Figure 11-3 "Communication Protocol Network Connection Library" volume page in the "SQL SERVER network utilities" dialog box
SQL Server 2000 client network utilities
The other side of the online network is the client system, which is set similarly to that on the server. To set the client system, perform the following steps on the client system:
1. Select the client network utility from the start/ASSEMBLY/Microsoft SQL Server, and the SQL Server Client setting utility dialog box is displayed, from 11 to 4. Most of these functions are the same as those in the SQL server network utility dialog box, but the client utility provides more options.
Figure 11-4 General page of the SQL Server Client setting utility dialog box
The SQL Server network utility dialog box simply lists some network connection libraries and their online parameters, however, in the SQL Server Client setting utility, you can specify the communication protocol and server alias enabled by the client preset. In the General tab, the enabled communication protocols are arranged in the order in which they are used. Take 11-4 as an example. The order of enabling the communication protocol is named pipe, TCP/IP. Therefore, the client tries to use the named pipe when going online with the server. If it fails, the client then uses TCP/IP to connect to the server. If the connection is still unavailable, the client will generate an online error message.
The server alias allows you to use only one specified communication protocol and ignore the settings for enabling the communication protocol list. When the communication protocol specified by the client cannot be connected, the client will not attempt to use other communication protocols. If you have multiple servers and are not using the same common communication protocol, you should place the most commonly used communication protocol at the top of the list of enabled communication protocols. In this way, the attempt to connect online can be minimized.
You can simply enable or disable the communication protocol. To enable the communication protocol, select the required communication protocol from the disabled communication protocol list, and click Enable. To disable the communication protocol, select the Protocol to disable from the list of enabled communication protocols, and click Disable.
You can modify the attributes of the enable communication protocol, click the communication protocol from the list of enabled communication protocols in the following order, and then press the content. However, the default value is an optimal choice for most networks.
On the General tab, you can also enable forced communication protocol encryption to protect data transmitted over the network. This option is only available when multiple communication protocols are enabled.
2. to define a server alias, click the alias tab. This tab lists any existing Server aliases. To add an alias, click Add to display the Add network link library Setting dialog box, from 11 to 5.
Figure 11-5 "add network link library Settings" dialog box
3. In this dialog box, you can add an SQL Server and specify its alias, and the SQL server uses the communication protocol you specified. This communication must have been set on the client side, and must be specified here for the client to use this specific communication protocol. When the SQL Server client tries to use the alias to bring it online, the Network Connection Library and the online parameters you set here will be used. Unless your application tries to connect to the server through an alias, the preset communication protocol is used.
4. Figure 11-6 shows the data link library option page for setting the public program on the SQL Server Client. This volume page displays information about the DB-LIB and the following check boxes: automatic conversion from ANSI to OEM and use of international settings. The first option enables you to enable the data link library to automatically convert character sets from ANSI to OEM when communicating with SQL Server. The second option allows you to get the date, time, and currency format from the system, instead of using hardware-encoded values.
Figure 11-6 configure the Data Link Library option page of the public program on the SQL Server Client
5. the SQL Server Client setting public program also includes a network link library volume page, 11-7. Similar to the network link library volume tag page of the SQL server network utility, this volume tag page only displays available network link libraries and their version numbers.
Many online problems may occur when the sequence of network connection databases on the client system is not set. When you encounter an online problem, check the Network Connection Library settings first.
Figure 11-7 configure the "Network link library" page of the public program on the SQL Server Client
SQL Server Network Connection Library and communication protocol
As mentioned earlier, SQL Server supports the following network connection libraries: Named Pipes (Named Pipes), TCP/IP, and multiprotocol) nwlink IPX/SPX, appletalk, and Banyan vines. Each network Connection Library corresponds to one or more different communication protocols. This section describes each network connection library.
The network communication protocol used on your SQL Server may be determined by the enterprise standard or an existing system. All SQL Server commands and functions support all network communication protocols, but some communication protocols are faster than others. In addition, some communication protocols support routing and name services, while others do not.
Microsoft developed the named pipe communication protocol a few years ago. The Named Pipe supports two modes: local and remote. When the client and server are in the same system, use the local name pipeline. When the client and server are not in the same system, a remote named pipe is used. When a named pipe is created online, the SQL server network utility decides to use the local named pipe or remote named pipe.
The named pipe is the preset client communication protocol and the network communication protocol preset in Windows NT 4.0 Server and Windows 2000 system. There is no named MPs queue in Windows 95/98. In these systems, the network connection library on the server side is TCP/IP, multiple communication protocols, and shared memory. Although named pipes are a good protocol, they are generally not used in large networks because they do not support routing and gateways. Compared with other communication protocols, such as TCP/IP, the named pipe requires more interaction between the server and the client.
TCP/IP is the most common network communication protocol, because it can operate on multiple platforms and has been recognized as a network standard, and operates fast. It is also the network protocol used by the Internet. TCP/IP Network Connection Library is one of the most efficient network connection libraries of SQL Server. The rich features of TCP/IP make it a good choice in the network link library.
Multiple communication protocols are added to SQL Server 7.0 and reserved to SQL Server 2000. The Network Connection Library is actually a combination of several network connection libraries. Therefore, it is less efficient than a specific network connection library, but it provides greater elasticity. The Network Connection Library of multiple communication protocols supports TCP/IP, nwlink IPX/SPX, and named pipes. When using a multi-protocol Network Connection Library, the first common communication protocols are used on both the client and server. If the client is connected to a server running different communication protocols, multiple communication protocols are an ideal choice.
When you integrate the SQL Server 2000 system into the NetWare network, nwlink IPX/SPX is an ideal communication protocol because it works perfectly. IPX/SPX has existed for a long time and is a high-performance and stable Network Connection Library.
Appletalk is a network protocol developed by Apple Computers for Apple systems. Windows NT and Windows 2000 support appletalk and allow Windows NT and Windows 2000 servers to be integrated into clients in the appletalk environment.
The Banyan vines Network Connection Library is used to use the vines network system, allowing Windows clients and servers to be integrated with the vines environment.
Via (Virtual Interface Architecture, Virtual Interface Architecture)
This Communication Protocol comes from two new trends: GigaNet and servernet II. It is a good choice for cluster servers.
Select network link library
The Network Connection Library you selected depends on the network communication protocol you used. When the Network Connection Library of the server and client is different, online problems often occur. If you cannot connect to the server, check the Network Connection Library definitions at both ends. In addition, use another program, such as ping or Microsoft Windows Explorer, to determine whether the problem is related to SQL Server or the network itself.
Network components and SQL server performance
The network is divided into two layers: software layer (including network communication protocol) and hardware layer. In this book, the hardware layer includes necessary software drivers to drive hardware. Each layer is independent of each other, and each layer can have one or more components. For example, you may run TCP/IP and IPX/SPX on the same network card, or use the same protocol to run different network cards. This structure is 11-8.
Figure 11-8 Network Level
Each network level has its own characteristics and efficiency considerations. As mentioned above, there are different reasons for choosing network protocols or network hardware components. Generally, the choice is based on business principles and how the network connects to other systems. This book does not mean that you must use specific protocols or network hardware. In this section, we will introduce factors that affect SQL server functionality and performance from the perspective of software and hardware.
Software layer-network protocol
As mentioned above, network protocols include named pipes, TCP/IP, nwlink IPX/SPX, appletalk, and Banyan vines. Basically, all network communication protocols have the same operation mode as those of SQL Server. Most network problems that may cause functionality or operation occur on the hardware layer. On the other hand, online problems often occur at the Network Connection Library layer or network communication protocol layer. If you encounter a problem when connecting the SQL Server client to the SQL Server 2000 Server System, try another method. For example, use Windows Explorer. If you can connect to the system through explorer but cannot use SQL Server, your problem may be related to SQL Server. Make sure that you are using the appropriate network communication protocol to try online. If multiple communication protocols are used, it is sometimes difficult to correctly identify which communication protocol you are using. If you can connect to the server through ping, Internet Explorer, or other external programs, the problem may occur when you select an improper Network Connection Library.
No matter which network protocol you use, there are many operational problems on the hardware layer. If the system is set within the limits of the network, the problems encountered in the future will be greatly reduced.
You must have an understanding of the hardware layer to determine whether a network operation problem has occurred. The physical hardware layer and communication protocol layer are independent, that is, different network communication protocols can be used on different hardware network devices. The network hardware you select determines the network performance. The traffic that can be processed by the network depends on the network type and speed.
Network bandwidth refers to the amount of data transmitted over the network within a certain period of time. The network bandwidth is sometimes defined by the network hardware name. For example, 10 BaseT or 100 Baset indicates 10-mbit/sec or-Mbps.
However, the measurement of network transmission volume is sometimes inaccurate. In the vast majority of network hardware, when the transmission scale is reduced, the amount of data that the network card can transmit will also decrease, because the load of each network transmission is certain. For example, the load required to transmit 64 KB data is about the same as that required to transmit 2 KB data. RDBMS (including SQL Server) often processes the transmission of a small amount of data. Therefore, the amount of data that your server can process may be less than the bandwidth of the network hardware.
Although there are many options, the most common network hardware may still be Ethernet. In recent years, the speed of the Ethernet network has greatly increased and will continue to rise in the future. Xerox, Dec, and Intel developed the Ethernet network in 1976. In the early days, coaxial cables were used, and the bandwidth of the Ethernet network was 3 Mbps. After the advent of the 10 BaseT technology, the network bandwidth increased to 10 Mbps. Then the 100 Baset technology increased the network bandwidth to 100 Mbps. In the near future, when the Gigabit Ethernet is mature, the network bandwidth will increase to 1-Gbps. The comparison between various bandwidths is listed below.
Network Type bandwidth
Coaxial Cable, Ethernet, 3 Mbps
10 BaseT 10 Mbps
100 Baset 100 Mbps
Gigabit Ethernet 1000 Mbps
Although the bandwidth of the Ethernet network is soaring, there are still some problems in the Ethernet network: Multiple Ethernet cards may transmit data at the same time, and if two or more network cards transmit data at the same time, collision ). Every network card with a collision will wait for a while and try to re-transmit the data. Although the time is short, it will continue to accumulate. The more collisions occur, the longer the waiting time for re-transmission.
When network traffic increases, the probability of collision also increases. If the traffic is close to the network capacity, the probability of a collision will rise sharply, as shown in Figure 11-9. The occurrence of a collision will reduce the efficiency. Therefore, it is very important to monitor network traffic and pay attention to collision. For example, you can follow a rough rule: the transmission traffic should not exceed 75% of the network bandwidth. Of course, this value will be exceeded at the peak, but it should not be too long.
Figure 11-9 Comparison between collision rate and network usage
Through the mechanism of Mark transfer, the mark ring network gives every member of the ring the opportunity to communicate with other members. Mark each time only one system in the network is allowed for data transmission. Because of this architecture, you can use the entire bandwidth of the network without causing excessive latency in communication.
Like an Ethernet network, different technologies provide different network bandwidths, as shown in the following table. However, because the mark Ring Network is a series of point-to-point connections, there will be no collision, so the entire bandwidth can be used almost. Like the Ethernet network technology, the mark ring network technology is continuously improved.
Network Type bandwidth
IEEE 802.3 marking ring 1, 4, or 16 Mbps
IEEE 802.5 100 Mbps
Gigabit marking ring 1000 Mbps
There are also many other network hardware options, including ATM and optical fiber.
As we have seen before, the network hardware type and speed used may affect the overall performance of the database system. If the network bandwidth is exceeded, data transmission will experience a bottleneck, resulting in transmission delay. Latency reduces the efficiency of the entire system.
You can calculate the maximum network performance based on the installed hardware, and have a concept of the crux of the performance problem. With this information, you only need to add a new network card to solve the problem. The first step in finding network problems is to periodically monitor the network. You can use the collected data to determine whether a network problem exists and develop a feasible solution.
The monitoring network is not as easy as you think. Generally, you need to purchase additional network monitoring hardware or software to effectively monitor the network. The factors discussed below allow you to determine whether it is necessary to purchase these monitoring devices.
First, all database servers and clients on your physical network do not necessarily use the same communication protocol. For example, the system that executes TCP/IP on the Ethernet can only see the traffic of TCP/IP at the operating system level, IPX/SPX packets will be filtered out on the device driver layer. Generally, network monitoring software requires a customized device driver and network level.
Moreover, the network card filters out data that is not suitable for your specific machine. Therefore, not all network data is transmitted to the driver or operating system. To observe all network activities, you should use custom device drivers and network layers. Without modification, a workstation cannot monitor all traffic on the physical network.
After the network monitoring hardware, software, or both are installed, you can clearly understand the transmission traffic processed by your network. This traffic may be caused by your system, but it may also be caused by routing or configuration problems. Troubleshooting of network hardware problems is beyond the scope of this book. After installing the network monitoring system, check the following items:
• What is the amount of data transmitted over the network during utilization? What is the difference between this data volume and the maximum bandwidth of network hardware?
• Packet size: How many packets are transmitted over the network? Is large packets more efficient or small packets?
• Is a large number of collisions occurring (if any? If so, why?
• Are there many unfinished transmissions that need to be re-transmitted? This may be a warning of a faulty network card or connection device.
Determine if there is a problem
After you collect the relevant performance information, you must determine whether the network is faulty. This is not easy. Network performance problems usually do not display an error message, but directly reduce the overall performance. To determine whether a problem occurs, you should compare the monitoring data with the previously provided settings.
It is a good solution to avoid exceeding 75% of the network bandwidth. If most of the network transmission traffic is small, you may want to further reduce the percentage of transmission traffic, because a large amount of small transmission requires more load than a small amount of Large Transmission traffic. In the Ethernet, reducing the number of transmissions also reduces the number of collisions, thus reducing the response time of network requests and accelerating the entire network.
Some problems may be more serious than bandwidth problems. You should check for high-rate collisions and errors. If you are near the critical value of 75% and there are many collisions, you may be close to the network bottleneck. Relatively, if the network traffic is small and there are many collisions, you may have hardware errors.
You should also check the transmission error, which implies that the hardware may be faulty. The faulty hardware may be any part of the network, such as the network card, cable, router, or bridge. Once a problem is identified, you should consult a network expert.
Solutions to network problems
There are several methods to solve the bandwidth problem based on specific situations. You can buy more or different hardware, segment the network, or even redesign applications.
One way to reduce network usage is to increase the bandwidth. Upgrading from 10 BaseT to 100 Baset can increase the bandwidth by 10 times. This method is simple and easy, but expensive. Try other replacement methods.
If the network traffic is too high, you should divide the network into several subnetworks Based on the department or working group. Through a sub-network, you can build your own network in every office or department, instead of the entire company on the same network. This method can reduce the number of systems on a single network and thus reduce traffic. Sometimes the network has slowed down for a while, but you have never noticed the increase before the problem occurs. To reduce network congestion, using a sub-network may be the best solution.
Another solution is to observe the network usage from a functional perspective. Is the network reasonably used? Does the application return too much data? Check the SQL Server client programs to ensure that they have no more requests than the required data columns. If there are many users, it is the easiest way to use a specific query to return data with the minimum number of data columns to reduce network traffic.
As you can see, there may be various problems and different solutions. Don't worry about these problems. Logical errors of applications are sometimes displayed as network bandwidth problems and scheduling problems. For example, do not back up the network during the busiest day.
In this chapter, I learned the basic concepts of the SQL server network and how to set the SQL server on the network. I learned about the hierarchical system used by SQL Server, from API to Network Connection Library, network protocol, and finally network hardware. These levels are independent of each other, but they are combined through various settings. Great flexibility is available when selecting APIs, network connection libraries, network protocols, and even network hardware. Remember to regularly check network traffic to avoid operational problems before they occur. We will discuss general operational issues in chapter 36th; in Chapter 12th, we will switch to the topic: Learn how to use Microsoft Cluster Server services to set up SQL server for a high-performance system.