You need 2 servers and a load balancer. Here's in steps:
- Turn all traffic on Server 2
- Deploy on Server 1
- Test Server 1
- Turn all traffic on Server 1
- Deploy on Server 2
- Test Server 2
- Turn traffic on both servers
Thing is, even in this case you will still have application restarts and loss of sessions if you are using "sticky sessions". If you have database sessions or a state server, then everything should be fine.
In most cases connection pooling problems are related to connection leaks. Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize()
method.
You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open
and Close
throws an exception:
var connection = new SqlConnection(connectionString);
connection.Open();
// some code
connection.Close();
The correct way would be this:
var connection = new SqlConnection(ConnectionString);
try
{
connection.Open();
someCall (connection);
}
finally
{
connection.Close();
}
or
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
someCall(connection);
}
When your function returns a connection from a class method make sure you cache it locally and call its Close
method. You'll leak a connection using this code for example:
var command = new OleDbCommand(someUpdateQuery, getConnection());
result = command.ExecuteNonQuery();
connection().Close();
The connection returned from the first call to getConnection()
is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.
If you use SqlDataReader
or a OleDbDataReader
, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.
This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:
- Run
sp_who
or sp_who2
. These system stored procedures return information from the sysprocesses
system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
- Use SQL Server Profiler with the SQLProfiler
TSQL_Replay
template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
- Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
- Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.
Best Answer
Reposted as an answer rather than a comment, was having a stupid moment there!
If you can't have downtime, then the best thing is to have another copy of the server running that you can divert users to when you want to update the primary. Then you divert your users back to the primary after the update.
You can use the State Server sitting on another server to ensure that any session state is maintained when they switch from one to the other.
We are currently experimenting with Microsoft's Web Farm Framework, which seems to do this kind of thing very well.
Our setup involves a front end server, a primary and secondary web server, and a separate state server. WFF keeps copies of web apps in sync on both machines and state server ensures that if a user switches servers between requests (or their current server goes offline), that they should not notice the change.
To upgrade the primary, take it out of load-balancing which will divert all of it's requests to the secondary server. Do your upgrade, put it back in to rotation, and then repeat the process with the second server.