how to migrate certain filegroups with partitions to another server

Question

Post reply

how to migrate certain filegroups with partitions to another server

Viewing 5 posts - 1 through 6 (of 6 total)

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

Site Owners SSC Guru Points: 80412 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

As you're finding out, Partitioned Tables don't make such changes easy. I drank the MS "Kool-aid" a couple of years back (only 1.2 TB total, though) and I'm in the process of changing the one I have to a Partitioned View.

For what you want to do, I believe you going to have to go through the throws of a "Switch Out", move the result table to a file/filegroup, change the Partition File and Schema, etc, etc, and, IIRC, that might all be for naught because I don't believe you can spread a partitioned table to another "server" like that.

Hopefully, a fellow by the name of Eddie Wuerch shows up. He has more very-large partitioning experience I know.

--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)

Steve Jones - SSC Editor SSC Guru Points: 739565 More actions · Answer 3

There were distributed partitioned views, but these didn't work well, created dependencies and didn't necessarily help performance - https://techcommunity.microsoft.com/t5/datacat/distributed-partitioned-views-federated-databases-lessons/ba-p/304955

Not sure they're still around. You can use a linked server for the queries, but I wouldn't join that into a local view. I'd have a separate view that users query to get archive data. Meaning they explicitly ask for it.

As for moving this data. I think bcp out is the best way, and the removing those partitions., as Jeff mentioned.

i1888 SSCommitted Points: 1970 More actions · Answer 4

Thanks Jeff, I managed to switch out the old data, and moved the data to new server.

i1888 SSCommitted Points: 1970 More actions · Answer 5

i1888

SSCommitted

Points: 1970

September 14, 2020 at 7:32 am

#3788193

Thanks Steve, I did use BCP, thanks again.