Managing the merge of DBA's production changes/tweaks with dev's pending DB changescripts.

Question

We maintain a set of change scripts that must be run on the DB when our web application is released. We waste a lot of time and experience some difficultly keeping these updated however, our DBA likes to (rightly) tweak stored procedures and schemas on the live system to maintain system performance.

Every so often we have to rebase our patches to the current schema and stored procedures, however, it is extremely difficult to detect which changes might conflict and work out which of our DBAs' changes we might be clobbering.

How do others manage the need for changes on live DBs against pending changes?

What processes can we put in place to make this process more smooth?

What is the best way to store, manage our schema and apply our/his changesets?

Thanks in advance.

Answer 1

DBAs should not ever tweak procs on prod only. They should also use source control and put the changes onthe other environments so that others making changes are aware of them.

Answer 2

Make any and all DDL changes to the DB schema script based and store then in your source code control. Especially changes your DBA makes - I would suggest getting your base schema and stored procs examined by a db developer and the DBA prior to checking them into your source code control (props to HLGEM for saying it). Moves into prod should be scripted and approved prior to application (ie, if the DBA finds stuff that needs to be changed, have the DBA open a defect and handle via that process).

Lock all such DDL changes away from your developers. The smart guys writing Java and C# should be communicating with your team db "specialist" on how to best accomplish the design goals and needs on the db side.

Limit production tweaks to those highly situation dependent cases, for example, many IT shops have a DBA who will define the physical storage setup based on db deployment scripts your app supplies and this is usually Ok. A wizard with your app to help less experienced people along with a top 10 list of recommendations for setup and basic tuning will go a long way.