Moving Filegroups on a SQL Server Database under Replication

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.

Start off by updating the files on the database you wish to move like so:

ALTER DATABASE DBName

MODIFY FILE(NAME = 'Logical file name' , FILENAME = 'New physical file path')

This will update the database and instruct it to change the location in which it looks for the file. This will take effect the next time the database is restarted.

Now we need to deal with the replication, which will prevent you from making changes to the database while it is active. This should be done by using Replication Monitor to disable the Log Reader Agent on any subscriptions that affect the database, either as a publisher or subscriber. On the SQL instance in SSMS, right-click the Replication item and select “Launch Replication Monitor”. Select the replication item that affects your database and click the Agents tab. Now, right-click the “Log Reader Agent” and click “Stop Agent”.

If the database being moved is a subscriber under replication, you’ll also need to disable the replication job in SQL Server Job Agent. This runs constantly on the subscriber DB and consumes the publication. You’ll recognise this by its name, which will be in a format like:

PublisherInstanceName-PublisherDBName-PublicationName-SubscriberInstanceName-SubscriberDBName-GUID

Once you stop this job, your database should no longer be under replication and you can safely move your files. So, take the database offline:

ALTER DATABASE DBName SET OFFLINE

Then move your files from the current directory to the new one, making sure that the SQL Server Service Account has permission over the location. Then, bring your database back online:

ALTER DATABASE DBName SET ONLINE

This should bring the database back up, with the data files now in your new location. Bear in mind that if your data files are large, this may take some time. Lastly, kick off the SQL Job (if you’ve moved a subscriber DB) and go back to Replication Monitor, highlight the Log Reader Agent and choose “Start Agent”. It may also be a good idea to reinitialize your Replication Snapshots while your here, since the database may have been offline for some time.

Let me know in the comments below if this worked for you, or if you have any more information or other ideas about how to move files for a SQL Server database under replication.