Primary Key and Idenity check apply to subscriber db for disaster recovery

Question

Primary Key and Idenity check apply to subscriber db for disaster recovery

Danette Mondou

Valued Member

Points: 70
More actions
March 18, 2004 at 11:16 am

#84539

Hi, We are currently trying to create a disaster recovery plan. Curently we have a publisher, distributor and a subscriber. The publisher has primary keys and identity checks that are not on the subscriber. We wish to create a script that will apply these to the subscriber. I currently have a script that will apply the primary key to appropriate tables. But to create/apply the indentity, you have to copy the data, drop the table, create the table with the identity and then copy the data back into the table. Or is there another way to create/apply the identity without doing this. By the way we are using Transactional Replication.
The following applies the primary key to the table dbo.app_parms to the column parmkey:
ALTER TABLE dbo.app_parms
add CONSTRAINT parmkey_pk PRIMARY KEY (parmkey)
Similar code, but for the identity to be applied the column actually has to be added, where in the code above it says to add the contraint primary key, but it applies it not actually add.
/* Add a PRIMARY KEY identity column. */
ALTER TABLE app_parms
add parmkey INT IDENTITY
CONSTRAINT parmkey_pk PRIMARY KEY (parmkey)
go
Any suggests.
Thanks for your help!
Danette

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

Are you saying that when you have created and populated the table app_parms the run this statement to add the primary key it doesn't work? Do you get an error message? You shouldn't have to save the data in some temporay place, recreat the table with the identity and rrecopy data. The alter statement should be fine. Are you getting an error wrt foreign key constraints?

Francis

Danette Mondou Valued Member Points: 70 More actions · Answer 2

ALTER TABLE app_parms

add parmkey INT IDENTITY

CONSTRAINT parmkey_pk PRIMARY KEY (parmkey)

go

error: Server: Msg 2705, Level 16, State 4, Line 1

Column names in each table must be unique. Column name 'parmkey' in table 'app_parms' is specified more than once.

If I do not have the column parmkey already there the command works.

fhanlon SSChampion Points: 13131 More actions · Answer 3

The line 'add parmkey INT IDENTITY' is trying to add a column called parmkey. If this column already exists you will get an error. If it already exists use the other command you mentioned:

ALTER TABLE dbo.app_parms

add CONSTRAINT parmkey_pk PRIMARY KEY (parmkey)

Francis

Danette Mondou Valued Member Points: 70 More actions · Answer 4

That does not resolve the problem. The first script allows you to apply a primary key. I need to apply the primary key and to apply the identity as well. As you can see in both commands it does an add, but the first only applies the primary and the second will not allow you to apply the identity. What I need to know is how to apply an identity to a previously created column.

Thanks, Danette

fhanlon SSChampion Points: 13131 More actions · Answer 5

Your are right Danette, looking at BOL you can't change a column to have an identity value. You must drop/recreate it.

See http://www.devarticles.com/c/a/SQL-Server/Replication-SQL-Server-2000--Part-1/6/ for some suggestions about using Identity with replication. In brief you should look in using NOT FOR REPLICATION on the identity fields.

1) Before subscribing, you must create the table at the subscriber (by default, replication will not transfer the IDENTITY property to the subscribers) and don't forget to create the IDENTITY property with NOT FOR REPLICATION option.

2) After creating the publication, go to publication properties window and choose the Articles tab. Click on the properties button (...) against the table that has IDENTITY property. Go to 'Snapshot' tab. Choose 'Keep the existing table unchanged'.

Francis

Danette Mondou Valued Member Points: 70 More actions · Answer 6

This is great if you want to make changes to your current subscriber/publisher. But we do not want to make any change. What we want to do is create a script that will apply the primary key and identity to the subscriber if anything should happend to the publisher. What we plan is during a disaster recovery, is to turn off the replication and use the subscriber as the production database. We could do this by applying a couple of scripts that will bring the subscriber up looking exactly like the publisher. One of the scripts we wish to run is to apply the primary key and the identity. The primary key part works great, but the identity part only want to work when a table is being created.

If you go into the design table, you can turn the identity on or off, I just want to be able to turn the identity on for the column specified in a script.

Thanks for all of your help thus far.

andrewbreen Grasshopper Points: 21 More actions · Answer 7

To add a column to a table (or replace a current column) with an identity value you need to

- rename your existing table to a temporary location,

- create the new table with the identify column in place

- set IDENTITY_INSERT <table> ON

- Copy the data back in

What I did to get round this is to create a whole new database using the replicated database as a base copy. So I do not modify the replicated database at all. This makes it easier when the primary db is set back to the primary server - everything starts working again.

By creating a new database I generated a script (using a DMO program) that creates the whole structure of the database and copies every table from the base replicated database.

So for every table we do something like

CREATE TABLE <table> ( ... ) ON PRIMARY

GO

GRANT ... ON <table> TO <role>

GO

SET IDENTITY_INSERT <table> ON

GO

IF EXISTS(SELECT * From ReplicatedDB.dbo.<table&gt

EXEC('INSERT INTO dbo.<table>

( <fields...&gt

SELECT <fields...<

FROM ReplicatedDB.dbo.<table> TABLOCKX')

SET IDENTITY_INSERT <table> OFF

GO

Once data is copied into tables we then apply the primary keys, foreign keys, etc - then I even scripted the stored procedures. Once the database is correct I still have my base replicated database and can then put the new database into a live/in use environment.

Danette Mondou Valued Member Points: 70 More actions · Answer 8

I believe the true answer to this question is that you cannot make a simple script to be applied to the subscriber. Here is another response that I got off the Miscrosoft Newsgroup website. So far I like this one the best which is very similar to fhanlon response #6 of this thread.

Here is the response from the MS Newsthread under replication: when you get the the specify articles dialog of the create publication

wizard, click on the browse button to the left of each table. In the

snapshot tab, select leave existing table unchanged.

Then you can script out the tables on the publisher with the contrainsts you

wish. Modify every constraint to be NFR (Not For Replication) and run this

script on the subscriber, then deploy your snapshot.

Thank you all for your help with this question.

Danette