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.
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
∙ 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 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.