This tool was created to manage many deployments of a product database, and to automatically generate SQL to migrate databases and/or validate database schemas. The tool supported both SQL Server and Oracle from a single configuration, and could update from any version of the tool.
The Problem
Acumentum had a hosted software product that could run on JBoss or WebLogic, and could use SQL Server or Oracle database. They hosted dozens of sites on behalf of clients, and some clients chose to self-host.
With each software release, Acumentum needed to upgrade the hosted database to the correct database schema, and provide the appropriate SQL scripts to upgrade the database and reference data. Some sites would be running very old versions, and the upgrade scripts would need to change many versions at once. For each upgrade script, it was also necessary to provide a rollback script in case the upgrade was aborted.
When we first investigated the hosted databases, we discovered inconsistencies between them. A database fix (such as changing nullable or altering column width) may have been patched on one client’s site, but not included back in the development scripts. We also found that the time and effort to generate the manual SQL scripts was error prone, and often scripts that worked on the UAT database would fail on a given clients database.
Another common problem is that a developer creating a table would typically generate a SQL script to create the table. If they subsequently added more columns or changed columns in the table, they would do this directly on the database because the SQL script was not written to change a single column. This increased the chance of failed deployments where the SQL script did not exactly match the actual database that had been tested against.
The Solution
First we created a tool that would extract the schema for each database into a standardised XML configuration (tables, columns, indexes, views etc) so that each hosted database could be compared to a master schema. This allowed us to quickly review all schema variations between hosted SQL Server and Oracle databases and ensure we had all fixes in the master schema.
Then we extended the tool to generate intelligent SQL for every schema update. If the table was missing, it would automatically create the table. If the table existed, it would check if any columns were missing and if so, it would automatically add them. If the column was different (eg. varchar(8) instead of varchar(16) it would automatically alter it. Similarly, it could delete columns, add/delete indexes, rename tables or columns and so on.
The usefulness of this tool was incredible. To create a new database table in a development database, the developer would simple add the Table and Columns to an XML schema configuration file (with the bonus that this configuration can be source code managed). Similarly, the developer could rename tables, drop columns, add indexes, and so on – all in the XML file.
On startup, the web application could be configured to auto-apply the schema, and it would apply all of the database changes made by the developer. The build-process would also auto-create SQL scripts to create-update the database, and to validate the database. This allowed the same smart SQL scripts to be run in hosted production environments where the web server did not have permission to modify the database (a standard hardening technique).
Furthermore, the tool automatically created SQL Server AND Oracle versions – without the developer needing to write any SQL. The only time a developer needed to handcraft any SQL was if we needed to perform a complex data mapping between software versions – which could then be integrated into the automatically generated SQL.
The Benefits
This tool saved lots of time by saving developers from needing to create SQL change scripts, and it was so easy to use that developers used the same tool to change their development and UAT databases as we used for the production deployments.
The success rate of deployments went up considerably. Previously, we would experience a 30% failure rate in running manual SQL scripts during a deployment, where the scripts would raise an error part way through. This required manual troubleshooting during the deployment, and changes and individual invocation of parts of the SQL scripts. Often this would cause the upgrade outage to take longer than the planned hour.
With this tool, deployment success rates went up above 99%. In a rare situation where a SQL script failure did occur, we could extend the tool to automatically improve its SQL generation to handle that corner case.
Finally, this tool also reduced maintenance issues on our hosted sites, because we could guarantee that every SQL database was running the correct, source code managed schema.