Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure.

The replicated database is read only, so I backed it up, restored it, and tested it.

I noticed straight away that the identity columns didn't work.

For instance, the original schema was like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT NULL,
[CarID] [int] NOT NULL,

Then, on the replicated copy, it looks like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CarID] [int] NOT NULL,

That means the identity columns are disabled, right?

Is there a way to get around this and make this database an independent copy in its own right?

Also, I noticed that the foreign keys are not propgated. I am aware that there are settings for this, but will propogating the foreign keys be a problem now that the identity columns are disabled (which are the primary keys)?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

If you want a standby copy of your data for HA/DR then is much better to use log shipping or database mirroring. Since these technologies create a physical database copy, you avoid exactly the problem(s) you mention. Also, schema changes 'replicate' much easier.

If you insist on transactional replication, seed your replicas with a backup instead of a snapshot. See How to: Initialize a Transactional Subscription from a Backup for a step-by-step guide.

share|improve this answer
    
Yeah, unfortunately I am not the DBA, so I have been given a couple of servers and told - restore the database and 'make it work' –  peter Dec 5 '11 at 22:06
    
So to break it down are you telling me that once I have the database in the state I have it I cannot restore it and use it separately? –  peter Dec 5 '11 at 22:09
    
You can, but you have to redo all the missing pieces not replicated by the initial snapshot seeding. It would be infinitely easier to start from a backup of the publisher. –  Remus Rusanu Dec 5 '11 at 22:22
    
Thanks. Testing it now on a small database (our main database is 50 GB!) –  peter Dec 5 '11 at 22:43
    
I think our DBA has seen the light. He is investigating mirroring. I believe there was a reason he stayed away from mirroring, but surely that reason would be smaller than all this. –  peter Dec 5 '11 at 22:53
show 5 more comments

NOT FOR REPLICATION on an IDENTITY column simply means that the identity column value is not incremented on the subscriber when a replication agent performs an insert operation. In plain English: When a change is propagated from the publisher to the subscriber, the IDENTITY value, at the subscriber, does not get increased on the subscriber. The value that the publisher gives to the subscriber is simply accepted. This is functionally equivalent to executing SET IDENTITY_INSERT ON; prior to your INSERT operation.

What all of this means is that the IDENTITY property for that table is NOT disabled, unless it is a replication agent inserting data into the table. This also means that propagating your foreign keys shouldn't be a problem.

Despite this, if you still want to remove this NOT FOR REPLICATION option for the identity field, execute the following:

ALTER TABLE [dbo].[Inspection]
    ALTER COLUMN [InspectionID] DROP NOT FOR REPLICATION;

Afterwards, you may want to reseed the identity. A brute force method of doing this is the following:

DBCC CHECKIDENT('dbo.Inspection', RESEED, 0);
GO

DBCC CHECKIDENT('dbo.Inspection', RESEED);
GO

I hope this helps in some way,

Matt

share|improve this answer
    
OK, thanks. That's interesting. It's still quite weird that MS would allow you to enter data into the replicated database if it is never going to be replicated back to the primary database - or perhaps I am wrong about that? –  peter Dec 6 '11 at 20:12
    
@peter I am not anything close to being an expert on replication, but I would imagine that this behavior is present due to the possibility of updatable subscribers. The other answer is probably closer to a true solution to your problem (use log shipping), but I did want to provide a little information on the specifics you asked about. –  Matt M Dec 7 '11 at 12:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.