Move clustered PK without breaking replication

• zach_john

  SSCertifiable

  Points: 5922

  More actions

  November 13, 2008 at 7:42 pm

  #127724

  Typically I would just add a new group, drop the constraint,and then add it back on to the new group.

  Replication has a problem with that. 🙂 Is there a way to tell replication to close its eyes we know what we're doing.

  ALTER DATABASE SomeDB_0810

  ADD FILE ( NAME = N'SomeDB_0810_DATA_16',

  FILENAME = N'K:\DATA\SomeDB_0810_DATA_16.NDF'

  , SIZE = 1024000KB

  , FILEGROWTH = 512000KB )

  TO FILEGROUP [SomeDB_0810_FG_16]

  GO

  --ALTER TABLE dbo.sometbl_0810

  --DROP CONSTRAINT sometbl_0810_PK

  --GO

  --

  --ALTER TABLE dbo.sometbl_0810

  --ADD CONSTRAINT sometbl_0810_PK

  --PRIMARY KEY CLUSTERED

  --(

  --TxnUID, WCSUTCPostDate

  --)

  --WITH ( PAD_INDEX = OFF

  --, FILLFACTOR = 100

  --, STATISTICS_NORECOMPUTE = OFF

  --, IGNORE_DUP_KEY = OFF

  --, ALLOW_ROW_LOCKS = ON

  --, ALLOW_PAGE_LOCKS = ON)

  --ON sometbl_0810_FG_16

  --go

  
  John Zacharkan

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  November 13, 2008 at 8:47 pm

  #898390

  I used to do something like:

  Script out the "Drop publication" command

  Script out the "create publication" command

  Run just the part of the drop having to do with your table to remove it from the pub

  Make table changes

  Run just the part of the create having to do with your table to add it back to the pub

  Set up a little test replication scenario in your dev database to try it out.

• zach_john

  SSCertifiable

  Points: 5922

  More actions

  November 14, 2008 at 7:53 am

  #898605

  Thanks man, trying to avoid touching replication.

  Would it make a difference if it the PK wasn't a cluster index?

  
  John Zacharkan

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  November 14, 2008 at 8:04 am

  #898620

  zach_john (11/14/2008)

  ---

  Thanks man, trying to avoid touching replication.

  Would it make a difference if it the PK wasn't a cluster index?

  I dunno

• Elisabeth Rédei

  Hall of Fame

  Points: 3873

  More actions

  November 14, 2008 at 8:36 am

  #898654

  Hi,

  Use CREATE INDEX ... WITH DROP_EXISTING

  CREATE UNIQUE CLUSTERED INDEX pkCOL1_CL on tblMyTable(col1)

  WITH (DROP_EXISTING = ON)

  on [fgMyFG]

  /Elisabeth

  elisabeth@sqlserverland.com
  MCITP | MCT
  http://sqlblog.com/blogs/elisabeth_redei/
  http://linkedin.com/in/elisabethredei