How to move a Published Table to a new File Group


How to move a Published Table to a new File Group

Chris Skorlinski
Microsoft SQL Server Escalation Services

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:

image

–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:

image

–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

Comments (1)

  1. BhavyaArora says:

    Great Information.

    What to do if there is Nonclustered Index on Primary Key and Clustered index on another column or there is no clustered index on table.

    Regards

Skip to main content