Replicating same table twice in different publication types

I have a table that I need to replicate twice. Once as part of an updating
subscriber transactional publication and once as a plain transactional repliaction.
This table has an identity column, which is causing the problem.
If I set the identity management to false, it complains that it conflicts
with how the article is already published. If I say true, then it complains
that this setting is ony requierd for updating subscriber publications.
Is there any way around this?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Hi Simon,
To let me better understand your issue, I would like to know the following
questions:
1. What do you mean replicate the table twice?
2. What is your SQL Server version?
3. What are the detailed steps so that I can reproduce your issue?
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.
================================================== ====
|||There are some publication properties which will affect an article for all
subsequent publications using this article.
This is one of them. One of the things you can do is use manual identity
range management if you have small numbers of subscribers.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a5b90c8c94fc14a0e5941@.msnews.microsoft .com...
> I have a table that I need to replicate twice. Once as part of an updating
> subscriber transactional publication and once as a plain transactional
> repliaction.
> This table has an identity column, which is causing the problem.
> If I set the identity management to false, it complains that it conflicts
> with how the article is already published. If I say true, then it
> complains that this setting is ony requierd for updating subscriber
> publications.
> Is there any way around this?
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
|||Hello Charles Wang[MSFT],
create a transaction replication publication with updating subscribers
create a table with an identity column
publish the table with identity management to auto
subscribe to the publication and table from one database
create a normal transactional replication publication
add the same table as an article. --This fails due tot he constraint on
the identity range management.
I want to do this because we have a scale out replication to architecture.
We run our websites against a subscriber DBs to distribute load.
I want to also puiblish to reporting server
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi Simon,
> To let me better understand your issue, I would like to know the
> following
> questions:
> 1. What do you mean replicate the table twice?
> 2. What is your SQL Server version?
> 3. What are the detailed steps so that I can reproduce your issue?
> 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.
> ================================================== ====
|||Hi Simon,
I understand your concern.
Since this is a product limitation, the replication could not be
implemented if there is Identity confiliction.
I recommend that you assign discrete ID value range for the table in
different publication.
For example:
publisher1.tblTest has the identity range:
[1-10000],[20001-30000],[40001-50000],[60001-70000],........
publsher2.tblTest has the identity range: [10001-20000], [30001-40000],
[50001-60000],.............
You can create a trigger for the table [tblTest], on publisher1, when the
current identity value reaches to the one max value in [10000, 30000,
50000,..., (2n+1)*10000,...] (n>=0), jump the current identity id to the
next minimal value in [1,20001,40001,...,2n*10000+1,...] (n>=0) by using
"DBCC CHECKIDENT";
on publisher2, you can use the same way, but the max values are in the set
[20000,40000,..., 2n*10000,...] (n>=1) while the min values
[10001,30001,...,(2n-1)*10000+1] (n>=1).
By this way, the two publications can work together without identity
confliction.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
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.
================================================== ====
|||Hi Simon,
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.
================================================== ====