Connection Pooling for Commerce Server 2007 and 2009

With as little as a couple hundred unique visitors on a website, we were seeing close to 300 SQL logons per second coming from Commerce Server. Every hit to SQL was creating a new connection. Luckily, this is a quick and easy fix to get Commerce Server to start reusing open connections to SQL by using connection pooling.

Take into account though, if you are using impersonation on your Commerce Server website then this is a security risk. If a one user reuses the SQL connection of a different user, they can assume the security rights of that previous user. See http://msdn.microsoft.com/en-us/library/aa545755%28v=cs.70%29.aspx for more information on those risks.

To enable connection pooling:

If you are using the 32-bit version of Commerce Server, change the value of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Commerce Server 2007\Profiles\UnsafeAuthenticatedConnectionSharing registry key to 1.

 

If you are using the 64-bit version of Commerce Server, change the value of the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Commerce Server 2007\Profiles\UnsafeAuthenticatedConnectionSharing registry key to 1.

 

Then, reset IIS

 

These registry keys are valid for Commerce Server 2007 and 2009, despite the registry path saying 2007.