Primary Key and Idenity check apply to subscriber db for disaster recovery
-
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
-
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
-
ALTER TABLE app_parms
add parmkey INT IDENTITY
CONSTRAINT parmkey_pk PRIMARY KEY (parmkey)
go
error: Server: Msg 2705, Level 16, State 4, Line 1Column 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. -
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
-
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
-
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
-
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.
-
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>
EXEC('INSERT INTO dbo.<table>
( <fields...>
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.
-
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 publicationwizard, 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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply