Managing database schema changes has always been challenging. Keeping track of what scripts to run in what order and getting database states just right in all the different environments is painful and error prone. However, there are some fantastic tools from Redgate which help us manage our database changes as well as creating a fully automated Continuous Integration and Continuous Delivery pipeline. ReadyRoll Core, SQL Prompt Core, and SQL Search are tools from Redgate that are included in each copy of Visual Studio Enterprise 2017 or in the Redgate SQL Toolbelt. Using these tools, you can commit your database schema changes right alongside your source code in the same repository. You can also seamlessly deploy these schema changes to the databases in all your environments in an automated manner.
ReadyRoll Core is a migrations-driven approach to database schema management. Being migrations driven means we can now shift the creation of database deployment scripts to the left so that these migration scripts can be source controlled and tested early for fast feedback. This gives developers more control over exactly how database changes will be deployed to the different environments.
We will walk through creating a ReadyRoll project, making schema changes to your database, and building out a CI/CD pipeline pushing your database schema changes to your different environments using Visual Studio Team Services.
Creating a ReadyRoll Visual Studio Project and Importing Schema from a Database
The first thing we need to do is verify that we have Visual Studio Enterprise 2017 with the “Data storage and processing” workload installed. Make sure the three Redgate tools are checked (Redgate ReadyRoll, Redgate SQL Prompt, and Redgate SQL Search).
Next, let’s create our database Visual Studio project. Usually, this project would sit in the same solution as the one holding your source code for your project.
- File > New Project , SQL Server > ReadyRoll SQL Server Database Project
- This creates your ReadyRoll project
Next, let’s assume we have a database that already exists and we want to import the schema into our ReadyRoll project. Alternatively, we can start from a completely blank schema and build up our schema through the ReadyRoll project as well.
- Because my source database is in SQL Azure, I want to create my working databases locally so I can work quickly and offline if needed. Right click the ReadyRoll project and select properties
- This opens up the project properties tab. Click on Debug
- Scroll down and check Always use default connection string for Shadow database
- Click Save All
- Click Connect to database button in the ReadyRoll window
- This opens up the Connect dialog, enter in the credentials necessary to connect to your database. In my example, I’m going to connect to an SQL Azure database named bikesharing-services-ridesname_prod on the database server bikesharing-services-dbserver.database.windows.net. After entering credentials, click OK
- Click Import database
- Wait for import to finish
- Click on Refresh (Verify Script)
- This verifies the migration script works correctly on the shadow database. Notice how there is now a big green check mark
- Notice how a new migration script has been added. The name of the script should be in the format of 0001_YearMonthDate_SomeNumber_username.sql
- Let’s change the name of the migration script to be 0001_Initial_Schema.sql. This will make the migration script name more readable and descriptive.
After importing the initial schema from our “production” database, let’s point our database project back to the local db instance
- Click on the Home button in the ReadyRoll window
- Click on Configure database connection link in the ReadyRoll window
- This brings up the Connect dialog, connect to your local instance of your database and click OK
- Click Deploy to deploy the initial schema to the local instance of your database
- Wait for the deployment to finish
Preparing Deployment Databases for ReadyRoll
In this example there are two target deployment environments. A Dev environment and a Prod environment. Both the databases are hosted in Azure and the Dev database is called bikesharing-services-ridesname_dev and the Prod database is called bikesharing-services-ridesname_prod. We have already imported the database schema from the Prod database, so we don’t have to do anything more with the prod database. At this moment, the Dev database has the same schema as the Prod database. We just need to prepare the Dev database for ReadyRoll.
- Click on Configure database connection
- This brings up the Connect dialog. Enter in the credentials to your Dev environment database and click OK
- Notice how we are now connected to the Dev environment’s database
- Double click the 0001_Initial_Schema.sql migration script to open the migration script. Notice how the Script status window shows the Dev database. Click on Mark as Deployed. This step ensures that the Initial_Schema migration script will not be ran against the Dev database in future deployments since it already matched the Prod schema.
Now, let’s connect ReadyRoll back to the local instance of the database that will do all our future development work against.
- Click on Configure database connection
- This opens the Connect dialog, enter in the credentials for your local database instance and click OK.
- Double click the 001_Initial_Schema.sql migration script to open it. Notice how the Script status window says this migration script has been deployed to your local database instance.
Making Changes to the Database Schema
You can use whatever tool you want to change the schema of your local database. ReadyRoll Core will then look at the changes and will create and add a migration script to your ReadyRoll project. In this example, I’ll use SQL Server Data Tools (SSDT) to change the schema of my local instance. I’ll add an extra column (LastServicedAt) to my local dev instance of my database. ReadyRoll Core will detect those changes and will create and add a migration script to my project. I will then manually tweak the update script so that it initializes the LastServicedAt column with data from the InCirculationSince column.
- In SQL Server Object Explorer, browse to your local database instance. In this example, I right click the dbo.bikes table and select View Designer
- In the table designer, I add a column LastServicedAt as a datetime2(7) and then click Update
- This opens the Preview Database Updates dialog. Click Update Database to apply the schema change to my local database
- Click on the ReadyRoll Core Edition tab to open the ReadyRoll window
- Click on Preview objects pending Import button
- ReadyRoll Core will now compare the schemas between your local dev instance and the shadow database. Notice how ReadyRoll Core detected the changes I did to the bikes table. Double click the row with the change to see the detected difference by ReadyRoll Core.
- Click on Generate scripts for all database changes that are pending import button
- ReadyRoll Core creates and adds the migration script for you.
- If we wanted to tweak the script in anyway, we can just go and change the SQL in the migration script. In this example, let’s go ahead and initialize the LastServicedAt column so that it is equal to InCirculationSince. On line 9, I’m going to add the update SQL and then save the migration script. I’ll go ahead, highlight those two lines and click on run so that I update my local dev database
- Click on Refresh (Verify Script) to verify the migration script
- Notice how after the verification we get a big green check. Now let’s click on Mark as Deployed to mark this second migration script as deployed to the local dev instance
- Change the name of the 2nd migration script to be 0002_Add_Last_Serviced_At_Column.sql to make it more readable.
- Commit all of your changes into your Visual Studio Team Services repository.
I changed the schema using SQL Server Data Tools in Visual Studio. I want to reiterate that you don’t have to use SSDT. You can use whatever tool you want to update your local development database, including SQL Server Management Studio (SSMS). ReadyRoll Core will compare the schema between your local developer database and the shadow to create and add the correct migration scripts to your project.
Setting up your Continuous Integration and Continuous Delivery Pipelines
Setting up your continuous integration pipeline in Visual Studio Team Services for ReadyRoll migrations is straightforward when using the Hosted VS2017 Agent. Everything just works.
ReadyRoll Core will need the database server (TargetServer), the database name (TargetDatabase), the Database user name (TargetUserName) and the password to the database (TargetUserName). ReadyRoll Core will use these variables to create the right scripts. For this example, I used my Dev database values. Next, ReadyRoll Core will also need to create a shadow database so we will need to pass in the shadow database name.
Create a new build using the Visual Studio solution template. In the build, add the following variables
Then in the task list, add a task to create the shadow database using the Command Line task. Set Display name to Setup Shadow DB. Set Tool to C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe. Set Arguments to create $(ShadowInstanceName) –s
Next, in your Visual Studio Build task, add the following MSBuild Arguments:
/p:TargetServer=”$(DatabaseServer)” /p:TargetDatabase=”$(DatabaseName)” /p:TargetUserName=”$(DatabaseUserName)” /p:TargetPassword=”$(DatabasePassword)” /p:ShadowServer=”(localdb)\$(ShadowInstanceName)” /p:GenerateSqlPackage=True /p:SkipDriftAnalysis=True
Make sure the build uses the Hosted VS2017 agent and queue a build. My build artifacts looks something like this
Setting up your release to deploy your schema changes is straight forward as well. Just use the Deploy ReadyRoll Database Package task. Enter in the path to the ps1 script that is created. Enter in the release version, the target SQL Server Instance, the Target Database Name, the Username and also the Password
Because I’m using environment variables, make sure you set your environment variables to the correct environment values
Now my deployments will deploy my schema changes to the different environments
There are many benefits to using a migration based approach to database deployments. The biggest is that by shifting your database deployment scripts to the left, developers now have much more control over exactly how database changes will be deployed. These migration scripts are also tested early for fast feedback. Redgate ReadyRoll Core makes this entire process seamless for developers in Visual Studio and Visual Studio Team Services. Migrations are checked in and out of source control right alongside the actual source so that both the source and schema is versioned together. CI and CD can easily be set up with pre-built tasks from Redgate that build and deploy database schemas using these migrations.
If you are interested in creating a Redgate ReadyRoll demo like what was shown in the Visual Studio 2017 Launch event, there is an in-depth walkthrough published here.