Ajarn Mark Caldwell Blog

Bringing Business Sense to the IT World…

SQL Source Control Contest

If you’re a regular reader of this blog, you know that I have written several posts about how important I think it is to protect your source code, to version it, and in particular, all the aspects I like about Red Gate’s SQL Source Control product.  But for a moment, let’s take a break from my writing and I want to hear your stories.  What nightmare situation are you in, or can you imagine, where source control for your database would save the world.  Or maybe your life is not so dramatic, but you do see a challenge that, if you just had a good tool like SQL Source Control, it would go much smoother.  What’s your pain?  You have read my writings, now tell me your story, and be in the running for a free copy of SQL Source Control from Red Gate.

Yes, that’s right.  Although I am just a fan of Red Gate, they have authorized me to give out a handful of licenses to blog readers who are willing to share their story by posting a comment to this blog entry.  Simply add your comment below (be sure to include a valid email address in the box that asks for that) to be entered.  The contest starts immediately and over the next few days, the best stories will win.

Legacy Comments

Julian K
re: SQL Source Control Contest

The first thing I do when starting at company is evaluate the backup procedures and source code repositories. I've worked in quite a few places where backups of database source code was not done. The typical explanation was that they backup the databases, so why would they need the source code? It's already in the database.

Needless to say over the years I've seen databases in smaller companies become corrupted and because no one is monitoring the sql server, the backups are of the corrupted database. Come time of a crash SQL Server has a fit trying to restore a database with errors inside it.

While monitoring and maintenance of problems is important, having a copy of the source code (in text) is invaluable. Sometimes there is a need to build a new testing environment, or a new developers machine. And if the production environment is 100GB+ as many big databases are today, it just doesn't make sense to wait around for all the data to be restored on a development machine.

If I find a company hasn't got their sql in source control, I script it out and check it in. But one of the common problems with scripting out things from Management Studio is object creation order. Sometimes a complex database that has grown organically over the years has too many dependencies to easily script out and just run.

Red Gate's tool does a much better job of detecting the dependencies and ordering the creation of objects so they can be re-created without errors.

Having the source code outside of sql server allows more developers to collaborate on projects. It allows each developer to see and check other developer's work runs without errors on their environment. It also allows you to run the changes through a test environment before putting it into production.

Having a personal copy of Red Gate's source control would make the task a lot simpler as I work with each company.

re: SQL Source Control Contest
@Julian K, excellent way to kick things off! Good point about good backups of a corrupted database, been there myself, unfortunately. And setting up developer environments...well that could probably be a whole series of blog posts.

Alan Dykes
re: SQL Source Control Contest
Early on in my database career (which was a lot of slop, to be honest) I broke functioning objects regularly (my own of course). I did a lot of data analysis and would from time to time create my own views, stored procs, etc. to accomplish what I needed. Most of the time that worked great but when I needed to change something, I can't count the number of times I broke it and went "I really wish I could go back to the previous version and try something else".

I started my current job about a year and a half ago. We have a production system in Access which I lobbied heavily for, successfully, to convert to SQL Server. Even though we source controlled the Access databases in Visual Source Safe, my predecessor had a habit of
1. Getting a latest copy of a database (we have about 90 Access databases for our system... good times)
2. Making changes over several weeks or months
3. Checking it out from source control
4. Copying his "working" database over what was checked out
5. Checking it back in

I don't know how many times I had to go digging through 20-30 old source control check-ins by hand to try to find a fix that got overwritten by another fix. Object level granularity in source control would have saved me so much time and effort.

re: SQL Source Control Contest
AH! Another excellent story Alan. I think many of us have had nightmares about Access. Too many to count on my part. :)