March 30, 2011 at 3:40 am
Hi i'm designing a new MS SQL 2005 (SP2) database that should be a cutted mirror of our production DB (that is Oracle).
I'm Using SSIS to connect on, query Oracle and insert record in my SQL 2005.
The strategy we are using is empting and filling the MS SQL tables daily (or hourly) based on our needs but last time we used this strategy (SQL 2000 this time) we had a problem with transactional log because it increased a lot and we had to manage the growing.
Question time:
1. Should I do something particular in my SSIS package to avoid transactional log grow up?
2. is this the best strategy for this scenario?
Any help/suggestion is really appreciated and ask me if someone need more infromation about my questions/scenario.
Thank a lot
March 30, 2011 at 6:00 am
If you drop everything and reinsert everything, you have to log the deletes and the inserts, so you need space in your log to support it.
First, best, choice; don't reload the world at every change. Instead learn how to do incremental loads so you only move the changes over from oracle. That will mean smaller transactions which means less transaction log use.
Second choice; break the loads down so that you isolate the transactions in smaller chunks. That will reduce the amount of log you need.
Further, is the database in FULL recovery mode? That's the default, so it wouldn't surprise me. That means you need to set up log backups or change the database to SIMPLE recovery. That won't eliminate logging issues, but it will help manage them. But understand the difference. From the sounds of things, I don't think you need point in time recovery of the SQL Server database, so setting it to SIMPLE should be OK. But if you do need point in time, you need to learn about FULL recovery and log backups. This is a quick and simple introduction[/url] to the concepts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2011 at 7:29 am
Thank you so much Grant, your reply is a good start point.
I dont have the time (now) to learn how to do incremental loads (you have something to seggest me read about this strategy approach?).
To spped up my work I will drop everything and reload the table content.
It's interesting when u say: "break the loads down so that you isolate the transactions in smaller chunks".
Do u have some example? considering I'm new to MS SQL and databases in general.
March 30, 2011 at 8:04 am
Andy Leonard is one of the best guys I know for teaching people how to use SSIS. Here's a blog post on incremental loads. It can help to get you started.
Problem is, from the sounds of things, you're in a bit over your head. Be cautious about the choices you make. What seems quick and expedient now, could be cause for serious trouble of the long haul.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2011 at 11:59 am
Thanks Grant, now I'm seeing all my work for this in a new prespective.
I really appreciated ur help.
April 4, 2011 at 6:32 am
CDC in SQL Server 2008 would be a step to start for incremental load.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply