OneScript

OneScript is an easy to use tool used by developers and DevOps to automate the release of database changes.

Benefits of OneScript

Removes many of the common causes of release failures for database changes.

Allows you to efficiently manage individual database change scripts committed to source control during the development process.

It automates the filtering, sorting and building of these change scripts into a single release script.

In addition to the Windows application, OneScript can be run from the command-line which allows the release process for database changes to be fully automated. This also means that it can work as a part of many automated deployment systems such as Jenkins and TeamCity.

The release of database changes becomes much more efficient, the risk of manual errors is removed and changes are 100% auditable.

It is a simple to use tool that fits in with the tools and practices developers already use.

Process Overview

Developers script out each change into its own small file named after 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:

/Tables/Customers.sql
/Stored Procedures/GetCustomer.sql
/Stored Procedures/SaveCustomer.sql

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 an additional requirement to translate changes into a different language.

The next step is to use OneScript to automatically filter and pull all the newly added or modified change script files from source control, sort them based on the sort orders assigned to the folders that they are in ("Tables = 100", "Views = 300", "Stored Procedures = 400", etc.), and then combine them into a single release script file that can be applied to a database to perform a deployment. OneScript does allow you to override the default filter or default sort order for any given file.

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 and result in a new release script file being generated.

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 exactly what has been tested.

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". You can copy the correct command-line with arguements from the OneScript Windows application after making your selections.

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.

Notice in the image above that it shows the sort order assigned to each of the folders for this project. Normally files are sorted by this number and then by file name. This request overrides the defaults to have the file dbo.Territories.sql included first in the release script and dbo.Products.sql to be included last.

• Subversion (SVN) - local or remote
• Team Foundation Server (TFS) - on-premise or off-premise
• Files on a disk or shared drive (for GIT, etc.)

Using the tools made by the makers of each database is safer and more efficient than hand coding SQL or making the changes in another language.

For example, when developers need to change the size of a column in Microsoft's SQL Server they can use Microsoft's SQL Server Management Studio. They can do this in the table designer and then select "Generate Change Script" from the menu. This will create about 70 lines of SQL code complete with the correct settings and the use of transactions. This script can then be committed to source control and then it will be a part of the next OneScript build.

For each of your projects you can define SQL that will be written into the generated release script file along with the individual change scripts. You can define SQL to be written at 4 different places - the start and end of the generated release script file and before and 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 change script files that cause errors during deployment.

The ability to inject SQL with placeholders opens up many possibilities for versioning, logging, auditing and control.

With this approach, source control will contain the history of changes for each database object including change date, branch and author. You can use source control to do a "diff" between versions to highlight the differences.

Above is the change log for the stored procedure GetCustomerOrders

We blogged (here) about how to use OneScript with PowerScript to automatically do the following:

1. When code is checked in to Subversion, automatically run (using a hook) OneScript's command-line to create a release script file containing all of the changes scripts files created or edited since the branch was created.
2. Restore a resent backup of the production database to the Deployment Test SQL Server instance.
3. Apply the release script file that OneScript just generated to the test database.
4. Determine if applying the release script file to the database caused any errors.
5. Send an email to the developers with “Success” or “Failed” in the subject line. Have the "process log" as the body of the email and have the results of applying the SQL release script file to the database as an attachment.

• Ability to run the OneScript Windows application
• Ability to run the OneScript command-line
• Unlimited OneScript projects
• Unlimited source control connections
• Unlimited OneScript database server connections