Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question
1  
Google Accidental dba and you will find there is a wealth of information for people like you who suddenly got thrown into being a dba. –  HLGEM Mar 2 '12 at 19:11
    
@HLGEM -- I'll take you up on that. Although I wasn't thrown into it. I actually chose to switch worlds. Always was fascinated with the database world. Our company had an opening and I saw that as an opportunity and I took it. –  Chris Aldrich Mar 2 '12 at 20:55
1  
@ChrisAldrich Welcome to the dark side. Feel free to chew the fat with your fellow database folk on the heap. –  Mark Storey-Smith Mar 3 '12 at 1:43
1  
@MarkStorey-Smith - And here I finally thought I saw the light! ;) Of course, the dark side is stronger.... –  Chris Aldrich Mar 5 '12 at 13:48

2 Answers 2

up vote 9 down vote accepted

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 & 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.

share|improve this answer
    
"use the same labels & versions there that you use for your apps." You may have implied this, but one additional thought is I've seen index versions change more frequently than app versions. so I'd version control even index revisions regardless of the app code version they were initially deployed with. –  Eric Higgins Mar 2 '12 at 16:53
1  
And do not allow any one ever to promote a change to Prod without a source Controlled script. Never under any circumstances make a table or toher opbject chanage using te GUI, all changes must be by script. –  HLGEM Mar 2 '12 at 19:05
    
+1 For giving the competition first. Also, I really like SQL Source Control. –  user4742 Mar 2 '12 at 19:55
    
While scripting out your database objects, don't forget to script out (sp_help_revlogin) Windows and SQL logins, and your SQL Agent jobs if for some reason you have to rebuild a database server completely. –  jl01 Mar 2 '12 at 22:28
1  
@EricHiggins Yes, if there are database changes independent of the code changes, you can deploy them, no argument. You should still be versioning and deploying them in conjunction with the app code, using the app's published methods (whatever they might be) for patches & hotfixes. You can still coordinate and lable the deployments so that you know what's deployed and what isn't. –  Grant Fritchey Mar 5 '12 at 13:42
  1. We maintain database scripts as part of our application codebase which is maintained under version control. However we use different "processes" for development and production code

  2. Development we maintain the following scripts:

    • base.sql - creates the schema tables and sample data
    • stagingchanges.sql - makes changes to the base.sql for staging environment, mainly email addresses, paths and other assets which might change
    • prodchanges.sql - makes changes to the base.sql for a production deployment. For new projects we usually get to test these out in actual production environments
  3. Maintenance

    • base.sql - a sanitized version of the production database
    • stagingchanges.sql and prodchanges.sql - as above
    • changerequest.sql (usually has the id of the change request) which applies any schema changes for the current change request we are working on
    • changerequest-rollback.sql - reverses the changes made for the change request and resets the database back to production
    • archive (folder) for previous change request scripts

So in maintenance mode all we need to do is apply the changerequest.sql script during deployment to production

share|improve this answer
    
How do you track changes to a base object (e.g. table, procedure or index) using this methodology? I may be missing something from your description of the process but it looks like your versioning the change script, not the artefact that's subject to a change. –  Mark Storey-Smith Mar 2 '12 at 17:43
    
Our needs call for versioning of the change scripts from base.sql (which is the current production version). If you require more fine grained control, instead of our single base.sql you can dump the definitions of the tables, procedures, and functions into individual files and track the changes in the files. In this case, you will always be able to track the changes at a more granular level. However you will only be able to track the the production versions, since the change script is work in progress until deployed to production. –  ssmusoke Mar 2 '12 at 17:59
    
I've always stored per artefact definitions, each in an individual file. If I want to see a production version, I pull the label or branch that's currently in production. If I want to see the UAT version, I pull the label or branch that's in UAT. If I want a change script to go from version X to version Y, I run a differential on the two labels or branches. I don't see how your process would be preferable to this. Not intending to be confrontational, just wondering how you've arrived at this process. –  Mark Storey-Smith Mar 2 '12 at 18:17
    
We have not yet had the need to separate the scripts into per artifact scripts, especially during development, so we have stayed with it. The artifact per file is the next step in our teams evolution, but is currently overkill for our needs. –  ssmusoke Mar 2 '12 at 18:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.