Fix identities after migrating through SQL Replica

  • Article

One of the most common migration procedures to SQL Azure is by configuring replication from your previous environment to your brand new SQL DB: https://msdn.microsoft.com/en-US/library/mt589530.aspx  

This is a very nice migration process, as it allows original database to be available in Production until very few moments before SQL Database goes live.
However, if your database has tables with Identity columns, you should have the following in mind: the tables that are created and filled by replication agents, without any manual action on them, are considered empty by identity mechanism.

The reason why they are considered empty is because identity has the property “NOT FOR REPLICATION”, as they should have. This means that DML operations made by Replication agent in the table are not tracked. Therefore, for identity concerns, the table is empty.

What would happen if you configure replication to migrate and then stop replication and redirect your application to your Azure SQL DB? First row to be inserted in these tables will use the identity criteria for empty tables: Start for the identity seed (usually 1). This is definitely not what we want.
Luckily, there is an easy way to avoid this problem. Before redirecting your application to your Azuer SQL DB, you can reseed your tables. As an example, you could execute below script.

DECLARE @tablename VARCHAR(50) -- table name
DECLARE @columname VARCHAR(50) -- column name
DECLARE @schemaname VARCHAR(50) --schema name
DECLARE @maxid INT-- current value
DECLARE @newseed INT --new seed
DECLARE @newseed_string VARCHAR(50)
DECLARE @sqlcmd NVARCHAR(200) -- cmd
CREATE TABLE #Maxid(value int)

DECLARE identity_cursor CURSOR FOR
SELECT OBJECT_NAME(ic.object_id), ic.name, s.name
FROM sys.identity_columns ic
join sys.objects o ON ic.object_id=o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
where o.type='U'

OPEN identity_cursor
FETCH NEXT FROM identity_cursor INTO @tablename, @columname, @schemaname

WHILE @@FETCH_STATUS = 0
BEGINSET @sqlcmd='INSERT INTO #Maxid SELECT TOP 1 '+ @columname+ ' from '+ @schemaname+'.'+@tablename + ' order by ' +@columname+' desc'
exec sp_executesql @sqlcmd
SELECT TOP 1 @maxid= value FROM #Maxid
SET @newseed=@maxid +1
TRUNCATE TABLE #Maxid
SET @newseed_string=@newseed
SET @sqlcmd='DBCC CHECKIDENT ('''+@schemaname+'.'+@tablename+''', RESEED, '+@newseed_string+')'
exec sp_executesql @sqlcmd
FETCH NEXT FROM identity_cursor INTO @tablename, @columname, @schemaname
END

DROP TABLE #Maxid
CLOSE identity_cursor
DEALLOCATE identity_cursor

Happy Azure-ing!