Primum Non Nocere

One of the items I ran into when I took over as the App DBA for SPAA was the complete disregard of source code control for SQL objects. At times there could be two, or maybe three, different scripts for the same object in the same build. So when the build occurred, it was possible to overwrite change A with change B, which in turn usually resulting in a defect. It was frustrating and it had to change.

So to put an end to this issue we instituted formal source code control for all SQL objects. The rules were:

  • Each SQL object would have a single source code, or SQL script, file.
  • Each SQL object script would contain all DDL, including security grants, so that they would be whole and complete.
  • Each SQL object script would be written in such a way as to Do No Harm (or in Latin: Primum Non Nocere).

Following these rules allowed us to ensure that all changes to a single object were contained within a single file and that file would have a history that could be tracked. They also ensured that if the file was executed, it contained everything necessary to allow proper operation of the SQL object. Lastly, it would allow the script to be run on any environment, at any time, and insuring that the database would be in the proper state.

Do No Harm

The Do No Harm philosophy had two main paths: table scripts, and everything else. The reason the table scripts have to be treated different is that they are objects with permanent state. All the other objects (stored procedures, functions, views, etc.) are stateless. Regardless of which type of scripts was managed, all scripts followed this simple rule:

All DDL manipulation occurred within the confines of a controlled database transaction.

This ensured that the script worked completely, or returned the object to its original state.

So for the stateless objects the rule was drop and recreate. Doing this provided several advantages:

  1. You could query the system tables for when the object what created. This works well if you, say, want to verify a turn the Turn Center just completed.
  2. If the object didn’t exist, it would just create it and move on. Before it was possible to get an error when you ALTERed an object that didn’t exist.

For objects with permanent state (tables), you have to maintain the state. Therefore you have to alter in place…And this is where it gets tricky. So for table scripts the following rules were followed:

  1. Tables are created once, so you had to check for existence before creating the table.
  2. DDL could be added to the script, but existing DDL was never altered.
  3. As new DDL is added to the script, the appropriate requirement checks had to be made prior to execution. Say for example you wanted to add a column, that was segregated from the original CREATE statement and you checked to see if the column exists prior to ALTERing the table and adding the column.

It did take a little time to train the developers to follow this philosophy, but the payoffs were well worth it:

  • It allowed HEAVY automation, including automated SQL builds to the lower environments.
  • It reduced downtime during turns.
  • It reduced turn preparation.
  • It allowed automation of turn verification.
  • It reduced defects.

Now all we need is a starting code base….

Creating the Code Base

To establish the original code base files, we needed a way to create ~12,000 individual SQL scripts. No short order by any imagination. So we used the Do No Harm templates as a base to construct a program that read the system tables of the PROD databases. The program extracted all the necessary information to reverse-engineer the T-SQL to a file, then that file was pushed to the code base structure of TFS. Easy!

The question you have to ask yourself is this: Why isn’t everyone doing this?

Leave a Reply