| Author |
Topic |
|
mightypenny_ph
Yak Posting Veteran
54 Posts |
 Posted - 2008-06-19 : 16:40:02
|
| assuming that you have two databases, the OLTP db and the OLAP db (take not that both have the same structure -- archiving purposes)... using table partitioning, is there a way where we can move 1 partition from the OLTP db to the OLAP db???i'm actually trying to use this example with both tables in the DB.. I tried to modify to use two databases but sql server is unable to move the partition...ALTER TABLE [Production].[TransactionHistory] SWITCH PARTITION 1 TO [Production].[TransactionHistoryArchive] PARTITION 2;SlayerS_`BoxeR` + [ReD]NaDa |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-19 : 16:43:44
|
| No you can't because each database has their own partition scheme and partition function.And OLAP and OLTP are not usually same structure, usually OLAP structure is Denormalized. |
 |
|
|
mightypenny_ph
Yak Posting Veteran
54 Posts |
Posted - 2008-06-19 : 17:05:22
|
| in our case, our OLAP is the same as our OLTP since where's using the same application to read the data from both dbs...i see... coz i thought i read something like moving partitions from one db to another... maybe i just mis-read it... :)SlayerS_`BoxeR` + [ReD]NaDa |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 16:55:01
|
| Does each partition have its own filegroup? If so, try drop partition in one db without delete it then add it in the other db. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-21 : 21:42:53
|
| Not really understood, How would you add same partition to other DB? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 22:34:56
|
| Once you dropped partition, it's just a data file on the disk. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 22:59:11
|
| Did some testing, it's doable if config partition in both dbs properly but not straightforward. |
|
|
|
mightypenny_ph
Yak Posting Veteran
54 Posts |
Posted - 2008-06-24 : 16:39:04
|
| you mean drop = then SPLIT MERGE right???SlayerS_`BoxeR` + [ReD]NaDa |
|
|
|
mightypenny_ph
Yak Posting Veteran
54 Posts |
Posted - 2008-07-08 : 01:36:36
|
quote:
Originally posted by rmiao
Did some testing, it's doable if config partition in both dbs properly but not straightforward.
can you tell me where i can find this? thanks alot...SlayerS_`BoxeR` + [ReD]NaDa |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-09 : 23:16:30
|
| There is no doc of that, have to try it yourself. |
|
|
|
|
moving partitions from one DB to another