MS SQL Data Warehousing - migrating 2000 partitionned view to 2005 partitionned table?

Author

migrating 2000 partitionned view to 2005 partitionned table?

Jéjé

2005-11-21, 3:23 am

Hi,

I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
by year. (Fact_Table_2003, Fact_Table_2004,...)

I want to test to migrate to 2005 and I want to use the new partitionned
table options.

how to do this easely?

thanks.

Jerome.

Darren Gosbell

2005-11-21, 3:23 am

I have not had to do this yet, so this is all theory from my
perspective.

But from what I have seen, you should be able to backup/restore your
database "as is" and then I from what I have seen, you just need to do
an ALTER TABLE...SWITCH to move the table data around. BOL has a topic
on "moving partitioned data" which lists one of the uses of SWITCH as
"Assigning a table as a partition to an already existing partitioned
table."

Assigning partitions to different tables is just a meta data operation
so it happens very quickly, no data is copied, pointers to the data are
just updated.

In a nutshell I think the process would go something like this:

1. migrate the database onto SQL2k5
2. join all the tables together using "ALTER TABLE..SWITCH"
3. drop the view
4. rename the table from step 2. with the same name as the view
5. re-create any Referential Integrity constraints

I would suggest doing a test on small sample database first (a copy of
one of the sample databases maybe)

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <ePn#4Tk7FHA.3876@TK2MSFTNGP09.phx.gbl>,
willgart@BBBhotmailA
AA.com says...
> Hi,
>
> I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
> by year. (Fact_Table_2003, Fact_Table_2004,...)
>
> I want to test to migrate to 2005 and I want to use the new partitionned
> table options.
>
> how to do this easely?
>
> thanks.
>
> Jerome.
>
>
>

Sponsored Links