Merge Replication? Aaaarghhh!

Can anyone tell me what should be contained in MSrepl_identity_range tables
on both the subscriber and the publisher? Both my tables contain completly
different data - pub has 83 rows and my single sub has only 3.
When are these tables populated and how? Can I populate them manually from a
system SP?
I refer to my previous post where Hilary Cotter thought there might be an
issue with these tables.
When executing : exec sp_MSfetchidentityrange N'CommentType', 0
I get the following error:
Server: Msg 21195, Level 16, State 1, Procedure
sp_MSfetchAdjustidentityrange, Line 92
A valid identity range is not available. Check the data type of the identity
column.
Thanks in advance...
Chris,
this is a bit more complicated than it seems...
I have had cause to manually change the identity range on a subscriber - I'm
not recommending it but it did lead to a better understanding of the
mechanism involved!
If you are using automatic range management this'll be taken care of when
you synchronize (run the merge agent). However, if it is not possible for
you to connect to the publisher, you could manually update
MSrepl_identity_range on the subscriber. This table is used to check if the
subscriber has used up its range or reached the threshold. The new range you
set would be obtained from MSrepl_identity_range on the distributor, which
is the master table and is used to generate new values. The values in this
table (MSrepl_identity_range on the distributor) would need to be changed to
avoid a future potential conflict. Finally, the check constraints on the
subscriber would need updating accordingly.
As an aside, note that there are some anomalies with automatic range
management: the first range is twice the requested size and the actual range
of values enforced by the check constraint is always one less than the size
selected - SQL Server 2005 managed identities for merge replication has been
redesigned to be more consistent.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul. Thanks
I ended up removing replication from the DB and reinstating. I'm now having
a problem with creating the publication from a generated script! See later
post.
Thanks anyway for you help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%239GGiaZrFHA.2996@.tk2msftngp13.phx.gbl...
> Chris,
> this is a bit more complicated than it seems...
> I have had cause to manually change the identity range on a subscriber -
> I'm not recommending it but it did lead to a better understanding of the
> mechanism involved!
> If you are using automatic range management this'll be taken care of when
> you synchronize (run the merge agent). However, if it is not possible for
> you to connect to the publisher, you could manually update
> MSrepl_identity_range on the subscriber. This table is used to check if
> the subscriber has used up its range or reached the threshold. The new
> range you set would be obtained from MSrepl_identity_range on the
> distributor, which is the master table and is used to generate new values.
> The values in this table (MSrepl_identity_range on the distributor) would
> need to be changed to avoid a future potential conflict. Finally, the
> check constraints on the subscriber would need updating accordingly.
> As an aside, note that there are some anomalies with automatic range
> management: the first range is twice the requested size and the actual
> range of values enforced by the check constraint is always one less than
> the size selected - SQL Server 2005 managed identities for merge
> replication has been redesigned to be more consistent.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

merge replication and identity field problems.

Hi there,
I have converted MS access database to sql 2000 database and front end
is in adp. i our db all of our table contain identity field as a
primary key and forgine key. I am using merge replication with the
publisher and distributer in the same server where original db is and
may have many subscriber (pull subscription) using msde who will
synchronize on demand. i change all the autonumber field in access as
identity field (not for replication) and relationship between table
(not for replication) is clear off. I am very much worried now if i
just publish the database and subscript is whether i am going to have
conflict with identity field which are primary key or its going to
workin fine. Actually i donot know how sql handel those identity field
with so many copy of subscriber. please give me some information how
should i proceed.
I have table call Job, jobcarrier, jobshots, joblogs, jobpersonnel,
etc where primary key is identity field and all the table contain
forgain key from job table. and our replicated database using the same
front end as we are using. please give me inf. how it work and what i
should do.
Thansk very much
Indra.
Indra,
you have a choice to either let SQL Server manage the identity ranges or do
it manually. If you select to synchronize your tables on initialization,
different seeds will be selected for each subscriber, and the size of the
allocated range is determined by yourself (on clicking the article
properties elipsis button a configuration form appears). This is probably
the easiest method. If you want to do it manually you might be interested in
Michael Hotek's algorithms to ensure no overlap
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Indra,
It would be easier for you to let SQL handle the identity values. Also I hope you are taking of specifying "Not for replication" for all your relationships.
Regards,
Karthik.
|||HI Paul,
I check all the information, BOL, artical, knowledgebase etc and try
to publishe merge replication with pull subscribtion and its not
working as the way it should work.
1. All the identity field has been assign as not for replication
2. all the relationship has been clear off the option (enforce
relationship for replication).
3. I could run the subcription and synchornzed the data.
4. i inserted in subscriber and in the publisher database it both give
the same identity field.
5. When i synchronized, it doesnot display any error message but the
data inserted at the subscriber has been deleted and data inserted at
the publisher has been trasfer to subscriber (the conflict with pk
data in subscriber has been deleted.)
6. when i check the pulication property the option for automatic
identity assign and maintain is not highlighted.
Could you please help me where am i making wrong and how i can do
this.
I will appreciate your help.
Thanks.
Indra.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<##LLZhNSEHA.3988@.tk2msftngp13.phx.gbl>...
> Indra,
> you have a choice to either let SQL Server manage the identity ranges or do
> it manually. If you select to synchronize your tables on initialization,
> different seeds will be selected for each subscriber, and the size of the
> allocated range is determined by yourself (on clicking the article
> properties elipsis button a configuration form appears). This is probably
> the easiest method. If you want to do it manually you might be interested in
> Michael Hotek's algorithms to ensure no overlap
> (http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
|||Indra,
automatic range management is not enabled by default. Using:
exec sp_MShelp_identity_property @.tablename = N'TestIdent', @.ownername =
N'dbo'
will let you know if it is enabled. To get SQL Server to manage the range
you'll need to put a check in the box on the article properties, Identity
range tab. As far as I know, this isn't possible to do retrospectively, so
you'll need to recreate the publication.
HTH,
Paul Ibison

Merge Replication and Dynamic filters

Hi,

I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.

I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.

I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?

Filtering on HOST_NAME() will not work because several subscribers are on the same server.

Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).

I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...

What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.

Thanks for your help,

Best regardsThink about using pull subscriptions - you'll have more flexibility, including accounts for every agent.|||I have found the way to filter on each subscriber by forcing a different host name for each subscriber:
In the job that launch the merge agent, I add a parameter -HostName [CustomizedName]
My publisher filters then on a different host name for each subscriber.

Merge replication and dynamic filters

Hi,
I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.
I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.
I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?
Filtering on HOST_NAME() will not work because several subscribers are on the same server.
Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).
I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...
What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.
Thanks for your help,
Best regards

Quote:

Originally posted by jj.lequeux
Hi,
I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.
I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.
I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?
Filtering on HOST_NAME() will not work because several subscribers are on the same server.
Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).
I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...
What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.
Thanks for your help,
Best regards

I have found the way to filter on each subscriber by forcing a different host name for each subscriber:
In the job that launch the merge agent, I add a parameter -HostName [CustomizedName]
My publisher filters then on a different host name for each subscriber.