Friday, December 10, 2010

Moving Replicated FullText Index in SQL Server

We needed to move the location of our full text index (FTI) on a subscriber. I thought this would be a pain because the replication, but it turns out to be pretty straightforward. Here's the system I am working with:

SQL Server 2005
Transactional Replication with an Updateable Subscriber.

Here are the steps I took:

0. Make sure you have appropriate backups.
1. Point all of the apps to the Publisher because we will need to take the Subscriber offline.
2. On the Subscriber, open the synchronization status by right-clicking on Replication->Local Subscriptions-> ans selecting View Synchronization Status.
3. Stop the Synchronization Service. (This really just pauses updates).
4. On the Subscriber run SELECT name FROM sys.database_files WHERE type_desc = 'FULLTEXT'; to get the name of the FTI.
5. On the Subscriber run ALTER DATABASE [DB_NAME] SET OFFLINE;
6. Move the FTI where to it's new home.
7. On the subscriber run ALTER DATABASE [DB_NAME] MODIFY FILE (Name=[FTI_NAME], Filename = "'new/location/on/disk/');
8. On the Subscriber run ALTER DATABASE [DB_NAME] SET ONLINE;
9. Start the Sync Service in View Synchronization Status.

That should be it. Hope this helps.