A new ReadyRoll release with a renewed focus

The focus of this ReadyRoll release can be summed up in a single word: usability.

It became clear after a recent usability study that there were a number of areas of the product that needed further development. To show that this feedback has taken to heart, I’ll detail some of the issues that were highlighted during this review, along with the work that has been done to improve the product.

Because addressing some of these issues has involved some fairly substantial changes to the product, I have decided to open 1.3 as a beta to incorporate further input from the community. Further beta releases may follow as needed, prior to the final 1.3 release.

 

DBSync Tool Workflow Improvements

The DBSync tool is a central part of the ReadyRoll database project system, responsible for listing of pending changes in the schema model and the generation of migration scripts.

This tool was originally built with the ability to author database changes in two distinct ways:

  • Offline workflow (Project → Database): User would make changes to project schema model, then use the tool to generate & add a migration script to the project, then deploy it to the sandbox database
  • Online workflow (Database → Project): User would make changes directly to their sandbox database, then use the tool to import changes into the project schema model and generate a migration script (also added to the project)

Problems with this approach:

  • It was messy: Removal of a generated migration script from the project required the user to revert changes in both the database and the project schema model
  • It was complicated: Users found that having a bi-directional flow was confusing because the origination of changes would often be unclear (particularly in the above scenario)
  • It was high maintenance: Having to manage both a schema model and a set of migration scripts in the project added significant overhead to the process of authoring changes

Or to put it more simply, by attempting to support two diametrically opposed workflows, the tool would tend to hinder productivity rather than enhance it.

Changes that have been made to address this:

  • The Offline workflow has been dropped; now only the Online workflow is supported
  • The Schema Object Model is no longer maintained in the ReadyRoll database project structure
  • The main “call to action” in the DBSync tool window — the Synchronize button — has been recaptioned “Import (Generate Script)” to reflect that the flow is now uni-directional

Users unanimously favoured the Online workflow, making the decision to scrap the Offline workflow a fairly straightforward one. Though there are still benefits of working offline, such as the ability to resolve conflicting object changes in source control or to annotate individual objects (i.e. to view an object’s revision history), it will only be reintroduced if this can be done without sacrificing the simplicity of the primary workflow.

By consolidating on a single workflow for synchronizing database changes, the path has been cleared for a whole bunch of other usability enhancements, including:

  • One-touch Import: You can now import changes from your sandbox database with one click of the  button; you no longer have to go through the intermediate step of previewing the pending changes first
  • Post-import stage added: Freezes the change list after Import but still allows you to view individual object differences (previously the change list would be hidden immediately upon sync)
  • Undo Import button added: Simply deletes the last the generated script file and refreshes the pending change list. Note that you can also undo earlier Imports by removing migration scripts from the project in reverse-chronological order
  • In-line notifications added: This replaces a number of confirmation and popup dialogs, reducing friction in the sync workflow
  • Pending change list now grouped by change type: If the change to be scripted by ReadyRoll causes dataloss in the associated object, it is now displayed prominently at the top of the change list. Displaying of groups can be toggled with the  button on the DBSync toolbar
  • Debug generated migration script: After you import your changes, ReadyRoll will open the script in the IDE to allow you to debug against a separate copy of your database. You can also make changes to the file to override the generated T-SQL, for example you might want to replace a DROP/CREATE statement with an EXEC sp_rename call.
  • Object selections retained in the project file: If you uncheck an object prior to performing an Import, it will not be re-selected during subsequent Imports. Additionally, any unselected changes will be collapsed into a new group called Not scripted after clicking Import

Please note that you can still initiate changes from Offline by using the Solution Explorer to add a new script to the project and supplying your T-SQL migration logic.

 

T-SQL Migration Script Editor Widget

Because migration scripts are designed to be executed a single time only, it can be important to know if and when an individual script has been deployed: if a script has already been deployed to the target database, special care is needed when making changes to it (in most cases its advisable to avoid making changes to deployed scripts, unless you’re in the position to recreate any affected databases).

The new script editor widget appears in the top-right hand corner of the editor window to provide you with basic detail on whether it has been deployed, and if so, the time and date of deployment.

 

Build Process Redesigned to Deploy by Patch

Previously, a ReadyRoll build would produce a singular, monolithic deployment script that contained every migration (scripts under the Deploy-Once subfolder) and pre/post deployment script in your project. Each migration would be wrapped in conditional statements to ensure that it is executed a single time only. The script also contained logic to create the database and supporting objects as needed.

You can see a sample of this type of script here: AdventureWorks_Create.sql.

Similar to how you might release an ASP.NET application, the idea was that you could build your project once and deploy it to any instance of the target database (i.e. DEV/TEST/PROD).

Though this deterministic style of deployment made the build artifacts quite portable, it was at the expense of script readability: because the script contained every migration that ever was, DBAs in particular expressed concern that there was little visibility of which migrations would be applied when the script was executed. Also, because every batch in the script was wrapped in a conditional statement, it was difficult to review the actual T-SQL of each migration.

With this in mind, the deterministic build has been replaced* with a method that deploys changes as a series of patches, where only the pending migrations are included in the script produced by the build. This is the type of approach you’ll find in OSS tools like LiquiBase and dbDeploy.

You can see a sample of a patch script here: AdventureWorks.sql.

Benefits of the patch-based build and deployment process:

  • The script that is produced by the build process can be peer reviewed prior to deployment
  • Builds are incremental, meaning that the engine only ever needs to process new or changed T-SQL files during build/deployment
  • The number and names of each migration to be executed are clearly displayed before and after deployment
  • If you’re using TeamCity for Continuous Integration, the pending migration count will also be displayed as part of the build status
  • Script layout is improved, with each pending migration from your project separated into distinct ‘regions’
  • Script contains logic to ensure that it is executed against the intended database and server only (aborts if not)

Note that this new style of build still incorporates the same error and transaction handling as before, ensuring that all deployments are carried out atomically (or rolled-back upon error).

Here’s an example of how you can create a patch script outside Visual Studio and deploy migrations to a remote SQL Server:

MsBuild.exe c:\src\AdventureWorks\AdventureWorks.sqlproj 
/property:DBDeployOnBuild=True /property:TargetServer=MYDBSERVER

The hope is that by making the deployment process clearer for everyone in your team, you can avoid coming a cropper with your DBA and focus on getting things done!

Additional enhancements to the build:

  • ReadyRoll now uses version 11.0 of the Microsoft SQL Server Command Line (SQLCMD) utility, often resulting in significantly improved deployment performance (e.g. AdventureWorks now deploys in 5 seconds instead of 50 seconds). This is (likely) owing to the switch from OLEDB to ODBC connectivity in SQLCMD 11.0 (more info about this switch is available on the MSN blog). If you haven’t installed the SQL Server 2012 Native Client & CLI, the ReadyRoll installer will bootstrap these dependencies for you.

* If you’d prefer to keep using deterministic builds, this is still available as an option under the Project Settings tab of the Project Properties window.

 

Online Database Development in Visual Studio (experimental)

In version 1.2, support for SQL Server Data Tools (SSDT) was introduced through a new type of Database Project. This made it easier to setup an isolated database development environment by integrating with a simplified version of SQL Server Express called SqlLocalDb.

Version 1.3 builds on this support by integrating the suite of online schema object designers with your ReadyRoll Database Project. What this allows you to do is to make online changes to your database without leaving the IDE.

As an example, here’s how you might create a new table using the SSDT designers:

  1. Show the SQL Server Object Explorer tool-window and expand your server and database to view list of existing objects
  2. Right-click Tables, select  and use the designer to add columns and constraints
  3. Click  on the designer toolbar to present the change review dialog
  4. Click Generate Script to generate a new migration script in your ReadyRoll Database Project
  5. Start the solution  to deploy the changes, or click the Deploy button in the ReadyRoll DBSync tool

In the above example, the migration was saved directly into the project, allowing the changes to be deployed locally and then checked into source control.

So you might be thinking, okay that’s sort of convenient, but not earth-shatteringly useful. Well where SSDT really shines is in its ability to refactor objects in batches. For example, say you want to rename a column that is depended upon by a number of views in your schema. Unless your views are schema-bound, SQL Server will let you break those dependencies with only a vague post-deployment message to alert you. However, if the SSDT designer is used you can get a heads-up on those dependencies before generating a migration script.

Try this refactoring example with the AdventureWorks sample project:

  1. Expand the AdventureWorks Table list in the SQL Server Object Explorer and right-click HumanResources.Department and select 
  2. In the designer, rename the GroupName column to Group
  3. When you click Update, the tool will highlight that a number of dependencies need to be resolved before continuing
  4. Switch to the Error List tool-window to view and make the necessary edits to resolve the dependency errors
  5. Click  in the designer to generate a script containing a batch of changes for all of the objects involved
  6. Start the solution   to deploy the changes, or click the Deploy button in the ReadyRoll DBSync tool

Hey presto, you’ve just batch applied a table change along with updates to its associated dependencies. Of course, if there are dependencies from outside the database on the affected object, you’ll still need to hunt those down and resolve them separately.

Please note that this integration is still at the experimental stage, so any feedback on how well (or otherwise) this works for you would be very welcome.

If you don’t already have SSDT installed, don’t worry: the ReadyRoll installer will give you the option to download this from Microsoft during setup.

 

Working with Existing Databases – Setting a baseline

One complication of with working with migrations is how to deploy a database that is already established in Production (i.e. beyond the point where you can simply drop and recreate the schema as needed).

Prior to version 1.3, each script generated by ReadyRoll would contain IF EXISTS T-SQL statements to test whether the operation was needed, thus implicitly creating a baseline during a database’s initial deployment. Unfortunately, this method added a fair amount of bulk to the generated migration scripts and also required the use of dynamic T-SQL (wrapped in EXEC calls) to deploy objects like views and stored procedures.

Version 1.3 replaces this with the ability to set an explicit baseline for that initial deployment. You can now specify the high-water mark for your database as follows:

  1. Create a new ReadyRoll project in Visual Studio
  2. Click Configure in the ReadyRoll DBSync tool and connect your project to a copy of your existing Production database
  3. Import all objects from the database to generate a Deploy-Once migration script in your project
  4. When deploying your database project for the first time, specify the BaselineUptoMigration property with the filename of the migration script as the value, e.g.
MsBuild.exe c:\src\AdventureWorks\AdventureWorks.sqlproj 
/p:DBDeployOnBuild=True /p:TargetServer=MYDBSERVER 
/p:BaselineUptoMigration=0001_Imported_Objects.sql

In this example, the AdventureWorks database on MYDBSERVER will be setup for first use with ReadyRoll by marking 0001_Imported_Objects.sql as done (thus preventing it from being executed against the target). Any subsequent migration scripts, however, will be deployed as normal.

As an extra bit of insurance, setting of a baseline for existing databases is now mandatory, avoiding the possibility of accidentally deploying over the top of an existing schema (hello [master] database!).

 

Summary

Though there are some fairly substantial changes in this release, there is one constant which remains ReadyRoll’s central pillar: migration-based schema deployment.

Certainly, ReadyRoll is not alone in supporting migrations, but the aim is to have a tool that the whole team can use to build and evolve your database schemas: to create a virtuous cycle of source control, continuous integration and transparent deployment that encourages experimentation in the Development phase and helps make the Production release a predictable and mundane event.

I’m looking forward to the next round of feedback to hear how the product is progressing towards this goal!

Download the latest beta of the ReadyRoll Extension for Visual Studio 2010 to get started.

You can leave some comments on the release below, or use the contact form if you would prefer to send feedback privately .

Advertisements

One thought on “A new ReadyRoll release with a renewed focus

  1. […] method is the best way to use SQLCMD variables at this time. If you’d prefer to use the Patch deployment script method instead (which provides a delta file of pending migrations and is only available via MSBuild), […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: