Deploying schema changes to SQL Server databases can be tricky when you’d like to automate parts of your workflow. For instance, how do you go about version controlling your schema changes? In application development there are many ‘no brainer’ tools you could use such as Git, or Subversion. However, there exists no obvious choice for SQL Server database development.
That’s where ReadyRoll can be useful. Designed to slot into existing workflows, ReadyRoll allows you to keep track of schema changes by representing each change as a T-SQL script, known as a migration script. By running these migration scripts in order, you can version your database schema.
Importantly, MSBuild for Visual Studio can then be used to turn the scripts into a deployment package which can be executed on any database. This gives you confidence that deployments across environments will be uniform.
When creating a new ReadyRoll project, the schema of your production environment can also be imported as a baseline. Further work can be built on top of the baseline in a sandbox development environment and, when you’re happy with the changes, the project can be deployed to production.
While baselining the production database is an essential part of integrating ReadyRoll into your workflow, the process previously took several manual steps to complete and required users to be familiar with some concepts unique to ReadyRoll.
After thinking how we could improve this initial process and give users a much better setup experience, we decided to introduce a simpler and more intuitive wizard. As David Simner, Technical Lead on ReadyRoll explains: “We wanted users to get all the way to deploying to production with ReadyRoll without reading the docs – the new setup wizard makes it straightforward.”
Importing a baseline schema with the new wizard
The new wizard walks users through the process of creating a project baseline from an existing database in several easy to follow steps:
Firstly, it prompts users to specify the location of the sandbox development database and deployment target, and then provides a quick overview of the baseline process. Creating the schema baseline is just a matter of clicking the Create baseline button.
ReadyRoll automatically generates a migration script which represents the current schema on the deployment target – the baseline schema. Two folders are also generated,
1.1.0-Changes. The baseline schema migration script is automatically placed in the
1.0.0-Baseline folder, and the baseline schema can be deployed to your sandbox development environment by clicking Deploy project. You can then make changes on your development environment, which will be placed in
This whole process takes less than a minute, and all necessary concepts are introduced and explained without being overwhelming. Before, you would have had to follow a lengthy documentation page to configure ReadyRoll correctly to handle a project baseline. With the wizard all of the configuration is done for you.
Alongside the project baseline functionality, the wizard offers a host of smaller enhancements to the general experience of setting up a new ReadyRoll project. For instance, we’ve improved the way database connections are validated. In the old setup UI, invalid database connections would trigger a large error dialog to replace the entire interface. Now ReadyRoll checks database connections when they’re specified, and displays information in context without breaking the flow of the setup experience.
Note to advanced users
If you’re an experienced ReadyRoll user, you’ll be aware of the concepts of Semantic Versioning, Offline Schema Model, and Programmable Objects. The new wizard turns on both Semantic Versioning and Offline Schema Model automatically. You will be prompted to enabled Programmable Objects later on in the process. This is the way that we recommend a ReadyRoll project should be set up.
The new ReadyRoll baseline wizard makes setting up a project relatively easy and allows you to include ReadyRoll in your existing workflow by simply importing your production database schema to a sandbox development environment.
ReadyRoll is a capability of the SQL Toolbelt, the suite of industry-standard database development tools that make you more productive, your team agile, and your data safe.
Also in Hub
Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to...
Also in Product learning
Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for p...
Also in ReadyRoll
The SQL Toolbelt includes ReadyRoll, which allows you to adopt a migrations-first approach to database source control and deployment. There are a number of different ways teams can get started with Re...
Also about ReadyRoll
The recent State of Database DevOps Report revealed that within two years, 80% of companies will adopt DevOps. That’s an interesting finding in itself, but the report also showed that 75% of compani...