frederico_fonseca (10/20/2016)

---

what we do at my shop is as follows.

All databases on DEV/UAT/FIT are named as follows

XXX_YYY_999_databasename

where XXX is one of DEV/UAT/FIT

YYY is the developer code/name

999 is the project being worked on

All block databases reference the others when required through the use of synonyms (which is also done on PROD)

Some issues (solved)

We have several type of databases - some are standalone, others work in blocks.

Standalone pose no problem - each developer gets its own.

block ones we have 2 situations

1 - One or more of the databases are static from the development point of view

this would be for example databases that are from other teams and which are not changed by a particular team.

In this case a single copy is shared by all developers, and synonyms target those commonly

2 - all databases are dynamic and changeable for each developer

In all the situations above we have sets of scripts that are called passing the desired database names, which on block databases could be several of them, and which create/restore the desired databases and apply the required synonyms.

As an example if one of our teams was working on the SAP project 3 databases would be created with following command

create_sap -Env DEV -User Frederico -Project 925

As would also be expected from the above setup, everything that accesses these databases, being it command line applications, web interfaces, SSIS or others do use configuration files that contain the desired names.

Some have to be changed by the developers as they work through the code, others may be done automatically by selecting which database to work with on startup of the application (this is the case for some web apps where the users (non prod) enter the database they wish to work on).

All development is done in branches, and goes through several stages until it is ready to go to production - once it reaches UAT all build is done automatically and gets promoted to the next level once the corresponding projects are signed off for next stage.