How to move a Published Table to a new File Group

I recently needed to move a Transactional Replicated table to a new File Group.  There are a couple of documented Schema changes that can break Replication. http://msdn.microsoft.com/en-us/library/ms151870(SQL.90).aspx However, I found most problems are solved if I first ensure all data between all peers/subscribers is synched, “no replicated transactions”, before making schema change, then sync the schema change, then start flow of data again.

Since the Primary Key is also the clustered index I could use the command below to move the Primary Key check constraint to file group called “Customers”

–Move Clustered Index to new filegroup

—    Primary Key = PK_Customer_CustomerID

—    PK Column = [rowguid]

—    Table = SalesLT.Customer

—    New File group = Customers

CREATE unique clustered index PK_Customer_CustomerID on SalesLT.Customer([rowguid]) WITH (DROP_EXISTING=ON) ON Customers

Before Move:

–Original “Primary” file group location

ALTER TABLE [SalesLT].[Customer] ADD  CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED

(

      [rowguid] ASC

)WITH (ALLOW_PAGE_LOCKS  = ON)

ON [PRIMARY]

GO

After Move:

–After PK moved to new Filegroup

ALTER TABLE [SalesLT].[Customer] ADD  CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED

(

      [rowguid] ASC

)WITH (ALLOW_PAGE_LOCKS  = ON)

ON [Customers]

GO

Chris Skorlinski
Microsoft SQL Server Escalation Services