Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in SQL Server 2005 and I have an IDENTITY(1,1) property set for an ID column. Records are inserted in a table at a local server as well as a remote server. I am using Merge Replication to match the data from local server and remote server.

What happens if I insert the same ID at the local server and the remote server?

Please suggest what am I doing wrong here.

share|improve this question

migrated from stackoverflow.com Oct 31 '12 at 0:46

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers 3

When using Merge with identity columns — will need to specify an identity range management option of either automatic or manual so that duplicates do not occur.

With automatic, replication will automatically manage the assignment of identity ranges for nodes participating in the Merge topology.

With manual, you will need to manually assign "islands" of ranges for nodes using DBCC CHECKIDENT.

Have a look at Replicate Identity Columns.

share|improve this answer
add comment

You should use a uniqueidentifer - a GUID - instead of an integer identity. Then both databases will have uniquely identifiable row ID that is guaranteed to be unique.

See http://technet.microsoft.com/en-us/library/ms151206(v=sql.90).aspx

share|improve this answer
    
I am creating billing system so biil can be made Locally(offline) or through a remote location (online) and want to generate a bill number which is meaning full –  Rakesh Sawant Oct 26 '12 at 11:45
    
Well you'll have to find some other way of distinguishing them. That doesn't affect the fact you're going to have to have a GUID. –  podiluska Oct 26 '12 at 11:48
add comment

When you state:

What happens if I insert the same ID at the local server and the remote server?

I assume you are doing:

SET IDENTITY_INSERT [TableName] ON
INSERT INTO dbo.[TableName] (ID, ...)
VALUES ([x], ....)
SET IDENTITY_INSERT [TableName] OFF

The short answer: Do not explicitly insert ID's with a specific value into a table with MERGE replication in place, as @Brandon Williams stated, you should be letting Merge replication handle the IDENTITY ranges for each DB in your MERGE topology.

From MSDN:Replicating Identity Columns (emphasis added by me)

When you assign an IDENTITY property to a column, Microsoft SQL Server automatically generates sequential numbers for new rows inserted in the table containing the identity column. For more information, see IDENTITY (Property) (Transact-SQL). Because identity columns might be included as a part of the primary key, it is important to avoid duplicate values in the identity columns. To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur.

For example, the Publisher could be assigned the range 1-100, Subscriber A the range 101-200, and Subscriber B the range 201-300. If a row is inserted at the Publisher and the identity value is, for example, 65, that value is replicated to each Subscriber. When replication inserts data at each Subscriber, it does not increment the identity column value in the Subscriber table; instead, the literal value 65 is inserted. Only user inserts, but not replication agent inserts cause the identity column value to be incremented.

Note: Automatic range management uses thresholds to identify when should the DB ask for it's next range of ID's. ie: For a high throughput table you would want a large range size and a low threshold so that:

  1. @identity_range You don't need to keep requesting a new range of ID's every few seconds
  2. @threshold You allow enough time for the subscriber to negotiate the next range with the publisher and do not risk clients receiving errors as unable to write records
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.