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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy