Talking about database connection __ Database

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.

Or

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.

So:

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

\\server section: Specifies the server on which the named pipe resides, and uses it to represent the local server that is running.

\pipe: is a fixed "hard coded" string that indicates the pipeline protocol.

\path_name: Pipe name, can be a multilevel directory. General listening is: \sql\query.

Example:

Default instance: \\.\pipe\sql\query

named instance: \\.\pipe\mssql$instancename\sql\query

2. Configure or view named pipes that SQL Server listens on:

Configure the grid protocol for each instance using SQL Server Configuration Manager:

3. Verify that SQL Server listens for Named pipes:

Need to check errorlog:

Named pipe configuration for clients:

Typically does not have a special configuration, the default is turned on, but recommended check

1. Client Network Utility--MDAC Database interface:

Cliconfg.exe, as shown, must ensure that the named pipe name that SQL Server listens on is the same as the default pipe name for client connections.

2. SQL Server Configurationmanager--sql server Native Client: Using the following diagram configuration

3. Use client SQL Server alias:

Aliases are configured at the client and cannot be set once on the server. You can use the SQL Server Configuration Manager or the SQL Server Client Network Utility to manage:

Troubleshooting steps for Named pipe connection issues:

1, use the server-side Network Utility to check the named pipe configuration and confirm that SQL Server is listening to the named pipe protocol.

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.

3. Check the connectivity of the network. To ping a server's IP and server name

4, check whether the client can pass the server's Windows authentication:

Net View \\servername

Net Use \\servername\IPC$

If the two command fails, there is a problem accessing the SQL Server server permissions.

5, to ensure that the client login account has access to SQL Server.

Some common connection problems:

One, most of the clients did not find the named pipe server, SQL Server

[Named Pipes] SQL Server does not exist or access denied.

[Named Pipes] ConnectionOpen (Connect ()).

Workaround:

(1), check network connectivity, such as Ping

(2) Check the named pipe configuration for SQL Server servers and clients

Second, access rights:

Login failed for user ' NULL ' or login failed Foruser Anonymous

Indicates connectivity is fine, just a problem with named pipe access server permissions. Don't forget to ipc$ shares, you cannot use Named pipes without permission to access ipc$. Can run:

NET use \\servername\IPC$ to test

Third, access rights 2:

Login failed for user ' User123 '

Indicates that the user does not have permissions to access the server's resources or to SQL Servers. Not a connection issue, but a permissions issue

To avoid the above problems:

(1) How to view the protocols used when a connection is established. Can be run in SSMs:

Net_Library describes the protocol, and if it is LPC, the representative uses the shared Memory

(2) In addition to using SSMS, the other is an ODBC data source. Run: ODBCAD32.exe.

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.

Third, connection failure check step--TCP/IP

The TCP/IP protocol has two basic things: IP address and port number.

When you add TCP/IP, you need to reboot the server.

For port numbers, you can point to TCP/IP in the Configuration tool, and then select Properties to see the port number on which it listens:

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:

http://support.microsoft.com/?id=832017

Alternatively, you can use the Netstat command to view a listening port: Netstat–an can see the port number in all the system's use.

Client TCP/IP protocol configuration:

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:

Steps to resolve TCP/IP connectivity problems:

Step 1: Verify that SQL Server does listen to the TCP/IP protocol:

The authentication protocol also needs to view errorlog. If you see the following, you are already listening for TCP/IP

If you find that there is no listening, you can use the Server Network Configuration Tool "Svrnetcn.exe" configuration. This tool needs to be downloaded.

Step 2: Verify that the TCP/IP port that the server listens on is the same as the default value for the client configuration or the value specified in the alias:

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.

Step 3: Check network connectivity:

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:

169.254.173.244 MySQLServer

Step 4: Use the Telnet command to check the ports that SQL Server listens on:

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.

Step 5: Check SQL Server access permissions for logged-in users:

As with Named pipes, you must ensure that the client logon account has permission to access SQL Server.

To reduce configuration errors, you can use the following measures:

1. Configure multiple static ports:

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.

2. TCP/IP Port bindings failed:

If the static port is occupied, the following error message appears:

Server SuperSocket Info:bind failed on TCP port 1433.

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:

http://support.microsoft.com/default.aspx?kbid=310099

Example: Protqry–n myserver–p tcp–e 1433

3, check the protocol used by the connection:

Execute the following statement by SSMs:

4, how to access the SQL Server behind the firewall:

Firewalls must be configured to allow traffic from *any* (ports greater than 1024) to 1433, and traffic from 1433 to *any*.

Specific instructions can refer to technical documentation:

http://support.microsoft.com/?id=287932

5. Specify the protocol and port of the connection in Managementstudio:

You can force the use of TCP/IP connection servers to specify ports.

6. Other:

When the above steps are unsuccessful, use the Network Monitor tool to capture network packets for analysis. It can get some other means to find out why. Detailed reference:

http://support.microsoft.com/default.aspx?kbid=148942

Iv. General network error (generalnetwork error)

1. Any step that may occur in the life cycle of the connection:

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:

2, usually only occasionally, or concentrated in a certain time period, and is likely to automatically disappear:

If sometimes not, you need to catch a group of Network Monitor logs, or even Windows kernel tracking, can always locate problems.

3. There are many possible reasons for the problem:

Common causes:

1. Server load:

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.

2. The busy application server does not use connection pooling:

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.

3. Network Transport Layer Problem:

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.

4. Windows operating system level issues:

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.

5. Improper System setup:

Sometimes you can modify some system settings from a security perspective, but being overly harsh can cause SQL Server connections to be unstable. It is common for TCP settings to be

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters under.

The SQL Server layer setting also has two places that can cause Gne errors, all in sp_configure:

L Priority Boost:sqlserver is started with high priority. Settings are not recommended in general.

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.

6. Antivirus software and firewalls:

These hardware and software may lead to manslaughter.

7. Network Equipment:

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.

8. Wrong network card configuration:

On a clustered server, there are at least two network adapters. If the heartbeat line can also access the public network, it is easy to appear gne.

4, Gne situation a lot, but still can do the following operation, slow down or solve:

1. Analyzing network topology and determining reliability of network

2. Involve SQL Server servers and client servers for a full health check to make sure it works properly.

L There is no obvious error in the Windows log

L CPU can not be long time 100%

L Windows cannot have system caching and memory problems.

L SQL Server can not have obvious errors in the errorlog, the key error is: AV, memory, disk, 17883/17884

L SQL Server cannot have a large scope, involving more than 100 connection blocking issues.

L Check sysprocesses system view, do not appear often have a large number of connections in the runnable/running state.

3. Configure SQL Server servers and client servers in the following ways:

(1), disable RSS:

Locate the registry:

Hkey_local_machine\system\currentcontrolset\services\tcpip\parameters\enablerss, set it to 0.

(2), disable Taksoffload:

Locate the registry:

Hkey_local_machine\system\currentcontrolset\services\tcpip\parameters\disabletaskoff, set it to 1

(3), disable TCP chimney:

Input: netsh in tip set chimney DISABLED

(4), disable TCPA:

Locate the registry:

HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\TCPIP\PARAMETERS\ENABLETCPA, set it to 0.

(5), restart the machine to make the settings effective.

(6) To upgrade all the related machine windows to the latest updated version. Network device firmware upgraded to latest.

(7) Check the SQL Server sp_configure inside priority boost and lightweight pooling have been altered.

4. To properly configure your network:

(1), confirm the registration form:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters is the default configuration

(2) Confirm that the NIC is not configured teaming.

(3), in the cluster environment, verify that the two network card configuration is correct.

(4) The function of confirming the automatic shutdown of the idle connection is disabled.

(5), temporarily shutdown anti-virus software and firewalls.

(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.