As application developers, it’s fairly well established that having a private environment to develop and debug changes is a good practice, as it helps protect us from outside changes that could destabilise our testing efforts. Given that this private environment is usually hosted on the developer’s PC, the challenges presented by hosting full-scale databases on a local SQL Server instance (i.e. the amount of disk space needed to restore a backup from Production not to mention the hassle of keeping the schema design in-sync with master) make the idea of using a private database environment seem implausible. So to save time and effort, we resort to connecting our applications to a common Dev database server and leave the database change management tasks to the DBA.

In my previous life as an application developer at a financial services company, I found that the practice of using a shared dev database would work quite well… up until the point someone made a change to the schema while I was in the middle of testing my application code, or someone would refresh the Dev database from another environment, dropping that stored procedure I just added through SQL Server Management Studio. We’d probably been putting up with all the problems that stem from working with shared databases because somewhere along the line an idea took hold that the database is simply too complicated and unwieldy for everyone to build and maintain their own environments.

In our case, that idea arose from the perception that legacy databases don’t lend themselves to typical application development practices; practices now strongly associated with the DevOps movement such as continuous integration and automated deployment. However, the more advances our team took in the area of automating the build and deployment of the “regular” solution assets (such as the web application and the service layers), the more backward our database change management process began to appear.

Getting out of that shared environment scenario and transitioning to private environments for database development not only helped us get out of that mire, but also fostered better habits within the development team. For instance, always checking our database change scripts into source control along with our application changes meant that our DBA got an early heads-up on changes in progress. Eventually, however, we wanted to start putting more rigour around how we handled our Production deployments and so, given the increasing pressure we were under to deliver value to the business more often, the need to automate the whole process was inevitable. Having all database changes go through our DBA was simply not going to scale.

This is where the need for a tool like ReadyRoll came about. ReadyRoll is an extension for Visual Studio that allows you to deploy your database alongside your application code; instead of connecting to a shared database in an unknown state, ReadyRoll deploys your database to a known state upon solution build. It does this by providing you with a tool to capture your database schema into a series of numerically-ordered change scripts, called migrations. After the migrations are authored at design time (using Redgate SQL Compare under-the-hood to generate the T-SQL code), it’s a simple case of re-running the scripts against a blank database to reproduce the database schema and seed it with a set of test data.

Having the database stored as as a project meant that, after we’d pulled the code down from source control, we didn’t even have to think about how to keep our sandbox database up-to-date; we’d just hit the Start button in Visual Studio, and ReadyRoll would automatically apply any pending migrations provided by our teammates before the debug session would start. Applying migrations in this way is a safe and repeatable process, because we only have to get the scripts “right” at the time of authoring; once checked into source control, the scripts are immutable and are applied directly to the target database without modification. The great thing about this approach is that we each could now decide when we want to integrate changes from other team members, rather than having that decision made for us. As we were already using TeamCity and Octopus to build and deploy our assets, and ReadyRoll ships with integrations for both tools, it was pretty straightforward to take the next step of automating our entire database deployment process.

Traditionally, the database has been a bit of a second-class citizen within the domain of application systems. But by including your database as part of your Visual Studio solution, you can start reaping all the benefits usually associated only with application assets, such as including a peer code review step in your development workflow and being able to continuously deliver new features through to Production. It just takes that little bit of initiative to get started, but with the right mix of tools and a bit of sweet talking with your beloved DBA, the results will start to speak for themselves.

ReadyRoll helps you kick the habit of using a shared database development environment by providing all your developers with not only the ability to deploy databases from scratch, but also to keep their private environments up-to-date with each other’s changes. You owe it to your database to give it a try today!