Ideas Technical Solution/Architecture for web application using SQ

[emmee]

I am hoping an expert could give me some direction/ ideas for a technical problem I'm looking at. I've got to use anSQL server database which have no experience of and I need to extract a subset of the data automatically onto a pre production server and then dynamically move this all from pre production to the production server. Tthen I need to produce the information in two ways i.e automatically generate an xml file and to build a online web application(asp.net) with a search interface to search this data.

I was thinking of the following to options but need advice on whether this is a good solution, whether it could be streamlined into something better, whether anybody has any other ideas.

IDEA 1
PL/SQL procedure to to pump data (subset) from source SQL server and build xml file (note the XML tags have to be specific ones) not the ones that are usually autogenerated by databases. Then some schedule to move xml file from one server to another. On the production server the web application(ASP.NET) queries the xml file to depending on the seach criteria entered by user and presents in user friendly web format. What i'm not sure is whether there would be a performance impact on this as the data in file is going to be very large. Would this have a performance impact because the queries could end up being very complex depending on the serach and the data brought back could also be of a large volume.

IDEA 2

Extract subset data from the source database and automatically copy this into a blank SQL server database on the pre production server and then copy again (automatically) onto the production server. On the production server, the web application is linked to the database and the search is carried out via calls to the database as opposed to the XML file as in the first idea. Problem is this idea seems a bit exessive in terms of the amount of databases being created/ used but the problem is that copying directly from source database to the production database is not an option for me.

Thanks for all your help in advance.

 

[BobRodes]

I would ask some of these questions in other forums. This doesn't seem to really be a question of OOAD architecture. You have pretty well identified the problem, and now you're really wondering if you should use an SQL Server database on your preproduction server or simply use XML files as an intermediate transfer device. Your main concerns are about performance. So, it seems to me that more expertise would be available to you on one of the SQL Server forums (look for any forum where gmmastros is on the MVP list, he's DA MAN where SQL Server is concerned), and the ASP.Net forum.

Bob