A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
Must be clarified that although the article is my summary collation, but a lot of knowledge is really not I can study analysis, through listening to training, reading, practice summed up, on the one hand, in order to give their own standby, so that later problems can be solved, on the other hand also hope to encounter the same problem friends can get some inspiration So the knowledge in the article may appear in many places.
We often encounter a lot of connectivity problems, while programmers often think that connecting the database simply to connect the →openconnection→ operation →close, but a simple connection action, often with a lot of things behind, fully understand, the development and management of a great help, After all, not even on the server everything else is in vain: first, from the development level, to ensure the stability of the database connection:
Reason one: Database connection is very "heavy" operation, consumes a lot of resources
In the common C/S mode, the following actions lurk behind the simple connection operation:
1, the client and the remote server Listener (Listenerprogram) to establish contact.
2. The listener either creates a process or thread to execute the database core program, or directly or indirectly passes the customer request to the existing server process, depending on whether the server is sharing the server.
3. Create a new environment for each session and track their behavior. Before the establishment of also do account password matching. It is possible that the DBMS also performs a login trigger, initializing stored procedures and packages (if they are invoked for the first time).
4, the handshake protocol to be completed between the client process and the server process.
Because of this, connection pooling technology is particularly important.
Reason two: The interaction between programs (including stored procedures) and databases costs:
Context switching between programs and DBMS has a cost, even if the connection is established but not interrupted. In this case, if the DBMS supports data passing through an array, it should not hesitate to use it.
Some novice programmers (without contempt) will simply connect to and disconnect the database in each insert, and easily deplete server resources If there is a large amount of data (the problem has already occurred). Once heard a Microsoft engineer said they go to serve customers an example, a mobile phone line, but the fifth line when the card is not, it is not open the sixth line. Later found that it is programming time to put the loop on the outer layer of the connection, each cycle once, it is necessary to connect, disconnect once. Cause a heavy load. Later put the loop into the connection, you can open to hundreds of production lines. The importance of visible connections. then, from the database management level:
When the database client application uses the database service:
The first step is to establish a connection on SQL Server. If both sides are on a single machine, that is the local connection. If you are not on a single machine, you need to pass through the network layer.
The second step, the client needs to tell SQL Server its identity. SQL Server then requires authentication (authentication) to be legal, giving a preset authorization (Authorization)
The above work has the client data driver (ODBC, OLE DB, nativeclient, JDBC, etc.) and SQL Server interaction completes, after the successful client user can begin to access the data.
During the connection process, if a problem is encountered, the client driver must throw an error message. Let us find the reason for the error:
1, the client driver failed to find the user specified SQL Server:
SQL Server doesn ' t exist or access denied
Although it does not exist or access is denied, it actually means that the specified SQL Server is not found
2, SQL Server has been found, and even the connection has been established, but for some unknown reason, the connection is terminated abnormally:
[DBMSSOCN] General network error. Chack your networkdocumentation.
A transport level error occurred when sending a request (PROVIDER:TCP provider error:0 A existing connection is forcibly Closed bythe remote host)
This error can occur at any time during the connection process, including initial and client-side instructions, for a number of reasons that are difficult to handle easily.
3, user authentication failed, SQL Server believes that the connection used an illegal user and refused:
Login failed for user "Null"
"Message 18456, Level 14, State 1, server <computer_name>, line 1th"
"User ' <user_name> ' login failed"
4, the certification process encountered errors, authentication action terminated abnormally
Failed to generate SSPI context
This error occurred to some of the SQL Server users who had previously been visited by power. The user clearly has access rights. However, on some machines, certain periods of time cannot be connected.
Sometimes errors can occur intermittently or even disappear automatically.
Here is a detailed description:
First, the choice of the agreement and alias:
Connect to the database first to enable the network protocol, whether it is a local or network connection.
SQL Server can listen for multiple protocol processing requests at the same time. The client (where the client is multiple, not specifically the front-end application) chooses a protocol to connect to SQL Server. If you do not know which protocol SQL Server is listening on, you can configure the client to attempt the connection sequentially:
SQL Server currently has 3 commonly used: Shared Memory, TCP/IP, and named Pipe
Shared Memory: Simplest protocol, no special configuration
Because the protocol can only connect to SQL Server running on the same computer. Indexes are not available for most connections. However, you can troubleshoot other protocols to ensure that connectivity issues are related to the network layer or to SQL Server itself. At the same time, it is also the fastest protocol.
TCP/IP: A common protocol widely used on the internet
Includes standards for routing network traffic and provides advanced security features that are the most commonly used protocols in business. It is also the most common network protocol for SQL Server.
Named Pipe: A protocol developed for the LAN
Part of memory is used by a process to pass information to another process, so the output of one process is the input of another process. The second process can be local (on the same computer as the first process), or it can be remote (on a networked computer). If you use a named pipe for programming, you will find that it uses standard WIN32 file system API functions (such as ReadFile and WriteFile) to send and receive data. Has nothing to do with the system base-level network Transfer Protocol. The basic process is as follows:
(1), SQL Server servers use the CreateNamedPipe function to create Named pipes and listen to them.
(2), the client uses the CreateFile and WriteFile functions to attempt to connect to the server's named pipe.
1, the named pipe is not a grassroots network protocol. Even if you use Named pipes, you configure TCP or other grassroots network protocols to ensure network connectivity between clients and SQL Server servers.
2. The named pipe is a protocol to be authenticated through the system.
Because it first accesses the ipc$ share of the server. This step must be certified by Windows. To connect to a pipe that is being monitored by SQL Server. This is the biggest benefit of using named pipes, taking advantage of the security mechanisms built into Windows.
The protocol should be selected according to different requirements, and if there are no special reasons, it is recommended to consider TCP/IP protocol first.
The connection determines which protocol to use.
First, the network protocol configuration control by the server. If it is not enabled, then there is no way to use it.
Second, the client can also set the protocol order.
Finally, the client can set the alias for a SQL Server service, specify how it is connected, and the client can also use the connection method in the cache where the last successful connection was made.
1.1, Server network configuration:
The network configuration is network in SQL Server Configuration Manager (Configuration Manager) Configuration
The result of the configuration is actually stored in the registry:
Hkey_local_machine\software\microsoft\microsoftsql Server\MSSQL. X\mssqlserver\supersocketnetlib under the various projects. Can be modified directly from here (but not recommended). The modification requires a restart of the service.
After restarting, check SQL Server's errorlog for confirmation.
Shared Memory After normal startup information is similar to the following:
XXXX-XX-XX xx:xx:xx.xx Server server local ConnectionProvider be ready to accept connection on [\\.\pipe\sqllocal\mssqlser VER].
Named pipe can be seen after normal startup:
XXXX-XX-XX xx:xx:xx.xx Server server named pipe provider IsReady to accept on [connection].
TCP/IP starts normally to see:
XXXX-XX-XX xx:xx:xx.xx Server server is listening on [' any ' <ipv4> 1433].-listening on 1433 ports on all IP addresses on the server
1.2, the role of SQL Server browser:
If the client connects to SQL Server using the TCP/IP protocol, you must specify the port that SQL Server is listening on. If you use Namedpipe, you must specify a pipe name. Before 2000, a computer can only install one instance. So SQL Server listens to 1433 ports altogether, and when 2000 introduces multiple instances, only the default instance can use this port. For a named instance, each reboot bound port may be different, and the user knows only the database server name and instance name, and for this reason, the SQL Server product group developed a set of SSRP for listening to UDP1434 ports. The listener service is concurrently represented by a SQL Server instance. When any client accesses an instance of SQL Server on this server, it asks the UDP1434 port first, and then the SSRP protocol tells the client the port number and the pipe name of the SQL Server instance that is installed on this computer.
But the slammer virus that occurred in 2003 caused the component to emit a large amount of network packets, which is the most dangerous virus associated with SQL Server to date. So the SQL Server Browser service is introduced from SQLServer2005 to replace the original mechanism.
SQL Server Browser uses SSRP to listen on UDP ports and accept unauthenticated requests. To reduce malicious attacks, SQL Server browser will be set to run in the security context of a low-level privileged user. To minimize the chance of being attacked. You can add a new user to sqlserverxxxsqlbrowser$ this local group. Permissions are as follows:
L Deny access to the computer over the network
L Deny local logon
L refuse to log on as a batch job
L refuse to log on through Terminal Services
L log in as a service
l Read and write SQL Server registry keys related to network traffic (ports and pipelines).
When SQL Serverbrowser is started, it starts and uses UDP 1434 ports. The registry is read, identifying all instances of SQL Server on the computer, and indicating the ports and named Pipes used. When multiple network adapters are available, the first encountered port is enabled.
If the SQL Server Browser service is not running and you provide the correct port number or named pipe, you can still connect to SQL Server if the default instance is running on port 1433, you can use TCP/IP to connect to the default instance. However, the following connection is not valid:
L A component attempts to connect to a named instance if all parameters, such as the port and pipe name, are not fully specified.
L A component that generates or passes the server/instance information that other components will then use to reconnect.
L connect to a named instance without providing a port number or pipe.
L CONNECT the DAC to a named instance or default instance without using the TCP 1433 port.
L Enumerate the servers in SSMs, Enterprise Manager, or Query Analyzer.
If an application accesses SQL Server over the network, to stop or disable SQL Servers Browser, you must assign a set port number for each instance, and then specify the port number in your application code. But there are also the following questions:
L must update and maintain client application code.
L If another service or application on the server occupies a port, it causes the instance to be unavailable.
If you report: SQL Server doesn ' texist or access denied, you can try specifying a port, or pipe name, to see if you can connect, if connected, because UDP 1434 is disabled on the network. A firewall or gateway is required to open the port. Be aware that the SQL Server Browser startup account has permission to read and write SQL Server registry keys related to network traffic. If the permissions are not sufficient, no error message is returned.
1.3. Client Network configuration:
Applications do SQL Server connections by loading data-driven controls on SQL Server. There are currently three kinds:
A. MDAC (Microsoft Data Access component):
Includes ODBC and OLE DB excuses. The main right and wrong. NET application services. The default is band, but you may need to update the version. Running on the command line: Cliconfg.exe can configure the network protocol for MDAC access components.
You can configure protocols and sequencing, and you can also starve to configure whether to use SSL (network transport encryption), whether to try shared memory, and so on. You can also get an effect by modifying the registry.
B. SQL Server Native Client:
is an independent data access API introduced after 2005 for OLE DB and ODBC. 05 with 9.0 version, 08 self with 10 version. It combines the SQL Server OLE DB provider and the SQL Server ODBC driver into a native DLL. In addition to the original features, also provides new features. Used to create new applications or to enhance existing applications so that they can introduce new functionality in SQLServer2005. such as Mars, UDT, query notification, snapshot isolation, and XML data type support.
If you use languages such as C # and you want to use the new features in 05 and 08, you should use the. NET Framework Data Provider for SQL Server, which is part of the VS2005. NET Framework. Provides the most powerful data access Components for 2005, 2008. For new features, you should choose to use SQL Server Native Client. Both it and MDAC support committed read transaction isolation using row versioning, but use it to support snapshot transaction isolation.
This component is not installed by default. They can be installed together at installation time, or they can be installed separately in the Sqlncli.msi of the installation files. If installed, you can see and configure it in SQL Server Configuration Manager.
C. Microsoft JDBC Provider: is Java-specific. There is a dedicated network configuration interface.
1.4, the Client network connection selection mechanism:
(1) SQL Server has its own network protocol configuration option, which determines what protocols SQL Server listens to. If it is not turned on, the connection request will not respond.
(2) If there are multiple instances, each port and pipe name is different. SQL Server Browser can know the network configuration information for all instances on this server by reading the registry information. When the client connects, it first communicates to SQL Server browser via UDP1434. This mechanism is that the network configuration can be transparent to the client.
(3) The client's database connection component can be configured with candidate network protocol and candidate priority.
When you have more than one protocol, use the following order:
1. Specify in the connection string (Connection string)
A. Server keywords: server=[protocol:]server[,port]
For example, specify a named pipe: np:myserver\myinstance
B. Network keywords: network=dbmssocn
You can only choose one of two methods.
2. Client alias:
If you specify an alias, you will go to TPC/IP to find the alias of this server, not successful directly to the error, will not try other network protocols.
3. Find the "Lastconnect" registry record for the appropriate data driver:
A set of Lastconnect records are maintained in the registry to record the last connected network configuration. If 1 or 2 steps are unsuccessful, the configuration here will be used. If this is not successful, the data driver will try to change the method 4
4. Select the network protocol according to the network configuration priority of the database driver, and ask SQL Server browser to dynamically learn the port number or pipe name. The connection only reports failure when all configurations are unsuccessful.
Second, the connection failure detection step--Named pipe
In Windows, interprocess communication mechanisms include mail slots, pipelines, sockets, and so on. In the case of pipelines, there are named pipes and anonymous pipes. Named pipes implement communication through the interprocess communication (IPC) mechanism. For one-way or two-way data communication.
How SQL Server Named Pipes works:
First create a named pipe on the server and listen, and then the client connects to the pipeline for a conversation.
1. Names of Named Pipes:
A UNC format identifies a named pipe:\\server\Pipe\path_name
2, use the Client Network Utility to check the client's Connection Agreement configuration to ensure that named pipes are enabled. The default pipe name for the client and server needs to be consistent with server monitoring.
Use this tool to attempt a system DSN that is connected to SQL Server. If an error is shown, there is a problem with the connection. The error number is returned, you can use the "NET HELPMSG error number" to query.
You can change this port number as long as the port is not occupied by another process. Port numbers that are generally above 5000 can be used. The port number from 1024~5000 is often consumed by systems and programs, so it is not recommended to take this range. You can view the port numbers used by Windows systems from this connection:
The client opens TCP/IP by default. You can also use Cliconfg.exe to configure. If the default instance is not 1433, you will need to make a corresponding change at default port. You can also use aliases to specify the port of the server. You can also use dynamic ports, as shown in the figure:
In addition to configuration, the default port for client connections needs to be consistent with the SQL Server Monitor. If you have an alias, you need to carefully see if the port it specifies is correct.
Be sure not only to ping the IP address of the SQL Server servers, but also to ping the names of SQL Server servers. If the name ping is different, indicating a problem with DNS or WINS server configuration, you can manually add IP addresses and server pairs in the Hosts file (SYSTEM32\DRIVERS\ETC), such as:
To verify the ports that SQL Server listens on, you can use the Telnet command, assuming IP is 192.168.1.1 and the port is 1234, you can use: telnet192.168.1.1 1234. If successful, a black screen with the cursor in the flash will be displayed. If unsuccessful, an error message is returned.
Simply use the Server Network Configuration tool to enter multiple comma-delimited port numbers in the TCP/IP protocol properties. Although listening to multiple ports is of little significance, if you think there is a network performance problem, you might as well increase the NIC, which is much better than raising a multiport port.
For this, you can specify a different port, or deactivate some services and restart the SQL Server service. If you want to see what program is consuming the port, you can use PortQry.exe (download required) to get it. Instructions for use:
This issue is essentially different from "SQL Server doesn ' t exists or access denied, which is not connected to the SQL Server service, but the former is already found, but the connection is building, sending client-side query directives, Or any step of receiving a data result set returned by SQL Server has been interrupted unexpectedly. To check for details of the error at this point:
If the server load is high, it is possible to send a lot of reset packets in the network, after more than the number of retries, the client will break the connection, throw Gne. This type of problem affects all connections, even local connections on SQL Server servers.
In a three-tier application structure, a middle-tier application server accepts a large number of database login requests at the same time, and if the connection pool is not used, SQL Server will have a heavy burden of maintaining the connection. There may be a few connections to take care of but it is easy to encounter Gne. If you open the connection pool, the load can be greatly reduced, the problem can be solved.
Because the database often transmits a large number of result sets, the network layer is more busy. If the network between two computers has frequent retransmission phenomenon, or a certain type of network packets are modified or discarded by a network device (gateway, route, firewall, etc.), then the probability of Gne appearing is higher. This type of problem only occurs between a specific set of SQL Server servers and clients. The same SQL Server may have a write client that is not a problem. There are more client issues across the network segment or across subnets.
SQL Server connections are established and maintained by Windows, so many of the behaviors at the Windows level affect SQL Server's connection stability. When a database, network is busy, Windows intentionally rejects some network requests in order to maintain its own security. Cause manslaughter.
L lightweight pooling: will make SQL Server switch to fiber mode plan. can affect SQL Server's operating mode, sometimes with gne side effects. This is not recommended because of the significant performance improvements that are not available in most cases.
Some applications connect to the database for a long time and almost never log out. If there is no action request, the connection is idle for a long time. For such a TCP connection, SQL Server makes a keepalive handshake every 30 seconds. Make sure the connection is valid. If the client does not have feedback on this, SQL Server interrupts the connection. The next time the client is used, it will receive Gne. Some network devices will disconnect directly after 30-40 minutes of idle time. can also lead directly to Gne.
(6), if possible, to move SQL Server servers and client servers as much as possible to physically close, intermediate network devices relatively few network segments. Modify the connection configuration for a different network protocol.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service