Want to bring DevOps to your Database?

It is widely accepted that if you are putting your database into DevOPs deployment processes – you need to at least have your database in source control. Good starting point? In fact there is something you need to do before you even commit your changes up into source control.

You need to be writing unit tests for your database.

When I started speaking to audiences about DevOPs I realised that not many people do unit tests for their databases. Why not??

Well it appears I was talking to the wrong crowd…. sort of.

Code first developers who design databases using Entity Framework are used to writing unit tests so most times they are testing code that will change the underlying database.

So that led me to ask the question: “Are DBAs doing Unit tests?”

Simply put – they are not.

Before I explain why you as a DBA or database DEV should be doing Unit Testing – let me explain what a unit test is:

The primary goal of unit testing is to take the smallest piece of testable software in the application, isolate it from the remainder of the code, and determine whether it behaves exactly as you expect.

This testing is done as part of the development process, and a unit test will check that the code being tested meets a specification, while a library of unit tests together will check the functions expected of the application. By automating the process , which allows the library of unit tests to be run frequently, and repeatedly, it allows us to find bugs earlier, and in smaller units of code, which are far more easier to debug.

Unit tests should be self contained enough that you are isolated in what you are testing so that you know whether you have a correctly performing test.  Too often we think of databases as massive stores of data and generally we only test for performance. However – with unit tests we are looking at far smaller sets of data, we want tests that are fast and we are testing the functionality/quality of the code rather than the execution speed.

The upshot is that once we embrace unit testing we can then start to utilise regression testing  which can allow us to refactor our database code just as easily (if not more confidently) as developers do for application code.

So if hiring a .NET developer who doesn’t do unit tests is unthinkable – why would we accept this as the norm for the people who are writing the stored procedures etc that touch/influence and ultimately own our most precious resource – our data…?

Because it is too hard?

I find tuning indexes and queries hard – writing a unit test to prove that my code won’t kill PROD seems way easier. Also if I find a bug in my code when I’m writing the code at 3pm – it’s way easier to fix it then than at 3am when an online banking system has crashed/burned or is now corrupt…. I’m sorry but saying unit testing is too hard is a cop out.

Because it is too slow?

Refer the example above – way easier to write a little bit of a unit test and prove that my change is going to work when it will only trash my DEV instance. Fixing it then is far quicker than when 1,000s of users are affect – because there are less people calling my phone/emailing me when I fix it in my DEV instance..

Because it is too new?

Not at all – SQL Server Data Tools (SSDT) has provided database developers the ability to do unit testing since 2012. https://blogs.msdn.microsoft.com/ssdt/2012/12/07/getting-started-with-sql-server-database-unit-testing-in-ssdt/

In fact there is an old article at SQLServer Central here  http://www.sqlservercentral.com/articles/Editorial/68586/ the original was written in 2009. In fact if you look on google there quite a lot of articles going back to 2008 – they even mention having the database in source control…

Because it involves learning a new language?

You don’t have to – tSQLt and SQLTest by Redgate both allow unit tests to be written in TSQL – which most DBAs thrive on. Even SSDT allow you to write unit tests in TSQL.

I have used SSDT a fair bit so if you are a database developer then I highly recommend you read “Creating and Running a SQL Server Unit Test”   https://msdn.microsoft.com/en-us/library/jj851212(v=vs.103).aspx

If you are a DBA then I highly recommend you look at tSQLt.org – the official website has lots of useful information. There is a slight learning curve – but after 15 minutes reading and trying it out – it is very simple to use. It allows you to isolate your testing to a particular schema and makes use of fake tables – equivalent of mocking –which allows us to take copy of the table as it is and test against it.

There is a great pluralsight course here:  http://pluralsight.com/training/Courses/TableOfContents/unit-testing-t-sql-tsqlt

David Green is the author of the above Pluralsight course and has written a fair bit about tSQLt – http://d-a-green.blogspot.co.uk/search/label/tSQLt

Greg Lucas has also written a lot about tSQLt http://datacentricity.net/tag/tsqlt/

His article on http://datacentricity.net/2011/12/advanced-database-unit-testing-with-tsqlt-testing-cross-database-tables  is particularly helpful.

Of course there are some other great utilities and I mentioned one earlier:

SQLTest by Redgate – which is an absolutely awesome tool for DBAs – mainly as it plugs straight into SQL Server Management Studio (SSMS)

https://www.red-gate.com/products/sql-development/sql-test/index

It uses the tSQLt framework and incorporates SQLCop SQL Test which will help you enforce best practices for database development and run static analysis tests.

Best part is if you are on a DevOPs for Database journey then you can fold those tests into your Continuous Integration processes to really bring up the quality of your database code.

At a high level this is how you should be getting unit tests and Continuous Integration going for your database:

1. Import the entire schema and scripts for creating your database and commit them into source control. This means that anyone can now recreate the database at a know point in time – consistently.
2. Have scripts for sample data that can seed the database as part of the build process.
3. Write unit tests for all code that will interact with the database and commit this to source control too.
4. Use a Continuous Integration build server to build the database schema, load sample data and run the tests. The same build server will be doing the same for application code and thus integration testing can occur.
5. The output of this will be a standardised package that will represent changes in the database that have been tested and thus of higher quality code that if you have not taken the time to write the unit test in the first place.
6. Every time code is written – write the unit test and commit to source control and build. The aim is that every time you commit – you build and test.

First and foremost, need to start looking at unit tests – today. Checkout out our training section if you are interested in conducting unit testing for your database.