Design Precaution with .NET Connection Pooling and IIS Virtual Directories

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
A few months earlier, I was setting up a web service library which would contain multiple versions of the same web service. The business behind hosting multiple versions of the same service was that if any changes were made to the web service to alter the input or output then a new version would have to be hosted which would be used as the active version of the service. This strategy took care of backward compatibility in systems that were already consuming the sevice and saved significant cost of quality and testing.

The web services would look something like below in the IIS (real application names have been masked).

IIS Root
|__AppName.ModuleName.WebServices
|__v1.0
|__ServiceName.asmx
|__v1.1
|__ServiceName.asmx
|__v1.2
|__ServiceName.asmx

-Each service version would contain 3 files:
(1) a DLL in the /bin folder
(2) an ASMX file and
(3) a WEB.CONFIG file.
-Each service version would be configured as an APPLICATION in IIS.
-All the services would connect to the same database (Oracle in this case).
-In the WEB.CONFIG file connection pooling would be true and pool size set to 25.
-The total number to allowed connections to the database was set to 300.

Now, there is a technical consideration to be made while this web hosting is done. Everytime a version of the service is hit for the first time, it will block 25 (pool size) connections. So if simultaneously two versions of the service hit the database then 50 connections will be blocked. Theoretically, 300 connections would be able to support only 300/25 = 12 versions of the service hitting simultaneously. This meant that if there is ever more than 12 versions of the same service hitting the database simultaneously, then the first 12 services will succeed. What happens to the 13th request - well I guess it will error - but I haven't tested it out. If you happen to read my article and test it please post the results.

In my case, a workaround was adopted - we simply decided to turn off connection pooling on the earlier versions and keep it active only in the most recent version.

Finding Oracle Tablespace Utilization (Free v/s Consumed space)

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------

Last week I was working on my project when we discovered that space monitoring was not active in our application's database. During the process of addressing this problem, I figured out how we can easily query the database to get a report on various tablespaces and their free space. The query I used is provided below (the results are in MBs), the Oracle version I used was 10g.

SELECT
tablespace_name,
SUM(space)/1024/1024 "Size MB",
SUM(free)/1024/1024 "Free MB",
(1-(sum(free)/1024/1024)/(sum(space)/1024/1024))*100 "% Used"
FROM
(
SELECT
tablespace_name,
sum(bytes) space,
0 free
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT
tablespace_name,
0 space,
sum(bytes) free
FROM dba_free_space
GROUP BY tablespace_name
)
WHERE TABLESPACE_NAME like UPPER('%')
GROUP BY tablespace_name
order by (1-(sum(free)/1024/1024)/(sum(space)/1024/1024))*100;