Wednesday, March 7, 2012

Is is necessary for a table which will be part of the Replication process to have a and Primary

Hi there,

We're going to use replication on our database. There are tables without any primary key.

Is a primary key necessary for replication?

I have also antoher question: We want to replicate the whole OLTP database (size 30 GB).

Is snapshot replication a suitable mechanisme for this? I thougt snapshot replication overwrites all of the data, not only changed data. Or should we use another replication method?

Thanks in advance,

Patrick de Jong

A primary key is necessary to replicate an article so that a unique record can always be found. Snapshotting a 30gb everytime might not be such a great idea. Transactional is probably a better way to go, depending on your situation.
Tim|||

Yes you need to have a primary key on each table for replication.

Depending on how often your data changes and how frequently you need to have the subscriber updated is the best way to decide if snapshot replication is the best method.

Typically snapshot is not the best option unless you have a very high data change rate and you only need the subscriber updated once or twice per day (or less frequently).

|||

Thanks for the answer's. The really helped me.

1.) My customer has te deside on which fields the pk must come.

2.) I think we will use transactional replication.

Greetz,

Patrick de Jong

|||

Hi Friends,

1.Yes a table involved in Transactional replication should have a primary key defined.Also if u hav opted for transactional publication with updating subscriber,a unique identifier is required for a table.

2.Since it is a OLTP Database it keeps changing frequently,my suggestion would be to go for Transactional Replication.

Always prefer Snapshot replication for datas which do not change frequently (OLAP environment)

Rgds

Deepak

|||

Thanks Deepak,

Today we've made a list of tables without a PK. It seems to be no problem to add the missing PK's because the tabels had an identity which was used as some sort of PK. Tomorrow we will test transactional replication.

Greetz,

Patrick de Jong

No comments:

Post a Comment