I m trying to figure out the pros and cons of using multiple environments on one physical server or a SQL instance per environment. So, heres the situation:
For our testing we have like 5 logical environments and the code progression is as follows Initial Integration(II) -> Integration(I) -> System Testing(ST) -> Security(y) -> Performance(P). For each environment I will have the a group of databases (say about 4 - DB1, DB2, DB3, DB4), and these such 4 will exist per logical environment.
I am trying to think of pros and cons of having ONE sql Instance and have multiple db's (so it will be like IIDB1, IIDB2, IIDB3, IIDB4, IDB1, IDB2 and so on) OR have one sql instance per environment, so technically this will be (server\InitialIntegration, server\Integration and so on and each will have DB1, DB2, DB3 and DB4)
So basically ONE instance and have multiple db's or per instance per logical environment.
One pro I can think of if its multiple instances is, I can bring one instance down and not affect others, but what about performance if I had multiple SQL instances, memor pressure etc?
One pro with ONE SQL Instance is, I can move data, code etc between db's easy than between instances.
I would like other thoughts.
Also, mind you, this is ONE physical server in SQL 2012.