question re: indexes and constraints in replication...
-
Hello,
I'm hoping someone can help me out here...
BACKGROUND: I'm trying to replicate a bunch of tables, and am having difficulty figuring out how to handle keys/indices/constraints.
The project is focused on co-locating a production database. So, I have data feeds going into my publisher, and am replicating to a subscriber --- which is meant to simulate a co-location box. The idea being that the subscriber might need to BECOME "primary production" at some point. Therefore, I need the subscriber to either have all of the "stuff" (keys, constraints, etc.) that the publisher has...or else I need to have scripts ready to be run on the subscriber that will build all of this "stuff", in the event that it needs to become the primary production box, prior to pointing out data feeds to it.
Looking at one table by way of example (below)...
When I set up (transactional) replication for each table, SQL automatically builds the primary key INDEX on the subscriber (and there's no way (to my knowledge) to choose NOT to do this), but it does NOT build the primary key CONSTRAINT on the subscriber (and I haven't been able to figure out how to get it to do this).
Here's what the table looks like on the subscriber after replication set up and snapshot:
sp_help address_t
index_name index_description index_keys
-----------------------------
PK__Address_t__5F7E2DAC clustered, unique located on PRIMARY AddressID
No constraints have been defined for this object.
---------------------------------------------------------
Now, I've scripted out the keys and constraints for all of the replicated tables -- I don't want to have foreign keys, etc. in place on the subscriber while it is being replicated to, as I assume this stuff could interfere with replication. So, I want a script that I can run on the subscriber to BUILD this stuff prior to turning it into the primary box (in the event that we need to do so).
The problem I'm having is that this script also contains statements to build the PRIMARY KEY constraints...and I cannot run a statement to create the primary key CONSTRAINTS on the subscriber's version of the table due to the fact that the primary key INDEX is already there (from replication):
This statement.........
ALTER TABLE [dbo].[Address_t] WITH NOCHECK ADD
CONSTRAINT [PK__Address_t__5F7E2DAC] PRIMARY KEY CLUSTERED
(
[AddressID]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
Yields this..........
"Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Address_t' named 'PK__Address_t__5F7E2DAC'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors."
-------------------------------------------------------
And I would PREFER to not have to drop and recreate all of the (primary key) INDEXES on the subscriber because that will consume extra time while we're trying (presumably in a hurry) to get the co-located subscriber turned into a primary.
Can anyone point me in a direction here?
I've got my trigger stuff figured out (created on subscriber with "Not For Replication" option enabled) and even figured out how to deal with the identity fields in some of my tables. But this index/key/constraint stuff is confusing me.
Thanks,
-Skip
-
You can make most constraints NOT FOR REPLICATION, though not a primary key constraint. Usually if an index has PK_ in front of it it's part of the primary key constraint, so it's strange that your system says no constraints exist.
What we do is: Create the table at the publisher and the subscriber, including indexes, primary key, triggers (NOT FOR REPLICATION), foreign keys (NOT FOR REPLICATION) and constraints (NOT FOR REPLICATION). Then set up the article to not create the object at the subscriber. This is for merge replication. When I've set up transactional replication, I haven't been able to get it to not create the destination objects.
Whatever you do, I recommend creating all indexes, constraints, etc. ahead of time so you don't have to worry about it. If you use NOT FOR REPLICATION, then the replications agents shouldn't run into a problem.
HTH
Dylan Peters
SQL Server DBA -
You have several options here. What works best is to backup the publication database and restore it on the subcriber.
Then go through your subscriber database and
1) make all constraints Not For Replication
2) make all triggers Not For Replication
3) make all identity columns not for replication
Then on the publisher make all identity key's have a increment of 2 and run dbcc to bring them all to the nearest odd number.
On the subscriber make all identity keys have an increment of 2 and run dbcc checkident reseed to bring them all to the nearest even number.
While you can put your schemas in place on the subscriber, use a post snapshot command, select include DRI, or use a create table script (creation_script parameter in sp_addarticle), if sysdepends is out of sync (as it ususally is) your snapshot will bomb.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply