ReadyRoll Frequently Asked Questions (FAQ)
At its core, ReadyRoll is functionally very similar to the countless open source software products out there in that it is a migrations-driven database deployment tool. However, ReadyRoll does a lot more than simply manage and order database change scripts. ReadyRoll provides:
- Seamless integration with the Visual Studio solution and MSBuild ecosystem, so you can build, source control and deploy your database right alongside your application code
- A ‘connected’ editing experience (the ability to develop changes by editing your database directly either in Visual Studio using the SQL Server Object Explorer or by using the table designers/query editor in SQL Server Management Studio)
- Auto-generation of migration scripts for schema and data changes (powered by SQL Compare), dramatically reducing how much SQL you’ll need to code by hand
- Branch/merge support for procedures/triggers/views/SQLCLR (Programmable Objects)
- Environment-specific values for your deployments (SQLCMD variables)
- Release planning support (Semantic versioning)
- Verification of the script before it is deployed for real (Script verification using a shadow database to test against)
- An Offline Schema Model, enabling you to quickly assess the contents of your database project at the object level without having to analyze a set of migrations scripts
- First-class integration with release management tools such as Visual Studio Team Services (VSTS) and Octopus Deploy.
Similar to CodeFirst migrations, ReadyRoll integrates with Visual Studio, allowing your database to be deployed right alongside your application code. This provides a simple “one click” build experience for all components of your application. The deployment approach of ReadyRoll is also similar to that of CodeFirst migrations, in that it involves executing a set of predefined migrations in sequential order.
Where ReadyRoll differs to CodeFirst, however, is its use of Transact-SQL for migrations authoring as opposed to the Fluent API of CodeFirst. Developers often gravitate towards Fluent due to the ability to write the migrations in a language that they are already comfortable with (e.g. C#). However what is not initially apparent is that there are limitations to working within the layer of abstraction that CodeFirst provides:
- There is no provision for objects outside the Entity Framework model, such as database views or functions (these must be deployed separately)
- Generation of T-SQL code is performed on-the-fly, so customization of the logic is difficult (e.g. if you need to make use of features of the T-SQL language that aren’t supported by CodeFirst)
- The use of snapshots in CodeFirst can result in unexpected challenges within team environments (i.e. when branching/merging code)
The alternative approach offered by ReadyRoll involves authoring and deploying your database changes using the native language of SQL Server, T-SQL:
- All objects are source-controlled and deployed together, making it easy to coordinate the deployment of all of your database changes
- Generation of T-SQL code is performed at design time, so customization of logic is as simple as editing the migration that ReadyRoll suggests for you
- ReadyRoll only stores T-SQL files within your project, simplifying branch and merge scenarios for your team
Ultimately, we believe that SQL Server itself offers the best domain specific language for deploying database changes. However this doesn’t mean that you need to sacrifice any of the smarts that CodeFirst offers in order to adopt T-SQL as your deployment language of choice: ReadyRoll can generate both your Up and Down scripts for your database, meaning that you don’t have to write mountains of boilerplate SQL code yourself:
- Up scripts are generated by importing changes from your ‘connected’ database, using the DbSync tool that ships with ReadyRoll (i.e. the databasefirst approach). These can be edited to adjust the migration logic as you need.
- Down scripts can also be generated and applied by the DbSync tool. Simply switch to the branch that you want to roll-back to, and click the “Revert” context-menu action. Read more about re-synchronizing your workspace in the documentation.
If you would prefer to use a CodeFirst approach to authoring your migrations, you can actually use Entity Framework in conjunction with ReadyRoll to generate your T-SQL scripts.
On the surface, there are some similarities between ReadyRoll and SSDT, given that they both reside within the Visual Studio IDE. In fact, ReadyRoll is actually implemented as a sub-type of the SSDT project system, meaning that it also fits seamlessly into the Visual Studio solution and MSBuild ecosystem just like SSDT.
However, despite these similarities, the change-authoring workflow and deployment behaviour of the two tools are quite different.
From a deployment perspective, the main difference between SSDT and ReadyRoll is that the former uses state-driven deployment, whereas ReadyRoll uses migrations-driven deployment. You can read about the differences between these two approaches in Vladimir Khorikov’s excellent series of blog posts on database delivery.
If you consider where SSDT and ReadyRoll sit on a spectrum between the state and migrations approach to deployment, SSDT is a purely state-based tool while ReadyRoll takes more of a hybrid, migrations-first approach.
This is partly because ReadyRoll’s Offline Schema Model and Programmable Object features bring some of the benefits of a state-based approach to a migrations-first tool. It is also because ReadyRoll uses the state-based approach to generate your migration scripts (allowing you to edit the contained logic, if needed). The script generation is powered by an industry-standard schema comparison tool, SQL Compare, under the hood.
Once the script is checked into source control, ReadyRoll’s migrations functionality kicks in and that migration script becomes immutable. This means that, as expected with a migrations-based approach, you can have confidence that the schema change operation will be performed in exactly the same way throughout Test/Staging/Production deployments.
In order to ensure the maintainability of your ReadyRoll project, we highly recommend that you consider implementing the following project options:
Enable programmable objects within your database project. This provides a state-like deployment experience for stored procedures, views, functions etc. Basically for any type of object that can be dropped and recreated without loss of state, ReadyRoll can create reusable object deployment scripts. For most environments, this should reduce the number of migrations in your project by a significant amount.
Enable semantic versioning and create “branch” folders with each release. This allows you to keep your migrations within version folders to make your solution easier to navigate. The typical granularity for these folders is either per-release or per-feature branch.
If you have already implemented the above options, and are still left with an unmanageable amount of migrations, you may want to consider consolidating your project’s migrations at certain version intervals (e.g. upon each major version). Read more about consolidating your migrations in the documentation.
ReadyRoll works best when each developer in your team has a private copy of the database to work with (either on their own machine or on a remote server). Having dedicated developer “sandboxes” provides the isolation needed to ensure that he/she can design and test their schema changes without affecting other developers. It also means that each developer has the ability to work on a different branch of code to their fellow team members, facilitating concurrent streams of development work.
Here are a few different techniques you can follow to help your developers create and maintain their own sandbox environments:
Create and populate your databases from scratch using project sources
This approach requires that all of the T-SQL logic needed to reproduce your database be stored within your ReadyRoll project, including schema, code, static data and (optionally) a set of transactional data (such as sample customers, orders, etc).
Depending on the complexity of your application system, you may need to include scripts to cover the deployment of non-database assets such as linked servers, scheduled jobs and logins. ReadyRoll can help with the scripting of schema, code and static data assets, however you’ll need to provide the logic to reproduce these assets, as well as the transactional data, within your environment.
The advantage of this approach is that, regardless of whether the scripts are generated automatically or written by hand, they can all be added as migrations within your ReadyRoll project. This means that, after the initial ground work is done, every developer will be able to create their entire sandbox by simply deploying the Visual Studio solution.
Create your databases from scratch using project sources, then use a separate tool to populate the database
This approach is similar to the above in that you would use the ReadyRoll project to deploy your schema, code and static data. However instead of scripting the transactional data, you might elect to use a third party tool to accomplish this, such as SQL Data Generator. Some of our customers have even written their own ETL process to import subsets of transactional data into their sandbox environments.
The advantage of this approach is that it simplifies your database project by keeping the maintenance of your transactional test data separate to that of your change authoring process. Drawbacks include having to establish and maintain the data population process, as well as the complexity of having to run an additional step after performing the initial schema deployment.
Restore your databases from backup
Instead of trying to reproduce your database from source control, you might elect to establish the baseline for your sandbox environment from an external source, e.g. by restoring your database from a backup file prior to starting development work. With this approach, ReadyRoll will not attempt to replace your schema objects, but rather it will only execute the migrations that have yet to be deployed to the database (if any).
The advantage of this approach is that your environment will come pre-loaded with real world test cases (presuming your storage infrastructure has the capacity to store individual databases for each of your developers). However if your organization requires that sensitive data be masked, this may introduce overhead to maintaining your team’s sandbox environments.
Additionally, there is a risk that unintended changes may be imported into migrations (and potentially deployed to other environments) if the restored database does not match the schema contained within the ReadyRoll project. To mitigate this, we recommend that databases backups taken from canonical sources only (e.g. a Production environment) and that any drift is removed from the database prior to authoring new changes (see ReSync your schema within this article).
Restore your databases from backup
Instead of trying to reproduce your database from source control, setting up your development environment by restoring from backup files is another option. However, if your organization requires sensitive data be masked, this may complicate the provisioning of dedicated development environments.
Another risk to be aware of is that it’s possible for unintended changes to be committed to source control by developers, if the database has been manually edited in Production (ie, if drift has occurred). To mitigate this, we recommend database backups are taken only from canonical sources, eg, the Production environment.
Of course, the disadvantage of working with backups is that the cumulative size of all these dedicated environments may be prohibitive for your local or network storage infrastructure.
Provision your database environment using SQL Clone
Instead of using backups, another option for provisioning dedicated development environments is to use SQL Clone, which lets you quickly create full copies of SQL Server databases and backups. When you subsequently deploy your database project, ReadyRoll will figure out which changes have yet to be applied, and execute only those migration scripts which are pending.
The advantage of this approach is that your environment will come pre-loaded with real world test cases. Additionally, having the ability to quickly create copies of the database simplifies the process of rolling-back unwanted changes, as you can always create a new copy and apply any pending migrations.
Despite the fact that ReadyRoll is geared more towards the isolated model, it can also work with a shared database: ReadyRoll’s incremental deployment approach means that, even if multiple developers deploy their project to a common database instance, the current state of that database will not be affected (unless there are any new migrations pending deployment to the database). However, there are a few restrictions to bear in mind when working in the shared development model:
- If you would like to author database changes in a “connected” fashion (e.g. directly on the database using SQL Server Management Studio or using the SQL Server Object Explorer in Visual Studio): to avoid migration scripts containing duplicate object changes being imported into the project, it is recommended that only one person makes structural changes to the shared database at any given time.
- If it’s unavoidable that multiple developers will need to author database changes concurrently, then an alternative approach is to make edits in a “disconnected” fashion: instead of making changes directly to the database, you can script the changes manually and add a migration directly to the project (e.g. use the Visual Studio Solution Explorer to add a new script to the project). When deploying the project, only your newly-added migration will be executed, preserving any other in-progress changes.
When it comes to schema changes, forward planning in the development process to avoid working on the same objects if the changes combined with usage of semantically-versioned folders should help avoid clashes between branches.
It’s interesting to note how infrequently this problem comes up in continuous delivery environments. The fact that development cycles tend to be shorter – thus branches don’t tend to last as long – plus when you consider that most schema changes in brownfield environments tend to be additive anyway means that collision is not something that is encountered often by our customers.
However in greenfield environments, you might find that a state-driven deployment style is easier to manage frequent table changes. For example, prior to deploying version 1.0 of your database, where there is no data to preserve, you might find the state-driven approach easier to use if your schema is evolving rapidly.
After version 1.0, you may want to switch to the more conservative change management process offered by a migrations-first tool like ReadyRoll to ensure that your data assets are handled carefully during deployment. When it comes to code changes (such as changes to stored procedures, functions and views), however, ReadyRoll shines in both greenfield and brownfield environments: its support for re-usable programmable object scripts provides the flexibility of the state-driven approach, without the sacrifice to deployment predictability.
You can definitely use ReadyRoll with an existing database. You just need to import your database into a new ReadyRoll project and configure a baseline within the project (setting the baseline ensures that you can deploy your project to any pre-existing database environments without issue). Read more about working with existing databases.
The topic of creating environments populated with real-world data is complex, and every team’s needs are different. There are three main approaches to creating and populating databases that work well with ReadyRoll:
- Creating and populating databases from scratch using project sources (e.g. using BULK INSERT)
- As above, but using a custom tool to populate the database rather than project sources
- Restoring a database backup onto test servers
For more information, see Data Population techniques with ReadyRoll.