11. Network settings for Microsoft SQL Server
Online service overview
SQL Server Application Interface
Network program library
Network components and SQL Server performance
Network monitoring
Summary of this chapter
After you have installed Microsoft SQL Server, you must set its network-related settings. Up to now, you or your Microsoft Windows NT or Windows 2000 system administrator may have set a needed network communication protocol. If there is not yet set, you can simply use the console to set the network communication agreement. The communication protocols you have chosen are usually determined according to the company's principles or other systems already set on the Internet. Although there are some differences between performance and functionality between different communications agreements, most of them can meet your needs.
In this chapter, you will learn how to set various components in SQL Server, including network hardware layers, network protocol layers, and SQL Server network program libraries. In addition, the connection elements of the database, such as DB-LIB, OPEN DATABASE Connectivity (ODBC), and ODBC Connection-Pooling features, and determine if SQL Server has bottlenecks of the network connection.
Network Services Overview The communication of the SQL Server user and the server-side communication requires a variety of software and hardware layers. Let us briefly look at these levels; their functions will have more detailed descriptions later in this chapter. The top layer is the Application Programming Interface (API). The API layer consists of one of the following services: DB-lib (old SQL Server API) ODBC (can connect SQL Server or other database products) OLE DB (ActiveX program designer) ODS (Open Data Service) API is located The top floor of the route library, online program libraries are typically abbreviated as NET-library or net-libs. The network program library translates SQL Server instructions and data into a system call, and communicates with the network protocol layer by the system call. The network program library is the SQL Server component, and the network protocol layer is a job system component. You can select one or more network program libraries from the following list: Named Pipes, TCP / IP Multi-communication Agreement (MultiProtocol) NWLINK IPX / SPX AppleTalk
The Banyan Vines network program can contain more than one network program, and the network protocol layer can also contain more than one network communication protocol, and each network program library communicates with one or more network protocols. The network protocol layer is a job system component using a network protocol language. The call request with SQL Server is encapsulated in a network call, so that the network can be transmitted on the protocol layer through the network. In addition to multiple communication agreements (MultiProtocol), each network communication agreement supports a specific network program library. The Multiple Communication Agreement option uses Windows 2000 to call the remote program call (RPC) feature of Windows NT. It also supports TPC / IP communication terminals, NWLINK IPX / SPX, and a list of tubes. For Windows NT or Windows 2000 servers, several different network communication agreements are quite common. These communication agreements will have a more detailed description in the "Network Program" section later in this chapter. The bottom layer is composed of a network hardware and a drive. This layer is usually independent of the network protocol layer, but there is still some dependencies. For example, some devices support only a specific set of network communication agreements. A wide range of online technologies, and new technologies are constantly being developed. The network hardware layer consists of many different technologies, including the following content: Token Ring Non-Synchronous Transfer Mode (ATM) Fiber Optics Digital Machine (MODEM) These communication layers are simultaneously located at the client and servo, as shown in FIG. 11-1. If you see, you need a lot of processing steps from ODBC calls to actual transmission. In this chapter, we must not only introduce how various communication layers work, but also discuss related issues such as troubleshooting. Figure 11-1 SQL Server Communication Layer
SQL Server Application Network To communicate with SQL Server, your application must use SQL Server language. One method is to use the tools provided by SQL Server, such as OSQL or SQL Server Query Analyzer (ISQLW) of the command column. These tools are useful for simple queries, but there is no power for daily applications. For example, for inventory processing, payment accounts and receivable accounts, using the GUI program is higher than that of typing long SQL statement. In fact, most of these applications don't know the SQL language. Typically, developers are written using APIs that can be connected to SQL Server. The API provides calls that can perform a lot of database functions. SQL offers several APIs, including DB-LIB, ODBC, and OLE DB. DB-LIB is the most original SQL Server API, which can be used in Microsoft SQL Server and Sybase products. ODBC is newer, more elastic, can be used to communicate with product communication with various associated database management systems (RDBMs). Program designers can also use OLE DB and other APIs to use SQL Server. This section will illustrate a variety of different APIs. DB-lib links have become part of SQL Server since the first publication of DB-LIB in 1988, and it is also the most primitive API of SQL Server. Although DB-LIB has become the inherent part of SQL Server, ODBC has gradually become the most important API. C and C languages and Microsoft Visual Basic support DB-lib. The DB-lib call is generated by the application code, and then transferred to the network protocol layer through the network program, then go to the network hard body layer. The ODBC link ODBC is a standard API developed by Microsoft to facilitate Windows PC to connect to a variety of different RDBMS. With the ODBC API, you can communicate with a variety of different system communication using the same application. Although ODBC is universal, it is not necessarily the most efficient API for all RDBMs. Typically, a specific RDBMS uses its built-in API to support many additional functions and optimized. The dynamic server page (ASP) is used on the Internet, and ODBC can be used to support other connections. Supports ActiveX and Microsoft Foundation Classes (MFC) and Extensible Markup Language, XML. In recent years, the support level of ODBC has increased rapidly, making it a API supporting a variety of RDBMS. Regardless of the RDBMS you want to connect, the ODBC API has the same form, but the ODBC driver has different. For each RDBMS you want to use, there must be a unique ODBC driver. This drive program converts the ODBC to the RDBMS network protocol of the machine. Updating version of RDBMS usually requires a new ODBC driver to optimize features, but there are often compatibility issues between two versions. Therefore, DB-LIB usually uses a specific network program library, while ODBC uses a variety of agreement network program libraries. The network program library is conducive to the ODBC application to connect to the server without choosing a specific agreement. The ability of the co-connect line in the ODBC connection area is started by ODBC 2.x.
Typically, whenever a different user logs into the application, the application will create an additional connection from the application layer to the database. Since the connection to the establishment and maintenance of these links to the database will take up a lot of system resources, the efficiency of this process becomes very low. Other ODP in a wiring region application uses an existing ODBC connection without a different connection. This feature is especially useful for Internet applications that need to be repeated. If the application requires a wiring region, the application must be registered at the time of startup. When an application requires an ODBC connection, the ODBC Connection Manager will decide whether to use a new connection or already already existing. The application does not know this decision, and the intention will continue to work according to the usual way. Once the ODBC link is completed, the application will generate a call for release, then the ODBC Connection Manager will re-control the control. If the link is idle within a certain period of time, it will be turned off. Related Information For additional information about the ODBC connection collection, please see Microsoft ODBC Software Development Kit (SDK).
Other APIs You can also use other APIs to communicate with SQL Server. These APIs include OLE DB, ODS (Open Data Serves, Open Data Services), and other SQL-DMF (SQL Distributed Management Architecture), SQL-DMO (SQL Distributed Management Objects, SQL Distributive Management) ) And SQL-NS (SQL Namespace, SQL Namespace). Typically, each agreement supports a specific function or a certain market proportion, and requires a customized interface.
Related Information Related messages related to these special APIs, please refer to the SQL Server 2000 line series.
The network program library SQL Server network program library layer, converts the API call into a specific protocol, and then transferred to the network protocol layer. The network program library layer is set in the client network utility (Client Network Utility), and the server network utility is used in the server side. Use these tools to set one or more SQL Server network program libraries on the server, or a network program library of the user-end system. The network program library set by the user is the same, and SQL Server will communicate normally. Single networks can accommodate several communication agreements at the same time. For example, on the same network, some user-end systems may communicate with SQL Server through a famous tube, and other client systems may communicate with SQL Server through TCP / IP.
The translation refer to the above-mentioned reader can understand that the SQL Server must install the tanker and TCP / IP at the same time to normal communication with the client.
SQL Server 2000 Server Network Utility Sets a variety of communication agreements on the servo end system. The server preset is installed with two network program libraries with a list of TCP / IPs. To set more online program libraries on the server, you can follow these steps:
From the Start / Program / Microsoft SQL Server, select the server network utility to display the SQL Server network utility dialogue, as shown in Figure 11-2.
Figure 11-2 "General" tab of the SQL Server network utility dialogue
There are two tabs page: General and communication agreement network program libraries in SQL Server network utility dialog: General tabs are used to enable and deactivate network communication protocols. The enabled communication protocol will be arranged in the list of the right hand, and SQL Server will try to use these communication protocols in its order. General tab allows the following operations: To enable additional communication protocol, first select one or more communication protocols in the list of disable communication protocol, then press Enable. To deactivate the communication protocol, first select one or more communication protocols in the list enabled communication protocol, then press it. To modify the properties that enable the communication protocol, select the communication protocol name and press attribute. Forced communication protocol encryption can be enabled via SSL (Secure Sockets Layer, Secure Channel Layer). Winsock Proxy support can be enabled. The communication protocol network program library tab is only used to display information. From this tab, you can see the version number of the online program library that is the most changed, as shown in Figure 11-3.
Figure 11-3 "SQL Server Network Utility" dialogue "Communication Agreement Network Program" tab
The other side of the SQL Server 2000 User-end network utility network connection is the user-end system, and its setting method is quite similar to the servo terminal. To set the user-end system, the following steps can be performed at the client system:
From the start / program / Microsoft SQL Server, select the user network utility to display the SQL Server user settings to set the public program dialogue, as shown in Figure 11-4. Most of these functions are the same as the SQL Server network utility dialogue, but the user-end utility provides more options.
Figure 11-4 "SQL Server User Setting Utility" dialogue "General" tab
SQL Server Network Public Program Dialogue is simply listing some of the network program libraries and their connection parameters, but in the SQL Server user settings, you can specify the communication protocol and server-enabled communication protocol and servers enabled. Alias. In the general tab page, the enabled communication protocol is arranged in accordance with its sequential sequence. Take FIG. 11-4 as an example, the order in which communication protocols is enabled is a name pipe, TCP / IP. Therefore, the client will try to use a list of tubes when connecting to the server. If it is not successful, the client will then use TCP / IP to connect with the server. If you still have no connection, the user generates a connection error message. Servo alias allows you to use only one specified communication protocol and ignore the setting of the enabled communication protocol list. When its designated communication agreement cannot be successful, the client will not try to use other communication agreements. If you have multiple servers, it is not the same commonly used communication agreement, you should put the most common communication protocols to the top of the enabled communication protocol. This will allow you to minimize the time when you try to connect. You can simply enable or deactivate communication agreements. To enable communication protocol, select the required communication protocol from the list of disabled communication protocol, then press Enable. To deactivate the communication agreement, select the communication protocol from the list enabled communication protocol, then press it. You can modify the properties of the enable communication protocol, and select the communication protocol in the list of communication protocols in the following order and then press the content. However, the preset value is an optimal choice for most of the network. From a general tab, you can also enable forced communication protocol encryption to protect information transmitted through network. This option is only available in enabling multiple communication agreements. To define a server alias, press the alias tab. This tab will list any existing servo alias. To increase an alias, press to increase, display the new network program library setting dialogue, as shown in Figure 11-5.
Figure 11-5 The "New Network Program" dialog box In this dialog, you can add a SQL Server and specify its alias, and the SQL Server uses the communication protocol you specified. This communication must have been completed in the user end, and must be specified here to use this particular communication agreement. When the SQL Server user attempts to use an aliaher connection, the network program library will use and the connection parameters you set here. The preset enable communication protocol will be used unless your app is attempting to connect through an alias and servers. Figure 11-6 is a SQL Server user-setting data program library option tab. This tab shows the information of DB-LIB and the following authentication blocks: ANSI to OEM automatic conversion and use of international settings. The first option allows you to enable the data program to automatically switch the character set from the ANSI to OEM when communicating with SQL Server. The second option allows you to get dates, time and currency formats from the system, rather than using hardware coding values.
Figure 11-6 SQL Server User End Setting Utility "Data Program Options" tab
SQL Server User Settings The utility also includes a network program library tab, as shown in Figure 11-7. Like a network program library tab with the SQL Server network utility, this tab only displays the available online program libraries and its version number. Many connection problems may occur when the order of network program libraries on the user end system is not at the time. When you encounter a connection problem, please check the settings of the network program library.
Figure 11-7 SQL Server User End Setting Utility "Network Program Library" tab
SQL Server Network Program Library has been mentioned before, SQL Server supports the following network program libraries: Named Pipes, TCP / IP, multiple communication agreements, NWLINK IPX / SPX, AppleTalk, and Banyan Vines. Each network program library corresponds to one or a different communication agreement. This section will briefly introduce each network program library. Network communication protocols used on your SQL Server may determine the system of corporate standards or prior to existing. All SQL Server Commands and Functions support all Internet communication agreements, but some communication protocols will be more fast than others. In addition, some communication agreements support routing and name services, and some are not supported. The famous pipeline Microsoft has developed a famous pipeline communication agreement several years ago. Total pipe support two modes: native and distal ends. When the user end and the server terminal are in the same system, the unit is used. When the user end and the servo terminal are not in the same system, the remote type is used. When connecting the connection via a list of tubes, the SQL Server network utility will decide to use this unit of doting or remote type. The aperating pipeline is a preset client communication agreement, as well as the preset network communication protocol in Windows NT 4.0 Server and Windows 2000 systems. There is no listing in the Windows95 / 98 system. In these systems, the server-side network program library is TCP / IP, multiple communication agreements, and shared memory. Although the tanker is a good agreement, it is usually not used by large networks because it does not support routing and latch. Compared to other communication agreements, such as TCP / IP, the listing of more servo is interactive with the user end. TCP / IP TCP / IP is the most common network communication agreement because it operates on a variety of platforms and has been recognized as a network standard, and the operating speed is fast. It is also an Internet protocol used by Internet. TCP / IP network program library is one of the highest effectiveness in the SQL Server network. TCP / IP rich features make it a nice choice in the network program library. MultiProtocol Multiple Communication Agreements are new to SQL Server 2000 in SQL Server 7.0. The network program library is actually a combination of several online program libraries. Therefore, its efficiency is not as separate, but it provides greater elasticity. Multi-communication protocol network program library support TCP / IP, NWLINK IPX / SPX, and a famous pipeline. When using multiple communication protocols, the first use is a common communication protocol in the client and server. If the client is connected to the server that operates different communication protocols, multiple communication agreements are an ideal choice. NWLINK IPX / SPX When you integrate SQL Server 2000 systems into the NetWare network, NWLINK IPX / SPX is an ideal communication agreement because it operates perfectly. IPX / SPX already has a long time and is a highly efficient and stable network program library. AppleTalk AppleTalk is a network agreement developed by Apple Compute for Apple System. Windows NT and Windows 2000 support AppleTalk and allow Windows NT and Windows 2000 server to integrate to the User of the AppleTalk environment. The Banyan Vines Banyan Vines network program is used to use the Vines network system that allows the Windows user and the servo to integrate 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 servors. Selecting a network program library You selected online communication agreement you use. Connection problems often occur when the server-side network library is not synchronized. If you cannot connect with the server, check the network program library definitions at both ends. In addition, use another program, such as ping or microsoft windows expedition, to determine if the problem is related to SQL Server or with the network itself. The network components are divided into two layers with SQL Server performance network: soft layer (including network communication protocol) and hardware. In terms of this book, the hardware layer includes a must-have to drive the hard body. The layers are independent of each other, and each layer can have one or more components. For example, TCP / IP and IPX / SPX may be operated simultaneously on the same network card, or use the same agreement to operate different network cards. This structure is shown in Figure 11-8. Figure 11-8 Network level
Every online level has its own feature and effectiveness. As mentioned above, choosing a network agreement or a network hard body element; usually, the choice is based on how the business principles and how the network is connected to other systems. This book does not want to specify that you must use a specific agreement or network hard body. In this section, we will introduce factors affecting SQL Server functionality and performance from the perspective of software and hardware. Soft layer - Online protocols As mentioned above, network protocols include a name pipe, TCP / IP, NWLINK IPX / SPX, AppleTalk, and Banyan Vines. Basically, all network communication agreements have the same way of operation with the relevant parts of SQL Server. Most of the majority occurs in the hard body layer if there is a network problem that causes functionality or operation. On the other hand, the connection problem also often occurs in the network program library layer or network communication protocol layer. If you have problems with the SQL Server user to the SQL Server 2000 server system, try a trial. For example, using Windows Explorer. If you can connect to the system through the Explorer, you can't use SQL Server, then your problem may be related to SQL Server. Make sure you try the connection used by the appropriate network communication protocol. If you are using multiple communication agreements, it is sometimes difficult to correctly indicate which communication protocol you are using. If you can connect with the server through ping, Internet explorer, or other external programs, then the problem is likely to occur in the wrong network program library. No matter which network agreement you use, there will be many operation problems in the hardware layer. If the system is set within the range of networks, the problems encountered later will be greatly reduced. The hard body layer you must have aware of the hardware layer to determine whether there is a network operation problem. The physical hardware layer and the communication protocol layer are independent, that is, various network communication agreements can be used on different hardware network devices. The network hardware you have chosen determines the performance of the network, and the traffic of the network can be denoted by the type and speed of the network. The network bandwidth network band refers to the amount of data transmitted through the network within a certain period of time. The network bandwidth is sometimes defined by the network hardware name, such as 10baset or 100BaseT, represents 10-Mbit / SEC or 100-Mbps. However, the measurement of the network transmission is sometimes not accurate. In the vast majority of online hardware, when the transmission scale is reduced, the amount of data transmitted by the network can also decrease, because the load of each network transmission is certain. For example, the load required to transmit 64 kb data is approximately the same as the load required to transmit 2 kb data. RDBMS (including SQL Server) often handles a small amount of data. Therefore, your server can handle the amount of data that will be less than the network hardware. Although there are many options, the most common network hardware may still be an Ethernet. In recent years, the speed of the Ethernet is greatly increased and will continue to rise in the future. Xerox, DEC and Intel have developed an Ethernet in 1976. Early use of coaxial cables, the bandwidth of the Ethernet is 3 Mbps. After 10baset technology, the network band is increased to 10 Mbps. Later 100baset technology increases the network band width to 100 Mbps. In the near future, when the Gigabit Ethernet is ripe, the network band width will increase to 1-gbps. The following is listed below comparison between various frequencies. Network type bandwidth coaxial cable Ettage 3 Mbps10Baset10 Mbps100Baset100 Mbpsgigabit Ethernet 1000 MBPS
Although the bandwidth of the Ethae B is raw wide, there are still some problems with the Ethernet: Multiple Bai Internet Cards may transmit information at the same time, while two or more network cards are just transmitting materials at the same time. Collision will occur. Each online card that has collided will wait for a while and try to re-transfer the information. Although time is short, it will be increasing. The more collisions occur, the longer the time waiting for re-transmission. When the traffic is increased, the chance of collision occurs will also increase. If the flow is close to the capacity of the network, the chance of collision will rise steep, as shown in Figure 11-9. The occurrence of collisions can result in a decrease in performance. Therefore, monitoring network traffic and paying attention to collision is a very important thing. For example, you can follow a rough rule: transfer traffic not exceeding 75% of the network bandwidth. Of course, the peak will exceed this value, but should not exceed the time for this value. Figure 11-9 Comparison of collision probability and network usage
The logo ring marker ring network passes through the registration number, so that every member in the ring has the opportunity to communicate with other members. The marker allows only one system in the network to perform data transfer. Due to this architecture, you can utilize the entire bandwidth of the network without excessive delay time in the communication. The marker ring network is like an Ethernet, and different technologies provide different network bandwidth, as shown in the following table. However, because the marker ring network is a series of point-to-point connecting, there is no collision, so almost the entire bandwidth can be used. As with the other Tong Tanet technology, the mark ring network technology is continuously improved.
Network type bandwidth IEEE 802.3 marker ring 1, 4, or 16 Mbpsieee 802.5100 Mbpsgigabit marker 1000 Mbps
There are also many other network hardware available, including ATM and optical fibers. Online monitoring, for example, we have seen before, using network hardware types and speeds may affect the overall performance of the database system. If the network bandwidth is exceeded, the bottleneck will cause the bottleneck to pass the transmission. Delay will reduce the performance of the entire system. The hardware you can install is based on the maximum performance of the Internet and there is a concept on the effect of the performance problem. Using this information, usually you can solve the problem as long as you add a network card. The primary step of finding a network problem is to periodically monitor the network, you can use the collected information to determine if there is a network problem and develop a viable solution. Monitoring efficiency monitoring network is not easy. You usually need to purchase additional online monitoring hardware or software to effectively monitor the network. Several factors discussed below allow you to judge whether it is necessary to purchase these monitoring equipment. First, all the database servo terminals and the client on your physical network do not necessarily use the same communication protocol. For example, the system that executes TCP / IP on the Ethernet is actually only (on the job system level), the IPX / SPX packet will be filtered out on the device driver layer. Typically, the network monitoring software requires a self-reserved device driver and a network level. Furthermore, the network card will filter out information that is not suitable for your specific machine; therefore, not all network data will be transferred to the driver or job system. To observe all network activities, customized device drivers and network hierarchies should be used. If you don't make a modification, the general workstation cannot monitor all traffic on the entity network. After installation of online monitoring hardware, software or both, you can clearly understand your network processing traffic. This traffic can lead to your system, but sometimes it may be traffic traffic caused by route or setting issues. Troubleshooting network hardware problems is outside the scope of this book. After installing the online monitoring system, let's check the following: How is the amount of use of the utilization through network transmission? Why is this amount of data to the maximum frequency width of the network hardware? How big is the package of the package size network transmission? Is the large packet incidence, or a small package? Collision (if) is happening a lot of collision? If so, why? Error Do you have a lot of unfinished transmission needs to retransmit? This may be a problem with a problematic network card or a warning of the connection device. Judging whether there is a problem after you collect the relevant performance information, you must judge whether there is a problem with the network, this is not easy. Network performance issues usually do not display an error message, while the overall performance is lowered. In order to determine if there is a problem, you should compare the information obtained by the monitoring and the original setting information. Do not exceed 75% of the network bandwidth is a good way. If most of the network transmission is small, you may want to further reduce the percentage of the transfer amount, because a large number of small transmissions require more loads than a small amount of large transmission. In the Other Internet, reducing the number of transmissions will also reduce collision, thus reducing the response time of the network request, so that the entire network is accelerated. Some problems may be more serious than bandwidth problems. You should check the high ratio collisions and errors. If you are near the 75% critical value, and there is a lot of collision, you may have been close to the bottleneck of the network. Relatively, if the network traffic is small, there are many collisions, you are likely to have hardware errors. You should also check the transfer error, and the transfer error suggests that the hardware may have problems. There is a problem with hardware that may be any part of the network, from a network card, cable, router, or bridge, etc. Once there is a problem, you should ask the network expert. To find out the solutions of the network problem, there are several ways to solve the bandwidth problem based on a specific situation. You can purchase more or different hardware, segment online, and even redesign the application. A method of reducing the percentage of a network is to increase bandwidth. Upgrade from 10 Baset to 100 Baset to increase the frequency width.
