Database change managementIntroductionTarantino Database change management provides a set of tools which make the process of propagating database schema and data changes to multiple environments frictionless. For a detailed presentation on this subject look at this: Database Change Management The problem that Database Change Management is attempting to solve:- Most significant business applications rely on at least one relational database for persisting data
- As new features are developed, database schema changes are often necessary – i.e. new tables, columns, views, and stored procedures
- Database schema changes and corresponding code changes must always be deployed together
- While deploying software to a production environment, code files and libraries may usually be deleted or overwritten – Database files, however, must be intelligently manipulated so as not destroy vital business data
The development tools available allow developers to make changes to their environement and do not address the problem of applying those changes to additional environments. (i.e. development, quality assurance, staging, production).
The solution to this problem:Successful database change management requires that a consistent process be applied by all team members. Without a consistent process than the tools provided in this solution will not provide its full value. The proposed/ideal process that uses the Tarantino Database change management tools, would consist of: - Each developer using their own local database to do their development work.
- Each environment using it's own database. i.e. Development, Testing, Staging, Production
- Each developer maintains his changes locally. When the database changes are ready to commit along side the application source code, the developer follows these steps:
- Create a change script that wraps all of the database changes into a single transactional change script. A Tool like Red Gate SQL Compare makes this a 30 second operation.
- Save the change script in to a folder in your source tree call Update.
- Commit the change script along with the source code that corresponds to the change.
- The continous integration server detects changes to the source control repository than it:
- It builds the application code.
- It executes the applications unit tests.
- Executes the database create task to create a new database with all of the changes that are in source control.
- Executes the projects integration (data access) tests.
- Marks the buils a success when all the tests pass.
- Each developer runs the build script locally after receiving new schema changes scripts from the source code repository.
- The testers, developers, change management managers execute the script using the Database Update (windows form) tool to run the database scripts against the Dev, Test, and staging environments when the environments receive their next update of the source code.
PrerequisitesThe following environmental conditions make the use of the database change managment process frictionless. - An automated build script. This includes compillation, unit tests, integration tests, versioning, packaging, publishing
- Source Control (why do some people still think a zip file is source control?)
- A continous integration server that has a seperate database instance dedicated to executing schema change scripts and integration tests
- A team that believes that a little process can go a long way.
- Seperate environments for testers, marketing/content types, and staging.
- The will to do things better
DetailsTo use the manage database tasks the following assemblies are need. Place these in your NAnt binary directory: - Tarantino.Core.dll
- Tarantino.DatabaseManager.Tasks.dll
- Microsoft.SqlServer.BatchParser.dll
- Microsoft.SqlServer.ConnectionInfo.dll
- Microsoft.SqlServer.Replication.dll
- Microsoft.SqlServer.Smo.dll
- StructureMap.dll
- The system is based on a set of conventions which allows incremental changes to a database schema.
The conventions are to to create two subdirectories in you Database Scripts folder Create - This is where your initial Schema change scripts. Update - This is where your change scripts should be placed. They should be named with the following convention ####-SCRIPTNAME.sql where #### is the script number with leading zeros. This will ensure that the first script 0001-firstSchemaChange.sql would get exectured first. <manageSqlDatabase
scriptDirectory="${database.script.directory}"
action="${action}"
server="${database.server}"
integratedAuthentication="${database.integrated}"
database="${database.name}"
username="${database.username}"
password="${database.password}"
/>
|
Sign in to add a comment
I'm not sure I like the requirement for leading zeros. This could lead to problems if ever the convention wasn't properly followed. Could a cleverer parser be implemented that can extract the patch ID from the filename (or even from within the file?) then sort an in-memory list of the files into a proper running order?
if you don't do leading zeros the file system puts 1 and 10 ahead of 2
I think an adaptor could be added to the system pretty easily to handle this situation.
We just follow the convention and with our high code coverage for our integration tests we ensures that if a convention is not followed and this causes a defect in the system, we will know about it.
We are open to patches, as long as they have full code coverage for unit tests, and the current behavior is set by default, for backward compatibility.
What do you all use for unit testing your database scripts, stored procs, ddl, etc?
I've looked at TSQLunit which is fine, but is there a better way?
Thanks - Jon
I'm glad its not just me that thinks this is useful. It's something that should be an easy task but everyone seems to do it differently!
I've used Fitnesse particularly with DBFit to test database tables, schema installs and upgrades with good success.
I also agree with the first poster about leading zeros/ I'd also be worried about having to renumber the script files if I need to insert a script later. Another approach, I've used in my projects, is to have a text file within the folder that contains a list of the script file names correctly ordered. Any scripts missing from the file order list are just added anyway so its not a problem if you forget (unless of course the script is needed in a particular order - in which case your tests should hopefully pick that up).
Where does this fit if you aren't creating a new schema from scratch. We have several systems that we'd like to apply this too, all in production and at various stages of maturity. Do we need to hive off backups from the moment we start using Tarantino and keep them foprever, or can our standard rolling backup procedure be used?