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)?
Best Answer
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.