With SQL Server 2005 and transactional replication, can I remove the primary key constraints on the subscriber, while leaving the primary key constraints on the publisher?
Primary I want to do this because I want to cluster on different columns than the existing clustered constraints. I don't think I can convert a constraint from clustered to nonclustered without dropping it first, and replication is already occurring.
This might work for snapshot replication, but I'm not sure it would work with transactional replication. The sole requirement on a table to allow it to be replicated is the existence of a primary key to allow each row to be uniquely identified.
You could try pausing replication and then try dropping the primary key constraint, recreating it as a non-clustered PK and then un-pausing replication. If SQL Server will not allow you to drop the PK, you'll find out before you do any damage.
The alternative is to break replication and reinitialise it.
Either way, you'll want to do the change during a maintenance window.
Directly, for primary key its not possible. Other way round: When you set the transactional replication, when you select the articles for replication, you can select different properties like "check foreign key contstraint". Set that property to false. In db1 convert your primary key to a foreign key with some new table tb1 containing that as primary key. So, ultimately , on your replication db db2, the foreign key constraint wont be replicated and thus allowing you to do what you want.
Why don't you leave the primary key and create additional non clustered indexes on the subscriber or will this not resolve your problem? If the reason to have other columns indexed on the subscriber is performance then this should be a solution.
Thanks for the replies, everyone.
Yes, I COULD create non-clustered indexes, but I WANT to create a clustered index. I want to know if that's possible without interrupting replication.
If it's not possible, I may have to resort to using only non-clustered indexes, which isn't optimal for my situation.
The basic of replication process is to maintain identical database organisation between different servers.
Your question here can be considered as asking if a replication process can be used to break this basic replication principle.
So the answer is no, but I am still interested in the reasons that let you formulate this question. Shall I say that this "dual primary key" option was seen as a way to solve another problem? I think you should go back to this initial issue and try to find another way to solve it.
It's not for failover, it's a replicated copy for reporting. I need the same data as the OLTP database, but the query requirements are totally different, and require different indexes. Unfortunately the existing primary key constraints are often clustered, and I want to create totally different clustered indexes.
I realize that transactional replication is not the typical way to replicate data for reporting. That's not my department and there's nothing I can do about that, so I'd prefer not to get side tracked on that issue.
It's clear to me that a primary key constraint is required for the publisher. But is that same constraint required to be on the subscriber?
I'm just trying to make the best of the constraints I'm operating under...
I did my own homework, and came to the conclusion that you can drop the constraints on the subscriber.
I set up a simple transactional replication scenario, dropped the primary key on the subscriber, and then did some inserts, deletes and updates, and verified that the changes were replicated to the subscriber.
I guess I should have done that to begin with. I didn't know it was going to be that easy :).