Post reply

how to migrate certain filegroups with partitions to another server

  • September 2, 2020 at 9:31 am

    #3785498

    Hi guys,

    I have 3 partition tables on multiple file groups on monthly basis., each month it generates about 1-2TB data on one partition, one filegroup. For more than 1 month old data, we set the filegroup to read-only.

    Now the question comes, disk space is nearly full, I would like to move more than 1 year old data another server, and will repeat the same job each month, we will need to query the old data, just not that frequently.

    I thought I can backup filegroup and restore filegroup, but apparently that won't work, as piecemeal restore is one time off thing, you cannot incrementally restore new filegroups.

    The only solution I can think now is using partition-switch on the old data filegroup(sure need to change the filegroup to writeable), then BCP or transfer data through linked server, then drop table, and since filegroup cannot be deleted, I have to shrink the data file to smallest.

    Do you have better solution? thanks!

     

     

     

  • September 3, 2020 at 10:10 am

    #3785942

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

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

  • September 3, 2020 at 3:56 pm

    #3786046

    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)

  • September 3, 2020 at 4:07 pm

    #3786064

    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.

  • September 14, 2020 at 7:29 am

    #3788190

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

  • September 14, 2020 at 7:32 am

    #3788193

    Thanks Steve, I did use BCP, thanks again.

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

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