Subscribe by RSS
At Dovetail we are always working to make customer installations and upgrades of our product a seamless process. We are proud of our efforts around upgrade safe customizations, however we found that our upgrades were still taking too much time because of the brittle nature of migrating the underlying data structures. This is not a new problem to anyone that has had to maintain and deploy even small applications. Versioning the database like we do our software is just a tricky problem.
For the past several years the community in software development has been collectively working on this problem. It was nice to come into a well understood problem space, and be able to pick and choose amongst many different tools. Ultimately, we chose the tool RoundhousE which allows us to run SQL scripts against a customer database in a controlled fashion, and provides support for logging errors and keeps an audit trail. RoundhousE is also aware that the code that runs against it has a version and will tie a version number like ‘3.0.3’ to the schema as tracking which scripts have been run against it. All in all, roundhouse is very powerful tool that has been very easy to plug into our compositional deployment process.
For the last month we have been polishing this process, making the process as smooth as glass for you. We hope that when you upgrade to our latest version, you will continue to appreciate the smoothness of our deployments which will now take even less of your support staff’s time.
In closing I wanted to leave some lessons learned should you decide to embark on this process yourself:
- separate your relational structure from transient items:
Managing the structural items like tables is a different beast from the transient items. Its easy to drop and recreate views, stored procedures, and maybe indexes. But we can’t drop and create tables (we would lose all of that data). Hence, only apply migrations to the things that actually need that level of work. Other things (as much as possible) should just be in a ‘drop/create’ mode.
- separate loading data from modifying structure
Another lesson I learned was to keep the code that manages the loading of data separate from the code that manages the persistence structure. Since your structure is going to change, you will have to modify the data load. Currently we are using Roundhouse to manage the structure, and then a home grown tool to actually push the data into the database.
- make schema changes idempotent (a fancy word for ‘run twice safe’)
I feel like this is just a solid practice, and was an important practice as we brought our various customers on to the migrations tool. Each customer was on a slightly different mix of scripts, and by making each change script check before blindly applying itself we were able to run all the scripts with a sense of calm serenity about it.
- make data loading idempotent
this is turning out to be a good thing for us too. As we add more data to be loaded it’s nice (lower friction) to be able to just call the load function without having to put the database into an empty state. We simply run the load process and if the item isn’t there it adds it. If it is there, it just skips it and continues on its merry way.
Further Reading:
Post new comment