Re: database version control question

Nhuan_at_Lieu_NoSpam.org <Nhuan_at_Lieu.org> wrote:
>Hi, I am relatively new DBA, in your opinion what is your
 suggestion in
>handling version controlling database changes from
>Development->QA->Production in an environment where the several
>developing teams have multiple and/or independent database
 change
>requests to the _same_ database at _different_ timeframe. Below
 is a
>typical example:
>DEV_TEAM1 have Table1 (increase a column size) &
 StoreProcedure1 update
>request and change priority is Important (not Emergency). This
 request
>to move into Production in 2 weeks. However, before the above
 change
>took place, DEV_TEAM2 request an "unrelated" functional change
 Emergency
>request to update Table1 (add an NULLable column) &
 StoreProcedure2.
>This second is to move into Production in 1 week. To summarize,
 Request
>2 comes to DBA after Request 1 but need to move into Production
 sooner
>than Request 1 with one common & unrelated table change. To
 make it even
>worst, we have 7 such outstanding and similiar requests right
 now on the
>queue and I need to way to keep track of these changes as to
 minimuize
>impact.
>Any systematic way to help to manage this chaos is highly
 appreciated
>and would like hear from you on your person experience how you
 did your
>change management.

At a first glance, I think I would allow analysts and programmers to create their own non-dbo tables and stored procs in Development. SQL in stored procs must refer to unprefixed tables. Then, the stored proc will access to the programmer's personal table, if one exists; else, to the dbo table.

But, in Production (and probably QA) I'd keep only the dbo version. To apply changes to a table, I'd ask for an evaluation period which allows DBA's to analyze the change requests, merge changes being applyed to the same table and obtain feedback from analysts about their tolerance to these merged changes. Then alter QA or Production tables, along with the dbo table in Development.

If a programmer feels he or she is using an outdated personal table, he/she may resync (with loss of data) by droping this personal table and re-creating the depending stored procs. Automatically, his/her applications will refer to the dbo table instead of the personal table. The dbo table is supposed to have the latest definition made in Production / QA.

If the programmer prefers to create a new table based on the actual Production table plus some changes, he/she should have easy access to the current Production DDL instructions (or to a reverse engineering tool capable of providing them).

Regards,
Mariano Corral

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!