Franck MIKULECZ's List: Databases

• Database is under version control
• The database version is stored in the database itself
• The database version is verified by applications on start-up
• In complex database schemas
• the best practice is to disable foreign keys before insertions and re-enable once the data is in.
• you should at least consider using them in your new projects
• Write environment-independent scripts

  • That's where Liquibase comes in handy
• should not contain any paths, names of database instances, SQL user names or linked server configurations
• split the whole database into several schemas
• multiple teams are working on a database
• Every schema has its own version and upgrade scripts
• DbVersion table

  • That's how they do it !
• other teams need to be consulted
• Never change upgrade scripts once they have been checked in
• it is hard to detect situations when different versions of the upgrade script have been applied to a database instance
• Reserve version ranges when working on multiple releases to simplify merges
• with automation the upgrade process could be easily plugged into the CI process.

•  
   
  1. Check the existing version of the database
   
  2. Search for the upgrade script corresponding to the current version (this step relies on file naming convention being aligned with db version)
   
  3. If the file has been found, then execute it and verify the output; quit on error
   
  4. If no scripts have been found, then quit
   
  5. Return to step 1
• it is good to verify consistency of your upgrade scripts after every commit
• Upgrade_1.0.1_1.0.2_rename_column.sql

  • Cool and simple...
• Any breaches should fail the build.

•  
   
  • The first being a restored depersonalized copy from production, on which upgrade scripts have been applied
   
  • The second being created from the database project(s) and comparing them. Any differences should fail the build.
• Backup the database before upgrade
• The step should be automated.

22 more annotations...