THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Should You Get Your Database Under Version Control?

CodingHorror has a blogpost titled Get Your Database Under Version Control

It is suggested that you also keep the data in version control. I am reading through the comments and I am amazed. One person writes "Once you have used ActiveRecord Migrations it is very hard to go back!"

This might work for a small or medium size database. My database is well over a terabyte, how would you keep that data under version control? Data gets modified every day. Some of the data is encrypted. Some of the data gets inserted into audit tables. Data gets replicated to other servers/databases. There are jobs that pull in data from real time systems every second. I do have different versions of DBs on staging and QA servers but only one on the production server. Changes have to go through change management, you have to open a ticket to do a change. This is not something you would do on a daily basis.

What is your opinion? Do you have the schema in version control? What about the data itself?

Published Monday, February 04, 2008 11:34 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

andyleonard said:

Hi Denis,

  Great topic - apologies for the short (and imcomplete) comment.

  Schemas - yes.

  Data - some. It depends on the data. Metadata, yes. Business Data, no. Metadata typically doesn't change as much anyway. It's the stuff that tells the web and application pages  - and perhaps other aspects of the database itself - how to look and behave. I use separate databases these days for metadata (because of DR mainly, but also for versioning).

:{> Andy

February 4, 2008 11:01 AM
 

Jeff said:

Well that is an interesting question and I would honestly say I think it is difficult if not impossible on some systems to version control the database. Now can I control the initial scripts, yes and I would consider those versions. Now those are the base installs of the database. Now you base install might not change, but other table may get added and removed without your control or by design. An example of this would be .net nuke or php nuke which ever flavor you fancy. It has a base database configuration however plug ins into the application are allowed to create their own table and modify the overall schema.

This happens in even larger scale apps as well. For Example peoplesoft, has the overall schema of its database changes by what ever pugins and add ins you buy. The base stays relatively the same but even some columns may be added to base tables. There are even ERP applications I see do this, plug ins and add ins will modify the schema.

Then versioning Data, no way. Now all my notes and tables and so on I do use full advantage of extended properties to keep track of my changes I even version the tables and columns and so on, however that is only simply adding a version number in the neta data, however I find this handy when seeing what has changed in the background. So much so that I even created a handy stored proc to just handle updates and inserts on 2005.

February 4, 2008 11:35 AM
 

Jeff said:

Oh and yes all my script are version controlled under source controlled environment. But I honestly consider once that script is executed, it is much like the birth of a child, you can raise it control what it watches on TV, control what it eats. However sooner or later once you release it into the wild, then changes are going to happen outside your control.

February 4, 2008 11:39 AM
 

Jamie Thomson said:

Schema - yes. Of course.

Data - Limited. If there is static lookup/reference/whatever data that needs to be in the DB at day zero then that data (or the scripts that create it) is in source control. If not, it isn't.

-jamie

February 4, 2008 12:35 PM
 

James Luetkehoelter said:

Schema - always

Data - lookup or control tables, sometimes (but always as create table/insert scripts)

Business data - never

I work with about 150 different databases with different clients, and I can't imagine any of them using source control for the data. I'm not sure I even see the practical need for it.

February 4, 2008 1:21 PM
 

BlackWasp said:

Certainly holding the schema in source control is an excellent idea.  Unfortunately the databases I am working with are generally too big and too fast moving to control in this manner.  I have much the same problem as yourself here!  I do find taking a backup file to source control of a new, empty (aside from base metadata) database is most worthwhile.

February 4, 2008 4:40 PM
 

David Markle said:

I'll add a bit to that -- My schema is always in version control one way or another.  So is my unit test data -- usually in the form of scripts, but sometimes in the form of an small SQL Server or Access database, so it can be a bit more easily edited.

But the database itself with a full complement of data is almost never in there, unless it's a very, very tiny project...

February 4, 2008 7:20 PM
 

Alexander Kuznetsov said:

Upgrade and rollback scripts need to be under source control too.

February 5, 2008 11:15 AM
 

Peter said:

Such an ambiguous topic. What do you mean by version controlling your database? Is it the software and its latest patch, the meta data, the records, and/or (stored) procedures/functions?

Time to define scope before muddying the waters.

February 5, 2008 9:35 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement