Adding a primary key to a subscribed SQL 2000 table -- sql server replication

databaseninja
10/30/2007 6:25:37 PM

Hello,

We've got a very large production database with transactional
replication between two SQl 2000 servers. We would like to set up
sequential (cascading) replication between the current subscriber and
a 3rd server... running SQL 2005.

The problem we have is that it turns out we do not have primary keys
existing on the subscribed tables. The tables have unique indexes on
them but not a PK.

I can't find a definitive answer on this so I'm just asking. Is it
possible to add a primary key constraint to a table in a subscription
in a SQL 2000 database that does not have one so it can be set up to
be published to a SQL 2005 environment?

If not... are there any work arounds.

Some of the tables in question are 100's of millions of rows and a
snapshot from the subscriber side is acceptable but we don't want to
snapshot the publication from the production table at this time.

Thanks for any pointers

Hilary Cotter
10/31/2007 8:17:08 PM

You need to include the include DRI option in your table articles snapshot
property page.

You can't really do this in a deployed environment without re-initializing.
I think your best choice at this case is to stop your distribution agent(s)
and manually create PKs on the subscriber tables, keeping in mind that the
next time you re-initialize these PKs will be wiped out again.

--
RelevantNoise.com - dedicated to mining blogs for business intelligence.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
[quoted text, click to view]

sql server replication : Adding a primary key to a subscribed SQL 2000 table