June 19, 2014 at 9:55 am
I have two questions.
Scenario 1
Let's say I connect the SSIS package directly to the production database and do the full ETL into the destination star schema database. The process takes a substantial amount of time. Would that have an adverse effect on the production (source) database?
Scenario 2
Let's say I connect the SSIS package directly to the production database and do the full ETL into the destination star schema database. Something crashes the process and I have to re-start. Would that have an adverse effect on the production (source) database?
June 20, 2014 at 1:14 am
The answer is probably yes to both. If you're going cross domain there may be enough advantage to moving the database locally for simple convenience. There's enough network hiccups that can happen at that point that I might do that in self-defense.
Running the data read off the source data causes read locks. This can/will interfere with update/insert/deletion locks. A way to avoid that issue can be to use RCSI, but you'll need a good amount of space designated for your tempdb if you do that.
However, this isn't really ETL at that point, it's just database copying. Your probably better off simply setting up a mirror and doing a snapshot just prior to your actual ETL work.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 24, 2014 at 11:21 am
Quick Update:
I decided that using a simple backup/restore might not be the best idea for staging. Why? Because:
1) We are backing up from one server on one domain and restoring to another server on another domain. A backup also backs up the users, roles, etc. This could cause hiccups.
2) There are views that call UDFs that call a linked server.
3) Also, some of the code refers to assembly xleratordb_financial. This is a third-party tool for financial analytics. This has nothing to do with the upcoming data warehouse.
Therefore, I created an SSIS package with a Transfer SQL Server Objects Task. That task just brings over the tables, DRI, etc. Of course, it also brings over the data.
So far, the package works in the development environment. Now I just need to justify to the boss why I'm taking this approach instead of backup and restore. Does anyone have any suggestions of how I can explain this to someone who is highly technical, but doesn't really get this whole "BI thing?"
June 24, 2014 at 12:28 pm
imani_technology (6/24/2014)
Quick Update:I decided that using a simple backup/restore might not be the best idea for staging. Why? Because:
1) We are backing up from one server on one domain and restoring to another server on another domain. A backup also backs up the users, roles, etc. This could cause hiccups.
2) There are views that call UDFs that call a linked server.
3) Also, some of the code refers to assembly xleratordb_financial. This is a third-party tool for financial analytics. This has nothing to do with the upcoming data warehouse.
Therefore, I created an SSIS package with a Transfer SQL Server Objects Task. That task just brings over the tables, DRI, etc. Of course, it also brings over the data.
So far, the package works in the development environment. Now I just need to justify to the boss why I'm taking this approach instead of backup and restore. Does anyone have any suggestions of how I can explain this to someone who is highly technical, but doesn't really get this whole "BI thing?"
And what happens the second time you run the package? Do you have a step which removes (nearly) all of the objects first?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 24, 2014 at 12:51 pm
Yes, I have it set to drop the objects before moving them over. That was the original goal of the backup/restore, to have a new copy in staging.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply