As a result every database refactoring is automated by writing it in the form of SQL DDL (for the schema change) and DML (for the data migration). These changes are never applied manually, instead they are applied to the master by running a small SQL script to perform the changes. ... This ability to sequence automated changes is an essential tool both for the continuous integration process in development, and for migrating production databases to a new release.
We have built the easy to use, powerful tool to allow you to implement the process described in the quote above. This small change to your current development process results in a significant productivity gains.
Our tool, OneScript, allows you to automate the release process for database changes in a fast, controlled and repeatable way. This leads to more frequent deployments and a shorter feedback loop.
It replaces the time consuming manual process prone to human error and enforces good process discipline.
Since OneScript only pulls scripts from your source control you get the added benefits of better visibility and traceability into your deployments which is useful and sometime a compliance requirement.
OneScript is used on software development projects to automate the creation of a single SQL release script file from individual SQL change script files that are managed in source control.
Developers create individual SQL change script files each with the same name as the database object that it creates or alters. These are checked into source control folders named after the database object's type. For example, "Tables", "Views", "Stored Procedures".
As an example, adding a column to the customer table might involve the following change script files:
The original content in the "/tables/Customers.sql" file was the CREATE TABLE command. Since this has been deployed to production and will never be used again we overwrite it with our new ALTER TABLE command to add the new column. This may seem counterintuitive but it results in the history of the script files in source control containing the history of changes to the database objects and it also reduces the number of active scripts that need to be managed.
Developers use this same approach (and tools) when scripting out changes for database objects, database code, and static data. These change scripts can be for SQL Server, Oracle, MySQL or any database that is changeable with scripts. There isn't the extra step of translating changes into XML format.
The next step is to use OneScript to pull all the newly added or modified change script files from source control, sort them based on the folders that they are in ("Tables", "Views", "Stored Procedures", etc.), and then combine them into a single release script file that can be applied to a database to do a deployment. OneScript allows for exceptions to the default file filters and default sort orders.
The fact that only scripts committed to source control can be included in a release script file leads to better control and a complete audit trail. This may also be required to meet regulatory or compliance requirements.
After OneScript generates a release script file it should be applied to a recent copy of the production database and then the changes should be tested. Any failures or changes to the change scripts should restart the process.
Eventually the same exact release script file that was applied to all of the pre-production environments and tested is used to deploy the changes to the production database. This makes a successful release much more likely and ensures that you are releasing what has been tested.
We blogged (here) about how to use OneScript with PowerScript to automatically do the following:
A release script file can be generated from the OneScript command-line. You normally define a filter like "include all files that have changed since the branch was created" or "include all files changed since a certain date/time". The OneScript Windows application can be used to make selections that build the command-line options that you want.
The fact that a release script file can be generated from a command-line means that OneScript can be used as a part of an automated build process and will work with different build tools such as TeamCity, MS Build, Jenkins or Hudson.
Everything that you can do from the command-line you can also do from the easy to use OneScript Windows application. Also on the rare occasions that you need to, you can easily include or exclude specific change script files or override a change script file's default sort order. OneScript filters and displays data directly from your source control as you are editing these overrides.
For each of your projects you can define SQL that gets written into the generated release script file along with the individual change scripts. You can define SQL to be written at the start/end of the generated release script file and SQL to be include before/after each included change script file.
OneScript has a lot of substitution placeholders that can be used in the injected SQL. Below is an example of a SQL template that will be valued and inserted before each included change script file.
The placeholders will be replaced with values from source control. So the %f will be replaced by each included change script file's filename right before the file's content is copied into the generated release script file.
This example injects SQL that prints the source control details for each included change script file right before that file's changes are applied to the database. It is used to immediately identify any change script file that cause an error during deployment.
The ability to inject SQL with placeholders opens up many possibilities for auditing and control.