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
--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 PK moved to new Filegroup
ON [Customers]