Abdul,
it's better not to use GUIDs for PKs as they are too
large (4x the size of an integer).
Identity columns can be used in merge replication - you
can enable sql server to maintian the ranges to ensure
they never overlap. Have a look at the article properties
of a table containing identity columns before it is
subscriber to, and there is a checkbox on the identity
range tab. The easiest way is tp ensure that the ranges
are so wide that there will never be any need for them to
be updated.
HTH,
Paul Ibison (SQL Server MVP)[vbcol=seagreen]
Thank you Paul for your reply. I used int primary key to avoid using GUID
because of speed issues but did not foresee that we would use replication
later on. Since there is a limit on using a integer key there is a possiblity
that all the numbers could get taken as there are a lot of rows, and many
rows get inserted daily.
Maybe a string key could work but GUID's are guaranteed to be unique
according to Microsoft. Maybe I can alleviate the speed issue by using a
combination of indexes and selecting only those records which need to be
viewed in the front end. This was the plan but I wanted to make sure that
GUID columns would not be affected when implementing merge replication as I
thought it adds its own guid column.
-Abdul Rauf
"Paul Ibison" wrote:
> Abdul,
> it's better not to use GUIDs for PKs as they are too
> large (4x the size of an integer).
> Identity columns can be used in merge replication - you
> can enable sql server to maintian the ranges to ensure
> they never overlap. Have a look at the article properties
> of a table containing identity columns before it is
> subscriber to, and there is a checkbox on the identity
> range tab. The easiest way is tp ensure that the ranges
> are so wide that there will never be any need for them to
> be updated.
> HTH,
> Paul Ibison (SQL Server MVP)
>
|||Abdul, integers go from -2billion to 2 billion so you're only going to have
a problem if the data spans >4billion!
If that is the case, you could partition the data using a composite key,
with one column as a subscriber identifier.
HTH,
Paul Ibison (SQL Server MVP)[vbcol=seagreen]
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Tuesday, March 20, 2012
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.
================================================== ====
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.
================================================== ====
replication and identity problem
Every time I start replication i get following error:
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>
Friday, March 9, 2012
Replication - Should I set the Primary Key to No?
Hello:
If I would like to do Transactional Replication - do I set the Primary Key in the Table to
Identity = 'No'
Identity = 'Yes' or
Identity = 'Yes (Not for Replication)'
Thanks,
BiancaIf you set it to 'Yes' , when the identity columns are transferred to the subscriber the identity property will not be transferred.
SQL requires that all identity columns use NOT FOR REPLICATION otherwise insert commands may not replicate properly
Originally posted by Bianca_J
Hello:
If I would like to do Transactional Replication - do I set the Primary Key in the Table to
Identity = 'No'
Identity = 'Yes' or
Identity = 'Yes (Not for Replication)'
Thanks,
Bianca
If I would like to do Transactional Replication - do I set the Primary Key in the Table to
Identity = 'No'
Identity = 'Yes' or
Identity = 'Yes (Not for Replication)'
Thanks,
BiancaIf you set it to 'Yes' , when the identity columns are transferred to the subscriber the identity property will not be transferred.
SQL requires that all identity columns use NOT FOR REPLICATION otherwise insert commands may not replicate properly
Originally posted by Bianca_J
Hello:
If I would like to do Transactional Replication - do I set the Primary Key in the Table to
Identity = 'No'
Identity = 'Yes' or
Identity = 'Yes (Not for Replication)'
Thanks,
Bianca
Replication - Identity Value problem
Hi,
I have a table with a tinyint (identity column). The current value in the
table is 2. And if I check with IDENT_CURRENT the next value is displayed as
3.
This table is an article in a merge replication. In EM if I check this
article the next value is shown as 127. And this is leading to problems in
replication. How can I get the 127 to something more manageable (say 3)?
Thank you.
Regards,
Karthik
You would be best not messing with this. However use DBCC
CheckIdent('tablename',reseed,3)
Hilary Cotter
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
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:ABEDD870-6C5F-473C-9B15-74594B434C29@.microsoft.com...
> Hi,
> I have a table with a tinyint (identity column). The current value in the
> table is 2. And if I check with IDENT_CURRENT the next value is displayed
as
> 3.
> This table is an article in a merge replication. In EM if I check this
> article the next value is shown as 127. And this is leading to problems in
> replication. How can I get the 127 to something more manageable (say 3)?
> Thank you.
> Regards,
> Karthik
|||Hilary Cotter wrote:
> You would be best not messing with this. However use DBCC
> CheckIdent('tablename',reseed,3)
>
I've noticed the same thing -- on an INT identity, the value suddenly
(after 3 inserts) jumps to about 1.5 billion -- 1/2 way through the
range, it seems. Is this a bug, or a "feature"?
I have a table with a tinyint (identity column). The current value in the
table is 2. And if I check with IDENT_CURRENT the next value is displayed as
3.
This table is an article in a merge replication. In EM if I check this
article the next value is shown as 127. And this is leading to problems in
replication. How can I get the 127 to something more manageable (say 3)?
Thank you.
Regards,
Karthik
You would be best not messing with this. However use DBCC
CheckIdent('tablename',reseed,3)
Hilary Cotter
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
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:ABEDD870-6C5F-473C-9B15-74594B434C29@.microsoft.com...
> Hi,
> I have a table with a tinyint (identity column). The current value in the
> table is 2. And if I check with IDENT_CURRENT the next value is displayed
as
> 3.
> This table is an article in a merge replication. In EM if I check this
> article the next value is shown as 127. And this is leading to problems in
> replication. How can I get the 127 to something more manageable (say 3)?
> Thank you.
> Regards,
> Karthik
|||Hilary Cotter wrote:
> You would be best not messing with this. However use DBCC
> CheckIdent('tablename',reseed,3)
>
I've noticed the same thing -- on an INT identity, the value suddenly
(after 3 inserts) jumps to about 1.5 billion -- 1/2 way through the
range, it seems. Is this a bug, or a "feature"?
Subscribe to:
Posts (Atom)