PK in Transactional Replication Implementation

Question

PK in Transactional Replication Implementation

SSC Enthusiast

Points: 161

March 27, 2006 at 6:51 am

Hi,I'm new to SQL*Server and implementing Transactional Replication.The active production database is in Sql Server 6 to be upgraded to Sql Server 2000 @ Windows 2003 with Transactional Replication feature.The plan is to build a new server, create the database using scripts from the production, create all the objects (tables, indexes etc) using scripts from the production, insert data using import/export from prodcution, then implement 'Transactional Replication' and perform testing.I've got a problem while implementing replication: some of the tables in the production environment dont have Primary key columns and I suppose Transactional Replication is not possible without all tables having primary key.To get around this, I added a dummy column to the target tables(tables without primary keys) with IDENTITY feeature and marked them as a primary key. But came across another problem that some tables already having identity column and that too with some repeated values in them!!! So I can neither add another Identity column nor mark them as primary key.Please suggest or show me alternate way around.- Is it possible to implement Transactional Replication with some table without primary keys?- How can I add a primary key column to the existing tables without suffering the application & codes? - Is there anything like auto updateable primary key column that wont ask the application forit's corresponding values?The objective is to come out of this constraint of primary key and implement the Replication successfully.Thanks & RegardsSangra

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Sangra

You're right - you do need a primary key on every table you wish to use for replication. There's no way round that.

Do your tables have any sort of uniqueness constraints on them? If so, you should be able to use the unique columns, or sets

of columns, to form simple or composite primary keys. If not, you need to revisit your database design and also ensure that

you don't have any duplicates in your data. Just adding an identity column won't remove the duplicates - as I have read here

and elsewhere, it will create uniqueness rather than enforcing it. Also, it may affect the way joins between the tables are

performed... you really need to understand your database structure and your application before changing anything. If you

have identity columns with duplicate values in (not sure how) then I suggest you drop the identity property, deal with the

duplicates, and recreate it.

Good luck!

John

Chander-314098 SSC Enthusiast Points: 161 More actions · Answer 2

Thanks John,

What I was doing so far (after consultation with design team) was:Create duplicate table2 with added identity column(dummy), insert into table2 select * from originaltable, marking identity column as primary key, drop originaltable, rename table2 to original table.

this way I'm able to get the same table with a dummy column as a primary key but I think you are right....In one or other way(now or later) it could be troublesome to the application or business needs.

I'll double check it how easily I can create a composite primary keys without affecting application or model.

Thanks for your quick reply.

Thank you very much

Regards

Chander