SQL Server Transactional Replication and Various Primary Keys
With SQL Server 2005 and Transactional Replication, can I remove the Subscriber's Primary Key Constraints while leaving the Publisher's Primary Key Constraints?
Primary I want to do this because I want clusters on different columns than the existing cluster constraints. I don't think I can convert a constraint from clustered to non-clustered without dropping it first, and replication is already in progress.
This might work for snapshot replication, but I'm not sure if it will work with transactional replication. The only requirement for a table to be replicated is a primary key that uniquely identifies each row.
You can try pausing replication and then trying to remove the primary key constraint by re-creating it as a non-clustered PC and then not pausing replication. If SQL Server won't let you drop your PC, you'll find out before doing any damage.
An alternative is to break replication and reinitialize.
In any case, you will want to make changes during the maintenance window.
source to share
This is not possible for the primary key itself. Alternatively, when you set up transactional replication, when you select articles to replicate, you can select various properties such as "check foreign key constraint". Set this property to false. In db1 convert your primary key to foreign key with some new table tb1 containing that as primary key. So ultimately, with your db replication to db2, the foreign key constraint will not replicate and thus allow you to do what you want.
source to share
The main replication process is to maintain identical database organization between different servers.
Your question here can be viewed as a question about whether the replication process can be used to violate this basic principle of replication.
So, the answer is no, but I'm still interested in the reasons that allow me to formulate this question. Should I say that this variant of "double primary key" was seen as a way to solve another problem? I think you should go back to this original problem and try to find another way to solve it.
source to share
I did my homework and came to the conclusion that you can drop the subscriber restrictions.
I set up a simple transactional replication script, dumped the primary key at the Subscriber, and then did some inserts, delete and update, and verify that the changes were replicated to the Subscriber.
I think I should have done this first. I didn’t know it would be that simple :).