Should test and prod use different schema names? 2

dba · Jan 19, 2000

I need some advice. Using the same schema names for test, acceptance and production would probably ease migration chores, at the risk of disaster if you slip up and do something in production when you meant to be signed onto test. 
 
However, using different schema names means changing all references, such as trigger "before update of ....", view SQL, etc. 
 
Any opinions? Other risks? Thanks.

carp · Jan 19, 2000

We normally see separate instances for dev/test/prod with the same schema names used across instances (for just the reason you sited - it makes migration a LOT easier!). However, there IS always the possibility of not being connected to the instance you THINK you are; I have a friend who could have SWORN he was connected to the dev instance when he dropped that table! 
 
One possible means of minimizing this risk is to have a different prompt for each instance. That way, when you are connected to PROD, you will see 
PROD> 
for your prompt (of course, this assumes a character-based interface!). 
 
Using different schema names would be murderous during migration (IMHO). One way to ease the process if you go this route would be to use public synonyms for all of your objects. Then you only need to redefine the synonyms after you port the objects over and your code should work fine. But it's still ugly!

MikeLacey · Jan 20, 2000

dba and carp, 
 
I think it depends on the number of developers you have - and on any procedures you have in place for object creation. 
 
I've just finished working on a project where the process worked like this: 
 
Two main schemas - dev and test - in the development and test database. Each developer has a private schema in the development and test database. One schema in the production database. 
 
Each developer had private synonyms to each dev object in the development and test database. No developer had access to the production database. 
 
1) A developer creates an object - or a new version of an existing object - in his/her own schema. Drops the synonym pointing at the dev object of the same name. 
 
2) When the developer is finished - the team leader imports those objects into his/her own private schema and tests. 
 
3) When the team leader is finished - the dba imports those objects into the test schema. 
 
All DDL scripts to change the database were saved as the work was developed. As objects were transferred to test these scripts were added to a "changes" script. 
 
Periodically a new release of the s/w ws made. An export of the test schema (not all the tables) and the changes script was used as the basis for an automated install procedure that was applied to each production database. 
 
All this probably seems just a tad complicated. But the development team supported thousands of users and a hundred or so production databases. Very few emergency fixes to production databases - users moaning about new features they wanted rather than about errors in the application. 
 
This was the biggest development team I've been the lead dba on and it changed the way I think about change control and the development process. 
 
The admin work for such a process is a pain and was difficult for some programmers to get used to but, once a few releases of the s/w had been made, the advantages were obvious to everyone and the complaints died away. 
 
Mike Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a>

carp · Jan 20, 2000

Mike - 
I like this a lot! Especially since it supports something I've always tried to pass on to new DBAs: critical code should never be in a user's schema (after it passes testing and goes to production). I've always endorsed a "generic" schema to hold the critical objects, and the DBA and security manager (if different than DBA) were the only people w/ the passwords. As an added layer of security, connect privileges could be revoked from the schema to preclude unauthorized connections.

dba · Jan 20, 2000

Mike and carp; 
 
Thanks for your replies. When you speak of a test or production schema, are you using one schema for all test tables, and one for all production tables? We did that on the mainframe and it worked well, but I'm not sure I want to deal with such long lists of objects with the tools I have in the Oracle world. 
 
Also, I am wondering what is the best way to change internal text of objects like triggers and views when migrating to different schemas. We are hoping to be able to use unqualified table names in such cases, as our change tool cannot handle making that sort of text change. I don't know enough yet to know if we are going to get into trouble doing that. Are you relying on manually maintained and edited scripts? 
 
 
Mike, when you have programmers changing synonyms to point to their own versions of objects, do these tables have RI on them? If seems that would be difficult to handle? 
 
We are planning to not allow programmers to create objects, accepting the task overhead up front in the DBA group to attempt uniformity of names, triggers, etc.. Are we being too mean? 
 
Thanks again for your help. Please forgive me in advance if I pile on too many questions. 
 
---- John Weil 
---- Crowley Maritime Corp.

MikeLacey · Jan 21, 2000

John. Don't worry about asking questions - that's pretty much the idea. I'll get my own back another day by asking something myself.... 
 
The two schemas, dev and test. Both these schemas reside on the Development and Test database. 
 
dev - contains completed development work. 
 
test - contains the next version of the application that will be released, it is generated by taking a copy of a production database and running the release routine on it (the Changes sql script etc.) 
 
Developers each have their own private schema and a private synonym for each object in it - so that that developer (and only that one) picks up the object from the private schema. 
 
The changes scripts are generated by the programmers (with help from DBA's) when applying their changes to the dev schema for testing by their team leader. 
 
Unqualifed names - presume you mean "products" rayher than "dev.products" - yes applications should not refer to schema names - if they do it breaks the whole thing. 
 
Change tool <smile> we wrote our own... Was written before I started - horrible inelegant thing written over several years that worked perfectly. Trick is to apply change scripts (written by your programmers with assistance from you) in the right order. You can do this manually and, if I had to develop the tool to do it automatically that's what I would do for a while, so that I understood exactly what i needed to do. 
 
Some programmers (IMVHO) are quite capable of creating their own create/update/install scripts and others - not. You know best here I think. In any case the point at which they create objects in their private schemas is when you need to be involved as after that the design will be set in stone. 
 
All this more than a bit time consuming. It needs time to set up and time to administer. Time=Management Commitment here, time pressures will kill the process quite quickly unless every suggestion to circumvent the change control process (due to lack of time, urgency) is met with a certain firmness. 
 
Regards 
 
Mike Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a>

dba · Jan 27, 2000

Mike, 
 
Thanks again. Hopefully there will be a migration tool to buy when the need gets bigger. -- John

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Should test and prod use different schema names? 2

dba

MIS

carp

MIS

MikeLacey

MIS

carp

MIS

dba

MIS

MikeLacey

MIS

dba

MIS

Similar threads

Part and Inventory Search

Sponsor