A great day at SQL In The City by Redgate

Friday I had a great day at Redgate’s SQL In The City presentation by Steve Jones (@way0utwest). There were three sessions presented which are summarized below.

Session 1 – Team-based Database Development with Version Control

I thought that this session was going to mostly review for me since I am already familiar with the Redgate SQL Source control product, and use it most every day, however there were a few things that I learned that will help me going forward. One of the key points that I had not considered before with SQL Source Control is:

Adding lookup data to source control. After the session and after thinking about it a bit, this now seems obvious, but it is something I had not considered before. I have always though of source control as a way of storing all the schema, stored procedures, functions, and other types of code that are in the database, but now I am going to look at adding lookup tables to the database. Consider tables like a zip code table, a states table, a country table, or other tables that don’t change very often, but are used by your database application all the time. In the past when changing this type of tables, I have written a migration script to manually add the new rows, for instance an insert statement to add a new zip code. But instead if I was to just insert the new zip code to the table that has source control in place for the data, I could then have that data stored in a consistent state that I could always get back to if it is needed. What I love about this is that I already have all the software licenses to do this, I just need to do it.

Session 2 – Automated Build and Test

This was my favorite session of the afternoon, with a task on my to do list to build a Continuous Integration (CI) server, Steve was able to provide all the solutions needed to accomplish my task.

tSQLt for Test Driven Development

Having recently started with TDD in the SQL Server environment I have recently created my own test harness, however this is not something that I want to be responsible for maintaining over time. tSQLt is a database unit testing framework that allows you to do TDD with SQL Server, as well as allowing you to do continuous testing in the CI environment.

Once tSQLt has been installed, when your run the tests the first time you get results showing that 10 tests succeeded and 1 failed.

Next the tSQLt walks you through how to find the code causing the failure and shows you how to fix it. Once you have fixed the code that is failing, You then run the tests again and get a passing score as shown here:

I can’t wait to replace the simple TDD system that I built. Redgate offers an integration tool so that all of the test cases can be shown in SSMS. I haven’t had a chance to try that part out yet.

Team City Continuous Integration

There is a free download of Team City for small environments. This should be enough for me to try it out and determine if it is doing to do what I need. I will post a follow up article after I give Team City a try.

Session 3 – Best Practices for Database Deployment

The main focus of this session was on Continuous Deployment. It was a good overall course, but not something that I am ready for yet. I need to get the Continuous Integration working first.

A couple quotes from Steve Jones on Continuous development were:

You only have two known states, Production and Source Control.

and

ABCD stands for Always Be Continuously Deploying.

These may not be exact quotes, but it is as close as I could get while taking notes.

The concept of two known states, Production and Source Control is interesting. Everything else can be moving or changing without everyone involved knowing about it.

Summary:

This was a great session, and I would like to thank Redgate and Steve Jones for providing me the information that I need to move forward to build a Continuous Integration system. If you have a chance to visit a SQL In The City session by Redgate, it is well worth time time.