Replication issues (sql server 2005)

[Maim]

Hi,

We have a pretty large database with Transaction Replication to a secondary server (pull subscription). Some tables are starting to get pretty large so I want to partition them.

On some tables, I have to recreate the primary keys with a new column used for the partition schema.

The problem is when I recreate the publisher and subscriber, the schema changes are not pulled by the subscriber and I get errors to that effect.

I'm wondering if it's at all possible for primary key changes to be replicated at all. All articles being replicated have copy table and index partitioning, nonclustered indexes etc. to true. Also truncate all data for existing objects as I can't drop and recreate because of foreign key constraints.

Any suggestions?

Thanks!

-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[mrdenny]

Also truncate all data for existing objects as I can't drop and recreate because of foreign key constraints.

That would be the problem. You will need to make the required schema changes manually then the replication will be able to load the data.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Maim]

Yeah, medenny, I was hoping not to have to run the change scripts manually but there doesn't seem to be any other choice... hehe.

Gotta love the job

-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook