Waste Not

One of the last projects in my dad's long career was in biomedical manufacturing.  It took five years and never went to market.  In that time, his team learned a number of things, particularly about the production of ceramic seals having certain tolerances.  The difficulty in doing this with sufficient quality ultimately stymied the project.  I remember asking him what would happen to all of the knowledge the team had acquired.  Would they share it with the manufacturing community?  How could it be ensured that others could learn from their experience?

Learning is at the heart of the Lean Startup movement, which takes its inspiration from the short cycle times of small batch Lean manufacturing processes.  Eric Ries wrote down the principles of the organizational processes that many successful startups have used.  Many startups find that their original idea goes through many iterations before becoming a successful product.  The worst thing a startup can do is spend a year 'perfecting' a product no one wants.  So, startups should get an (imperfect) product out there, see how people use it via actionable metrics, and modify it and their assumptions accordingly.

The Lean philosophy is essentially about embedding the scientific method into product development processes.  Since you can't rely on customer feedback to learn how people use or could use a product, you should build reporting into your software from the start, not as an afterthought.  Your software should also be able to run A/B tests where some users are given a new interface while a control group sticks with the old one.  The ways in which customers will use a product should be tested by its use.

In a certain way, this is all very straightforward.  No one wants to think that they are not thinking scientifically when evaluating a product, but being Lean is hard.  The scientific method has to be at the heart of everything you do.  It touches what is built as much as how it is built.  The product development and product planning teams have to work very closely together.  Developers can't just build whatever product people think up.  I know of many organizations that could not work this way.

Though Ries's advice is sound for software startups, his main target is waste at a societal level.  Think of all the billions of dollars and hours of time wasted pursuing products that were not or could not be rigorously hypothesized, tested, or proven due to imperfect processes.  The problems of product development are people problems, not technological problems.  Ries shows how to cut waste at an organizational level, with benefits that will be felt throughout society as people start to adopt the principles.

Still, Lean doesn't really help with the problem facing my dad's organization.  What techniques or technologies can be used to share information between organizations?  This may not be a general problem that can be solved.  And, after all, if companies share everything about what they've learned, what advantage do they have in the marketplace?  By writing down Lean principles, Ries is helping to share best practices about management, but there are plenty of domain-specific problems and best practices out there to be tackled.

Database Version Control Goals

I'm currently version controlling a database and developing version control processes which will be used by a new team at my company. Everyone knows you should version control your database, but what exactly this means and how to go about doing it are not obvious. As I've mentioned before, version controlling databases is much different from version controlling application code, because you can never start from scratch with a database.

To meet the minimum requirements of a version control system (VCS), you must be able to:

1. Maintain a directory structure of all database objects
2. Maintain change/upgrade scripts
3. Isolate development from production code

The most basic version of Subversion will allow you accomplish these tasks, with two caveats. First, you have to either build your directory structure from the ground up, or you have to form some kind of process that will script out your database. I've tried the latter with Powershell, SSMS, Visual Studio database projects, and custom .Net code using SMO. In my opinion, the least bad option is .Net, since the code is not too difficult to write and you can customize it to your needs. You can add in functionality to script out only objects changed after a certain date. However SMO is incredibly slow. It took me 2 hours to create a baseline for my database.

The challenge shared by all of these home-brewed approaches for scripting out database objects is that you have to be consistent. For instance, if sometimes you use SSMS and sometimes Powershell, the object scripts may be slightly different and create false positives in your VCS.

If you stick with these minimum goals, you are also hampered by the annoying and uncertain process of having to maintain change scripts on top of your object scripts. I'm sure you always test your change scripts thoroughly on test environments that exactly mirror production, but you can never be 100% sure that your codebase actually matches your production database. Finally, if you want to create sandbox environments, you'll have to create another process for turning your object scripts into database generation scripts. This is more important if you work at a software company and need to be able to reproduce defects in old, supported code than if you work in an in-house database system. In either case, it's still a good idea to be able to create sandboxes for developers so that people don't break each other's code from working in the same database.

For these reasons, you also want to be able to:

4. Generate change scripts automatically
5. Create database environments with test data at any version
6. Know what version a database is

Now we're talking about incurring some monetary costs, but most likely ones that will pay for themselves. The two main options for achieving these advanced goals are Red Gate tools and Visual Studio Ultimate / Database Edition.

Visual Studio database projects are a cool idea, since you can populate object scripts and compare them against your database, as long as you have something better than VS Premium. You can get Anhk SVN or Visual SVN to version control directly in VS. VS has an 'offline' development model, meaning that you do your code in VS and then later create change scripts by 'compiling' your database. I've had issues with circular dependencies among databases, making this an unfeasible process. You get what you pay for with Red Gate tools, and you'll really want both Source Control and SQL Compare. Source Control just helps you deal with the basic goals in an elegant way, but, when paired with SQL Compare, it will allow you to create scripts to turn any database at any version and turn it into one of any other version. You can also get tools like Data Compare to sync your test and production environments, and you can use the Data Generator to create non-sensitive test data. These features improve the development process, but are not necessarily within the scope of version control.

There are a few options for knowing what version a database is on. SQL Compare will allow you to compare the database with a version in a repository. You could also create a table that stores the database version, which is useful but will never provide 100% certainty about all the objects. Finally, you can create extended events for database objects but Phil Factor doesn't recommend it in the eBook, since there are not many options for documentation. Database version control is slowly making it's way out of the Dark Ages and into the modern, Agile world that other developers have long taken for granted. Once you are able to handle all of the advanced goals, you can do even more things, such as automate the build process and run automated test scripts, a process called Continuous Integration. I must admit that the developments like those Red Gate has made take some of the mystery and the fun away from good ole database development. But I'll probably get over it.

Links:
-Grant Fritchey's article on Simple Talk, which is chapter 3 in the Red Gate eBook
-Michael Baylon's own list of goals and solutions
-Another good article