Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
1  
define high amounts of data – HLGEM Jun 7 '12 at 20:36
It's not so much the amount of data, though that is potentially hundreds to thousands of rows at a time on a potentially rapid schedule, it's the fact that inserting such data requires the table to be reindexed each time. – Bob the Builder Jun 7 '12 at 20:41

1 Answer

up vote 2 down vote

We have a good sized Enterprise system that has many of SSIS packages taking data in and out of the system daily.

Some of the strategies we have used are:

Only process deltas especially in large files. To figure which records are deltas we use change tracking and send that data to tables in a separate database, so the process of figuring out the deltas when we receive a full file is pushed off onto a staging server. Only records that are new or changed go to the real production server. On our busiest server, we have moved all teh processing except the final load off to a completely separate server.

If the file goes directly to table that does not also have transactional data changes or multiple data sources, we might have two tables (and A and B version) and a view that selects from the active table. So we do our processing on the inactive version (possibly including dropping and recreating indexes), switch the table in the view (meaning there is about 1 second of down time fromteh user perspective), then update the new inactive table.

SSIS packages can be designed to run very quickly or not so quickly. So we may spend a lot of time performance tuning one that takes too long and affects production.

Run the packages during the time period when there is the least usage on the server.

share|improve this answer
I like this strategy, and it's one I've been thinking of adopting as currently there is little strategy used here. I have two questions: 1). How do you handle the downtime there is (understanding that it's a minimum), does it break your application or is your app capable of stalling while it updates? 2). How do you strategize on SSIS packages that can't avoid running at peak times? – Bob the Builder Jun 8 '12 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.