June 21, 2011 at 1:28 pm
Hey guys,
Scenario:
We have different databases on different servers...We use them all for reporting purposes...We are creating a VM server as a datawarehouse to put all these databases all on one server and that is where the reporting will take place...All the databases are on different servers within our network...What we need is for the new databases to mimic (same data) the ones from the other servers...First thing that came to mind is Replication -First I set up Transactional Replication...This did not work b/c there are some tables that we cant modify that doesnt have primary keys...So next was lets try Snapshot Replication...That worked for all the databases except one and it is the main one that is used for reporting...It is a 29 GB db that has 1500+ tables...And the reason it didnt work is because some of the tables had sorted columns set up and it kept failing saying bulk load failed...
Solution:
What can I do? What is the best way to get those databases set up on the reporting server with that data synced up every 24 to 48 hours??? What I am about to try now is setting up a backup/restore that would run every night or every other night in SSIS? Surely there is a better way of going about this...Any suggestions would be greatly appreciated! Thank you in advance
June 21, 2011 at 1:50 pm
June 21, 2011 at 1:57 pm
asm1212 (6/21/2011)
Solution:
What can I do? What is the best way to get those databases set up on the reporting server with that data synced up every 24 to 48 hours??? What I am about to try now is setting up a backup/restore that would run every night or every other night in SSIS? Surely there is a better way of going about this...Any suggestions would be greatly appreciated! Thank you in advance
As Calvo mentioned, Log shipping may be an option, but you'd be best off pushing that throughout the day.
I wouldn't try pushing 30 gigs+ nightly through SSIS. It can work but it's not really the best way, and you'd need a reasonably beefy SAN to handle it at a decent speed.
I personally would approach it with backup/restores, or if you have enterprise, look into mirroring and snapshots. My usual goto for something like this is replication but you've seen some of the difficulties with that.
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 21, 2011 at 1:59 pm
I woul dbe curious why you could not add a primary key. Assuming these are used for an app you could safely add a PK with out affecting the app in any way. Not having a PK seems like bad mojo.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 21, 2011 at 3:00 pm
Oh trust me...That was my first thought when Transactional Replication didnt work...I asked if we could modify the tables and just insert a TableID as indentity, but we cant modify that database b/c it is like a 3rd party database...
I am just going back to Snapshot Replication and going through all the tables and not publishing the articles (tables) that are giving me fits!
June 21, 2011 at 4:53 pm
Depending on how your storage works, anything could work. 30GB isn't a huge amount and SSIS has been shown to load 1TB in about 30 minutes. That's optimized, and probably not what you can expect, but 30GB isn't huge.
However you really don't want to have to do that if you don't want to. What about when it's 100GB? Is that in the foreseeable future?
Ideally you'd just like to move deltas. Can you tell what's changed in most tables? Could you replicate some tables and SSIS the others?
If you're moving the whole thing, why not backup/restore?
June 21, 2011 at 4:59 pm
Steve Jones - SSC Editor (6/21/2011)
Depending on how your storage works, anything could work. 30GB isn't a huge amount and SSIS has been shown to load 1TB in about 30 minutes. That's optimized, and probably not what you can expect, but 30GB isn't huge.
I agree with moving deltas being the optimal, but it's going to have a lot to do with your equipment and pipe as to what's large. In my current scenario, 30 GB is painful with a straight pipe, no transformations. We're talking hours.
YMMV, but that's on SAN equipment for us. Yep, we know where the bottlenecks are, and we can't fix it directly without approvals from on high due to price and policies.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply