2

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 propagated. I am aware that there are settings for this, but will propagating the foreign keys be a problem now that the identity columns are disabled (which are the primary keys)?

Improve this question

2 Answers 2

Reset to default
5

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.

Improve this answer
10
  • 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' Commented Dec 5, 2011 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? Commented Dec 5, 2011 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. Commented Dec 5, 2011 at 22:22
  • Thanks. Testing it now on a small database (our main database is 50 GB!) Commented Dec 5, 2011 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. Commented Dec 5, 2011 at 22:53
2

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

Improve this answer
2
  • 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? Commented Dec 6, 2011 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. Commented Dec 7, 2011 at 12:46

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.