How to set up Redgate SQL Source Control with Continuous Integration

Question

My question is this: What is the best setup for managing SQL changes in a development team?

Our team consists of 4 developers, each with their own copy of a database. When committing SQL/Application changes to our TFS server, we wish to ensure that any build errors do not get propagated to other developers. So, we are going to implement continuous integration to assist with this.

The idea is that 1.SQL and application code changes are committed to TFS. 2. A central database gets the SQL updates, and we build the application. 3. Unit tests are executed on the build server. 4. If any of these steps fail, the checkin is rejected and the database gets rolled back to the state it was in before the commit.

What is the best way to set up our Redgate SQL source code to implement this?

Answer 1

If you want to use SQL Source Control, based on your requirements this is a possible setup to consider.

For each developer machine:

• Install SQL Source Control
• Link each developer database to your TFS repository using Dedicated database as development model.
• Install SQL Prompt to write your SQL more easily
• Configure SQL Test for writing unit tests for SQL Server

On the build server:

• Install Redgate DLM Automation (there are Add-Ons to simplify setup)
• Configure a Validate build task to validate the schema by checking the database can be built successfully from scratch
• Configure a Test build task to run SQL tests
• Configure a Sync build task to update your central database with the SQL updates
• Run your application unit tests
• If the test fails, you can run a custom script that revert the last check-in, use the sync build task again to roll back the database changes and trigger a new build. You can use Redgate DLM Automation PowerShell cmdlets to do it.

The last step could be tricky. I honestly prefer and recommend to use branches instead of relying on a single central database. In this way, each developer can be working fully independently and you can merge the new changes in the master branch only when the work has being validated on each individual branch.

If you want to go further and also implement deployment you can use Redgate DLM Automation Deployment to create a release database package and deploy your database changes to production directly from your build server or using a release tool like Octopus Deploy.

Finally, I would also advice you to have a look at Redgate ReadyRoll especially if you are considering a migration-first approach to database changes.

As you can see, there are different ways of using Redgate tools to manage database changes and there is no single best way of setting them up. It always depends by the specific requirements and problems you need to solve.

Hope this helps.

Answer 2

You can use a Database Project. It can contain the entire database schema plus stored procedures. During a build, it will verify that the stored procedures match the schema.

Then enable the Gated Check-in option in build definition, it accepts check-ins only if the submitted changes merge and build successfully.

For the data written to database, it's based on your test method, you can set the method to delete the data if the test failed, or you shouldn't be writing to a real database. Instead you should mock the database classes. This way you don't actually have to connect and modify the database and therefor no cleanup is needed.

For more information you can reference below articles:

• How To Unit Test – Interacting with the Database
• Database cleanup after Junit tests