replication and the identity column

I have been dealing with transactional replication a little bit and I am a
bit confused with the identity column and its affect on replication.
It seems like SS doesn't want to replicate the identity the subscriber. The
Dialogue box directed me to set the property to "not for replication", when I
did that the identify is blank in the subscriber db.
If I want to use the subscriber DB as a DR database, and the app needs the
identity column to be the same, isn't there a way around this limitation.
can someone point me to a resource that explains this concept?
thanks
Hi,
I understand that you would like to replicate the identity column to your
subscriber, however the process failed. You would like to know why.
If I have misunderstood, please let me know.
This is a known by design limitation. Please refer to this article:
Managing Identity Values
http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx
You may perform a test to check if the third method using other columns as
primary keys helps. If it could not fit your requirement, I am afraid that
you may consider to use an application to implement the replication by
yourself.
Please feel free to let me know if you have any other questions or
concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||The simplest solution is to set up the subscriber as a queued updating
subscriber, and allocate a large range for the identity ranges. This will
ensure the identity column is retained, as well as retaining the PKs.
HTH,
Paul Ibison
|||I normally use bi-directional transactional replication with odd values on
one side and even on the other. IE an odd seed on the publisher , and an
even on the subscriber. Set the increment to 2.
Put the schema in place on the subscriber and put the data there. Then do a
nosync and ensure that the identities are the correct values.
Use DBCC checkident to verify that the next assigned value on the Publisher
is odd and even on the subscriber.
While you can use queued updating for this, queued is designed for
situations where the majority of your dml occurs on your publisher not your
subscriber and it uses triggers to track changes occurring on the
subscriber. This will add latency to every DML occurring on the subscriber.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>I have been dealing with transactional replication a little bit and I am a
> bit confused with the identity column and its affect on replication.
> It seems like SS doesn't want to replicate the identity the subscriber.
> The
> Dialogue box directed me to set the property to "not for replication",
> when I
> did that the identify is blank in the subscriber db.
> If I want to use the subscriber DB as a DR database, and the app needs the
> identity column to be the same, isn't there a way around this limitation.
> can someone point me to a resource that explains this concept?
>
> thanks
|||thanks, so this is someting that would be set up when creating the subsciber?
i don't see this option anywhere on my test replication scenario i have
running.
"Paul Ibison" wrote:

> The simplest solution is to set up the subscriber as a queued updating
> subscriber, and allocate a large range for the identity ranges. This will
> ensure the identity column is retained, as well as retaining the PKs.
> HTH,
> Paul Ibison
>
|||thanks for the reply. in this scenario would the identity values be the same
on the subscriber side then? doesn't read that way.
"Hilary Cotter" wrote:

> I normally use bi-directional transactional replication with odd values on
> one side and even on the other. IE an odd seed on the publisher , and an
> even on the subscriber. Set the increment to 2.
> Put the schema in place on the subscriber and put the data there. Then do a
> nosync and ensure that the identities are the correct values.
> Use DBCC checkident to verify that the next assigned value on the Publisher
> is odd and even on the subscriber.
> While you can use queued updating for this, queued is designed for
> situations where the majority of your dml occurs on your publisher not your
> subscriber and it uses triggers to track changes occurring on the
> subscriber. This will add latency to every DML occurring on the subscriber.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>
>
|||They would be the same. Rows entered on the publisher will be odd, rows
entered on the subscriber will be even.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:DB5F2603-41BF-4C98-B1C9-D69C2BFCC184@.microsoft.com...[vbcol=seagreen]
> thanks for the reply. in this scenario would the identity values be the
> same
> on the subscriber side then? doesn't read that way.
> "Hilary Cotter" wrote:
|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====