September 28, 2011 at 3:15 am
Hi, I am a novice SQL Server 2005 data warehouse administrator / BI analyst / DBA. 1 yrs experience.
I have a legacy warehouse and BI platform working off a copy of the operational database. The operational system will now be accepting a new line of business that the legacy warehouse and BI will either trip over, or continue to work without distinguishing between the lines of business, which is useless.
There is a new warehouse in the pipeline to replace everything delivered by the legacy system, but there is a chance that it won’t be fully-functional before the operational system starts accepting the new brand, so there will come a stage when the legacy system becomes useless and the new strategy isn’t fully functional.
Because of all the development currently underway to bring in the new platform, developing the legacy platform to work with the new operational system is not viable. To keep the legacy system running without patching/fixing etc, I need to control the data at source. In short, I need to maintain a copy of the production database that contains only one of the two lines of business that will be on the live operational database.
I'm not at a technical stage because the system developer hasn’t released the new schema, so I’m just trying to get a feel for what options are available, if any at all. My immediate thought is to have a nightly log shipping job followed by a job that deletes the “new” line of business.
I would appreciate to hear any thoughts or advice you may have.
Thanks
September 28, 2011 at 6:09 am
I'd look at two different ways of doing this, and neither is moving the whole thing and then deleting.
1) You can use transactional replicatoin with filters so that only the data you want is moved. Not knowing you structure, I don't have a clue how difficult that would be. It can be hard if you can't easily filter the data.
2) SSIS to move only the data you need. From the sounds of your log shipping scheme you have a mechanism to identify what you want to keep. Just use that to identify what you want to move and move only that.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply