Six Ways to Fail At Local Database Development

A while ago I was assigned to a project to move all of all the business logic from Oracle packages into C# code. Before I could even start moving the code I had to create a suite of tests covering those business rules in the database. The Oracle packages read data from various tables, performed some logic, wrote some data in other tables, and then returned a status code. The tests would insert the necessary data, run the Oracle packages, and then verify the status code and data was correct. When the business rules were finished being ported over I could run the same tests, but this time run the code instead of the Oracle packages and the results should be the same.

Initially I setup the test data in the development environment and it worked for a short while, but the data kept changing because other developers would inadvertently touch the data. The only way I could guarantee the data was in a state for reliable testing was to set up all the necessary data before each test, tear it down after the test, and repeat. To avoid all the confusion that goes along with this I setup a local instance of the database. I used Oracle’s SQL Developer IDE to generate the necessary schema scripts and manually applied them to my local instance.

The project I created the integration tests for had no database schema changes. It was one of the rare projects where it was all business logic. In fact the mandate was we should be able to swap out the Oracle packages with the code I was working on and no one should notice a thing. Other projects were still making schema changes. Every once in a while I manually would run a diff and sync up the schema on my local instance with the test environment.

It worked like a charm and the project was a success.

My manager instantly saw the benefits of local database development and how well the integration tests worked. The team was going to start using local databases.

It failed spectacularly.

Why did it fail? I didn't think about the below items.

#1 – Initial Setup

Database server setup can be very complicated when starting from scratch. There are databases to create, tables to import, security to setup, and so on. I did all the setup in an ad hoc manner, when something failed I would fix it using the UI. I never scripted out my changes or kept a log of what needed to be setup. When it came time for other team members to setup their own databases I had to work through the exact same issues again.

In order for the team to adopt local database development all the necessary setup should be placed in a batch file or a script file. SQL Server’s setup.exe has a command line interface and it is possible to run scripts through a batch file using SQL Server’s SQLCMD utility. Before releasing it for others to use run the scripts locally. Have one or two other people try them out and make any necessary adjustments.

#2 – Lack of Automated Deployment

The project I just finished didn’t have any schema changes; I never worked through how to promote schema changes to testing environments in an automated process. This was the major downfall of the process, and the reason why it was abandoned shortly after I left the company.

If the process is manual it is a guarantee something will be missed during promotion to higher environments. Something is always missed. It is possible to get lucky and get a few releases out the door without missing anything but it will happen. There are a number of tools out there that automatically apply changes. Most of better ones are from Red Gate (SQL Compare, SQL CI, etc.). There are also some open source tools out there that promote changes such as RoundhousE. I’m not going to tell you which tool to use. I’m just going to tell you to use a tool to automatically promote changes.

#3 – No Source Control

Developer A would make changes to their local instance Developer B needed. Developer A would have to either manually tell them what they need to add, script it out for them, or connect to their instance and make the changes themselves. Neither one is ideal. The whole point of source control is to do that very thing.

Compounding the problem was the lack of a truth center. Production represents the latest stable release. A pre-prod or staging environment represented what the next release will be. Dev is always in flux, columns are being added and dropped. When there is a conflict between the environments which one wins out? The schema stored in source control represents what the database should be. It answers all the questions as well as keeps track of when changes were made and by whom.

The database needs to be under source control. Red Gate provides a very nice tool call SQL Source Control. It generates the creation scripts based off a linked database. If you prefer open source, RoundhousE uses scripts which can also be saved in Source Control.

#4 – Schema chaanges discovered and applied manually

The lack of automated promotion also meant a lack of automated syncing of the local instances with development or what is in source control. This meant developers would have to periodically run a diff between their local instance and what was on the development instance. Using Oracle’s SQL Developer to perform the diff on a large database like the one our team had was not ideal. The diff was so flaky it was only possible to apply one feature at a time, the first diff dealt with permissions, the second with tables, the next one indexes. If there were a lot of changes in one day a developer could spend their entire day keeping up with all the changes.

Tools such as Red Gate’s SQL Source Control automatically determines the difference between what is in Source Control and the local instance. It provides a nice interface to get the changes locally. Looking at the open source tool community, RoundhousE also provides a way to get changes from source control locally. The interface is not as nice, it is a command line, but it is possible to do it automatically. Whichever tool is used, the process to get schema changes applied locally has to be automated and easy to run.

#5– Lack of Test Data

Ask any developer why they connect to and test in the highest possible environment (pre-prod, QA, dev) and their answer is guaranteed to be “because it has the most data.” With the integration test suite I came up with, all the necessary data was inserted and then cleaned up for each test. I didn’t have to worry about, so I never came up with a way to get test data from different environments. Telling the developers they just needed to write a few scripts was not the way to go. More often than not multiple insert statements will need to be written just to test a single customer or product or feature.

It is possible to approach this a number of ways. The easiest is to use a tool, such as Red Gate’s SQL Data Compare or SQL Data Generator. But that might be overkill depending on your needs. You could also make use of linked servers connecting the local instance to a test environment. Then it is a matter of writing some scripts to pull data into the local instance. No matter which route is chosen the options should be saved into source control. SQL Data Compare has it’s own file structure, and there is no reason why a script cannot be saved.

#6 - Unable to Copy Data

During testing QA will put a record in such a state that causes an error. When that happens we tell the tester to stop what they are doing so a developer can take a look at it. Otherwise they might make some change that fixes the data.

A more elegant solution is to have a way to copy the data from the testing database to the local database. In a perfect world you can have the ability to copy the data multiple times but somehow given a new record each time, giving the developer multiple chances to find the problem as well as test the fix.

Without this ability developers will connect to the test database server. They need a way to fire up the debugger and step through the code.

Conclusion

Without a proper plan it is very easy to fail at local database development. Especially when developers are used to developing in a shared environment. It is very important to work through the process with a few people before presenting it to the entire team and/or division. The process doesn’t have to be perfect, the analogy I used is “we just moved into a house and we have put away all the dishes where we think they should go, but after a few weeks we might change our minds.” At the very minimum, make sure the above pitfalls have been addressed. Be flexible with the process and don’t get frustrated. A lot of cheese is being moved.