Sooner or later, with any SQL Server database of a suitably large scale, you’re going to run into a situation where you start hitting hardware limitations. It may be disc space, it could be IO issues caused by concurrent access of multiple filegroups and databases on the same controller. Either way, you’re likely to hit the point where you make the decision that you need to move some filegroups around to distribute the load across several drives. Alternatively, you may just want to reorganise your DB files into a more logical structure.
This is very straightforward to do normally via backup and restore, or simple detach and attach. However, when your database is under replication, either as a publisher or subscriber, this simple process becomes a little more difficult. Thankfully, there’s a very simple solution to this, which I came across the other day while spreading our DB across multiple drives.read more