we are migrating a datawarehouse application from asp - sql 2000 to dotnet2.0 - sql 2005
currently we are in the process of coming up with an optimum solution for backend design using sql server 2005.
This application pulls data from 3 different source systems (oracle,sql server and mainframe db2).
we have 3 different staging databases corresponding to each of the source application database.
For eg : RetailGarments application is an oltp application with backend oracle database .
we have a staging SQL server database called Retailgarments_stg for our datawarehouse application.
similarly OnlineTravelBooking is an OLTP application with DB2 database .we have corresponding
OnlineTravelBooking_Staging database for staging this data.
Each of these source systems have more than 100 tables and we are currently pulling most of the
tables to our side from the source .
These staging data from different datasources are accumulated in to a cleansed Schema database
which is used for Reporting and other OLAP requirements.
Our question is related to data pull from the source system.
Current SQL Server database pulls these data from sorce system using Stored proc dynamic queries containing
link server openquery.
We would like to improve the performance as part of sql server 2000 to
View Complete Post