SQL Server 2008 - Replication without a primary key in the publisher ?
-
Dear All, I have a table that has a huge number of data, this table has no primary key. I need to create a replication that replicate this table and any new transactions applied to this table. And so, I think I should choose either "Transactional publication" OR "Transactional publication with updatable subscriptions", but in both options it does not accept to create this replication because the source table has no primary key. How can I solve this problem to create a good replication? :sigh:
Kind Regards OBarahmeh
-
Dear All, I have a table that has a huge number of data, this table has no primary key. I need to create a replication that replicate this table and any new transactions applied to this table. And so, I think I should choose either "Transactional publication" OR "Transactional publication with updatable subscriptions", but in both options it does not accept to create this replication because the source table has no primary key. How can I solve this problem to create a good replication? :sigh:
Kind Regards OBarahmeh
obarahmeh wrote:
in both options it does not accept to create this replication because the source table has no primary key
Why not create a primary key??? Just add an IDENTITY column to your table and you have a valid primary key to use in replication. Although I'm wondering also why don't you have any primary key. In my opinion it's a very strange situtation.
obarahmeh wrote:
And so, I think I should choose either "Transactional publication" OR "Transactional publication with updatable subscriptions"
I would be very careful with Transactional publication with updatable subscriptions. Technically it works fine, but you have to do a good data modeling so that you don't run into update conflicts (same data is updated in different places). This can be quite challenging sometimes. Also note that this feature is going to be removed in future versions so if using SQL Server 2008, you shouldn't use "transactional publication with updatable subscriptions".
The need to optimize rises from a bad design.My articles[^]
-
obarahmeh wrote:
in both options it does not accept to create this replication because the source table has no primary key
Why not create a primary key??? Just add an IDENTITY column to your table and you have a valid primary key to use in replication. Although I'm wondering also why don't you have any primary key. In my opinion it's a very strange situtation.
obarahmeh wrote:
And so, I think I should choose either "Transactional publication" OR "Transactional publication with updatable subscriptions"
I would be very careful with Transactional publication with updatable subscriptions. Technically it works fine, but you have to do a good data modeling so that you don't run into update conflicts (same data is updated in different places). This can be quite challenging sometimes. Also note that this feature is going to be removed in future versions so if using SQL Server 2008, you shouldn't use "transactional publication with updatable subscriptions".
The need to optimize rises from a bad design.My articles[^]
Ok I will adda an Identity column. But are you sure that this method (Transactional publication with updatable subscriptions) makes update conflict??? So, why SQL 2008 contains this method if there is a problems when we use it? And then what is the ideal solution for making replication in SQL server 2008?
Kind Regards OBarahmeh
-
Ok I will adda an Identity column. But are you sure that this method (Transactional publication with updatable subscriptions) makes update conflict??? So, why SQL 2008 contains this method if there is a problems when we use it? And then what is the ideal solution for making replication in SQL server 2008?
Kind Regards OBarahmeh
obarahmeh wrote:
But are you sure that this method (Transactional publication with updatable subscriptions) makes update conflict
The method doesn't make them, your application (modifications) may cause conflicts.
obarahmeh wrote:
why SQL 2008 contains this method if there is a problems when we use it
For example: you have the same row on two different SQL Servers which then replicate the modifications to each other. The replication interval is, let's say 15 minutes. Now you update the same record on both SQL Server. Next time the modifications are replicated, SQL Server sees that the exact same row has been modified on both ends. Now the question is, which modification is correct? There's no simple answer to that. In some cases it may be the earliest or the latest or perhaps it's both modifications combined. So the problem isn't in the technology. It is something that you as a designer must be aware of and model the data and the applications so that conflicts do not occur or that they are rare. The same conflict situation concerns all modifications (update, insert and delete).
obarahmeh wrote:
what is the ideal solution for making replication in SQL server 2008?
This depends fully on the requirements.
The need to optimize rises from a bad design.My articles[^]