I’m working with SQL Server 2008 Enterprise edition and have an OLTP system with two large tables that need to be relocated to a different filegroup. The challenge is that I need to do this without any service interruption.
These tables are quite active with around 100-130 new records being added every second and 30-50 existing records getting modified. Each table contains approximately 100 million rows with six columns (one of them being a geography data type).
I’ve been searching for solutions but most approaches I found suggest creating a duplicate table, copying all data from the original, then dropping the source table. This method would cause downtime which I cannot afford.
Would table partitioning be a viable approach to accomplish this task? I’m open to any suggestions that would allow me to transfer these tables to another filegroup while keeping the database online and operational.
Interesting challenge! What’s your current setup like - are these partitioned tables or just regular heap/clustered ones? Have you thought about using readable secondaries with AlwaysOn to test the migration first? And what’s going on with your filegroups - running low on space or trying to boost performance?
Table partitioning can work here, but you’ll need to plan it carefully. Create a partitioned table on your target filegroup, then use partition switching to move data in chunks. This keeps everything online and maintains transactional consistency. That said, I’d still go with the online index rebuild approach I mentioned earlier - it’s more straightforward. Just heads up: your system will see higher IO and CPU usage during the rebuild, which usually takes several hours for tables your size. Watch your transaction log space closely since both methods generate tons of log activity. I’ve done similar migrations before, and scheduling during off-peak hours really helps reduce the performance hit on your OLTP workload.
online index rebuilds are perfect for this! just rebuild your clustered index on the new filegroup and the table moves over with zero downtime. works great on enterprise edition, though expect some performance hit while it’s running.