Post reply

Multiple environment DB's on one instance or one instance per environment on a physical server

  • February 20, 2013 at 10:04 pm

    #274456

    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.

  • February 21, 2013 at 3:13 am

    #1589863

    Well, you need to review a lot of things and also business decisions needs to be kept in mind. Few Pointers:

    1. One bad database can cause issue with the entire instance impacting all other DBs as well, so each DB should be maintained very intelligently.

    2. Security policies should be strongly setup on single instance as there would be different set of users needing accesses to perform different activities on the databases.

    3. Easy to manage a Single Instance then multiple instances.

    4. The server configuration (sp_configure) should be configured keeping in mind all other DBs/applications pointing to this instance.

    5. TempDB should be configured to make sure it serves the requirement for all DBs on the single instance.

    6. Maintenance jobs should be created/scheduled keeping in mind the entire server configurations.

    There could be more other experts might suggest, but these things atleast should be kept in mind when deciding.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply