Database Connection Pooling Behavior For Multiple IIS Sites.
Mar 28, 2006
How does database connection pooling work for multiple "copies" of the same web application on the same server? My IIS 5 win2K server has 32 sites, all pointed to the same code. Each site responds to a different IP/URL(www.a.com, www.b.com, etc.) for SSL reasons. DB is SQL 2000.
Based on perf stats, as a new site spins up, a new pool is created. So when I hit site 1, I see 10 pooled connections. Site 2 bumps it up to 20, etc. However, I don't see it go up to 320, so it isn't linear.
BTW, I'm trying to build a case for having 1 site/application that all URL's point to in order to conserve memory, connection pooling, caching, etc.
Any help is greatly appreciated.
-Allen
View 2 Replies
ADVERTISEMENT
May 3, 2007
My application works with the Connection pooling ODBC. Everything works well, when it data base this down the SQLConnect function finishes steeply my application.
View 3 Replies
View Related
Feb 23, 2008
hi all. my question is simple: can i have 1 sql data base for multiple sites?
View 3 Replies
View Related
Oct 5, 2007
I am trrying to figure out what the best replication would be to use and or setup...
Her eis the current goal and structure..
We are just moving over to a new custom POS system that will be using SQL databases....We have have three locations and we want each location to be independent in case of network connectively failures to our primary location.
Basically, all three locations will be running SQL server 2005 and the POS app...
We want replication to occur overnight, so that each location will have the other locations transactions from the previous days, etc...
Essencially I want all three locations to "syncronize" their data every night....basically two-way replicaiton between all three sites...
Master Site will have say databaseA that the local POS system will use
Sencond Site will have say databaseA that the local POS system will use as well
Third Site will have say databaseA that the local POS system will use well...
Any thoughts or Ideas will be helpful
Thx
Martin
View 1 Replies
View Related
Mar 13, 2002
Hello all, I am in the process of doing some research and thought I would query you guys (and gals) for your opinions. I have never setup replication and my company is bringing up multiple sites, multiple GIS servers with one master GIS server. They want them to be replicated. Network bandwidth throughout the day is a concern and I may have an option to run replication at night. All servers will be continuously connected and they tell me that each site will be owners of it's data so data conflict should not be an issue. I originally thought qued-updating subscribers and transactional replication but it sounds like that does not work if the servers will always be connected. Any opinions on this anyone? Also, keep in mind that this is GIS data so there will be pretty big blob files in the database. Thanks in advance. RayH
View 1 Replies
View Related
Mar 28, 2007
Is it possible to host more than one site on the server that hosts reporting services?We have a web app that is accessed through intranet. This site is hosted (IIS)on the same server as the sql server 2005 database that is used as the backend. Is it possible to run reporting services from this same server? or does the reporting services default site require to be the only site hosted?
View 1 Replies
View Related
Feb 21, 2007
When i run this
sp_who
I see the connection showing up and in the next second it is gone.
The connectionstring i have is
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;pooling=true;Min Pool Size=2;Max Pool Size=50;Connection
Lifetime=120;Initial Catalog=test;Data Source=TESTMACHINESQLEXPRESS;
And then in the log, the log grows almost every second 3 entiries !!
"Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...."
"Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...."
"Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...."
So the problem is that pooling does not seem to work. I dont see any
connections being held for more than 1 second
View 1 Replies
View Related
Sep 28, 2007
I am looking for some guidelines for SQL Server connection pooling.
I would like to know the number of recomended connections for a SQL server based upon number of CPU's and or memory and I want to monitor or check the connection pooling? I would also like to know any guidelines around SQL server connection pooling.
View 2 Replies
View Related
Dec 22, 2005
Hi,I'm using ASP 3.0 and ADO with a web based MS SQLServer 2000 database.Webpage forms collect criteria for SQL queries (dynamic SQL?). These are mostlySelect queries to return inventory lists. Inventory details are posted bymeans of Insert Update and Delete queries. All connections use the sameconnection string. A new connection is opened each time the data is accessedand closed and set to null/nothing afterwards. If site traffic becomes highwill connection pooling be handled automaticaly (by MTS)?Many thanksRay Allison
View 1 Replies
View Related
May 14, 2007
I have created an instance of sqlconnection and using transaction i am executing 5 insert statements (Database - SQL Server 2005). I have the finally block where i call the dispose method of the transaction and close method of the connection object.I often get the below mentioned error:
Exception Type: System.InvalidOperationException
Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)
Source: System.Data
How do i resolve the above issue?
View 8 Replies
View Related
Feb 25, 2008
Hi
I have a Community Server Forum in my site with a 2500 hit per day and I confused with Connection Pooling.
Is it necessary for my site to use Connection Pooling? If it€™s necessary, what is the best value for following items in order to maximize performance?
Pooling
Connection Reset
Enlist
Max Pool Size
Min Pool Size
Connection Lifetime
Thank you very much.
View 1 Replies
View Related
Jul 23, 2006
If I have two ASP.NET applications installed on the same machine, and both are using the same connection string to access SQL Server 2005. Are they share the same pool or use separated pools?
I need to be sure that how many connections would be connected to my database server. For example, the max. connection pool size is 100, if both Web app share the same pool, then there're may be maximum 100 connections to database. It they use separated pools, then connections may grow to 200.
View 4 Replies
View Related
Dec 1, 2006
I've got a function that returns a connection to a DataReader:static public SqlDataReader GetDR(string sql, string connString)
{
SqlConnection myConn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, myConn);
SqlDataReader dr;
myConn.Open();
dr = cmd.ExecuteReader();
return dr;
}I call it like so:SqlDataReader dr = PAS.GetSqlDataReader.GetDR(sql, connString);And I'm always careful to close the connection when I'm done using it via dr.Close(). Except I just started noticing that when I run my app andstart doing a fair amount of work around it - I very quickly get a timeout that I've run out of connections. D'oh! I've triple checked my code andverified that I always remember to close my connections. It's there - every single time!So I'm obviously missing something. Should I not be using a function like GetDR since it doesn't close the connection within the function?
View 4 Replies
View Related
Jan 16, 2007
Hello, I have a question about Connection Pooling. I use the following Code on three defferent machines with the same result. No machine has any special configuration. OS is Win XP Pro Sql Server 2005. One Server has Windows 2003 Web Edition with Sql Express 2005. 1 for(int i=0; i<500;i++) {
3 SqlConnection con = new SqlConnection("Data Source=127.0.0.1\DB;Initial Catalog=Test;User Id=sa;Password=secret;Connection Lifetime=30;Pooling=true;");
4 SqlCommand cmd = new SqlCommand("SELECT TOP 1 * FROM test", con);
5 con.Open();
6 IDataReader reader = cmd.ExecuteReader();
7 reader.Close();
8 con.Close();
9 con.Dispose();
10 }
11
When monitoring The Counter logins/sec in Perfmon logins/sec getsup to 500 per second when i call this method (everytime!). MSDN says when logins/sec does not stay zero after initial call, pooling does not work. Can you tryy it on your machine an tell me what experience you make? Does Pooling really does not work?
View 5 Replies
View Related
Feb 16, 2007
Hi, I'm trying to optimize an ASP.NET 2.0 app where, with a load of 100 users, the CPU usage on the web server is a reasonable <20% but the db server (SQL Server 2005) often goes up to 100% and never below 20%. When viewing the Performance Monitor counter SQL Server :General Statistics/User Connections this shows that there are 50-65 connections. When the number of connections go up the db server's CPU usage also goes up along with web page response times.When viewing the Sql server Activity Monitor it, for all connections, always says that the Login Time and the Last Batch are the same. This has got me wondering if no connections are pooled and that all connections always are closed and reopened and that would explain why the db server is so jammed.Could someone give me some pointers as what to check to find optimization points. Regards,Mathias
View 2 Replies
View Related
Jun 9, 2007
There's always been a lot of confusion about what prevents connection pooling -- when I was learning .Net 1.0, I was told that any differences in connection strings -- even cosmetic ones -- would prevent pooling (or, more accuratlely, that they could only share connections with identically formatted connections). For example,
conn string1 =
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
View 2 Replies
View Related
Jun 10, 2008
I want to know what is min size ,max size,Connection Timeout and Connection Leak in connection pooling i have searched it on Google i found this below answer but still i have a confusion Please explain me in simple language with example"Setting
an initial size other than zero can reduce the start time of the client
applications because it ensures that a certain number of connections
are always available. That could be an issue at around 09:00 in the
morning when all users start their applications and connect to the
server at the same time.
Setting the maximum size of the connection pool is
usefull if you suspect that you have blocking problems due to the large
number of concurrent queries. In this case you may be able to reduce
blocking by limiting the maximum size of the connection pool. Of
course, the pool already limits the number of concurrent connections,
so you may never encounter this problem"
View 2 Replies
View Related
Jan 4, 2005
Can anybody help me to solve this problem
I have a web application. In this app i have one class genral for database transections. I create new object of this class on every page in page load event & dispose it on UnLoad event. After sometime this app gives connection pooling exception.
Means connection are not closed, but i disposed the object like
tmpDatabase.Dispose()
tmpDatabase = Nothing
Plz can anybody tell me the mistake i m doing.
Thanx
View 4 Replies
View Related
Nov 7, 2005
Hi,
I was asking in one Technical Test, the question is not exactly like,
but i want post the outline of that(because i don't remember the exact
one)
Suppose u have connection with Sql Server database, and let say some 30
users are already connected (through some aspx page), if one more new
request is coming, is it created new path or it will reallocate or
destroy existing connection poll and assign??
Can anybody tell me?
if possible please post some of Basic and Adavanced questions regarding Sql Server (with aspx)
Thanks
Jen
View 2 Replies
View Related
Jun 27, 2001
Hi everyone
I am using VB5 SP3 and ADO 2.5 to connect to SQL Server 7 SP2 and I'm trying to test connection pooling.
I open three ADO Connection objects as follows:
Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=pubs;User ID=sa;Password=;OLE DB Services=-1"
The second and third connections open virtually instantly, which seems to indicate that pooling is working, however, when I list processes in SQL Server it shows one process for each separate ADO connection. I thought the whole point of pooling was to make all these ADO connections map to the one physical connection and therefore one process. What is going on?
Just to make sure, I also tried it with RDO 2.0 and ODBC connection pooling and I get exactly the same results.
I'm very worried about this because it could result in our using up twenty or so SQL Server connection licences per workstation instead of one.
View 2 Replies
View Related
Jul 20, 2005
Hello:How does one pool a connection object? I have the same application runningon 4 different machines, all connecting to the same server/SQL Server 2000instance for DB activity.Some posts have mentioned pooling the connection objects to reduce overhead,but how do I do that for the 4 separate computers.Appreciate any response.Regards,Ryan Kennedy
View 1 Replies
View Related
Jul 20, 2005
Hi,Is there a way to control the connection pools using SQL Server 2000? Iknow how to alter the connection string to dothis(http://msdn.microsoft.com/library/d...ry/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider. asp), but I amlooking for a setting in SQL Server that allows me ultimate control over allconnections to the DB. eg: I'd like all connection pools to timeout after5min of inactivity, set the maximum pool size, etc. I am assuming that thereis some part of the GUI that allows this, or maybe even some (ugh) registryediting! ;-)Thanks,Lance
View 1 Replies
View Related
Feb 15, 2008
We have an large SQL database being accessed by a C# application. Each hit on the database opens a connection then closes it when done. We are currently experiencing errors when more than 20 users access the database at one time. One of the things I am wondering is if we are using connection pooling. From BOL I am lead to believe connection pooling is off by default. Can I turn on connection pooling as a default in SQL Server or do I pass 'pooling=true' as a parameter on each connection string?
Thanks.
View 5 Replies
View Related
Apr 2, 2008
Hello,
I use IIS 6.0 with php and the authentication method is the €śintegration windows authentication€?
I have a problem i d€™ like to use odbc connections pooling to access my database, however the authenticated user must be member of the administrator group to use this feature.
Best regards
sltex
View 3 Replies
View Related
Oct 23, 2007
Hello,
I tried without success to activate ODBC connection pooling for an ASP (classic) application running on Windows Server 2003. I used the ODBC administration applet and created the registry value described in "INFO: How to Enable Connection Pooling in an ODBC Application".
What are the steps to correctly activate ODBC connection pooling with IIS 6?
Thank you for your answers,
Radu
View 2 Replies
View Related
Apr 10, 2007
Hi
I would like to find out, what is the actual formula to set the right amount of connection pool size in a conn string. In my scenario, when 400 concurrent users are logged into the website, SQL Server starts timing out. I am thinking the following connection string should fix it, but I would appreciate if I can get some tips to optimize connection pooling. I really need to find out the criteria or the formula to set connection pool size in a connection string. I got the following conn string over the web, by just searching for optimized connection pools, but I am not sure if this will work. Also please explain max and min, and how what criteria do i use to set that size, I mean what does in the following example min pool size of 5 and max pool size of 60 means. Thanks.
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!
View 3 Replies
View Related
Apr 10, 2007
Hi
I would like to find out, what is the actual formula to set the right amount of connection pool size in a conn string. In my scenario, when 400 concurrent users are logged into the website, SQL Server starts timing out. I am thinking the following connection string should fix it, but I would appreciate if I can get some tips to optimize connection pooling. I really need to find out the criteria or the formula to set connection pool size in a connection string. I got the following conn string over the web, by just searching for optimized connection pools, but I am not sure if this will work. Also please explain max and min, and how what criteria do i use to set that size, I mean what does in the following example min pool size of 5 and max pool size of 60 means. Thanks.
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!
View 1 Replies
View Related
Mar 26, 2008
Hi,
I posted the long version of this over on the ADO.NET framework group:
http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/51597180ec77f543/70e804505c2df829#70e804505c2df829
The short version is:
In .NET 2.0 SP1, Microsoft have deprecated the "Reset Connection" connection string API which, when set to false, stops connection pooling from calling "sp_reset_connection" on connections drawn from the pool. We need this for our DB security: we "EXECUTE AS" on connection open and since we cannot tell when it will close (passed to 3rd parties and there is no useful "closing" event), we must "REVERT" the connection next time it is drawn from pool e.g. OPENS. We must do the revert before sp_reset_connection is called or SQL Server kills the connection (security violation) - we accomplish this by keeping track of connections' SPIDs. We implemented this using EL DAAB + custom SQL provider which handled connection open event. Now it is entirely broken!
This all worked fine in .NET 2.0 RTM when we could control when sp_reset_connection was called (could delay until after revert). Now I've run out of options and will have to rewrite the entire security infrastructure. What I'd really like is a decent "closing" event on SqlConnection (statechanged will fire after close - too late!). As I asked in the full post above.. how are we expected to build scalable apps with the features that Microsoft tell us to use when they don't work together?
How can I REVERT my connection before it is sent back to the pool?
If any of this isn't clear, please see above linked post.
Many thanks,
Simon
View 1 Replies
View Related
Jun 14, 2008
My total test page is shown below. I monitor the connections by SP_WHO2. Without the second call, connection pooling seems to be working, ie I refresh my browser repeately but the number of connections as seen from SP_WHO2 does not increase.
However, if I have the second call, every time I refresh the page at the browser, the number of connections increases by one. This is obviously not acceptable in a real world application.
I tried both Integrated Authentication (with no impersonation) and using a hardcoded service account. Both have the exact same results. In fact this test is not about multi-user yet, it is the same single user just refreshing the same page.
May I know what have I done wrong? All the documentation from Microsoft says close the connection after using it. In the case of SqlDataSource how do I close the connection?
Thanks
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource1.SelectCommand = "Select ID from Master";
System.Data.SqlClient.SqlDataReader reader =
(System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
if (reader.HasRows && reader.Read())
Label1.Text = reader["ID"].ToString();
SqlDataSource1.Dispose();
//second call: read from another table
SqlDataSource1.SelectCommand = "Select Name from Students";
reader = (System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
if (reader.HasRows && reader.Read())
Label1.Text += reader["Name"].ToString();
reader.Close();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
DataSourceMode="DataReader"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
View 2 Replies
View Related
Mar 30, 2007
Guys,
We have 2 main database servers, with our clients accessing either one DB or another via a website click. Our application, in the background, chooses randomly, which server to connect to. This approach achieves simulated load balancing.
For efficiency purposes, we also have the application manage connection pooling.
Recently, we realized that our current "simulated load balancing" is not good enough, as it blindly chooses which server to connect to, without considering the current workload of that server. We are now considering to acquire a load balancer [i.e. hardware piece] so that the load is balanced more wisely, by it. Our only concern is connection pooling. Will connection pooling still work with the new, proposed topology [i.e. applications sit to the left of the load balancer and the db servers to the right of it, with the load balancer in the middle].
If anyone has had any experience with this and could share or give advice, it would be appreciated.
Thanks a lot!
View 2 Replies
View Related
Jul 23, 2005
I have an ASP (IIS 4.0) based website fronting a SQL Server 2000database. I am trying to avoid using dynamic SQL for queries, and I amconsidering prepared statements as an alternative...All the advice I have seen strongly suggests that connection objectsshould not be explicitly cached, either at the application or sessionlevel. Therefore, I am creating a connection for each page (sameconnection string), and closing it immediately after use. By myunderstanding, this is utilising the MTS connection pool and is themost resource efficient method to use.However, I have seen a number of comments to the effect that connectionpooling and prepared statements do not mix.For example..."Avoid creating prepared statements or temporary stored procedures.These create server resources attached to a specific connection.Because you might not get the same connection from the pool the nexttime you ask for one, you lose the benefit of creating them in thefirst place. Worse, because the connection might reside in the pool fora long time, it can get clogged up with the useless but expensiveserver resources created by each "user" that creates them." - RobMacdonald(http://msdn.microsoft.com/library/d...00/html/Rob.asp)Is this still the case? This article was written in 2000, and I washoping there was now some workaround for this. Are there any SQLServer/driver level settings that can fix or alleviate this problem.Are there any other options besides prepared statements or storedprocedures? Thanks.
View 2 Replies
View Related
Mar 15, 2008
Hi
I have a DLL deployed in SQL2005 which uses Thread pool. Since DLL exists in DB we can directly access it using context connection = true.
In my case the following works fine, if i execute the same out side Threadpool
SqlCommand cmd = new SqlCommand("select 1...", connection);
SqlContext.Pipe.ExecuteAndSend(cmd);
When i execute inside threadpool, either by passing opened connection object, or tried to open connection to "context connection = true" i am getting following error.
System.InvalidOperationException: The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code. at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_Pipe() at hiqidty4.hiqidty4.Identify_Search(Object identifyin)
Let me know if i am missing any more here
Thanks in advance
View 1 Replies
View Related
Mar 5, 2008
Hi All,
I've written a script component that essentially uses a connection manager to connect to a database and executes a stored proc to return a retail price for a product sold in a store. This works fine with small volumes of data. (50 or so executes) When I use larger volumes of data (4500 executes, eventually will be 2.5 million) I get connection pooling errors as follows:-
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
This I can fix on the 4500 data volumes by setting the connection pool to 10000.
My question is... is this the correct way to fix the error, or is there an underlying problem that needs to be sorted? My script code is as follows.
Public Overrides Sub SalesData_ProcessInputRow(ByVal Row As SalesDataBuffer)
Dim InSightConnectionManager As IDTSConnectionManager90 = Me.Connections.InSight
Dim InSightConnection As SqlConnection = CType(InSightConnectionManager.AcquireConnection(Nothing), SqlConnection)
Dim RetailPrice As Decimal
Dim SQLCmd As SqlCommand
SQLCmd = New SqlCommand()
With SQLCmd
.Connection = InSightConnection
.CommandText = "upDWGetRetailAtTimeOfTransaction"
.Parameters.Add("@StoreID", SqlDbType.Int)
.Parameters.Add("@Barcode", SqlDbType.Char = 13)
.Parameters.Add("@TDate", SqlDbType.DateTime)
.Parameters.Add("@RetailPrice", SqlDbType.Decimal).Direction = ParameterDirection.Output
.Parameters("@RetailPRice").Precision = 19
.Parameters("@RetailPRice").Scale = 5
.CommandType = CommandType.StoredProcedure
End With
With SQLCmd
.Parameters("@StoreID").Value = Row.StoreCode
.Parameters("@Barcode").Value = Row.ScannedEAN
.Parameters("@TDate").Value = Row.TransactionDate
.ExecuteNonQuery()
RetailPrice = Convert.ToDecimal(.Parameters("@RetailPrice").Value)
End With
Row.RetailPrice = RetailPrice
End Sub
Thanks in advance for any help
View 8 Replies
View Related
