Share SQL DB Among Dozens of Sites

[Guadala Harry]

I'm looking at taking on a project that would require dozens of ASP.NET
applications (on different servers) to share one centralized SQL Server 2000
database. Unfortunately this won't all be inside a nice big corp intranet
where we have lots of control over server placement and access; rather, it
will be hosted on the Internet by some hosting provider yet to be chosen.

I'd appreciate some suggestions for the data access layer and where it would
best be physically located and accessed. AS I understand them, the options
are basically:

1. distribute the DAL (i.e., have multiple copies of it) in an assembly
installed locally with each ASP.NET application. In this case, each ASP.NET
application/site would communicate directly with the SQL Server (which would
require no firewall interference with such communication).

2. centralize the DAL (i.e., have one copy of it) in one assembly installed
in on one server that is accessible from each ASP.NET application. In this
scenario, each ASP.NET application would work with the DAL via .NET
Remoting, and the DAL components would interact with the SQL Server on
behalf of all of the ASP.NET applications.

3. another option just like #2 above, but using a Web service in place of
Remoting. I've at least dismissed this option due to performance
considerations (Remoting would be faster given the .NET - to - .NET
communication).

Any feedback would be greatly appreciated regarding the trade-offs between
options 1 and 2 - given the consideration that this will all be installed on
some Internet hosting provider's network.

Thanks in advance.

 

[Peter Bromberg [C# MVP]]

I'd use option #1, (the most common scenario) unless you have a very
specific reason for wanting to use Remoting. Another reason for this is that
you describe your setup as being hosted by some hosting provider, and
working with them to get all your remoting configs and infrastructure set up
could be frustrating.
--Peter