Multi Company DataBase

Question

Multi Company DataBase

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 1

nalmir.hugo (6/22/2009)
First of all, thanks for Lynn Pettis,Jeff Moden,PaulB,ps,WayneS for your comments. All the points stressed out here are very important. As Jeff said, this is not a simple question and the restore options questions are very pertinent. We also don't want to give peoples a Hard Attack by sending them an E-mail reporting that a restore operation has been completed. Once again Thanks for all.

Very cool... glad you're taking all of this the right way. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 2

Andy Hughes (6/22/2009)
Each of these tables could be partitioned on CompanyID with the partitions residing on a separate filegroup(s). This should make it possible to point-in-time restore for a single company (filegroup)?

This reminds me of a multiple-database-in-a-database debate I had on here some months back.

Sadly, in this case, a full-file backup and partial restore strategy would not work. The problem is with the partitioning (assuming Enterprise Edition there) over multiple filegroups. Ordinarily one could just restore selected filegroups (plus PRIMARY) to bring some parts of the database online; but in this case all filegroups would have to be restored to bring the partitioned table online.

I have used full-file based backup before to allow partial restores, but wouldn't even contemplate it in this case (just adding that for the record!)

Paul

edit: clearer wording, I hope

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

ChillyDBA SSCrazy Points: 2539 More actions · Answer 3

Paul White (6/23/2009)
Sadly, in this case, a full-file backup and partial restore strategy would not work. The problem is with the partitioning (assuming Enterprise Edition there) over multiple filegroups. Ordinarily one could just restore selected filegroups (plus PRIMARY) to bring some parts of the database online; but in this case all filegroups would have to be restored to bring the partitioned table online.

Thanks Paul, you've saved me some pain here

Undoubtedly I would eventually have had cause to experiment with this approach myself, but hadn't considered this limitation with the partitioned tables/filegroups mix.

Andy

Life as a DBA: Living in a box, but thinking outside of it.

Blog: www.chilledsql.com[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 4

Andy Hughes (6/24/2009)
Thanks Paul, you've saved me some pain here
Undoubtedly I would eventually have had cause to experiment with this approach myself, but hadn't considered this limitation with the partitioned tables/filegroups mix.
Andy

Hey no worries Andy,

Actually I try to encourage people to mess about with full-file backups, partial restores and so on - simply because they are probably the least well understood of the backup/restore options, sadly.

Your idea of separating the foreign-key table into partitions on separate filegroups was clever - and just the sort of 'odd' thinking I can identify with. Go you!

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi