March 13, 2009 at 7:32 pm
Hi
I have used transaction replication to enable us to run a seperate reporting server. I am also keen to be able to make use of the DB's on the reporting server in a fail over situation where they would become the production DB's. I have seen a couple of articles on this forum that have helped greatly but I am not clear on a point about how the identity columns are treated.
I understand that is is necessary to change the 'not for replication' property for the identity to 'false' so that the insert into the subscriber is incremented. However (assumption 1 - please confirm) if the identity column does not contain a continuous range of numbers then the identity value on the subscriber will be less that the largest value in identity column leading to problems when failing over to it.
If this assumption is correct, would reseeding the identities on the subscriber prior to bring it on as the production DB solve the problem? If so does anyone know of any script that will do this task ( my t-sql is not good enough to crack this one).
Thanks in advance
Matt
March 17, 2009 at 8:01 am
how big is the table, if you have a problem it may be easier just to run a new snapshot
March 17, 2009 at 9:11 am
If this assumption is correct, would reseeding the identities on the subscriber prior to bring it on as the production DB solve the problem?
Assumption Correct. Reseeding subscriber prior to bring it backup solves the problem.
If so does anyone know of any script that will do this task ( my t-sql is not good enough to crack this one).
DBCC CHECKIDENT ( 'table_name', RESEED )
Is all you need BUT it has to be run on every table with identity columns
* Noel
March 18, 2009 at 6:12 pm
Hi. Thanks again for you assistance.
I searched around for a script to automate the reseeding and came across one that checked the identity values. I tweaked it so that it reseeds with the columns maximum value (as BOL initially lead me to believe that columns originally created using 'not for replication' are not reseeded unless you specify a new seed value). There may be a more efficient way to do it but I thought I would share the script in case it was of use to anyone else in the future.
Matt
***************************************************************************************/
SET NOCOUNT ON
declare @table_name as varchar(200)
declare @col_name as varchar(200)
declare @stmt as nvarchar(1000)
declare @value as int
declare my_cursor CURSOR
FOR
SELECT QUOTENAME(t.name),c.name
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
open MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @table_name, @col_name
WHILE (@@FETCH_STATUS=0)
BEGIN
--select @stmt =N'dbcc checkident('+@table_name+',NORESEED)'
select @stmt = N'declare @value as int select @value=(select max('+@col_name+') from '+@table_name+') select '''+@col_name+''',@value GO dbcc checkident('+@table_name+',reseed,@value)'
--select @stmt
exec sp_executesql @stmt
FETCH NEXT FROM MY_CURSOR INTO @table_name,@col_name
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO
March 19, 2009 at 12:45 pm
Thanks for sharing.
Actually there are no much more efficient ways to do it, you are on the right track. This is one of the reasons identity columns and replication are not very good friends 😉
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply