Question: I’m a partner (one of six) in a small business and am trying to deploy our web site with an attendant web-based application (survey data collection and reporting). The system is being designed in Java and is using SQL Server as the back-end. We were thinking of launching this on a box running MS Small Business Server 2000 (as it would then also obviate a number of other small business software needs, such as email with Exchange server, internet security, etc.). Is this deployment strategy reasonable? Does SBS handle enough anonymous TCP/IP connections to do e-commerce where we don’t know how many people my be trying to connect to our database to buy stuff?
Any help you can provide will be greatly appreciated.
Lee’s Answer:
Great question and my answer, I hope, will help you a bit. There are a couple of things you need to consider and understand first before you can determine if your platform is adequate.
Based on your question here is what I would interpret as your solution:
You are using SQL Server to store your data surrounding your survey data and this will be where you will be reporting from. You may be using Java as your middle tier and connectivity component and possibly your GUI. Since you are using Small Business Server I will assume you are using IIS as your web server.
Whether you can support concurrent connections will be driven by the following factors:
- The Java application / connectivity piece – does it use connection pooling (a term used to describe the ability to perform concurrent connections from different places using the same connection)? By doing connection pooling , you cut down on the number of individual connections, which may be an issue (e.g. I have a 10-user license for my version of SQL Server , so I use connection pooling to reduce the number of connections into SQL Server). Also, connection pooling is generally quicker since you don’t have to spend time establishing the connection and closing it.
- The way your connection / pages have been built. By this I mean the following. Typically what happens is you run some query which gets some data (your survey questions, perhaps) which is then rendered to the web page. Now your application can be built in one of two ways – at this point it could disconnect (ie not hold any resources in the database) until the user has finished typing in their responses and hit the submit button or it may retain its hold on the database, waiting for the user to hit the submit button. If you use method 2, certain things may be an issue since you would create a locking situation. If you use method 1, then this is an optimal utilization of resources since nothing is waiting on anything else, and the next time you worry about connectivity is when the page is submitted to the server.
So, given these items, your infrastructure technology (MS SBS) accounts for 15% of the overhead, while the rest is in the way the person coded the Java app and in the way they wrote the SQL to get to the database. My app is hosted on a Dell Power Edge Server with 1 CPU and 512MB of memory on a 256KB line (admittedly I don’t use IIS – I use Apache for my web server for a number of reasons) and I get fairly good response times since I optimized the SQL Side and minimize the interaction between the pages and the server.
In terms of the data you are describing, I can’t imagine the volume is large (10M+ rows). I suspect you have small amounts of data (depending on whether this is an open ended questionnare or pick the best answer) and the data is valuable for small periods of time during the collection and analysis period, and typically analysis succeeds collection (ie the majority of the reporting occurs after the collection completes). Given that, you have several options available to you. After the survey completes and its reported on, move it off the database into a secondary instance where you can do aggregate reporting on it (a warehouse) which is not available online and then the amount of data on your primary server will remain small. Also, the size of your individual data points assuming its not open ended surveys are probably fairly small, so if your database is designed correctly you should be able to get pretty good performance from what you are doing – assuming indexes are correct and the code is solid.
One thing you may want to try is running the application on a small box and then running the app from that box to see what response you get. If the response is not good, e.g. 5-10 seconds per request (a good recommendation is 2 seconds per page max), then it’s unlikely you are going to get lightning-fast performance when you go to the real thing.
I hope that helps give you a direction. Good luck,
Lee
Lee Gould , President of Rocketrainer (http://www.rocketrainer.com) a revolutionary web based athletic coaching ASP. Lee does database architecture and development consulting for Oracle, Sybase and Microsoft SQL Server. Lee is a co-author of Transact SQL Programming published by O’Reilly and has been a database professional for over 14 years, working on Wall Street for 7 years. Lee holds an MBA from Seton Hall University.
