ReadyRoll 1.3.3.0: Octopus support added!

Today I am excited to announce Octopus support in ReadyRoll SQL Projects! If you’re not familiar with it, Octopus is the automated release management tool for .NET that everyone’s talking about.

ReadyRoll is a SQL Server database change management tool for Visual Studio 2010/2012.

In a sense Octopus and ReadyRoll are spiritual cousins in that they both support the build once, deploy many times tenet of Continuous Delivery: the practice of ensuring that the same build artifacts (binaries, scripts, etc) used in Dev testing are carried all the way through to Production.

ReadyRoll supports this practice by following the imperative database change pattern: migration scripts are fleshed-out in Dev and incrementally deployed through Test and Production.

With the release of version 1.3.3, ReadyRoll now builds a type of NuGet Package-based file that can be consumed and deployed to all your environments by Octopus.

Just follow a few simple rules for authoring and maintaining your change scripts and you will be a step closer to achieving database deployment nirvana: a repeatable, reliable release process for all of your SQL Server projects (oh yes, and those ASP.NET applications/Windows Services as well!).

This is how it works. First enable artifact packaging within your ReadyRoll SQL project settings:

Then click Add/Edit NuSpec File to add a package metadata file to the project:

Add version details within the Assembly Information, accessible from the SQLCLR tab:

Now switch to the Release configuration and Build the solution to test the new settings. ReadyRoll will use OctoPack to create an Octopus-compatible NuGet package:

Opening up the contents of the output package will reveal a handful of deployment assets:

Now set up your project for Continuous Integration and collect the produced artifacts from the \bin\Release folder. Here we’re using TeamCity:

After performing a build, the package will be ready to serve via a NuGet feed. Note I am skipping over quite a few steps here for sake of brevity, but Paul Stovell has written a great guide to setting up OctoPack with TeamCity if you need any help getting started.

Now let’s setup a Octopus Project to consume the package from the TeamCity feed:

And add a $DatabaseServer variable for each SQL Server in the target environments:

To test out the deployment, click Create release and then Deploy to [Environment]. As this is the initial deploy to the Staging environment, the database will be created from scratch and all project migrations applied:

In subsequent deployments, the package will only deploy any new migrations that have been added to the project (if any).

When you’re ready to deploy onto other environments, including Production, click Promote to… [Environment]. The exact same set of migrations will execute against your upstream environment, rolling-back in the event of any issues, giving you a predictable deployment outcome every time!

Summary

This is the first release to include support for Octopus, however there’s still a few more features to come that will strengthen the integration between the tools, including support for SQLCMD variables and also new functionality to help with managing migrations throughout lifecycle environments.

Get started by downloading ReadyRoll 1.3.3.0 now!

Announcing ReadyRoll SQL Projects for Visual Studio 2012!

Today I am excited to announce the first beta of ReadyRoll SQL projects for Visual Studio 2012!

tl;dr ReadyRoll is a new type of SQL Server project for VS2012 (and VS2010 Sp1) that deploys using T-SQL migrations. It integrates with the SSDT database object designers for migration script authoring, but also allows you to work Online within SSMS. Download beta version 1.3.1 (expires 1st November 2012).

What’s new in VS2012?

The ‘DataDude’ team at Microsoft have made a big investment in the database development tools in Visual Studio 2012, collectively known as SQL Server Data Tools (SSDT)*.

One of the best features is the new table designer within the SQL Server Object Explorer: Not only does it prevent you from accidentally breaking dependant objects (e.g. views and stored procedures), it also lets you make changes to correct any invalid column references and then apply the changes to the database in a single, atomic batch. Neat!

Another great innovation is the replacement of SQL Server Express with SqlLocalDb, which provides a lightweight instance of SQL Server, making it easier for developers to establish a truly isolated development environment.

*Not to be confused with the (rather confusingly named) SQL Server Data Tools (SSDT) Business Intelligence tools.

What is ReadyRoll?

ReadyRoll complements these new tools by introducing a simple project system which allows you to deploy databases using your own scripts. By making the best of what Visual Studio has to offer — integrated design, build, test and source control — database schemas are no longer the poor cousins of the AppDev world!

How does it work?

Within your ReadyRoll database project, you provide a set of T-SQL scripts with a unique ID and a numerically prefixed filename: when you deploy your database project, ReadyRoll will execute your scripts in the order provided. A log table within your database provides an audit of each deployment, ensuring that each script is executed a single time only.

This is one of the most well established paradigms in database change management, and is used by tools such as Rails ActiveRecord migrations, Entity Framework CodeFirst, as well as many OSS deployment tools.

How does this differ to ‘DataDude’ projects?

ReadyRoll uses imperative deployment, where each user script defines CREATE/ALTER/DROP statements. By contrast, the projects built-in to Visual Studio use declarative deployment, where each database object is defined using a CREATE statement. In declarative projects, these object templates are authored at design time and then synchronized with a physical database during solution build (internally, the project system generates imperative T-SQL on-the-fly). While this system supports a high degree of change automation, it may not suit all teams.

How are scripts authored?

ReadyRoll provides a number of ways to create new scripts in your projects, depending on how you prefer to work.

Working within Visual Studio only

Visual Studio 2012 (and Visual Studio 2010 with SSDT) comes with a rich set of tools that let you work online with your database, so that you never have to leave the IDE.

When you create a new ReadyRoll project, a new database will be automatically created for you.

To get started, open the SQL Server Object Explorer (SSOX) window and expand the (localdb)\Projects SQL Server instance to reveal your new database. Navigate to the schema and begin creating new objects using the built-in designers.


When you want to apply your changes, click Update and then Generate Script: ReadyRoll will add the generated script to your project for review prior to deployment. When ready to deploy, press Start [F5] or use the Deploy Project command.


ReadyRoll will then perform a “gated deployment” on your database: after the script is parsed, your migration script is deployed to a copy of the project database (named MyDatabase_MyUsername_SHADOW). This step ensures that not only does your script contain valid object references, but also that your target database does not drift from your project database. For example, it prevents an ALTER operation occurring before a CREATE operation.

Here’s the output from the Deploy command:


You can then check your scripts into source control, deploy to upstream environments (DEV/TEST/PROD) or receive scripts from other team members.

Working with SQL Management Studio (SSMS) + Visual Studio

If you’re a T-SQL junkie or prefer an environment that’s geared purely for database design and administration, the good news is that you can also work Online with your database using SSMS.

Once you’ve created a new ReadyRoll project and connected it to your database, you can write and execute T-SQL directly within SSMS, or use the designers if you prefer.


Once you’ve finished your work, switch back to Visual Studio, open the ReadyRoll DBSync toolwindow and click Import (Generate Script). This will generate and add a new migration script to your project.

(click to see a full view of the window)

If the generated script doesn’t do what you expected (eg. a table rename is scripted as a DROP/CREATE), you can simply paste in your own T-SQL logic. If you made an unintended change, click Undo (Delete Script), select the object in the list and use the Revert context-menu command to return the object(s) to the previous state.

You can then check your scripts into source control, deploy to upstream environments, and/or receive scripts from other team members. Be sure to check the DBSync tool to ensure that all scripts have been applied to your database before continuing work within SSMS.

Working with pre-scripted T-SQL

If you’ve already got some scripts written, or if you need to deploy scripts provided by a vendor (eg. ASP.NET Membership Provider object creation scripts), simply create a new project and then add a new Deploy-Once script file.


Paste the T-SQL into the new script, and then click Deploy Project or, if the objects have already been created in the target database, click Mark as Deployed to prevent the script from being re-executed.

Working with existing databases

If you would like to import objects from an existing database, firstly connect the new project to a copy of the database and click Import (Generate Script) within the DBSync tool: a single script to create all of the imported objects will be generated and added to the project. You can then create additional scripts using any of the above methods.


To deploy an existing database to upstream environments (DEV/TEST/PROD), you may need to set a baseline in each target environment. This can be done in the UI or via command line. To set a baseline using the UI, firstly connect the project to the target database and open the imported object script for editing. Click Mark as Deployed to prevent the script from being re-executed on the target.

How do I publish to a remote database server?

ReadyRoll projects can be deployed to a remote server by command line or automated using a Continuous Integration server like TFS Build or TeamCity.

To perform a deployment from the command line use the following syntax:

MsBuild.exe MyDatabase.sqlproj 
  /p:TargetServer=MYSERVER\INSTANCE 
  /p:TargetDatabase=MyDatabase 
  /p:DBDeployOnBuild=True

This will produce a SQL file containing any unapplied scripts for the target server/database, and then use the SQLCMD utility to execute the generated script against the target.

If you would like to set up automated deployment using Continuous Integration, provide the following settings in a new build configuration:

Runner type: MSBuild
Build file path: MyDatabase/MyDatabase.sqlproj
MSBuild version: .NET Framework 4.0
MSBuild ToolsVersion: 4.0
System Properties:
∙ TargetServer = MYSERVER\INSTANCE
∙ TargetDatabase = MyDatabase
∙ DBDeployOnBuild = True

Note: Unfortunately you will need to install VS2012 and ReadyRoll on your build server/agent to perform your deployments. Build dependency management will be simplified in an upcoming release.

Download

Download the latest beta of the extension for Visual Studio 2012 to get started!

Note that the extension is also compatible with Visual Studio 2010, but it is recommended that you first install VS2010 Sp1 and SSDT pre-requisites (bootstrapped in the installer).

During the beta phase, I’m particularly keen to hear feedback about how well (or otherwise) ReadyRoll works for you. You can leave some comments on the release below, or use the contact form if you would prefer to send feedback privately.

Follow

Get every new post delivered to your Inbox.