March 12, 2013 at 5:41 pm
DB Size - 4tb
Table Size - 160gb
Partitioned - Yes
FGs - 159
row count - 205,363,396
indexes - 1CI, 4NCI
Partitioned code:
USE [xx]
GO
/****** Object: PartitionScheme [PS_PRTN_ID_159] Script Date: 3/12/2013 6:36:32 PM ******/
CREATE PARTITION SCHEME [PS_PRTN_ID_159]
AS PARTITION
[PFN_PRTN_ID_159] TO ([FG_PRTN_159_P1], [FG_PRTN_159_P2], [FG_PRTN_159_P3],
[FG_PRTN_159_P4], [FG_PRTN_159_P5], [FG_PRTN_159_P6], [FG_PRTN_159_P7], [FG_PRTN_159_P8], [FG_PRTN_159_P9], [FG_PRTN_159_P10],
[FG_PRTN_159_P11], [FG_PRTN_159_P12], [FG_PRTN_159_P13], [FG_PRTN_159_P14], [FG_PRTN_159_P15], [FG_PRTN_159_P16], [FG_PRTN_159_P17],
[FG_PRTN_159_P18], [FG_PRTN_159_P19], [FG_PRTN_159_P20], [FG_PRTN_159_P21], [FG_PRTN_159_P22], [FG_PRTN_159_P23], [FG_PRTN_159_P24],
[FG_PRTN_159_P25], [FG_PRTN_159_P26], [FG_PRTN_159_P27], [FG_PRTN_159_P28], [FG_PRTN_159_P29], [FG_PRTN_159_P30], [FG_PRTN_159_P31],
[FG_PRTN_159_P32], [FG_PRTN_159_P33], [FG_PRTN_159_P34], [FG_PRTN_159_P35], [FG_PRTN_159_P36], [FG_PRTN_159_P37], [FG_PRTN_159_P38],
[FG_PRTN_159_P39], [FG_PRTN_159_P40], [FG_PRTN_159_P41], [FG_PRTN_159_P42], [FG_PRTN_159_P43], [FG_PRTN_159_P44], [FG_PRTN_159_P45],
[FG_PRTN_159_P46], [FG_PRTN_159_P47], [FG_PRTN_159_P48], [FG_PRTN_159_P49], [FG_PRTN_159_P50], [FG_PRTN_159_P51], [FG_PRTN_159_P52],
[FG_PRTN_159_P53], [FG_PRTN_159_P54], [FG_PRTN_159_P55], [FG_PRTN_159_P56], [FG_PRTN_159_P57], [FG_PRTN_159_P58], [FG_PRTN_159_P59],
[FG_PRTN_159_P60], [FG_PRTN_159_P61], [FG_PRTN_159_P62], [FG_PRTN_159_P63], [FG_PRTN_159_P64], [FG_PRTN_159_P65], [FG_PRTN_159_P66],
[FG_PRTN_159_P67], [FG_PRTN_159_P68], [FG_PRTN_159_P69], [FG_PRTN_159_P70], [FG_PRTN_159_P71], [FG_PRTN_159_P72], [FG_PRTN_159_P73],
[FG_PRTN_159_P74], [FG_PRTN_159_P75], [FG_PRTN_159_P76], [FG_PRTN_159_P77], [FG_PRTN_159_P78], [FG_PRTN_159_P79], [FG_PRTN_159_P80],
[FG_PRTN_159_P81], [FG_PRTN_159_P82], [FG_PRTN_159_P83], [FG_PRTN_159_P84], [FG_PRTN_159_P85], [FG_PRTN_159_P86], [FG_PRTN_159_P87],
[FG_PRTN_159_P88], [FG_PRTN_159_P89], [FG_PRTN_159_P90], [FG_PRTN_159_P91], [FG_PRTN_159_P92], [FG_PRTN_159_P93], [FG_PRTN_159_P94],
[FG_PRTN_159_P95], [FG_PRTN_159_P96], [FG_PRTN_159_P97], [FG_PRTN_159_P98], [FG_PRTN_159_P99], [FG_PRTN_159_P100], [FG_PRTN_159_P101],
[FG_PRTN_159_P102], [FG_PRTN_159_P103], [FG_PRTN_159_P104], [FG_PRTN_159_P105], [FG_PRTN_159_P106], [FG_PRTN_159_P107], [FG_PRTN_159_P108],
[FG_PRTN_159_P109], [FG_PRTN_159_P110], [FG_PRTN_159_P111], [FG_PRTN_159_P112], [FG_PRTN_159_P113], [FG_PRTN_159_P114], [FG_PRTN_159_P115],
[FG_PRTN_159_P116], [FG_PRTN_159_P117], [FG_PRTN_159_P118], [FG_PRTN_159_P119], [FG_PRTN_159_P120], [FG_PRTN_159_P121], [FG_PRTN_159_P122],
[FG_PRTN_159_P123], [FG_PRTN_159_P124], [FG_PRTN_159_P125], [FG_PRTN_159_P126], [FG_PRTN_159_P127], [FG_PRTN_159_P128], [FG_PRTN_159_P129],
[FG_PRTN_159_P130], [FG_PRTN_159_P131], [FG_PRTN_159_P132], [FG_PRTN_159_P133], [FG_PRTN_159_P134], [FG_PRTN_159_P135], [FG_PRTN_159_P136], [FG_PRTN_159_P137], [FG_PRTN_159_P138], [FG_PRTN_159_P139], [FG_PRTN_159_P140], [FG_PRTN_159_P141], [FG_PRTN_159_P142], [FG_PRTN_159_P143], [FG_PRTN_159_P144], [FG_PRTN_159_P145], [FG_PRTN_159_P146], [FG_PRTN_159_P147], [FG_PRTN_159_P148], [FG_PRTN_159_P149], [FG_PRTN_159_P150], [FG_PRTN_159_P151], [FG_PRTN_159_P152], [FG_PRTN_159_P153], [FG_PRTN_159_P154], [FG_PRTN_159_P155], [FG_PRTN_159_P156], [FG_PRTN_159_P157], [FG_PRTN_159_P158], [FG_PRTN_159_P159])
GO
This is a DW server and we are planning to add additional columns. we would like to test this in the test.
Q is what will be the best way to emulate production i.e. have partitions, fgs and populate the table. Copying the table structure and moving data will be simple but i am trying to find a less time consuming way to move partitions (if possible) and fgs (if possible)
any input will be greatly appreciated
March 12, 2013 at 6:32 pm
1- Pre create the table as it pleases you.
2- Populate the table - I assume the servers can "see" each other.
As a proof of concept you may want to pre create the table then migrate only a couple of partitions.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply