development and implementation procedures....

Hello everyone,

I am looking for some help.

I am responsible for five SQL2000 database servers. One is production, two for development, one for testing and one for development and testing of a sub-product. We also use Source safe. Although sometimes, depending on the activity going on on each of the servers, the roles of development and testing servers get interchanged.

The problem I face as a DBA is to co-ordinate with all the developers and the people testing the applications and databases. Stored procedures, triggers and schema changes are not consistent across the same database on different servers. Multiple copies of databases get created on the same server. This happens primarily because, once development has been completed, the database is moved to the testing servers. As the testing progresses and issues are resolved, the scripts are changed, etc. As there are many developers and people testing, it is just impossible to keep track of who is doing what and where the latest source code is. And especially creates a problem during implementation. Some of you may relate to this problem.

I am new in this position and would like to put a process in place where things are better organized.

If some amongst you would like to share your thoughts and experiences or what you have done at your place of work to make your life easier, I would highly appreciate it. Also, you could direct me to places where I can get some information on this subject.

Thanks in advance and apologies for this rather lengthy question.I would say make sure you are the only admin or dbcreator on the testing server (and naturally the production server).

Once you are assured of your Oz-like powers, declare that the testing server is going to be made in the image of the production server. Ideally this should happen when no one is actually testing anything.

Once you have done this, demand of the programmers that they submit scripts for their changes. These will normally consist of a few alter or create table statements, and alter/create procedure statements. It is very rare to see a large number of tables being dropped with short shrift in the course of an internal development project.

The testing server databases can be replaced by copies of the production server at any time (in some places quarterly, but more usually by request before testing a major update). Datebases are never moved from testing to production.

Only after the testing is complete, and you are assured the upgrade can be done with no errors on production, then you can run those same scripts you ran on testing before on production.

Any old-hand programmers will probably take some offense at losing privileges on testing or production, but the benefits should outweigh the complaints. Hope this helps.