Why do you need lots of SQL Server instances? You specify the name of the server in the connection string and then connect to it. If you have COM, you still have to tell your application what server the COM component is on and then the COM component still needs to know what server to use. I really think you're overcomplicating this.

If you're using .Net, you're always using ADO.Net to access data. What you are talking about comparing is the .Net Data Provider compared to ODBC. I don't think you'll find significant speed differences. The advantage comes from the .Net data provider being written in managed code. If you're using .Net, you should always choose the .Net data provider if one is available.

And as for rewriting in ASP.Net, don't rule out MVC.

>No, unfortunately I mean classic ASP, which is what our website front end is written in. We are eventually going to move this to asp.net but are not at that point yet.
>
>On the VFP side everything is written in straight native VFP, no ODBC. Because some of our clients are hitting the 2 gig limit on their history tables, we want to move these to SQL but we don't have the time or money to do a full conversion of our system. So we are looking at doing JUST the history tables and keeping the main tables native VFP. We ALSO have different history tables on our web side that we also want to move to SQL. These will be accessed BOTH by classic ASP and VFP COM.
>
>The reason we are thinking about building a .NET middle tier COM is that this would be able to be called by VFP or Classic ASP and it could be called from anywhere. If we went straight ODBC, we would be stuck installing SQL on every server (and virtual server) since we data warehouse most of our clients, whereas if we built an .NET COM that uses ADO.NET instead of ODBC, we feel that this would allow us the flexibility to have one or two main SQL servers that we would need to access. It would also START us to moving our business logic to a middle tier and we can stick our toe in the migration pool. Also, we are going to be doing some benchmarks to see how ODBC and ADO.NET perform. Our guess is that ADO.NET will be much, much faster on large databases.