November 3, 2015 at 12:57 am
Hi,
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 fiels is a primary key?
Best regards,
Pedro
November 3, 2015 at 2:03 am
Adding a column to an article is managed with a DDL command replicated at the subscribers. In this case it will be something along these lines:
ALTER TABLE SomeTable ADD SomeColumn int identity(1,1)
This adds the column at the subscriber, but does not guarantee that the subscriber gets the same values in the column that you have at the publisher. The values in the column are added based on the execution path that the optimizer decides to pick to order the data (usually clustered index order if a clustered index exists, otherwise physical order of rows in the heap). This means that the same logical row idenitifed by the rowguid column can get two different values at publisher and subscriber, which is not possible.
I hope this clarifies thing for you.
-- Gianluca Sartori
November 3, 2015 at 2:04 am
BTW, if you need to perform this operation, you will need to drop the article from the publication and add it back after adding the identity column.
-- Gianluca Sartori
November 3, 2015 at 3:17 am
It seems like your answer is based on the fact that I will create a column (identity) on an archical already publicated.
But the question is :
Suppose I create a new table which has an Identity column.
After and only after, create this table I publish it (so it becomes an artical).
In this case, can I have the problem of difference in identity fields between the subscriber and the publisher?
Thank you
November 3, 2015 at 4:19 am
No: in this case they will have the same values (that's why this operation is permitted, while adding an identity column afterwards is forbidden).
If you publish your table with an identity column, publisher and subscribers work on assigned identity ranges.
New rows inserted at publisher will have identity values in the publisher range and each subscriber will create identity values in their assigned ranges. When a range is exhausted, a new range is allocated. The merge agent takes care of this.
You can also override this behaviour with a custom range management.
As far as existing rows (at the time of publication) is concerned, they will be published with their values in the identity column. The application of the initial snapshot at the subscribers will insert the rows with their identity values unchanged, so publisher and subscribers will have the same exact data.
When new rows are inserted at publisher and/or subscribers, the merge agent will insert the rows without changing the values in the identity columns.
Hope it makes sense now.
-- Gianluca Sartori
November 3, 2015 at 5:27 am
Slight word of warning I found with identity fields and merge rep in sql 2012
The default range size from memory is 10,000. But SQL Server preallocates 1000 at a time.
For example
Subscriber has 10,001 to 20,000 range for the identity field
a.Insert row it gets 10,001
b.Insert row it gets 10,002
c.Reboot device (we were using rather rubbish devices that crashed unexpectedly)
d.Insert a row it got 11,001
after c and d occurred a few times without a sync (rubbish, remote not connected to network all the time devices) it tried to insert to 20,001 and fails horribly
Due to not being able to change the fact they were not on the network or completely rubbish I had to create a sproc that ran on server start to reseed each identity field in the database
November 3, 2015 at 6:58 am
It's a well know issue with SQL Server 2012, documented here: https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity
Basically, SQL Server 2012 pre-allocates identity columns 1000 at a time and every service restart determines a gap in the identity columns.
Trace flag 272 reverts to the pre-2012 behaviour.
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply