This will be the first in a series of blog posts about a problem and solution to an every day problem I have found myself in the middle of for a while now. As I expect the solution to grow over time I shall try to do some following up on the matter a little now and then.
The basic problem is a non-unique one for people in the software development loop: Version control of source code. As a company developing software products, it’s crucial for us being able to quickly find the correct version of our source code, whether it’s about deploying the latest stable release of the system, a given version, or the vNext for demo purpose. Intermingling different version is bad at best and extremely critical at worst. Also, with the ability to quickly roll-back crappy check-ins or finding the person doing the last check-in of a component to ask those specific questions is highly valued in the steadily faster spinning hamster wheels.
So, you might say, what is the problem really? There are tons and tons of great versioning tools out there. Microsoft TFS, Subversion, Git, ClearCase, Visual Source Safe, what have you. Surely we have one or more of those systems at our disposal? Yes, indeed we do. For quite a while now MS TFS has been the weapon of choice, since it integrates smoothly with both our development process (check out my earlier posts about Scrum) as well as our .NET-based code and Dev Studio. And by that the story would have been told. But…
As it often turns out in real life, nothing is really as simple as that. We have som deviances which complicates the simple scenario. To start out, our main product is one built on the foundation of Microsoft SQL Server. The DBMS is truly great and I must say I have turned a hatred and despise into something better described as tolerance, and in some cases true love. Lucky so, since all business logic is placed in stored procedures, user defined functions, triggers and views.
Better yet, almost no tables have the technical keys you expect to find in traditional databases and relations. These keys exist, of course, but only implicitically – and (oh my) – their names are coded using a special kind of syntax and hard-to-find semantics. The system is probably one of a kind and would send the heads spinning on the best DBAs. To add further complexity, the logic is intertwined with data itself, and very often with specific row versions of data, generations of data etc. As a result of this, there is not a single DB instance holding the true view of system versions. There are several.
To be more specific we have exactly 156 databases on 4 different servers running per today. These 156 databases are made from about 187 000 objects inalles. Many of these databases are old and should be offlined, others are just temporary ones which have since long passed their expiration dates. Again, others are exact copies of each other and just dumb and redundant.
The problem really is that no-one can be sure what is being used – and when. As you probably have guessed by now, these 156 databases are not under source control at all and it is all about disciplin with our devs and consultants in word-to-mouth and tedious hours using diff tools such as SQL Delta and Adept SQL Diff that we have the slightest chance of deploying the correct versions when needed.
Also, since the situation has gone a bit out of hand, it has now reached a point where it is almost impossible to implement a source control system such as TFS. Even if we’d manage to do that it would be very hard to find what version really is deployed to what server at any given time. Just because a sproc is checked-in in TFS it does not mean it also have been deployed to one or more servers.
Step 1 ought to be: Start gathering information about our 156 databases to see what is used, when it is used and who the people really are, committing the changes.
@stakes 