Multiple database versions on same server.

We have a environment where we have one development server, one stage
and one prod. On dev and stage we need to have multiple versions of
the database to be able to do hot fixes for previous versions of the
code at the same time as we develop the new version.
Right now we just have different names for the database, for example
OURAPP1.1 OURAPP1.2 and so on. The problem with this is that we also
have other databases that follows the same naming, for example
OURAPP_Security1.1 and so on. Sometimes we have to make queries
between these databases. This creates a problem because we can't
reference for example a table in the other database from a stored
procedure because the database in our production environment always
have the same name OURAPP and OURAPP_Security.
Well my question is really what the best strategy to handle this is.
My only idea is to create different instances for each version instead
and then always have the same name for the databases. Is this the
normal way to handle this? Anyone faced the same problem?
On Apr 17, 12:35 pm, stuck...@.gmail.com wrote:
> We have a environment where we have one development server, one stage
> and one prod. On dev and stage we need to have multiple versions of
> the database to be able to do hot fixes for previous versions of the
> code at the same time as we develop the new version.
> Right now we just have different names for the database, for example
> OURAPP1.1 OURAPP1.2 and so on. The problem with this is that we also
> have other databases that follows the same naming, for example
> OURAPP_Security1.1 and so on. Sometimes we have to make queries
> between these databases. This creates a problem because we can't
> reference for example a table in the other database from a stored
> procedure because the database in our production environment always
> have the same name OURAPP and OURAPP_Security.
> Well my question is really what the best strategy to handle this is.
> My only idea is to create different instances for each version instead
> and then always have the same name for the databases. Is this the
> normal way to handle this? Anyone faced the same problem?
You may need to change the SP with appropriate databasenames with
replace all for both dev and staging SPs.
Another option is to keep a table in dev and staging and store databse
name in that table. Write a dynamic query which picks up database name
from this table name
Caution: Dynamic Query may run slow and also prone to sql injection.
I don't think you can use synonym ( are you on SQL 2005 ?)