replication question

Hi,
We basically want to have a second hot copy of our database and keep it
up-to-date via transactional replication (non-updating subsrcriber) We are
using SQL Server 7.0.
If we restore the latest backup of the published database onto the
subscriber so we have everything in sync to start, manual inserts on the
publisher database fail to replicate because of identity column insert
problems. When it trys to replicate the insert to our subsriber database, it
seems to blowup because the table on the subsriber has identity ids as well.
How do we get around this? We only want the identity columns on the
subsriber to function when we make the subscriber the live database in the
case that the publisher has problems. Otherwise during replication, we just
want the IDs origianlly issued on the publisher to replicate to the
subscriber as intended.
thanksRead BOL for details on this topic:
Managing Identity Values
You can manage identity values by:
a.. Allowing Microsoft® SQL ServerT 2000 replication to automatically
manage identity columns by dynamically allocating ranges of identity values
to the Publisher and all the Subscribers.
b.. Using the Transact-SQL NOT FOR REPLICATION option when defining the
identity column.
c.. Using a primary key other than the identity column (for example, a
composite key or a rowguid column), if an identity column is not necessary.
This strategy eliminates the overhead of managing identity columns on the
replicated data.
You have a few options as stated above. I'd suggest to use rowguid column if
you can, avoiding identity column in general, if possible, when it comes to
any type of replication. Otherwise, "NOT FOR REPLICATION" identity column
would be my 2nd pick. This will preserve the identity values coming from
Publisher, and Subscriber's indentity values won't get incremented via
replication.
However, once your production server goes down and your backup server goes
live, the Subscriber's ID values will increment because of INSERTS. Now, if
this column is your primary key, then there's a potential that ID values
from Subscriber's and Publisher's will collide at some point, unless you
plan ahead. Also, you have to think about sync this data back to the
Publisher once the machine is back online.
Hope it helps.
HH
"aaz" <aaz@.webcapacity.com> wrote in message
news:edCboTVjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We basically want to have a second hot copy of our database and keep it
> up-to-date via transactional replication (non-updating subsrcriber) We are
> using SQL Server 7.0.
> If we restore the latest backup of the published database onto the
> subscriber so we have everything in sync to start, manual inserts on the
> publisher database fail to replicate because of identity column insert
> problems. When it trys to replicate the insert to our subsriber database,
it
> seems to blowup because the table on the subsriber has identity ids as
well.
> How do we get around this? We only want the identity columns on the
> subsriber to function when we make the subscriber the live database in the
> case that the publisher has problems. Otherwise during replication, we
just
> want the IDs origianlly issued on the publisher to replicate to the
> subscriber as intended.
> thanks
>
>|||On the other hand, you could use log shipping instead,
problem solved.
Regards
John