Partitioning an existing table into multiple filegroups

by Laughing Vergil   Last Updated November 08, 2018 22:06 PM

I am working on a SQL Server 2016 SP2 Standard edition server, with an in-house application that is 24/7 used in multiple locations across multiple countries. The current application has one database per Customer (company using the application and central server). Eventually, there will be hundreds of Customers with databases in this system.

There is also one "Central" database, which includes all archival data from all of the individual DBs. A customer Id value is used to keep all of the different data separated.

To speed access to archival data, and to simplify maintenance, the current plan is to partition this Central DB, with the appropriate by-customer tables partitioned into one partition per customer, each on a separate physical file in a separate DB partition.

However, this DB and the relevant tables are already in use in an unpartitioned way. I am researching what needs to be done to split things out into the desired data structures.

Example: There is a table (Orders) that includes CustomerID. The desire is to split this existing table so there is one Customer per Filegroup/file based on the CustomeId. The partitioning function, partitioning scheme, and other miscellany is simple enough to figure out. However, the only examples I can find for splitting a table across filegroups involve creating a new table. The documentation doesn't explicitly state that ONLY a newly created table can do this in partitioning, and some older articles I have read (SQL 2005 and 2008) say that you can't do this.

So, the final question: Other than by creating a duplicate table with the correct partitioning, migrating the data, and renaming the old and new tables, is there any way to partition an existing table into multiple different new filegroups / physical files?

Directions to examples for SQL 2016 (or 2012 if it can be done there or later) will be accepted as well.

TIA



Related Questions




archiving partitioned filestream table

Updated January 12, 2018 08:06 AM