sql server - Sync identity columns between test and prod -
we have table holds enumeration values , application parameters values in our app database. these records use identity columns primary keys. have dev, test , prod databases. need sync primary keys between test , prod databases every time release update.
what want ask is, preferred method of syncing identity columns? 1 option thought of remove identity property primary key column , manually create ids same on dev , prod.
what thoughts?
you need have reason add identity
property pk
. apparently identity
automates way pk generated , allows forget code max(id)+1
. if doesn't make sense in case (as far understand values pre-defined , codebase expects concrete values) removing identity column should solution.
also practise add consitency checks in datascripts , let them fail or notify if there conflicts. non-identity approach like:
if exists (select 1 enum_table id = @idtoinsert) --notify there conflict -- else insert @idtoinsert
in 1 of projects find out weekly refresh between test , prod databases suites best us, consider investigating variant too.
pros:
- you have fresh/consistent data/schema reproduce prod issues
- no need remove identity column lookup tables (in case makes sense - enum values can appear not via manual scripts)
cons:
- may not sufficient when many teams working on same test env, feel depends on how process organised
hope helps
Comments
Post a Comment