Best practices for change management with indexes Our IT shop is first starting to build a group of DBA's. All of us (myself included) have come over from the application development/architecture world, so the DBA world is still fairly new to us.

Along with building a DBA group, we are looking to build change manage procedures and processes (hopefully based on best practices) for when we need to move changes.

I've found the following post which is helpful for mostly trigger, stored procedure, and/or DDL changes. But it doesn't necessarily address indexes or vendor databases.

We have a mix of both our own and vendor databases. In our case some of the vendors (though not all) are working with our company to build the database(s) and applications. We are in the process of performance testing our applications now before they "go live". Thus we are analyzing indexes (or the lack thereof) pretty heavily.

As we come across indexes that we feel should be made, how do we best deal with change management with regard to these, both for our own databases as well as for any vendors?

What do you do in your shop? I'm worried less about tools then about the process.


  EDIT: So far, I'm appreciating the feedback, comments, and answers for this question. I have noticed that some of the answers are a bit
  tool specific. I'm looking for more "agnostic" practices, if that can
  be had.
  
  However if agnostic is not possible, then for tool sets, we use IBM
  DB2 LUW (and that actually on AIX) mostly. We have some DB2 on Windows
  and DB2 for i (IBM's i5/OS), but we are mostly AIX DB2. We do use
  source control, specifically Subversion.
  
  Again, looking for general best practices, but above is what we use
  that would be vendor specific.
  
  EDIT:
  Current Decision: We intend to track our reasoning as well as our changes. So we are going to open an issue in our issue-tracking
  software (which in our case is JIRA). Now we can add in documentation
  as to what priority the change has, data that backs up what the change
  should be, the change, and the results of the change from another
  environment where the change was tested.
  
  We then also intend to keep track of our changes in scripts in SVN
  (much like was suggested below). This way we can track what version of
  what exists where. This can be recorded in our JIRA issue (and in any
  other auditing software we use, ie. pasted links). We can know with
  more certainty what change went to what environment and why. We can
  then also track if the index was something we added beyond the vendors
  implementation or ahead of their implementation, etc.)
I would strongly recommend that you treat your database basically the same way as you treat your application code. You can script your database out to it's component parts and check those into source control and then use the same labels &amp; versions there that you use for your apps.

To get the objects into source control there are a number of tools you can use. Microsoft has a tool that is nicknamed Data Dude. It works with Visual Studio. They're also preparing to release a new tool called SQL Server Database Tools (SSDT), again, working with Visual Studio. My company, Red Gate Software, makes a tool that works with SSMS called SQL Source Control.

In terms of process, I wrote several chapters for the book Red Gate Guide to Team Development. It's available as a free download (or if you want to kill a tree you can purcahse one from Amazon). I go into a lot more details about working with databases in development teams there.