Database Objects and Source Control

Replies to This Discussion

Permalink Reply by Daniel Leuck on July 26, 2008 at 5:27pm

Hi Stephen. What exactly are you placing under source control? SQL scripts? DB dumps? We definitely keep any DB related code under source control.

How can we enforce it when users can just modify the database directly?

I would responde, "We have bigger issues than source control policies if end users are modifying the database directly." :-)

It would be too much trouble to get everyone on-board.

That is where a strong CTO comes in handy. I don't believe tech organizations should be democracies. A good CTO solicits opinions and then sets non-optional policies based on his or her best judgment.

Permalink Reply by Stephen McMahon on July 27, 2008 at 10:08am

My goal is to put all stored procedures, UDFs and scripts for creating tables and views under source control. Not long after I made that post, I went ahead and scripted out the objects and put them in source control anyway. I can't force them to maintain it but at least we'll be covered to a certain point. Fortunately I've gotten their attention and we'll be having a conference call regarding this on Monday (tomorrow).

As for the CTO thing, well, we don't have one. My employer operates more as a staffing agency than a development shop. I've been on assignment at our largest (and sometimes only) client, Schlumberger, for almost 7 years now. They do have a CTO, but from what I can tell, the IT landscape would be best described as a semi-connected archipelago. It is not uncommon to find out much too late that there are 2 or more similar initiatives evolving simultaneously without knowledge of one another. I'm a wannabe standards, patterns & practices freak so I'm at odds with this 24x7. Maybe my new surroundings will yield new opportunities.

Daniel Leuck said:

Hi Stephen. What exactly are you placing under source control? SQL scripts? DB dumps? We definitely keep any DB related code under source control.
How can we enforce it when users can just modify the database directly?
I would responde, "We have bigger issues than source control policies if end users are modifying the database directly." :-)

It would be too much trouble to get everyone on-board.

That is where a strong CTO comes in handy. I don't believe tech organizations should be democracies. A good CTO solicits opinions and then sets non-optional policies based on his or her best judgment.

Permalink Reply by Daniel Leuck on July 27, 2008 at 6:37pm

My goal is to put all stored procedures, UDFs and scripts for creating tables and views under source control.

I can't imagine a professional developer opposing you on this, but I suppose everyone is entitled to their own wrong opinion :-) That stuff should absolutely be under source control for all the same reasons you would put Java, C# or Ruby code under source control. I don't see how they could make an argument for one and not the other. If you are dealing with people that haven't used a source control system before I would let them know that these days they have nice UIs and integration with popular IDEs. Checking something in and out of Subversion or Perforce from Eclipse, IntelliJ or VisualStudio is as easy as right-click commit, right-click update.

Fortunately I've gotten their attention and we'll be having a conference call regarding this on Monday (tomorrow).

Excellent! I hope it goes well.

As for the CTO thing, well, we don't have one. My employer operates more as a staffing agency than a development shop. I've been on assignment at our largest (and sometimes only) client, Schlumberger, for almost 7 years now. They do have a CTO, but from what I can tell, the IT landscape would be best described as a semi-connected archipelago.

Ah - I've been in that situation. I can be very frustrating.

Permalink Reply by Stephen McMahon on December 28, 2008 at 9:32am

I have since actually convinced them to start keeping all of our database code in a source control system. I demoed SourceGear Fortress to our main client and my boss and they said that if I could figure out how to extend Fortress to create a work flow for deploying code to our test environment for everyone and to production for only a few then they'd go for it. So I created a set of web services and a clickonce application that allows us to select and order the database objects before pushing them to their location on the servers. We now use this for deploying both web and database content directly to the servers. For web content, the files are written directly to the file system. For database objects, I use SQLDMO to commit the change to the database server(s). For each deployment action, the associated change ticket is updated with a comment that contains the details of the change (full path and version number of each object, the username and the server that it was deployed to). The client tool allows the deployer to adjust the order in which the objects are deployed so that we can make sure that dependent objects are pushed after the objects on which they depend.

It is still a work in progress and we keep a wiki for tracking the planned changes and a wish-list for new features. The next thing that I'll be working on is a rollback feature to allow a full set or an individual object to be rolled back to a specific version or label.

I'm finding that SourceGear Vault and Fortress are excellent products that is reasonably easy to extend. They also have a very responsive and knowledgable support team.