I've appended the following information in a previous post but I asked the 'why' instead of the 'how'.
So here's the question...
SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most important/accessed table is 1.6 TB, located on the primary filegroup. It's currently about 95% fragmented and so most all queries need to do a full scan of its 600M+ rows. Not surprisingly, it's an IO bottleneck and I'm unable to defrag in any acceptable window of time. My goal is to move this table out of the .mdf and cluster/partition onto its own filegroup (
ONLINE=ON & SORT_IN_TEMPDB=ON). This new filegroup would be physically isolated on a dedicated disk array.
But I'm very concerned about any issue that may cause (or necessitate) a rollback (things such as a power failure, deadlocking, corruption, unacceptably slow performance during the operation, etc.).
Further, given the length of time this clustering/partitioning/moving process may take, any backups taken during that time would be useless. They'd immediately go into recovery after being restored and, again, rollback/rollfoward time would be unacceptable. Same deal if I were to turn on full logging and use transactional replication or log shipping -- worthless secondary or subscriber.
How would you handle this process?
I've thought about making a copy of the table, triggers on the source to keep the copy up-to-date.
I've got large buffers of space on all volumes involved. I want to pull the trigger. Boss says 'not yet'.
Thanks for reading...