We are trying to setup a somewhat warm standby server using SQL Server 2000 Standard and one way transactional replication. (The powers to be won't spring for the Enterprise edition for the log shipping functionality)

If I have the wizards do everything it works great. The problem is that if we ever need to point our applications to the backup DB server then all the ID fields are INTs (not identity fields) and everything is going to fail.

I went through every table on the publisher and changed all the identity fields so they are set to Identity YES (Not for replication). I dumped that database to tape then restored it on the subscriber. I ran the sp_scriptpublicationcustomprocs on the publisher then ran the output script on the subscriber. The replication monitor seemed to be happy until the very first record was inserted into a table on the publisher. When it went to replicate the data over it errored out with Cannot Update identity column. I verified the Not for Replication setting on that table.

My boss read somewhere about an unsupported version of log shipping that can be run on SQL Standard. Has anyone heard of this and is it worth looking into

HELP!!

Thanks

Denny,
although log shipping out of the box is only available for enterprise
edition, it can be done on any edition with a bit of handcoding. At the end
of the day, it is simply copying a log from one box to another and restoring
it without recovering it, with a bit of logging thrown in for reporting
purposes. If you don't want to create it yourself, there are scripts to do
this on hte web (eg
http://www.sql-server-performance.com/sql_server_log_shipping.asp), in the
sql server resource kit (and the back office resource kit) but Microsoft
Product Support doesn't support these utilities.
HTH,
Paul Ibison

I just wanted to clarify a couple of things in regards to what I would like to accomplish

I really don't want to define identity ranges for both servers. The subscriber isn't going to be used unless the publisher dies and we need to point traffic to the backup server (subscriber)

Since the subscriber is over a frame relay link we want to apply the snapshot manually by dumping to tape, driving the tape to the second location then restore it on the subscriber. Because we apply it this way the seed and increment is identical to the publisher

I feel I am missing a key part here. It is my understanding that if the not for replication setting is set, then Replication is allowed to insert a value into the identity field on the subscriber. We will not be inserting records directly into the subscriber unless we need to fail over to it so that is why we didn't define ranges for each server

TIA

The error occurs because the store procedure on the subscriber does not
know about the identity column on the subscriber. You can ALTER the insert
procedure for all the tables that you added the identity to on the
subscriber and change it to not insert into the identity column.

Rand
This posting is provided "as is" with no warranties and confers no rights.