Transactional Replication PT3 - Dealing with identity columns
- The database to be replicated was not designed with replication in mind.
- Tables contain identity columns and timestamps.
- The subscribing database must be fully functional and allow data input, data must not be updated at the publisher from the subscriber
- Everything must be scripted to allow for change control, auditing and repeatability, the use of the GUI or wizard(s) is not an option
PT2 .. described how to extract the table data required to identify timestamp columns, primary keys etc.
To make use of identity columns we have to set the identity property to YES ( not for replication)
This script will name and identify those identity columns , 1 = the property is set, 0 = the property is not set and needs to be chnaged prior to starting replication
select obj.name as "Table name"
,obj.id
,isnull(col.name,'') as "Identity Column Name"
,columnproperty( obj.id,col.name,'IsIdNotForRepl') as "Identity not for Repl"
from dbo.syscolumns col join dbo.sysobjects obj on col.id=obj.id
where obj.xtype='U' and obj.name not like 'sys%'
and col.autoval is not null