Design modification for Large MS SQL database

I need help designing the data layer of a web service that uses SQL 2000 standard edition. Since the data is spread among 53 databases (on the same server), queries across the databases are getting a little complex. I am looking for someone to help design a solution. I need someone to take a look at my database structer, analyze it, make recommendations, and write a short document on how to best proceed with web service and stored procedure logic. Details: I have a large data set which is spread across 53 databases (and growing). Each database is about 1 gigabyte in size including indexes. Each database has one table and about 4 million records each. I am not using the Enterprise version of SQL2K, just the standard version. So federating is out. In the past, I had issues with tables becoming corrupt and having to constantly restore from backup. This would cause the web site to be down as a result. Now I have moved to a new server and have divided up the databases so that one month's data is in each database. This seems to be more stable. But creates other complexities (user management, maintenance plans, etc...). This makes it easier to take one month's data offline for repairs without effecting the rest of the data set. It also allows me to detach one database, copy it to my ftp site, and reattach it and have the ability to easily ship the database to other users. I am also creating a "mirror" of the data set on a second server. My goal is to make it so that the data is always accessible. When one server is down, the other is "hot" and available. The web sites will be using round robin DNS so that they are also duplicated. Each web site will know the location of the primary and secondary database. So if the first query fails, it will try the second data source. continued in Platform... ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows? (depending on the nature? of the deliverables): a)? For web sites or? other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software? installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform ASP.Net 2.0, MSSQL 2000, Web Services, Design (continued) There are also desktop applications that will need to access the data. So I think I need to create a web service first, and then have the ASP.NET web site and the .Net applications consume the web service. So in order to be redundant, the web service also needs to be mirrored on both servers. Each database contains one month's data. Some queries just want the data from one date. But some queries want all of the data for one entity for a whole year, so I have to join the data across multiple databases. I need someone who has gone through this type of scenario to make recommendations about what logic should be in which layers. What make sense to put into a stored procedure and what should be put into the web service? Also if there is a better way to do this, then that should be part of the document.