For the moment, this is a hypothetical question. I've got a database that has a high level of transactional data coming into it. I also have a database that supports my website (my application database) to handle my shopping cart, orders, user accounts etc.
Various pieces of information that are reported on the site, for example stock levels, invoice data, item ordering statuses, new catalog items and a host of other pertinent information needs to be fed to the application and displayed on an ongoing basis, a fairly large amount of data.
Right now, the data is coming into the application database by SSIS and my DBA is telling me that because of the large number of SSIS packages required, it's infeasible to engineer a database that is modeled properly for our client facing applications, which is causing all sorts of problems for our application developers... I'm currently calling B.S. on this, but I'm putting it out there to you experts to give me some advice on best practices for handling input of high amounts of data from the back-end and re-indexing to maintain performance of the website including high requirement features such as catalog search, ordering and user account maintenance.
My initial thoughts are to feed data into the back end system using SSIS into transitional tables which can be loaded and re-indexed without affecting the 'live' tables, and then switch the transitional tables for their live counterparts. Is this feasible? Does it make sense? Is there a better practice? I'm not a DBA so it would be helpful to get some expert advice to take back to the team.