MSSQL connection string, do you really understand?

Original: MSSQL connection string, do you really understand?

When I first interviewed a few years ago, the examiner found out I was a newbie. So he asked me to write a connection string, although I knew at the time that the X type of connection string, but then did not write to one, after working for many years I still can not write a correct connection string, but I know when to send new people, It's a good idea to have him write a connection string.

The following is a commonly used string of ADO connection MSSQL
"Data Source=.;i Nitial catalog=mydba;integrated Security=sspi "
In the IIS environment, we might write this.
"Data Source=.;i Nitial Catalog=mydba; User id= username; pwd= General I do not tell him;

use efficient protocols (although we have been doing this, but have not noticed)
First of all to understand how we use the MSSQL Database service communication, of course, the advantage of Microsoft products is that even if you do not know, he will help you choose the most appropriate way, in the case of IIS and MSSQL server, the default use of shared memory.
Figure 1)

To confirm how ADO is built, the connection can be through, management-and process information in Enterprise Manager

You can see the ADO provider,. Net SqlClient Data Provider

Use LPC (local call, shared memory)

If you change the connection string above to "Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi "
Note that there are multiple SQL2000 instances installed on my computer, one of which is sql2k, after opening a connection using the connection string above, and then looking at the process information, it turns out that LPC is now TCP/IP (note that the. Net SqlClient Data provider corresponds to the row) This indicates that now that you have TCP/IP enabled to connect to the database,
Of course, in the case of IIS and SQL Server, it is recommended that you use LPC so that the speed and performance are superior.

using ports in the connection string
In general, we use the TCP/IP protocol, the default port is 1433, sometimes you may want to change this port, you need to use "Server Network Utility" (under the beginning->microsoft SQL Server), configure TCP/IP Protocol and specify a new port.

This specifies 8888 new ports for instance SQL2K, and note that you should not hide the server (HSelect, save your changes and connect to the following connection string.Data source=127.0.0.1,8888;initial catalog=mydba; User Id=sa ", also in the process information you can see a TCP/IP connection with the user named SA. But when we try to connect to this SQL Server remotely, there is usually a problem with a firewall on the general server and only open web,ftp, A few limited ports such as Remote Desktop, and you specified above 8888 is not in its column, so when you are offsite to connect to the database will be error, ( Additional connection string such as "Data source=60.188.86.49,8888;initial CATALOG=MYDBA; User id=sa;pwd= ", note that the port number is separated from the IP or domain name with a comma", " To solve this problem you need to open port 8888 in the firewall.
When you're done, you can access the database using the ip+ port above, but before you use the above connection string, have you tried a similar
"Data source=www.wow52.cn\sql2k;initial catalog=mydba; User id=sa;pwd=; "The form of the connection string, where the domain name plus the instance name of the look, you may try to find that it does not work, anyway, I did but found that no, although the form is very intuitive, but how can not?
Of course, if you are careful, look back and see a "Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi "form of the connection string, this is the form of the ip+ instance, which is essentially the form of a domain + instance, so why not? The problem is also on the firewall, but this time you need to open a UDP port 1434 to use for services running SQL Server Resolution Protocol (SSRP). When you use domain name + instance name access, the parsing service resolves the database connection as a ip+ port. Now that you are viewing the process information in Enterprise Manager, you can see a TCP/IP connection,
SQL Server Resolution Protocol is superseded by SQL Server Browser in SQL2005, where a remote connection is required and in the form of a domain name or ip+ instance name, you need to start the service and open the appropriate port in the firewall. Finally about the port section, there is a point is SQL2000, with SQL2005 more support dynamic port, that is, the port is set to 0 (SQL2005 specific reference Configuration tool), this time to make the database in a remote place to properly access, You need to add the Sqlserver.exe to the firewall in the form of add-on, such as the program location of the instance SQL2K is
D:\Program Files\Microsoft SQL Server\mssql$sql2k\binn\sqlservr.exe, depending on your installation location, SQL2005 can be viewed in the Server Network Configuration tool. In addition to adding sqlserver.exe programs, you also need to open 1434 UDP ports, or C:\Program Files\Microsoft SQL server\90\shared\ when using SQL2005 Sqlbrowser.exe added to the firewall, Sqlbrowser.exe is also using the 1434 UDP port.

None of the above is in my MSSQL2000!

If you are unlucky enough to have this situation, the most important thing is to check your SQL version, run the select @ @Versionin the Query Analyzer, or see "About ..." in the Help menu. If the version is not 8.00.2039 ( now talking about SQL2000), then hurry up and get the next SQLSP4 to play.
If patching doesn't help, make sure you've enabled the TPC/IP protocol in the SQL Server Network Utility and opened the relevant ports in the firewall.

Some properties in the connection string

Connection Lifetime ConnectionSurvivalTime, the default is 0, which means infinite length, in seconds. In a SQL cluster environment, we set the value to specific.

Connection Timeout ConnectionBuildTime, the default is 15 seconds, popular speaking when you Conn. Open wait 15 seconds after the connection is not successful, then will throw a connection timeout error, which is why other errors come so suddenly, and the database connection time-out error, you have to wait for 15 seconds reason.

Pooling Use connection pooling, default is on, use Pooling=false to prohibit.

Min Pool Size,max pool Size By default, the minimum is 0, and the maximum is 100.

About the pooling property is turned on by default in all of the connection strings above, connection pooling can effectively improve database access performance, because creating a connection requires a lot of resources, especially when offsite access (commonly referred to as LAN, preferably not across the Internet database access) more so, So you do not need explicit prohibitions in most cases (perhaps you don't care that Microsoft has been helping you), in fact, in the rare case where you use Conn.Open (), Conn.close (), instead of actually opening a connection, take an available connection to the connection pool and return a connection, The Conn.Open operation creates a connection only if no connection is available in the pool, but if the number of available connections in the pool reaches max pool size, he will no longer create a new connection, but wait for the arrival of an available connection, which is connection timeout. Of course, your conn. The connection pool involved in the open operation depends on your connection string, such as:
"Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi "
And
"Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi; ", pay attention to the back more;"
will open two separate connection pools, and ADO determines whether strings are the same based on a 2-binary comparison of strings, so any change (space, case) will be judged as a different connection string.
Have the following code
-----------------------------------
String Strconn= "Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi;";
for (int i=0;i<10;i++) {
SqlConnection conn=new SqlConnection (strconn);
Conn. Open ();
Conn. Close ()
}
With
String Strconn= "Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi;";
for (int i=0;i<10;i++) {
strconn + = ""; Space
SqlConnection conn=new SqlConnection (strconn);
Conn. Open ();
Conn. Close ()
}
Two pieces of code run separately, look at the management-----process information, Enterprise Manager (refresh first), you can see that only one process is enabled earlier, and then 10 processes are enabled, and each process corresponds to one connection. Previous Code 10 open +close () The operation uses the same connection in the same connection pool, and the second one opens 10 connection pools, one for each connection pool.

And look at the code below.
for (int i = 0; I <=; i++)
{
Thread t = new Thread (new ThreadStart (Command));
T.start ();
}
---------------------
Command definition
private void Command () {
String Strconn= "Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi;";
Sqlconnection conn=new Sqlconnection (strconn);
SqlCommand cmd=new SqlCommand ("a long-running query", conn);
Conn. Open ();
Cmd. ExecuteNonQuery ();

Conn. Close ()
}
After running, you can see that the process information--management-------The 10 connection process to the MYDBA database is opened, but it is important to note that these 10 processes belong to the same connection pool, in order to prove this you can change the above connection string to
private void command () {
String Strconn= "Data source=127.0.0.1\sql2k;initial catalog=mydba;integrated
Security=sspi; Max pool size=5; "; Added Max Pool size=5;
SqlConnection conn=new SqlConnection (strconn);
SqlCommand cmd=new SqlCommand ("a long-running query", conn);
Conn. Open ();
Cmd. ExecuteNonQuery ();

Conn. Close ()
}
As a result, he only opened 5 connections to the MYDBA database, which proves that they are the same connection pool. Of course, the prerequisite for a successful demonstration here is that the CMD executes the task time in 15 seconds, or else the 5 thread connection time-out occurs, and the other uses the SQL2000 tracking tool ( SQL2005, SQL Server Profiler) is a great tool for you to observe the above experiments!

MSSQL connection string, do you really understand?