Merge Replication Identity Ranges
Apr 5, 2008
I have a database table with a simple identity column. As a starting point... when I run the following queries... this is what I get:
SELECT Max(AliasID) FROM Account_Managers
1300006
DBCC CHECKIDENT ('Account_Managers')
Checking identity information: current identity value '1300006', current column value '1300006'.
Now... I set up merge replication, with the plan of allowing SQL Server to manage my identity ranges for me. I set up my ranges to be 100000 on the publication, 100000 on the subscriber, with a threshold of 80.
I would EXPECT to see the constraint
([AliasID]>(1300006) AND [AliasID]<=(1400006)) on the publisher and
([AliasID]>(1400006) AND [AliasID]<=(1500006)) on the subscriber.
However... what I do get is:
([AliasID]>(1300006) AND [AliasID]<=(1400006) OR [AliasID]>(1400006) AND [AliasID]<=(1500006)) on the publisher and
([AliasID]>(1500006) AND [AliasID]<=(1600006) OR [AliasID]>(1600006) AND [AliasID]<=(1700006))
Can anyone tell me why I get the OR... which essentially makes my identity ranges twice as large as they should be?
Thanks in advance.
- Alan D. Nelson
View 2 Replies
ADVERTISEMENT
Jan 31, 1999
Dear all,
How does an identity is affected by merge replication? Can sql control it so that there will be no replication conflict?
Here's the scenario, I have several tables with an identity column. the database resides in a server in the head office, there are several remote sites and remote users which will be running sql 7 desktop edition. Each remote will download only the data subset he is allowed to. Each site will be allowed add new rows on several table. Then they will update the head office server through replication.
Thanks
Reden
View 1 Replies
View Related
Aug 8, 2007
Sever: sql 2000
Replication: Merge
Issue:
I am having an issue with my audit table, This table is filled by Triggers on various tables through the database. All triggers are defied with "not for replication"
I have allocated 500k ranges, with 80% threshold to the publisher and subscriber databases for this table. The table only holds 225,000 records.
From time to time I get the following error "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'PublicationName', table 'AuditHistory', column 'AuditID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."
When I looked into the issue yesterday I noticed that the identity range being used by replication was 334300001 -> 334799999, however the maximum value in the table was 334300096, meaning that only 95 records were inserted, which means it is no where near the 80% threshold.
Somehow the identity seed on the AuditHistory table had been changed to 334800104, which is outside the allowable range.
My question is what could cause the identity seed to get set to such a high number??
any thoughts would be great!
View 2 Replies
View Related
May 16, 2015
We are running merge replication. My identity value was out of sync and the inserts stopped working. I Reseeded the identity value to 1500000 and ran "sp_adjustpublisheridentity" which set that to the new range (and table constraint) in the MSMerge file and table. I discovered my error and Reseeded the table back to 150000. How to I get the new range to be created when it is LESS than 1500000 in the MsMerge file???? Can I use the sp_restoreidentity....???
View 3 Replies
View Related
Nov 3, 2015
I was reading this:
"Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows."
I don't understand...
If I create a table with an identity column and publish it. Can the values on the subscriber be different when the data is replicated?
Suppose I have a this table:
1 Name1
2 Name2
3 Name3
Column 1 is identity field and column 2 the name of employees.
If I publish this table , the data can be inserted on the subscriber .pe, with 2 name1 and 1 name3 and 1 name2?
What about if the identify fields is a primary key?
View 6 Replies
View Related
Jan 8, 2008
Hi all i have setup merge replication with one publisher and 3 subscribers
The replication worked fine for about 2 months then i recieved the following error on all my subscriptions
The Publisher failed to allocate a new set of identity ranges for the subscription.
This can occur when a Publisher or a republishing Subscriber has run out of identity
ranges to allocate to its own Subscribers or when an identity column data type does
not support an additional identity range allocation. If a republishing Subscriber has
run out of identity ranges, synchronize the republishing Subscriber to obtain more
identity ranges before restarting the synchronization. If a Publisher runs out of identit
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
I have updated my sql server to service pack 2
The publisher is sql Standard edition and the subscribers are Express edition
Any help would be great
Thanx
View 3 Replies
View Related
Jul 23, 2007
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.
What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e
On Server 1 - Values start from 1 then 2,3,4 etc etc
Server 2 - 24001, 24002, 24003 etc etc
Server 3 - 46001, 46002, 46003 etc etc
Server 4 - 68001, 68002, 68003
My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?
Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.
cheers
View 1 Replies
View Related
May 8, 2006
Hello,
We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.
View 1 Replies
View Related
Feb 2, 2007
Hi All;
I am trying to set up Merge Replication on a database and want to set the IdentityRange Management to Auto for all my tables which use a Identity column.
In the wizard, on Article Properties Page, I can do this by selecting a Table, and going for its properties, but this is a tedious task as I have ~300 tables to set this property on.
Is there another way or a global location where I can set the property to true and even mention the ranges and the threshold, so that I dont have to pick each table and set it individually.
I am also aware of the fact that I can Generate a Script and modify it and run that, but I was looking for some way to do this in the wizard.
Thanks!
View 1 Replies
View Related
Dec 21, 2005
Hi,
I read the BOL on how the publisher will had out identity ranges to subscribers, but it was not clear if this was also the case for anonymous subscribers. Will merge replication with identity columns work with anonymous subscribers that sync via HTTPS?
Thanks,
Darrell Young
View 1 Replies
View Related
Sep 21, 2005
I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.
View 1 Replies
View Related
Feb 6, 2007
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
View 7 Replies
View Related
Feb 6, 2007
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
View 2 Replies
View Related
Feb 6, 2007
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
View 3 Replies
View Related
Apr 25, 2006
Hi All,
The following is the report from the SQL Server Mobile Subscription wizzard, Any Ideas?
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error)
Messages
A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit
HRESULT 0x80045647 (0)
Invalid parameter @subid specified for sys.sp_MSmerge_log_idrange_alloc_on_distributor.
HRESULT 0x0000523F (0)
The operation could not be completed.
- Finalizing Synchronization (Stopped)
- Saving Subscription Properties (Stopped)
Initially i thought it might be that some of the articles had primary keys that were of type nvarchar rather then Int thus resulting in no identity range being able to be assigned to those articles.
Test 1: I tried removing all articles that had nvarchar primary keys and left only one table that had an identity Int primary key colum. I then ran the snapshot agent. I then run through the Subscription Wizzard again and the error was the same.
Test 2: Then reading the error message again i tried those tables that didnt use identity columns and the wizzard completed successfully. Any idea what would be wrong with my articles that have identity columns. The article properties for the identity columns use the Identity Range Management defaults.
Any help would be appreciated.
View 18 Replies
View Related
May 30, 2008
Hi all,
I have a SQL Server 2005 Compact Edition database that synchronizes with a SQL Server 2005 Standard database via merge replication.
The problem I have is when I cancel a synchronization, the database is left in such a state that I cannot add any new records. I dug around a bit and have found that in the INFORMATION_SCHEMA.COLUMNS view, AUTOINC_MAX has been incorrectly set to the same value as AUTOINC_MIN for the primary keys, which explains the 'out of range' error I get in my program when running off such a database.
In contrast, a non-corrupted database has AUTOINC_MAX set to AUTOINC_MIN + 999, presumably because of my subscriber identity range of 1000.
If this is the only thing that has gone wrong, all I need to do is programmatically set the AUTOINC_MAX values to AUTOINC_MIN + 999, however I can't find out where to do this (and INFORMATION_SCHEMA.COLUMNS is a view so it can't be updated obviously.)
I have also heard that 'compacting' the database may help, but I don't know how to do this either!
Any help would be immensely appreciated!
Regards,
Jonathan.
View 8 Replies
View Related
Jan 17, 2007
Hi.
First of all, I apologize for my english
I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.
But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"
If i'm watching the status of the leftover replication through the replication monitor, i get this error message :
"Error messages:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"
I checked the given links but they're useless.
So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Failed to pr"
I didnt have any idea to correct this issue, so I would appreciate any help.
Thanks.
David.
View 7 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Mar 6, 2007
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Nov 22, 2006
I am using merge replication with automatic identity range management
I want to be able to give a disconnected subscriber a warning message if a certain percentage of the allocated subscriber identity range has been used
- so that they can make sure they reconnect soon to obtain a new identity range
I can get the current identity value OK, but I can't find a good way of finding the current permitted primary and secondary ranges
The information is available in the check constraint, but I don't really want to have to parse this!
CONSTRAINT [repl_identity_range_0DF907AC_40CF_4849_9BD0_2173C90A3805] CHECK NOT FOR REPLICATION (([ContactID]>(10000040000.)
AND [ContactID]<=(10000050000.) OR [ContactID]>(10000050000.) AND [ContactID]<=(10000060000.)))
I can't find any system view which will provide this information
Is there a recommended way of doing this?
Thanks
aero1
View 3 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Dec 1, 2005
I have implemented replication between 2 servers (SERVER1 AND SERVER2) and for test purposes i just have 1 table(Table1) in the publisher.
View 1 Replies
View Related
Feb 14, 2002
Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance
View 1 Replies
View Related
Aug 12, 2002
Hi,
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.
Please help me anybody.
View 6 Replies
View Related
Jul 31, 2000
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
Thank you in advance for any help!
View 1 Replies
View Related
Jan 22, 2001
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"
What does this mean.
Thank You,
John
View 1 Replies
View Related
Dec 19, 2000
Hi,
I read some where that replication has two types conflict resolution, 1. row based and 2. Column based...
If I am right...
Can any one point me how to find out this option and how to set it up....
Thanks,
Mohammed.
View 1 Replies
View Related
Sep 16, 1999
Hi all,
I have a merge replication going between 4 servers. The problem is when ever I do some BCP transfer to one of the tables in one of the servers. It puts the data in that table. But that Data does not get replicated to any other server like it should.
Please Advice on what to do. Is there any option I am forgetting to set or something.
Thank you for all your time in advance.
Aziz
View 3 Replies
View Related
Sep 12, 2003
I have successfully tried merge replication on single server with 2 databases.
now i want to do the same with different servers,
when i create pull subcription on server 2 which user account should I use?
it is giving log in failure
i tried using windows admin account and also the 'sa' account.
please help me out
thanks
View 1 Replies
View Related
Feb 23, 2004
Hi All,
I have posted this earlier and I am re-posting it simplifying what I had said.
The scenario is:
I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.
When I do pull merge subscription I have two choices -
1.Bringing schema and data to subscriber from publisher
2.Not bringing the schema and data from publisher to subscriber.
Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.
Is there any way I can make this work??
Niben
View 10 Replies
View Related
Jan 2, 2007
Hi,
I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated? If not, what other methods should I use?
I would really appreciated any comments or advice out there!
-whitebelt
View 14 Replies
View Related
Mar 21, 2002
I had set up merge replication. I got these error messages where replications starts "Column names in each table must be unique. Column name 'PubID' in table 'bonflict_DBName_PHP_Data_Publications' is specified more than once "
PHP_Data_Publications table defind as:
CREATE TABLE [dbo].[PHP_Data_Publications] (
[PHP_Pub_ID] [uniqueidentifier] NOT NULL ,
[PHP_Data_ID] [uniqueidentifier] NOT NULL ,
[PubID] [uniqueidentifier] NOT NULL ,
[UserID] [uniqueidentifier] NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pub_Year] [datetime] NULL ,
[Pub_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PHP_Data_Publications] WITH NOCHECK ADD
CONSTRAINT [DF__PHP_Data___rowgu__01E91FA0] DEFAULT (newid()) FOR [rowguid]
GO
CREATE UNIQUE INDEX [index_1466488303] ON [dbo].[PHP_Data_Publications]([rowguid]) ON [PRIMARY]
GO
Thanks,
Chanthol
View 3 Replies
View Related
